Query with parameters much slower than normal query

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Query with parameters much slower than normal query

Post by brace » Tue 30 Nov 2010 17:14

I experience a very bad behaviour.

I have a TMSQuery with parameters like

select * from table
where user = :UserID (of course much complex query with more parameters)

simply doing

msquery1.parambyname('UserID') := 14
msquery1.open;

takes 30 seconds

by doing

select * from table
where user = 14

it takes 3 seconds.

Why is this possible?

bork
Devart Team
Posts: 643
Joined: Fri 12 Mar 2010 07:55

Post by bork » Wed 01 Dec 2010 11:55

Hello

I tried both queries (with parameter and without) and they have the same performance. Probably it depends on the table structure. Please send me SQL script to create your table and the exact query that is executed so slowly. Also I need to know how many records are there in the table that is used in your query.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Thu 02 Dec 2010 22:36

i sent an email to support with a sample project and a test database. Please let me now if you can understand something from that. At my end I can reproduce the problem on 2 different machines with 2 separate installations of sql server.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 03 Dec 2010 12:48

We have not received your email. Please, send it to dmitryg*devart*com once more.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Fri 03 Dec 2010 16:23

done. Before I sent to support_at_devart_dot_com. now to dmitryg*devart*com. Let me know. Thanks.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Mon 06 Dec 2010 17:45

Were you able to reproduce the problem? Could you run my sample and install the DB?

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 08 Dec 2010 11:31

The investigation of the problem is in progress. As soon as we solve the problem we will let you know.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Wed 08 Dec 2010 14:46

Thanks 4 the reply. I am eager to know the solution.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 09 Dec 2010 10:43

We have investigated the problem. This behaviour is connected with the specificity of SQL Server work and not connected with SDAC. We have tested the same example with ADO and other third-party components, and the performance difference when executing the query with and without parameters is the same.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Thu 09 Dec 2010 11:51

So what should be an advantage (using parameters) becomes suddenly a problem...

What do you suggest for this?

Should I override ParamByName with ReplaceStr?

But did you find anything about why this case is slow or you just compared SDAC vs ADO?

Thanks.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 09 Dec 2010 13:48

Parameters usage should improve the performance when you use one SQL query with different parameters values many times. In this case SQL Server caches the prepared query for the first time and uses it afterwards. But if you use one small SQL query with parameters, server spends much time to analyse parameters, and the amount of data transmitted between client and server increases.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Fri 10 Dec 2010 10:58

May be I pinpointed the problem.

I corss checekd and SDAC is not the cause, but the problem is DISTINCT.

By removing distinct the queries are almnost the same

While distinct for some reasons makes the performance worse in case of parametrized query.

Can you confirm this?

(you can just remove distinct from my example and you'll notice it)

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 14 Dec 2010 09:31

Yes, with our SQL Server the performance is worse only when the DISTINCT clause is used. It confirms that such behaviour is connected with the specificity of SQL Server work and not connected with SDAC.

Post Reply