exec sp_executesql

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
Post Reply
RonBrisola
Posts: 14
Joined: Wed 14 Nov 2012 12:59

exec sp_executesql

Post by RonBrisola » Thu 28 Apr 2016 13:58

Helo,

I'm using this driver, version 160, with DelphiXE2.

Today I noticed, trough SQL Profiler, that the commands executed with sp_executesql are slower than a "clean" command.

Is there a way to fix this? Is there a parameter in TSQLQuery that not use sp_executesql?

Here it's the command with sp_executesql,
the statistics showed in Profiler was Reads: 3498, Writes: 9, Duration 140:

Code: Select all

exec sp_executesql N'UPDATE TBESTO004 SET VALUNITMOVTO =  @P1 , 
                      QTDMOVTO     =  @P2 ,     
                      VALMOVTO     =  @P3 ,     
                      HISTORICO    =  @P4      
 WHERE CODEMP  =  @P5                             
   AND NUMLANC =  @P6',N'@P1 money,@P2 bigint,@P3 money,@P4 nvarchar(1),@P5 nvarchar(2),@P6 nvarchar(9)',$16.3750,18,$294.7500,N'',N'01',N'002667024'
[/size]

Here it's the "clean command, without parameters,
the statistics showed in Profiler was Reads: 10, Writes: 9, Duration 0:

Code: Select all

UPDATE TBESTO004 SET VALUNITMOVTO = 16.375, 
                      QTDMOVTO     = 18,     
                      VALMOVTO     = 294.75,     
                      HISTORICO    = ''     
 WHERE CODEMP  = '01'                            
   AND NUMLANC = '002667024'
[/size]

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: exec sp_executesql

Post by azyk » Fri 20 May 2016 09:49

Please let us know the whole runtime code you are using to execute the specified query with TSQLQuery.

RonBrisola
Posts: 14
Joined: Wed 14 Nov 2012 12:59

Re: exec sp_executesql

Post by RonBrisola » Wed 17 Aug 2016 12:08

Hello,

I'm really sorry for the delay to answer you back, but now I have another SqlQuery that runs extremely slow with parameters and really fast without it.

It's possible to send you an attachment with the code that I'm running?

A piece of the code is this:

Code: Select all

   
   sqlSemMovto.Close;
   sqlSemMovto.SQL.Clear;
   sqlSemMovto.SQL.Add(' SELECT TB11A.CODCONTA, TB11A.VALSALDO       ');
   sqlSemMovto.SQL.Add('   FROM TBCONT011 TB11A                      ');
   sqlSemMovto.SQL.Add('  WHERE TB11A.CODEMP  = :ParCodEmp           ');
   sqlSemMovto.SQL.Add('    AND TB11A.ANOLOTE = :ParAnoLote1         ');
   sqlSemMovto.SQL.Add('    AND TB11A.MESLOTE = :ParMesLote1         ');
   sqlSemMovto.SQL.Add('    AND NOT EXISTS                           ');
   sqlSemMovto.SQL.Add('  ( SELECT DISTINCT TB11B.CODCONTA           ');
   sqlSemMovto.SQL.Add('      FROM TBCONT011 TB11B                   ');
   sqlSemMovto.SQL.Add('     WHERE TB11B.CODEMP   = TB11A.CODEMP     ');
   sqlSemMovto.SQL.Add('       AND TB11B.ANOLOTE  = :ParAnoLote2     ');
   sqlSemMovto.SQL.Add('       AND TB11B.MESLOTE  = :ParMesLote2     ');
   sqlSemMovto.SQL.Add('       AND TB11B.CODCONTA = TB11A.CODCONTA ) ');

   sqlSemMovto.ParamByName('ParCodEmp').AsString   := FormPrincipal.FCodEmp;
   sqlSemMovto.ParamByName('ParAnoLote1').AsString := Copy(MaskEditUltimoANOMES.Text, 3, 4);
   sqlSemMovto.ParamByName('ParMesLote1').AsString := Copy(MaskEditUltimoANOMES.Text, 1, 2);
   sqlSemMovto.ParamByName('ParAnoLote2').AsString := Copy(MaskEditProximoANOMES.Text, 3, 4);
   sqlSemMovto.ParamByName('ParMesLote2').AsString := Copy(MaskEditProximoANOMES.Text, 1, 2);
   sqlSemMovto.Open;
   sqlSemMovto.First;

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: exec sp_executesql

Post by ViktorV » Wed 17 Aug 2016 12:35

To investigate this behavior of dbExpress driver for SQL Server, please compose a small sample demonstrating the problem and send it to viktorv*devart*com, including database objects creating scripts.
­

RonBrisola
Posts: 14
Joined: Wed 14 Nov 2012 12:59

Re: exec sp_executesql

Post by RonBrisola » Wed 17 Aug 2016 14:16

Thanks for your help ViktorV.

I just send you an e-mail.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: exec sp_executesql

Post by ViktorV » Thu 18 Aug 2016 09:05

This question is not related to dbExpress driver for SQL Server functionality, but is related to SQL Server architecture specificity.
You can make this sure by running a query without parameters first in your sample, and then - with parameters.
To solve the issue, please contact SQL Server technical support or post a corresponding question on specialized forums.

RonBrisola
Posts: 14
Joined: Wed 14 Nov 2012 12:59

Re: exec sp_executesql

Post by RonBrisola » Thu 18 Aug 2016 19:04

Thanks for your reply ViktorV.

I see that this is related to "parameter sniffing" on SQL-Server.

But why most of queries run fast and others don't?
Is there a way that the dbExpress driver do not send queries to SQL-Server with sp_executesql procedure?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: exec sp_executesql

Post by ViktorV » Fri 19 Aug 2016 08:23

SQL query is send via OLEDB driver. The SQL Server protocol doesn't allow execute a query with parameters in any other way, except using sp_executesql. And we can't affect this.

Post Reply