SQL server query v slow vs same query in mysql

[sandy771]

I have two databases, mysql and sql server, each with the same tables and data

a query run on mysql is about 100 times faster than the same query on sql server. The relevant columns are indexed on both databases.

Why the huge difference in execution speeds? What can I do to optimise the sql server queries?

My code is basically as follows

sql = "select hvalue, category from ttab where hvalue = :hvalue";
Mysql->SQL->Clear();
Mysql->SQL->Add(sql);
Mysql->Prepare();

while(!ttab->Eof)
{
mysql->ParamByName("hvalue")->AsString = ttab->FieldByName("hval")->AsString;
mysql->Execute();
// some code to run on the very rare occurence of a match
ttab->Next();
}

[AndreyZ]

To investigate this problem I need the following information:
- the script to create and fill the ttab table for both SQL Server and MySQL;
- the exact version of SQL Server and MySQL server and client. You can learn it from the Info sheet of TUniConnection Editor.
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor;
- the exact version of your IDE.
Also you can try creating a small sample that demonstrates this problem and send it to andreyz*devart*com . This way I will be able to help you faster.

[sandy771]

Thanks Andrey - sorting that out now.

[sandy771]

Hi Andrey - I sent the info and test project through on Friday afternoon - I hope you got it OK. Please let me know if I need to supply anything else.

[AndreyZ]

I have investigated this problem. The point is that SQL Server executes queries with not specified parameter data types for a long time. We cannot influence such SQL Server behaviour.
To avoid the problem, you can use either of the two ways:
1. set the DataType property of the "hash" parameter to ftString before preparing a query. Here is a code example:

Code: Select all

...
UniQuery1->SQL->Clear();
UniQuery1->SQL->Add(sql);
UniQuery1->ParamByName("hash")->DataType = ftString;
UniQuery1->Prepare();
...

2. set the DescribeParams specific option to True. In this case UniDAC will fill the data type for the "hash" parameter itself. Here is a code example:

Code: Select all

...
UniQuery1->SpecificOptions->Values["DescribeParams"] = "True";
UniQuery1->SQL->Clear();
UniQuery1->SQL->Add(sql);
UniQuery1->Prepare();
...

[sandy771]

Thanks Andrey

I'll run a test later and get back to you.

Cheers

[sandy771]

Thanks that did the trick

[AndreyZ]

I am glad I could help. If any other questions come up, please contact us.