Query with parameters much slower than normal query

brace · 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 · 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 · 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 · 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 · 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 · 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 · 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 · Post by **brace** » Wed 08 Dec 2010 14:46

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

Dimon · 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 · 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 · 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 · 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 · 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.