Feature request:TMSQuery.GenerateParametrizedSQL

brace · Post by **brace** » Mon 20 Dec 2010 10:40

Considering http://www.devart.com/forums/viewtopic. ... highlight= and http://www.devart.com/forums/viewtopic. ... highlight= I have a feature request (a good reason to buy SDAC upgrade

):

inrtoduce a new behaviour, different than the "TDataSet one", for ParamByName. for example it could be a new property called TMSQuery.GenerateParametrizedSQL.

Let's consdier a MSQuery1 for which the SQL is

Code: Select all

select * from table where ID = :ID

doing

Code: Select all

MSQuery1.ParamByName('ID').AsInteger := 45;
MSQuery1.Open;

CASE 1: MSQuery1.GenerateParametrizedSQL is True (default) the query sent to the server will be a parametrized one (current behaviour):

Code: Select all

exec sp_executesql N'select * from table where ID = @P1',N'@P1 int',45

CASE 2: MSQuery1.GenerateParametrizedSQL is False the query sent to the server will be a "hardcoded" query (NEW required behaviour):

Code: Select all

select * from table where ID = 45 -- simple Search&Replace performed by ParamByName!!!

It is clear that in some scenarios (exepcially when the queries are executed only once or when there are performance problems as described in the linked posts, the developer has an immediate benefit in changing the value of the property!).

Please comment on this suggestion.

======================

As an extra reference for why this is useful please see also this question on StackOverflow I posted: http://stackoverflow.com/questions/4408 ... parameters

AndreyZ · Post by **AndreyZ** » Tue 21 Dec 2010 08:27

Hello,

We have investigated this question. We don't plan to implement this functionality until it is requested by several users. But you can get similar functionality by using macros instead of parameters. Here is an example:

Code: Select all

  MSQuery.SQL.Text := 'select * from &TestTable where id = &TestID';
  MSQuery.MacroByName('TestTable').Value := 'tbltest';
  MSQuery.MacroByName('TestID').AsInteger := 10;
  MSQuery.Open;

brace · Post by **brace** » Tue 21 Dec 2010 11:44

Thanks for the reply and the suggestion.

Of course my request come because I have tons of lines of code where I use ParamByName.

Anyway now that you told me about this MacroByName feature may be the suggestion can be modified, like this:

the new property could be

