Devart Forums

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?

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.

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.

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

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

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

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

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

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.

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.

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.

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)

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.