Feature request:TMSQuery.GenerateParametrizedSQL

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
AnHa
Posts: 8
Joined: Wed 09 Feb 2011 13:42

Post by AnHa » Fri 20 May 2011 08:24

Hello AndreyZ,
I think this is the right way to prioritize the user requests.

But I have to ask another question: I've seen that slow queries with parameters on my previous version which was ADO-based and changed many queries to simple sql-text without parameters (therefore this feature request has not the highest priority for me). If that is a ADO/OLEDB-problem, possibly it's better to make a SpecificOption for MSSQLprovider, which you can faster implement and test?
What is your experience (as devart) in your other products (.Net, DBExpress): Are there parameters on SQLServer-queries also bad?
If not: Can there go something wrong in the implementation of the parameters for the MSSQLProvider?

AndreyZ

Post by AndreyZ » Fri 20 May 2011 12:09

This problem is connected with the specifity of SQL Server. As you can see in the first post of this topic, brace wrote examples of SQL code that are sent to SQL Server in both cases (with and without parameters). The point is that SQL Server executes some queries faster if they don't have parameters (i.e. they have plain text).

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

Re: Feature request:TMSQuery.GenerateParametrizedSQL

Post by brace » Wed 03 Oct 2012 23:02

Hello, since I need to close an internal request could you please update me on this feature: is it planned or not?

AndreyZ

Re: Feature request:TMSQuery.GenerateParametrizedSQL

Post by AndreyZ » Mon 08 Oct 2012 08:22

We have this feature in our tasks, but we do not plan to implement it this year.

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

Re: Feature request:TMSQuery.GenerateParametrizedSQL

Post by brace » Mon 08 Oct 2012 09:17

Thanks for the reply.

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

Re: Feature request:TMSQuery.GenerateParametrizedSQL

Post by brace » Tue 14 Jan 2014 08:49

Just keeping an eye on this. Any changes in plans about alowing to send non parametrized querise to sql server even Parameters are used?

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

Re: Feature request:TMSQuery.GenerateParametrizedSQL

Post by brace » Tue 14 Jan 2014 08:49

any progress on this task? Thank you.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Feature request:TMSQuery.GenerateParametrizedSQL

Post by AlexP » Tue 14 Jan 2014 15:07

Hello,

We implement new features depending on their complexity and relevance, and since this feature is rather laborious and unclaimed, we don't plan to implement it in the nearest future.

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

Re: Feature request:TMSQuery.GenerateParametrizedSQL

Post by brace » Thu 16 Jan 2014 11:19

THank you. As far per "laborious" i am not sure it is. If it is done for SDAC only.

I didn't research too much anyway i noticed that MacroByName is drastically faster than ParamByName only in some compelx queries so manaully replacing ParamByName with MacroByName is an option.

This faeture could anyway give extra speed for free to all the users that stick with ParambyName as the only way to do things.

This seems interesting:
http://stackoverflow.com/questions/1093 ... parameters

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Feature request:TMSQuery.GenerateParametrizedSQL

Post by AlexP » Fri 17 Jan 2014 11:18

Hello,

If this feature will be added - it will be added for all the DAC products. Therefore it is a rather time-consuming feature.
Value substitution to a SQL query instead of parameters (or macros), as well as the use of the RECOMPILE option, will help increase performance in queries, that are executed rarely. If a query is often executed, and parameters often change, then the use of these approaches will decrease performance in comparison to the use of parameters.

Post Reply