|
Have you tried adding "LIMIT 1" to the query constraint?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles
|
|
|
|
|
create table PendingQueue (
id int not null,
DueTime datetime not null,
Payload varbinary(max),
cnstraint pk_pending_id nonclustered primary key(id));
create clustered index cdxPendingQueue on PendingQueue (DueTime);
go
create procedure usp_enqueuePending
@dueTime datetime,
@payload varbinary(max)
as
set nocount on;
insert into PendingQueue (DueTime, Payload)
values (@dueTime, @payload);
go
create procedure usp_dequeuePending
@batchsize int = 100,
@retryseconds int = 600
as
set nocount on;
declare @now datetime;
set @now = getutcdate();
with cte as (
select top(@batchsize)
id,
DueTime,
Payload
from PendingQueue with (rowlock, readpast)
where DueTime < @now
order by DueTime)
update cte
set DueTime = dateadd(seconds, @retryseconds, DueTime)
output deleted.Payload, deleted.id;
go
Specially see this line
set DueTime = dateadd(seconds, @retryseconds, DueTime)
How this line instruct sql server to retry the update data again after 10 minute if fail to update data first time. this is not clear to me. please some one explain if you understand properly. thanks
|
|
|
|
|
It doesn't. It simply updates the DueTime column in the PendingQueue table, and returns the Payload and id columns of the affected rows.
There must be some other code which processes and removes the record from the PendingQueue table which you haven't shown.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I have some confusion about lock (ROWLOCK ,UPDLock AND xlock). i want to know the difference among these locks. where to use UPDLock, when to use RowLock and when Xlock with a example for better clarification.
1)
BEGIN TRANSACTION
SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE ID=6822
In the above sql rowlock and XLOCK both use as a result from other session records 6822 could not be read or modify. XLOCK alone is capable to lock the rows....so why one should use ROWLOCK & XLOCK together ?
if i use only xlock & HOLDLOCK then it will not serve the purpose ?
2) Tell me with example what is the difference between ROWLOCK & UPDLOCK ?
ROWLOCK prevent other session to modify data and UPDLOCK does the same thing. so what is the difference
between ROWLOCK & UPDLOCK ?
Please anyone explain these difference with example as a result at my end i can run the example code and understand.
Thanks
|
|
|
|
|
|
Sir Thanks for your reply & link.
i read about ROWLOCK & UPDLock but still not clear. they wrote very briefly. if possible sir please discuss the difference between ROWLOCK & UPDLock hint with example.
Thanks
|
|
|
|
|
Mou_kol wrote: i read about ROWLOCK & UPDLock but still not clear MSDN pretty clear.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I was looking for an efficient way to store "States" of entities in the DB. For example I had OrderStatus and that can be mapped to a table called Status and have the ID and the status Name be present in a join. If however I wanted to add more statuses for other Entity tables I don't want to have to create a new table for every Entity, is there a way to store all these statuses in one table? Thanks
|
|
|
|
|
|
Richard Deeming wrote: It's a bad idea.
I , the last project I was asked to advise on was managed by a PM who was adamant that this was the "modern" way to go (I was accused of being an old fart who was stuck in the 90's). Started off as a 2 column table and the last I saw of it was a 5 column, 2 table structure.
Luckily I walked away from anything to do with the project.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I take this point, I restructured my schema to just have 1 table per type for the time being, I was just trying to have less tables and then ran into this anti-pattern. Thanks for the input.
|
|
|
|
|
I need help resetting the values of each users in a row at once with random numbers by either button click or loading a page.
Just like a reset page or button to assign random numbers to each users without repeating at once.
|
|
|
|
|
You most likely need to use a set of UPDATE statements in order to use different random values.
|
|
|
|
|
I need a real-time database for inserting at least 500fields per second. (I have been selected TimeScaleDB (Postgresql Extension) for my purpose, currently). So, the matrix of the table will be 500(Fields)x86400(Rows).
This means that for a 500 double-precision field it will be around 500*8bytes=4000bytes which for 86400rows it will be 345,600,000bytes (345.6M).
I know a software which it is recording the same structure of data and the final daily volume of its database is only 84M~85M. Its Extension is *.tag so, i think it is a DataFlex database. It is not a free database and i also never used it.
Question:
1- Which free database is the best to use for real-time data storing and querying (at the moment of storing) based on your experience or knowledge?
2- Is there any database to have such a volume or even less for this size of data?
Thank you for your Help.
|
|
|
|
|
|
But Its Volume Will Be The Same As PostgreSQL!
|
|
|
|
|
Why are you concerned about sizes? Disk space is cheap.
|
|
|
|
|
For one year the size will be 120GB for Postgresql but for the mentioned DBS will be only 30GB. it is a great difference (one fourth).
|
|
|
|
|
That would mean the other database is storing the data compressed.
That's obviously something you can do in many databases, but it might add performance issues.
Wrong is evil and must be defeated. - Jeff Ello
Never stop dreaming - Freddie Kruger
|
|
|
|
|
|
What is wrong with MySQL[^]
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
|
There are various ways to "compress" (and structure) redundant database data. "Size" doesn't tell much of a story.
And you don't generally "query" real-time data; plot it, maybe; record it, certainly; usually to a binary file that later gets translated and loaded (to a database) for information purposes.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Member 13325846 wrote: 1- Which free database is the best to use for real-time data storing and querying (at the moment of storing) based on your experience or knowledge?
SQLite, in memory database storage would be my choice. Stores db in memory, instead of disk.
SQL Server has the same option.
..but both not real time, just really, really fast. Anything on Windows is not guaranteed real time, by technolgy. If you need real time, reseach QNX or the likes.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hi,
when writing in Access database using ADO and record binding, I run into an exception after some time.
I wrote a small console program to demonstrate the problem :
#include <Windows.h>
#include <iostream>
#import "c:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF", "EndOfFile")
#include "icrsint.h"
_COM_SMARTPTR_TYPEDEF(IADORecordBinding, __uuidof(IADORecordBinding));
void OpenDatabase();
void WriteToDatabase(int i);
void CloseDatabase();
_ConnectionPtr m_pConnectionPtr = 0;
DWORD64 cnt_recordset;
int main()
{
HRESULT hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);
m_pConnectionPtr = NULL;
m_pConnectionPtr.CreateInstance(__uuidof(Connection));
int cnt = 0;
cnt_recordset = 0;
OpenDatabase();
while (1)
{
std::cout << cnt++ << " : " << cnt_recordset << std::endl;
for (int i = 0; i < 1000; i++)
WriteToDatabase(i);
}
CloseDatabase();
CoUninitialize();
}
void OpenDatabase()
{
bstr_t strCnn("Provider=MSDASQL;DSN=TestDB;User ID=sa;");
m_pConnectionPtr->Open(strCnn, "", "", NULL);
}
void CloseDatabase()
{
if ((m_pConnectionPtr->State == adStateOpen))
m_pConnectionPtr->Close();
}
void WriteToDatabase(int i)
{
cnt_recordset++;
class CMyRecordSet : public CADORecordBinding
{
BEGIN_ADO_BINDING(CMyRecordSet)
ADO_VARIABLE_LENGTH_ENTRY2(1, adInteger, m_nID, sizeof(m_nID), m_IDStatus, FALSE)
ADO_FIXED_LENGTH_ENTRY(2, adInteger, m_value, m_valueStatus, TRUE)
END_ADO_BINDING()
public:
int m_nID;
int m_value;
ULONG m_IDStatus;
ULONG m_valueStatus;
};
HRESULT hr = true;
_RecordsetPtr pRs("ADODB.Recordset");
CMyRecordSet rs;
IADORecordBindingPtr picRs(pRs);
hr = pRs->Open("TTagData",
_variant_t((IDispatch*)m_pConnectionPtr, true),
adOpenKeyset, adLockOptimistic, adCmdTable);
hr = picRs->BindToRecordset(&rs);
rs.m_value = i;
picRs->AddNew(&rs);
pRs->Close();
}
The exception occurs on closing the recordset after the AddNew :
inline HRESULT Recordset15::Close ( ) {
HRESULT _hr = raw_Close();
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
return _hr;
}
Exception number is 0x800a0c93
This happens after a couple of hundreds of thousends writes.
Am I missing something?
Any help is appreciated very much!
|
|
|
|