Post reply

query with exec sp_executesql problems

  • December 11, 2019 at 7:02 pm

    #3707119

    Hi,

    I've a query that runs with exec sp_executesql that runs very long time (i stopped it after 30 min) . if i parse the query to run without the exec sp_executesql it runs less then 1 sec. i clean the cache,even restart the SQL server service and nothing help. why i get those weird results when run it with the exec sp_executesql  ?

    THX

    with exec sp_executesql

    exec sp_executesql N'select str(round( dbname.dbo.FNCITEMS.IACCOUNT , 0), 17, 0) , ltrim(rtrim( @P1 ))

    from dbname.dbo.ACCOUNTS B inner join dbname.dbo.ACCOUNTS on ( dbname.dbo.ACCOUNTS.SECTION <= - ( @P2 ) ) and ( dbname.dbo.ACCOUNTS.SECTION >= - ( @P3 ) ) and ( dbname.dbo.ACCOUNTS.COMPANY = @P4 ) inner join dbname.dbo.FNCITEMS on ( dbname.dbo.FNCITEMS.IACCOUNT = dbname.dbo.ACCOUNTS.ACCOUNT ) and ( dbname.dbo.FNCITEMS.STORNOFLAG <> @P5 ) and ( dbname.dbo.FNCITEMS.GL > @P6 ) and ( dbname.dbo.FNCITEMS.FINAL = @P7 ) and ( dbname.dbo.FNCITEMS.CURDATE <= system .dbo. tabula_eofyear ( @P8 ) ) and ( dbname.dbo.FNCITEMS.CURDATE >= system .dbo. tabula_bofyear ( @P9 ) ) and ( dbname.dbo.FNCITEMS.ACCOUNT = B.ACCOUNT ) inner join pritempdb.dbo.T$$STACK TMPACC on ( TMPACC.ELEMENT = dbname.dbo.ACCOUNTS.ACCOUNT ) and TMPACC.T$LINKID = ltrim(rtrim( @P10 ))

    where ( B.VATSECTION >= case when ( ( @P11 = @P12 ) ) then ( - ( @P13 ) ) else ( - ( @P14 ) ) end ) and ( B.VATSECTION <= - ( @P15 ) ) and TMPACC.T$LINKID = ltrim(rtrim( @P10 ))

    ',N'@P1 varchar(32),@P2 bigint,@P3 bigint,@P4 bigint,@P5 char(1),@P6 bigint,@P7 char(1),@P8 bigint,@P9 bigint,@P10 varchar(33),@P11 bigint,@P12 bigint,@P13 bigint,@P14 bigint,@P15 bigint','WIN7:e0666401st:1576075388',1,110,-1,'Y',0,'Y',16303680,15779520,'WIN7:p0666400141:1576075388',1,1,10,9,9

    without sp_executesql

    select str(round( dbname.dbo.FNCITEMS.IACCOUNT , 0), 17, 0) , ltrim(rtrim( 'WIN7:e0666401st:1576075388' ))

    from dbname.dbo.ACCOUNTS B inner join dbname.dbo.ACCOUNTS on ( dbname.dbo.ACCOUNTS.SECTION <= - ( 1 ) ) and ( dbname.dbo.ACCOUNTS.SECTION >= - ( 110 ) ) and ( dbname.dbo.ACCOUNTS.COMPANY = -1 ) inner join dbname.dbo.FNCITEMS on ( dbname.dbo.FNCITEMS.IACCOUNT = dbname.dbo.ACCOUNTS.ACCOUNT ) and ( dbname.dbo.FNCITEMS.STORNOFLAG <> 'Y' ) and ( dbname.dbo.FNCITEMS.GL > 0 ) and ( dbname.dbo.FNCITEMS.FINAL = 'Y' ) and ( dbname.dbo.FNCITEMS.CURDATE <= system .dbo. tabula_eofyear ( 16303680 ) ) and ( dbname.dbo.FNCITEMS.CURDATE >= system .dbo. tabula_bofyear ( 15779520 ) ) and ( dbname.dbo.FNCITEMS.ACCOUNT = B.ACCOUNT ) inner join pritempdb.dbo.T$$STACK TMPACC on ( TMPACC.ELEMENT = dbname.dbo.ACCOUNTS.ACCOUNT ) and TMPACC.T$LINKID = ltrim(rtrim( 'ITSIK_WIN7:p0666400141:1576075388' ))

    where ( B.VATSECTION >= case when ( ( 1 = 1 ) ) then ( - ( 10 ) ) else ( - ( 9 ) ) end ) and ( B.VATSECTION <= - ( 9 ) ) and TMPACC.T$LINKID = ltrim(rtrim( 'WIN7:p0666400141:1576075388' ))

     

  • December 12, 2019 at 2:03 pm

    #3707392

    I'd suggest capturing the execution plans for both versions of the query and compare them. You can use the estimated plans just fine, you don't need the actuals for this. Use the SSMS compare facility to get a very good view on plan differences.

    The possible cause is specific values versus parameterized values. The plans should give you some indication of this (look for really specific row counts).

    Side note, a CASE statement in your WHERE clause as you currently have it, is likely to lead to performance issues. Also, ltrim & rtrim could just be trim. Looks like you have a bunch of custom functions in there too? All possible tuning opportunities.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply