September 26, 2007 at 9:15 am
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)
September 26, 2007 at 10:22 am
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
September 26, 2007 at 11:06 am
what type of connection to the databases is the application using? It's possible the application is not using the most efficient protocol.
September 26, 2007 at 12:11 pm
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
September 27, 2007 at 6:52 am
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/
September 27, 2007 at 7:33 am
And, lookup "parameter sniffing" on this site or Google...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2007 at 6:47 pm
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
September 30, 2007 at 6:58 pm
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.
October 1, 2007 at 2:26 pm
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.
October 1, 2007 at 2:47 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply