execsql vs direct statement

• alberto_pierotti

  Newbie

  Points: 5

  More actions

  November 3, 2003 at 3:33 am

  #106472

  Hi, I noticed very different performance of these two statements that returns the same recordset:

  FIRST:

  SELECT ...FROM DocNumerazioni

  WHERE (TipoNum=1 AND CodClasseNumeraz=130 AND PeriodoRifNumeraz=2003.00 AND CodSerie=1 AND Num=472330

  AND AppDigitata='') AND (DBGruppo='PM')

  SECOND:

  - exec sp_executesql

  N'SELECT ... FROM DocNumerazioni

  WHERE (TipoNum=@P1 AND CodClasseNumeraz=@P2 AND PeriodoRifNumeraz=@P3 AND CodSerie=@P4 AND Num=@P5

  AND AppDigitata=@P6) AND (DBGruppo=''PM'')',

  N'@P1 smallint,@P2 smallint,@P3 numeric(8,2),@P4 int,@P5 int,@P6 varchar(3)',

  1, 130, 2003.00, 1, 472330, ''

  the table have 500K records, and the first statements is very fast, the second very slow

  (milliseconds vs seconds...)

  BTW the second statement is obtained using ado with a parametrized statements, and the documentation claims

  that is faster..

  Does anybody any explanation of this behaviuor?

  Is there any configuration problem ?

  TIA

  Alberto

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 3, 2003 at 4:39 am

  #480611

  Take a look at BOL for sp_executeSQL -> Using sp_executeSQL.

  This is somehow expected behaviour.

  What about a third approach?

  Putting your statement into a sproc and call this from ADO?

  Frank

  http://www.insidesql.de

  http://www.familienzirkus.de

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Eric Pitre-55567

  Grasshopper

  Points: 23

  More actions

  July 13, 2004 at 1:45 am

  #514341

  The answer is in your code....

  The first is faster because the code is direct, absolute...

  The second requires much more intrepretation, compiling plus use of quotes is creating literals of some code which requires more work.

  Parameters are faster, if used Properly in stored procedures vs dynamic queries, the reason they are faster because they are more likely to be compiled and in memory..... and even referencable in the buffer where as dynamic queries typically do not...

   

  Also, in your tests,

  You do know there is a difference in the first and second execution of the same dynamic sql block of code?  Hopefully, this is not your error.