|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
very strange SQLI have been tuning some SQL today, and i came across this problem. I have these statements (these are done to reproduce my problem, the real SQL is much bigger) /* test one */ declare @parent varchar(254) set @parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' select * from fnumdetail inner join casedef ON CASEDEF.CASE_INTREF = FNUMDETAIL.FND_KEY where fnd_parent = @parent /* test two */ select * from fnumdetail inner join casedef ON CASEDEF.CASE_INTREF = FNUMDETAIL.FND_KEY where fnd_parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' As you can see, they are the same the first one in generating over 200,000 reads, the second one is only doing 4 reads which is what i would expect. Looking at a query plan, the slow one was doing a table scan, the fast one is just using the index. This was fixed by adding (FASTFIRSTROW), very strange, even stranger if this is part of a union it went back to going slow, this fix for that was to make this the first statement in the union. Can anyone tell me why it behaves like this ? Steve Steve Drake wrote:
Show quote > All, Parameter sniffing. That is, SQL Server can examine parameters sent to > > I have been tuning some SQL today, and i came across this problem. > > I have these statements (these are done to reproduce my problem, the > real SQL is much bigger) > > /* test one */ > declare @parent varchar(254) > set @parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' > select * from fnumdetail inner join casedef ON CASEDEF.CASE_INTREF > = FNUMDETAIL.FND_KEY > where fnd_parent = @parent > > /* test two */ > select * from fnumdetail > inner join casedef ON CASEDEF.CASE_INTREF = FNUMDETAIL.FND_KEY > where fnd_parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' > > As you can see, they are the same the first one in generating over > 200,000 reads, the second one is only doing 4 reads which is what i > would expect. Looking at a query plan, the slow one was doing a table > scan, the fast one is just using the index. > > This was fixed by adding (FASTFIRSTROW), very strange, even stranger > if this is part of a union it went back to going slow, this fix for > that was to make this the first statement in the union. > > Can anyone tell me why it behaves like this ? > > Steve stored procedures in order to optimize SQL statements. However, local variables sometimes are not optimized. The better test is to create the stored procedure and pass in the fnd_parent value as a parameter and use that value unaltered in the SQL statement. I did, this started as a SP with one Param, I then reproduced the problem
without the SP, eg entering SQL into ISQLW. Steve Show quote "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:emtPR1tjFHA.572@TK2MSFTNGP15.phx.gbl... > Steve Drake wrote: >> All, >> >> I have been tuning some SQL today, and i came across this problem. >> >> I have these statements (these are done to reproduce my problem, the >> real SQL is much bigger) >> >> /* test one */ >> declare @parent varchar(254) >> set @parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' >> select * from fnumdetail inner join casedef ON CASEDEF.CASE_INTREF >> = FNUMDETAIL.FND_KEY >> where fnd_parent = @parent >> >> /* test two */ >> select * from fnumdetail >> inner join casedef ON CASEDEF.CASE_INTREF = FNUMDETAIL.FND_KEY >> where fnd_parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' >> >> As you can see, they are the same the first one in generating over >> 200,000 reads, the second one is only doing 4 reads which is what i >> would expect. Looking at a query plan, the slow one was doing a table >> scan, the fast one is just using the index. >> >> This was fixed by adding (FASTFIRSTROW), very strange, even stranger >> if this is part of a union it went back to going slow, this fix for >> that was to make this the first statement in the union. >> >> Can anyone tell me why it behaves like this ? >> >> Steve > > Parameter sniffing. That is, SQL Server can examine parameters sent to > stored procedures in order to optimize SQL statements. However, local > variables sometimes are not optimized. The better test is to create the > stored procedure and pass in the fnd_parent value as a parameter and use > that value unaltered in the SQL statement. > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com On Fri, 22 Jul 2005 17:26:20 +0100, Steve Drake wrote:
Show quote > All, The query optimizer looks at the query at compile time, not runtime, and> > I have been tuning some SQL today, and i came across this problem. > > I have these statements (these are done to reproduce my problem, the real > SQL is much bigger) > > /* test one */ > declare @parent varchar(254) > set @parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' > select * from fnumdetail inner join casedef ON CASEDEF.CASE_INTREF = > FNUMDETAIL.FND_KEY > where fnd_parent = @parent > > /* test two */ > select * from fnumdetail > inner join casedef ON CASEDEF.CASE_INTREF = FNUMDETAIL.FND_KEY > where fnd_parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' > > As you can see, they are the same the first one in generating over 200,000 > reads, the second one is only doing 4 reads which is what i would expect. > Looking at a query plan, the slow one was doing a table scan, the fast one > is just using the index. > > This was fixed by adding (FASTFIRSTROW), very strange, even stranger if this > is part of a union it went back to going slow, this fix for that was to make > this the first statement in the union. > > Can anyone tell me why it behaves like this ? > > Steve does not have access to the value in @parent. Since that value might be NULL, it disregards the index on FND_KEY. In actual fact, I have not seen this behavior with queries as simple as your example, only ones that use a number of variables in the where clause. but.... if @Parent is NULL, it should still use the INDEX, if it doesn't
than that is a major problem. its happens from a SP and when you enter the SQL in ISQL. I think the query optimizer is doing a bad job here. Steve Show quote "Ross Presser" <rpresser@NOSPAMgmail.com.invalid> wrote in message news:143i76qp8ofq5$.dlg@rosspresser.dyndns.org... > On Fri, 22 Jul 2005 17:26:20 +0100, Steve Drake wrote: > >> All, >> >> I have been tuning some SQL today, and i came across this problem. >> >> I have these statements (these are done to reproduce my problem, the real >> SQL is much bigger) >> >> /* test one */ >> declare @parent varchar(254) >> set @parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' >> select * from fnumdetail inner join casedef ON CASEDEF.CASE_INTREF = >> FNUMDETAIL.FND_KEY >> where fnd_parent = @parent >> >> /* test two */ >> select * from fnumdetail >> inner join casedef ON CASEDEF.CASE_INTREF = FNUMDETAIL.FND_KEY >> where fnd_parent = '28CD626A-69B4-42C8-8576-3BF9294E6D70' >> >> As you can see, they are the same the first one in generating over >> 200,000 >> reads, the second one is only doing 4 reads which is what i would expect. >> Looking at a query plan, the slow one was doing a table scan, the fast >> one >> is just using the index. >> >> This was fixed by adding (FASTFIRSTROW), very strange, even stranger if >> this >> is part of a union it went back to going slow, this fix for that was to >> make >> this the first statement in the union. >> >> Can anyone tell me why it behaves like this ? >> >> Steve > > The query optimizer looks at the query at compile time, not runtime, and > does not have access to the value in @parent. Since that value might be > NULL, it disregards the index on FND_KEY. > > In actual fact, I have not seen this behavior with queries as simple as > your example, only ones that use a number of variables in the where > clause. On Sat, 23 Jul 2005 14:27:11 +0100, Steve Drake wrote:
> but.... if @Parent is NULL, it should still use the INDEX, if it doesn't Nobody is saying this is a *good* feature, but it is one that is fairly> than that is a major problem. > > its happens from a SP and when you enter the SQL in ISQL. > > I think the query optimizer is doing a bad job here. well known. The optimizer will do better with stored procedure parameters and literal values, than it can do with local variables. Not much you can do about it except redesign your procedure to use fewer variables - or to use fewer at a time.  |
|||||||||||||||||||||||
Other interesting topics