different execution time

  • Hi All,

    I need help with a query that runs pretty fast (duration=238, all index seeks) from the QA(Management studio query window) but when issued from the application it takes much longer (duration=5890, plan below).

    There is a difference how the statement is showing in the Profiler when issued by the application/jdbc.

    I would appreciate any idea on how to approach this situation in order to improve the performance.

    I have all appropriate indexes, stats, etc.

    Thanks a lot,

    Mj

    Slower running statement:

    declare @p1 int

    set @p1=3

    exec sp_prepexec @p1 output,N'@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000)',N'SELECT ENTITY_ID FROM (SELECT ENTITY_ID FROM NAME WHERE (LFN_HASH = @P1 AND SYS_DELETE_DT IS NULL) OR (FIRST_NAME LIKE @P2 AND LAST_NAME LIKE @P3 AND SYS_DELETE_DT IS NULL)) FIRST_TABLE ','SCHWENGRMARY','CHA%','KEN%'

    select @p1

    StmtText - long

    --------

    Filter(WHERE:([mj42].[dbo].[NAME].[SYS_DELETE_DT] IS NULL))

    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1015]) WITH UNORDERED PREFETCH)

    |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))

    | |--Concatenation

    | |--Index Seek(OBJECT:([mj42].[dbo].[NAME].[IX_ID_LFN_HASH]), SEEK:([mj42].[dbo].[NAME].[LFN_HASH]=[@P1]) ORDERED FORWARD)

    | |--Filter(WHERE:([mj42].[dbo].[NAME].[FIRST_NAME] like [@P2]))

    | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1014]) WITH UNORDERED PREFETCH)

    | |--Nested Loops(Inner Join, OUTER REFERENCES:(LikeRangeStart([@P3]), LikeRangeEnd([@P3]), LikeRangeInfo([@P3])))

    | | |--Compute Scalar(DEFINE:([ConstExpr1007]=LikeRangeStart([@P3]), [ConstExpr1008]=LikeRangeEnd([@P3]), [ConstExpr1009]=LikeRangeInfo([@P3])))

    | | | |--Constant Scan

    | | |--Index Seek(OBJECT:([mj42].[dbo].[NAME].[IX_NAME_LAST_MID]), SEEK:([mj42].[dbo].[NAME].[LAST_NAME] > LikeRangeStart([@P3]) AND [mj42].[dbo].[NAME].[LAST_NAME] < LikeRangeEnd([@P3])), WHERE:([mj42].[dbo].[NAME].[LAST_NAME] like [@P3]) ORDERED FORWARD)

    | |--RID Lookup(OBJECT:([mj42].[dbo].[NAME]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

    |--RID Lookup(OBJECT:([mj42].[dbo].[NAME]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

    Fast running statement:

    SELECT ENTITY_ID FROM (SELECT ENTITY_ID FROM NAME WHERE (LFN_HASH = 'SCHWENGRMARY' AND SYS_DELETE_DT IS NULL)

    OR (FIRST_NAME LIKE 'CHA%' AND LAST_NAME LIKE 'KEN%' AND SYS_DELETE_DT IS NULL)) FIRST_TABLE

    StmtText - good

    --------

    Filter(WHERE:([mj42].[dbo].[NAME].[SYS_DELETE_DT] IS NULL))

    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1006]) WITH UNORDERED PREFETCH)

    |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))

    | |--Concatenation

    | |--Index Seek(OBJECT:([mj42].[dbo].[NAME].[IX_ID_LFN_HASH]), SEEK:([mj42].[dbo].[NAME].[LFN_HASH]='SCHWENGRMARY') ORDERED FORWARD)

    | |--Filter(WHERE:([mj42].[dbo].[NAME].[FIRST_NAME] like 'CHA%'))

    | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1005]) WITH UNORDERED PREFETCH)

    | |--Index Seek(OBJECT:([mj42].[dbo].[NAME].[IX_NAME_LAST_MID]), SEEK:([mj42].[dbo].[NAME].[LAST_NAME] >= 'KEMþ' AND [mj42].[dbo].[NAME].[LAST_NAME] < 'KEO'), WHERE:([mj42].[dbo].[NAME].[LAST_NAME] like 'KEN%') ORDERED FORWARD)

    | |--RID Lookup(OBJECT:([mj42].[dbo].[NAME]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

    |--RID Lookup(OBJECT:([mj42].[dbo].[NAME]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

  • i'm no expert in jdbc but can't you run the query without the use of sp_prepexec?

    it's it that is adding extra time to exec time.

    _______________________________________________

    Causing trouble since 1980

    blog: http://weblogs.sqlteam.com/mladenp

    SSMS Add-in that does a few things: www.ssmstoolspack.com

  • what type of connection to the databases is the application using? It's possible the application is not using the most efficient protocol.

  • I have faced this problem before in jdbc. what happens is that your prepared stament was previously compiled with a set of parameters that have a very different distribution of data than the ones you were trying to reuse.

    The solution is to either not use a prepared statement or use the RECOMPILE Statement level option ( available in 2005 only ) in your query to force recompilation.

    Cheers,


    * Noel

  • your one plan is parameterised, the other is using literals. This is why.

    You'll need to work on the parameterisation to get optimal execution.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • And, lookup "parameter sniffing" on this site or Google...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I do not believe that this is an issue of using bind variables or literals.

    Please, note that the slow statement is the one that uses bind parameters, not literals. I thought that this is what is desirable to avoid recompilation of the plans - the statement with the literals is faster.

    It looks to me like a server side cursor but not sure.

    It has something to do with the jdbc driver - I'm using MS jdbc 1.1 and was not able to test it with 1.2. Anybody tried that?

    On the topic of "parameter sniffing" - my values are highly unique. From about 20 mln records no more then 5-6 will have the same name.

    Thanks a lot,

    mj

  • mjschwenger (9/30/2007)


    I do not believe that this is an issue of using bind variables or literals.

    Please, note that the slow statement is the one that uses bind parameters, not literals. I thought that this is what is desirable to avoid recompilation of the plans - the statement with the literals is faster.

    It looks to me like a server side cursor but not sure.

    It has something to do with the jdbc driver - I'm using MS jdbc 1.1 and was not able to test it with 1.2.

    Anybody tried that?

    Thanks a lot,

    mj

    When you execute a query the server will look in the cache for a plan that matches the text. With literals that plan will match the query. With variables the plan will be for the values of those variables when first run. You can also get a different plan for the same literals in variables.

    Try updating statistics and clearing the cache (recompile on one of the tables) to force a recompile.

    You might need to change the way the query is coded or give a hint. This would be a lot easier if it was in a stored procedure.


    Cursors never.
    DTS - only when needed and never to control.

  • The parametered statements DON'T run faster, as mentioned the plans are cached, but that doesn't imply that they work better. However, looking at the plan it looked the same as best I can tell.. There are the statments to lookup the parameters.

    As Jeff mentioned. Look at Parameter Sniffing.

    I would also suggest wrapping this in a SP and calling it that way.

  • mjschwenger (9/30/2007)


    I do not believe that this is an issue of using bind variables or literals.

    Please, note that the slow statement is the one that uses bind parameters, not literals. I thought that this is what is desirable to avoid recompilation of the plans - the statement with the literals is faster.

    It looks to me like a server side cursor but not sure.

    It has something to do with the jdbc driver - I'm using MS jdbc 1.1 and was not able to test it with 1.2. Anybody tried that?

    On the topic of "parameter sniffing" - my values are highly unique. From about 20 mln records no more then 5-6 will have the same name.

    Thanks a lot,

    mj

    I am going to repeat myself one more time. This problem is because the plan was cached with a parameter set very different from the first one that was used to compile the plan. I have had this issue *MANY* times and it always boils down to the use of some form of recompilation. Use either, statement level or batch level recompilations.

    cheers,


    * Noel

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

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