Hi Guys,
I need help in one fine tuning problem. I have developed one Datalayer that it have more implementations depedending on Database Server.
Our older implementation it is based on the db Library from Sql Server. And this implememtation we have replace it with an OLE DB ATL Consumer Templates implementation.
After comparing the performance of course and after we saw that the OLE DB ATL implementation faster is, we said it is a good decision. The compare i have made it with Microsoft SQL Server 2000 for both implementations.
But I forgot to test one statement and this was the Stored procedure exec statement.
All the other statement were faster with ATL OLE DB in compare with DB Library but the ExecStoredProcedure not, and I mean select, delete, update, insert and BCP insert are faster.
After making research why the OLE DB exec Stored Procedure call it is not faster I found the reason for: the OLE DB it is asking the SQL Server before running the store procedure the parameter structure( info like name, type, size etc ..).
If the stored procedure call it is fast (very fast - by example if i read only one row from one table based on a cluster index) then the overload for asking the parameter information it is too big in compare with db Library run time who it is not asking such information.
Lets say I have a stored procedure who it is reading from the database one book with a specific autoid.
So I would expect to run on a SQL Server side this statement:
exec di_sp_di_book_autoid @.autoid = 452
but starting the SQL Profiler I saw that instead of one statement the OLE DB it is sending 2 statements:
exec [demo_test]..sp_procedure_params_rowset N'di_sp_di_book_autoid', 1, NULL, NULL
exec di_sp_di_book_autoid @.autoid = 452,
First statement it is reading the information about the input and output parameters like type size and others... and after it runs the stored procedure only.
The ATL OLE DB it have a lot of properties that somebody can used them to control the behavior of different OLE DB Classes.
I've tried to find one property ( in OLEDB named Parameter) to deactivate this first call to win time and performance of course maybe loosind some error tolerance.
Here I attach the example code as Visual C++ Project example in Visual Studio 2003:
I describe how you can create such a project:
Create one Sample Project with just one Windows Dialog,
One this Dialog make 2 Buttons. In the first button event handler write this connection code:
void CSPTestDlg::OnButtonConnect()
{
USES_CONVERSION;
CWaitCursor waitCursor;
if (m_pDataSource = new CDataSource)
{
CString sServer = "DIKON_NT_005\\SQL2K";
CString sDatabase = "demo_oledb";
CString sUserId = "sqluser1";
CString sPassword = "***";
CDBPropSet dbinit[2] = {DBPROPSET_DBINIT, DBPROPSET_DATASOURCEINFO};
dbinit[0].AddProperty(DBPROP_INIT_DATASOURCE, sServer);
dbinit[0].AddProperty(DBPROP_INIT_CATALOG, sDatabase);
dbinit[0].AddProperty(DBPROP_AUTH_USERID, sUserId);
dbinit[0].AddProperty(DBPROP_AUTH_PASSWORD, sPassword);
dbinit[0].AddProperty(DBPROP_INIT_LCID, (long) 2057);
dbinit[0].AddProperty(DBPROP_INIT_PROMPT, (short) 4);
if (m_pDataSource->Open(_T("SQLOLEDB.1"), dbinit, 2) == S_OK)
{
_variant_t v;
HRESULT hr = m_pDataSource->GetProperty(DBPROPSET_DATASOURCEINFO, DBPROP_MULTIPLERESULTS, &v);
AtlTraceErrorRecords(hr);
if (m_pSession = new CSession)
{
if (m_pSession->Open(*m_pDataSource) == S_OK)
{
GetDlgItem(IDC_BUTTON_CONNECT)->EnableWindow(FALSE);
GetDlgItem(IDC_BUTTON_DISCONNECT)->EnableWindow(TRUE);
GetDlgItem(IDC_BUTTON_EXEC)->EnableWindow(TRUE);
return;
}
else {
MessageBox("Cannot open session!", NULL, MB_OK | MB_ICONERROR);
}
}
}
else {
CString sMessage;
sMessage.Format("Failed to connect to server '%s', database '%s'...", sServer, sDatabase);
MessageBox(sMessage, NULL, MB_OK | MB_ICONERROR);
}
}
delete m_pSession;
m_pSession = NULL;
delete m_pDataSource;
m_pDataSource = NULL;
return;
}
In the second button handler pls copy this code:
void CSPTestDlg::OnButtonExec()
{
DWORD dwStart = GetTickCount();
DWORD dwSum[3] = {0};
for (int nCount = 0; nCount < 10000; nCount++) {
DoTestManualAccessor();
}
DWORD dwEnd = GetTickCount();
DWORD dwTime = dwEnd - dwStart;
CString sMessage;
sMessage.Format("Time: %.3lf sec", dwTime / 1000.0);
MessageBox(sMessage);
return;
}
Here come in plus the code that truns in the second button :
void CSPTestDlg::DoTestManualAccessor()
{
CString sqlStatement;
sqlStatement = "{call di_sp_di_book_autoid(?)}";
CCommand<CManualAccessor, CRowset, CMultipleResults> cmd;
VARIANT v[nParameters];
for (int n = 0; n < nParameters; n++) {
VariantInit(& v[ n ]);
}
v[nParameters-1].vt = VT_I4;
v[nParameters-1].lVal = 15;
/*
CDBPropSet dbinit(DBPROPSET_ROWSET);
dbinit.AddProperty(DBPROP_SERVERCURSOR, true);
dbinit.AddProperty(DBPROP_IRowsetChange, false);
*/
HRESULT hr;
hr = cmd.Create(*m_pSession, sqlStatement);
//hr = cmd.Prepare();
hr = cmd.CreateParameterAccessor(nParameters, (void*) 0x01, 0);
char c_buf[4096] = {NULL};
wchar_t w_buf[4096] = {NULL};
cmd.AddParameterEntry(1, DBTYPE_VARIANT, sizeof(_variant_t), &v[0], NULL, NULL, DBPARAMIO_INPUT);
//(CDBPropSet*)&dbinit
//hr = cmd.Open((CDBPropSet*)&dbinit, NULL, false);
hr = cmd.Open(NULL, NULL,false);
AtlTraceErrorRecords(hr);
if (cmd.m_spRowset) {
cmd.CreateAccessor(3, (void*) 0xfefefefe, 0);
long id = 0;
cmd.AddBindEntry(1, DBTYPE_I4, sizeof(id), &id, NULL, NULL);
long ref = 0;
cmd.AddBindEntry(2, DBTYPE_I4, sizeof(ref), &ref, NULL, NULL);
char name[256] = {NULL};
cmd.AddBindEntry(3, DBTYPE_STR, sizeof(name), &name, NULL, NULL);
hr = cmd.Bind();
do
{
hr = cmd.MoveFirst();
while (hr == S_OK)
{
hr = cmd.MoveNext();
}
long out;
hr = cmd.GetNextResult(&out);
}
while (hr == S_OK);
}
for (int n = 0; n < nParameters; n++) {
VariantClear(& v[ n ]);
}
}
In the header file you must declare this 2 members:
CDataSource* m_pDataSource;
CSession* m_pSession;
In the Header File where the Dialog declared is you must include this fiels too:
#include "atldbcli.h"
#include "comutil.h"
#pragma comment(lib, "comsupp.lib")
Now the Project must run.First correct the login information of course for the connection on your MS SQL Server 2000.
Who wants to get the complete project pls give me your email adress and I will send you the complete Project.
Some comments about this very simple example are: I was commenting the Prepare call because instead of making the call faster it was making the call slower.
And I was trying to set with help of the class CDBPropSet some propertis to make the call faster.The same problem no succes with all porperties I have tried.
The question is it: is my code ok ?
Somebody knows any optimization that i can use ?
It is possible to deactivate this call for asking the parameter info that ATL OLE DB it is making automatically ?
Hi Vasile,
Try binding the parameter as something other than DBTYPE_VARIANT, e.g.
int a1 = 2;
cmd.AddParameterEntry(1, DBTYPE_I4, sizeof(a1), &a1, NULL, NULL, DBPARAMIO_INPUT);
I believe that binding as a variant causes oledb to go once to the server to figure out the actual data type the server is expecting. If the command is going to be executed repeatedly, this is not so bad since the server won't have to do the conversion - better to do that on the client, for the sake of scalability.
Note that if you execute the command in a loop, without destroying the CCommand object between iterations, the sp_procedure_params_rowset call is only done once.
- Dave
|||
Hi David,
I have tested your solution. It is working! Thanks a lot.
In the test program(release version) running the stored procedure 10000 times with the Variant bind it takes 15 seconds and with the int bind it takes 7.5 seconds.
So it takes double so much time with the Variant as with the int.The overload making the bind with the Variant it is to much I think. Maybe should be made clear in the documention form ATL Consumer Templates this costs, for who it is taking the decision to do the Variant bind instead of more type specific bind.
About the second idea it is good too. It is allready implemented but the problem it is I have a IConnection interface who it is having 2 object implementations with DB Library and with ATL OLE DB Consumer Templates. So everything it is encapsulated the client doesn't know what implementation it is using only one Object from our application it takes the responsability to choose and set the implementation.
I said this to know that we are not working directly with CCommand objects in the client code. The CCommand object in the OLEDBConnection I am not destroying it after every call but in general I am using not more then one call one after another from the same stored procedure(I am saving the last Store procedure name executed so if the next call it is made with the same stored procedure I don't need to make the initialization part but in general more then 99% from the use cases it is not helping because in the real case the execution of one store procedure it is followed by the execution of another stored procedure ).
Don't take the test application as I send it to you the real case. It is just a test program to isolate the execute store procedure implementation and to be able to test the performance and the behavior of OLE DB.
Your solution was helping me a lot. Thanks again!
sureshv wrote: