Database

1. Read the question carefully
2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar
3. If a question is poorly phrased then either ask for clarification, ignore it, or mark it down. Insults are not welcome
4. If the question is inappropriate then click the 'vote to remove message' button

1. Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
   
   
2. Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
   
   
3. Keep the subject line brief, but descriptive. eg "File Serialization problem"
   
   
4. Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
   
   
5. Be careful when including code that you haven't made a typo. Typing mistakes can become the focal point instead of the actual question you asked.
   
   
6. Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
   
   
7. If you are posting source code with your question, place it inside <pre></pre> tags. We advise you also check the "Encode "<" (and other HTML) characters when pasting" checkbox before pasting anything inside the PRE block, and make sure "Use HTML in this post" check box is checked.
   
   
8. Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
   
   
9. Please do not post links to your question into an unrelated forum such as the lounge. It will be deleted. Likewise, do not post the same question in more than one forum.
   
   
10. Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
    
    
11. If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
    
    
12. No advertising or soliciting.
    
    
13. We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.

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

BEGIN TRANSACTION
    
 SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE ID=6822

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.

#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)

      // Column m_nID is the 1st field in the table.
      ADO_VARIABLE_LENGTH_ENTRY2(1, adInteger, m_nID, sizeof(m_nID), m_IDStatus, FALSE)

      // Column m_bCritical is the 2nd field in the table.
      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;
  // open recordset
//_RecordsetPtr  pRs = NULL;
//pRst.CreateInstance(__uuidof(Recordset));

  _RecordsetPtr pRs("ADODB.Recordset");
  CMyRecordSet rs;
  IADORecordBindingPtr picRs(pRs);
  hr = pRs->Open("TTagData",
                 _variant_t((IDispatch*)m_pConnectionPtr, true),
                  adOpenKeyset, adLockOptimistic, adCmdTable);
  //Bind the Recordset to a C++ Class here.
  hr = picRs->BindToRecordset(&rs);
  // fill in data
  rs.m_value = i;
  // add new record to the table
  picRs->AddNew(&rs);
  // picRs->Release(); tried this but no result

  pRs->Close();
}

inline HRESULT Recordset15::Close ( ) {
    HRESULT _hr = raw_Close();
    if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
    return _hr;
}