ParametersBehaviour and as values pbDefault, pbMacro (if the second is chosen ParamByName behaves like MacroByName, but without the need to use & instead of :.

This is slightly more userfriendly.

AndreyZ · Post by **AndreyZ** » Tue 21 Dec 2010 14:53

You can implement the functionality you need in the following way:

Code: Select all

procedure TMainForm.btnOpenClick(Sender: TObject);
begin
  MSQuery.SQL.Text := 'select * from tbltest where testid = :testid and teststr = :teststr';
  MSQuery.ParamByName('testid').AsInteger := 10;
  MSQuery.ParamByName('teststr').AsString := 'test';
  MSQuery.Open;
end;

procedure TMainForm.MSQueryBeforeOpen(DataSet: TDataSet);
var
  sqlt: string;
  i: integer;
  q: TMSQuery;
begin
  q := TMSQuery(DataSet);
  sqlt := q.SQL.Text;
  for i := 0 to q.ParamCount - 1 do
    sqlt := AnsiReplaceText(sqlt, ':' + q.Params[i].Name, QuotedStr(q.Params[i].AsString));
  q.SQL.Text := sqlt;
end;

brace · Post by **brace** » Tue 21 Dec 2010 15:52

Thanks this is a good suggestion.

THe problem is that I have many TMSQuery in different datamodules, è some of the defined in code, like

Code: Select all

MyQuery := TMSQuery.Create;
  MyQuery.Sql.Text := 'select * from tbltest where testid = :testid and teststr = :teststr'; 
  etc...

Moreover I am already using BeforeOpen event in some cases.

Could you suggest a centralized solution?

AndreyZ · Post by **AndreyZ** » Wed 22 Dec 2010 14:37

You can create the following class:

Code: Select all

TDummy = class
  class procedure BeforeOpen(DataSet: TDataSet);
end;

class procedure TDummy.BeforeOpen(DataSet: TDataSet);
var
  sqlt: string;
  i: integer;
  q: TMSQuery;
begin
  q := TMSQuery(DataSet);
  sqlt := q.SQL.Text;
  for i := 0 to q.ParamCount - 1 do
    sqlt := AnsiReplaceText(sqlt, ':' + q.Params[i].Name, QuotedStr(q.Params[i].AsString));
  q.SQL.Text := sqlt;
end;

and use this functionality when you need it in the following way:

Code: Select all

procedure TMainForm.btnOpenClick(Sender: TObject);
begin
  MSQuery.BeforeOpen := TDummy.BeforeOpen;
  MSQuery.SQL.Text := 'select * from tbltest where testid = :testid and teststr = :teststr';
  MSQuery.ParamByName('testid').AsInteger := 10;
  MSQuery.ParamByName('teststr').AsString := 'test';
  MSQuery.Open;
end;

brace · Post by **brace** » Thu 23 Dec 2010 10:15

Ok thanks for the suggestion, anyway after discovering this I feel that the performance of my application is improving of a factor 2 by using these "no parameterized queries", either with marco by name or with the technique you suggested.

So i would say now that ParamsByName is a "niche approach" that should be taken only in some particular cases.

And I will vote again for my suggestion, because I guess it will "for free" improve the performance of many other SDAC powered applications.

(the suggestion I made in the past that you implemented about OpenDataSets really made a terrific improvement in the performance when the server latency is high, here it is the same basic idea: "simple thing, great benefit" - of course this particular suggestion will make the query faster, it doesn't deal with server latency).

AndreyZ · Post by **AndreyZ** » Thu 23 Dec 2010 12:35

We will investigate the possibility of adding this functionality in the future. As soon as we solve this question we will let you know.

brace · Post by **brace** » Tue 17 May 2011 14:40

Just a quick post to ask it yo made any progress on this investigation.

AnHa · Post by **AnHa** » Wed 18 May 2011 06:30

Can you review http://devart.uservoice.com/forums/1046 ... ?ref=title , if this is what you want and add a vote?
As seen in other feature request's: more votes -> higher priority for implementing!

AndreyZ · Post by **AndreyZ** » Wed 18 May 2011 08:56

Thank you, AnHa.
If there are many voices for this suggestion at uservoice, we will implement it.

brace · Post by **brace** » Thu 19 May 2011 12:55

Ok I did, thanks. Now the score is 5.

Anyway I strongly suggest to do this, SQL Server performance with parametrized queries is terrible with many complex queries.

I was forced to use MacroByName in many places at least to gain some 200% speed improvement (it occurs with queries with many joins and many parameters tipically).

PS: By the way this is not a task like "create a seprate product for SQLLite" or "Rewrite the driver", it is quite easy and it would give a great performance improvement to all users, I don't think everyone is profiling as I don, so I may guess that many users don't know about this big issue (that is not linked to DevArt, but to SQL Server itself, anyway by adding 1 property DevArt could help the developers to bypass the SQL Server problem).

AndreyZ · Post by **AndreyZ** » Thu 19 May 2011 14:34

For the time being we have tasks of higher priority, and we don't have any term for implementing this functionality.

brace · Post by **brace** » Thu 19 May 2011 15:02

is there a published roadmap?

AndreyZ · Post by **AndreyZ** » Fri 20 May 2011 06:51

We don't have any public roadmap. For the time being our chief priority task is to add support of 64bit environment to all DAC products.
From now on we implement new features basing on their necessity (it's determined by our users voices). This way we can implement features that most of our users want to see in our products.
We will implement the feature you suggested, but we cannot provide any timeframe.