Can I speed up database access using CRecordset with ODBC to SQL server?

[vfoos]

Hello everybody,

I have written a C++ command line program that does calculations and at the end, writes the results to an MS-SQL server database using CRecordset classes and ODBC connection. The problem is when I execute the program multiple times on one computer, so that several processes intend to write results to the database at the same time, some processes fail to write all data and do not proceed until the end (get stuck). I guess to solve this issue I have to speed up the database access. What I experienced that the opening and closing of CRecordset objects takes some significant time.

A typical code section looks like that:

m_CRecordset_tbl.Open();

for (int dot=0; dot<1000; dot++)
{

m_CRecordset_tbl.m_pDatabase->ExecuteSQL("SQL command...");

}

m_CRecordset_tbl.Close();


Does anyone know a solution to make this approach (especially the opening and closing of CRecordset tables) faster? Thanks a lot in advance!!!

[kbw]

It doesn't sound like a speed issue. It may be a table locking/transaction issue on the database. Run up SQL Server's Profiler to see what's going on.

[vfoos]

Thanks a lot, I have to get into using the profiler as I have never done that before. Beside that, do you know a way how to open CRecordset objects (tables) in a faster way (maybe by handing certain parameters to the open() function). I opened the recordsets as "dynaset". It looks like it takes too long as the database has filled up with data over the time and the problems occurred only now.

Many thanks...

[kbw]

I recently discovered SQLAPI++, a C++ library for encapsulating database access across all the popular database, including SQL Server. It may be worth taking a look, all this stuff may already be sorted there.

I would have thought that you're loop should be more like:

for (int dot=0; dot < 1000; ++dot)
{
    m_CRecordset_tbl.Open();
    m_CRecordset_tbl.m_pDatabase->ExecuteSQL("SQL command...");
    m_CRecordset_tbl.Close();
}

If you really need performance, maybe you should consider ADO/BulkInserts.

[vfoos]

Thanks, I will have a look in ADO/BulkInserts.

I am opening and closing the m_CRecordset_tbl objects outside the loop as opening and closing takes most of the time.

Thanks for your help...

Volker