Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-11 : 21:33:24
|
Hey guys! I have this query below... and it runs fine from query analyzer, but as soon as I put it into a procedure... it runs like crap.I did notice that the execution plan for the quick results shows 63% for tableA and only 1% for each of the tableDs. The slow results show 5% on tableA and 13% for each of the tableDs.Any ideas!?!?! Select A.Col0, B.Col1, B.Col2, B.Col3, B.Col4, B.Col5, B.Col6, B.Col7, B.Col8, Col20 = isnull('Med' + D1.Col9+',','') + isnull('Den' + D2.Col9+',','') + isnull('Dis' + D3.Col9+',','') + isnull('Ter' + D4.Col9+',','') + isnull('Vis' + D5.Col9+',','') From ( Select * From TableA Where Col14 = @Col14 and Col15 = 1 and Col16 = 1 and Col17 is not null and Col12 is not null and Col13 is null ) A Inner Join TableB B On A.Col14 = B.Col14 and A.Col0 = B.Col0 Inner Join TableC C On A.Col14 = C.Col14 and A.Col0 = C.Col0 and A.Col11 = C.Col11 and C.Col13 is null Left Join (Select Col10, Col9 = Case when Col19 = 2 then 'O' when Col19 = 3 then 'D' When Col19 = 4 then 'F' else null end From TableD Where Col18 = 1) D1 On C.Col10 = D1.Col10 Left Join (Select Col10, Col9 = Case when Col19 = 2 then 'O' when Col19 = 3 then 'D' When Col19 = 4 then 'F' else null end From TableD Where Col18 = 2) D2 On C.Col10 = D2.Col10 Left Join (Select Col10, Col9 = Case when Col19 = 2 then 'O' when Col19 = 3 then 'D' When Col19 = 4 then 'F' else null end From TableD Where Col18 = 3) D3 On C.Col10 = D3.Col10 Left Join (Select Col10, Col9 = Case when Col19 = 2 then 'O' when Col19 = 3 then 'D' When Col19 = 4 then 'F' else null end From TableD Where Col18 = 4) D4 On C.Col10 = D4.Col10 Left Join (Select Col10, Col9 = Case when Col19 = 2 then 'O' when Col19 = 3 then 'D' When Col19 = 4 then 'F' else null end From TableD Where Col18 = 5) D5 On C.Col10 = D5.Col10 CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-11 : 23:44:07
|
I would give it a try without using derived tables. It may help the query analyzer to "do the right thing".Select A.Col0, B.Col1, B.Col2, B.Col3, B.Col4, B.Col5, B.Col6, B.Col7, B.Col8, Col20 = isnull('Med'+Case when d1.Col19 = 2 then 'O' when d1.Col19 = 3 then 'D' When d1.Col19 = 4 then 'F' else null end+',','') + isnull('Den'+Case when d2.Col19 = 2 then 'O' when d2.Col19 = 3 then 'D' When d2.Col19 = 4 then 'F' else null end+',','') + isnull('Dis'+Case when d3.Col19 = 2 then 'O' when d3.Col19 = 3 then 'D' When d3.Col19 = 4 then 'F' else null end+',','') + isnull('Ter'+Case when d4.Col19 = 2 then 'O' when d4.Col19 = 3 then 'D' When d4.Col19 = 4 then 'F' else null end+',','') + isnull('Vis'+Case when d5.Col19 = 2 then 'O' when d5.Col19 = 3 then 'D' When d5.Col19 = 4 then 'F' else null end+',','')From TableA a Inner Join TableB B On A.Col14 = B.Col14 and A.Col0 = B.Col0 Inner Join TableC C On A.Col14 = C.Col14 and A.Col0 = C.Col0 and A.Col11 = C.Col11 and C.Col13 is null Left Join TableD D1 On C.Col10 = D1.Col10 and D1.Col18 = 1 Left Join TableD D2 On C.Col10 = D2.Col10 and D2.Col18 = 2 Left Join TableD D3 On C.Col10 = D3.Col10 and D3.Col18 = 3 Left Join TableD D4 On C.Col10 = D4.Col10 and D4.Col18 = 4 Left Join TableD D5 On C.Col10 = D5.Col10 and D5.Col18 = 5where a.Col14 = @Col14 and a.Col15 = 1 and a.Col16 = 1 and a.Col17 is not null and a.Col12 is not null and a.Col13 is null CODO ERGO SUM |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-12 : 00:04:03
|
You can start by rewriting the query like thisSelect A.Col0, B.Col1, B.Col2, B.Col3, B.Col4, B.Col5, B.Col6, B.Col7, B.Col8, Col20 = Case D1.Col19 when 2 then 'MedO,' when 3 then 'MedD,' When 4 then 'MedF,' else '' end + Case D2.Col19 when 2 then 'DenO,' when 3 then 'DenD,' When 4 then 'DenF,' else '' end + Case D3.Col19 when 2 then 'DisO,' when 3 then 'DisD,' When 4 then 'DisF,' else '' end + Case D4.Col19 when 2 then 'TerO,' when 3 then 'TerD,' When 4 then 'TerF,' else '' end + Case D5.Col19 when 2 then 'VisO,' when 3 then 'VisD,' When 4 then 'VisF,' else '' end From TableA A Inner Join TableB BOn A.Col14 = B.Col14and A.Col0 = B.Col0Inner Join TableC COn A.Col14 = C.Col14 and A.Col0 = C.Col0 and A.Col11 = C.Col11Left Join TableD D1 On C.Col10 = D1.Col10 and D1.Col18 = 1Left Join TableD D2On C.Col10 = D2.Col10 and D2.Col18 = 2Left Join TableD D3On C.Col10 = D3.Col10 and D3.Col18 = 3Left Join TableD D4On C.Col10 = D4.Col10 and D4.Col18 = 4Left Join TableD D5On C.Col10 = D5.Col10 and D5.Col18 = 5Where A.Col14 = @Col14 and A.Col15 = 1 and A.Col16 = 1 and A.Col17 is not null and A.Col12 is not null and A.Col13 is null and C.Col13 is null That should be better, to improve it more you'll need to give the table structure and some sample data. I suspect your table design may not be optimal. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-12 : 00:51:29
|
"it runs like crap"Parameter sniffing (in the Sproc version)?Kristen |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-12 : 09:00:28
|
Thanks for the suggestions..MVJ - Query Analyzer is doing the 'right thing' the store procedure isn'tMVJ & snSql - I tried the query without the derived tables and the result was the same...Kristen - could you elaborate on what you mean and how I might verify?Also... I should mention that tableC is actually a view that references a single table (tableC1) joined to itself. (not a cross join). Here is a showcontig for this table since I believe this is the likely culprit:Also, below are the two plans with the noticable difference in redDBCC SHOWCONTIG scanning 'TableC1' table...Table: 'TableC1' (704929783); index ID: 1, database ID: 8TABLE level scan performed.- Pages Scanned................................: 14170- Extents Scanned..............................: 1791- Extent Switches..............................: 7230- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 24.51% [1772:7231]- Logical Scan Fragmentation ..................: 21.57%- Extent Scan Fragmentation ...................: 64.04%- Avg. Bytes Free per Page.....................: 4100.1- Avg. Page Density (full).....................: 49.34%DBCC execution completed. If DBCC printed error messages, contact your system administrator.Execution Tree: generated from query directly in QA--------------Compute Scalar(DEFINE:([Expr1021]=isnull('Med'+[Expr1006]+',', '')+isnull('Den'+[Expr1009]+',', '')+isnull('Dis'+[Expr1012]+',', '')+isnull('Ter'+[Expr1015]+',', '')+isnull('Vis'+[Expr1018]+',', ''))) |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) | | | | |--Filter(WHERE:(isnull([TableC1].[Col12], 'Jan 1 2000 12:00AM')<>isnull([TableC1].[Col12], 'Feb 2 2000 12:00AM') AND [TableC1].[Col12]=NULL)) | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col21], [TableC1].[Col11], [TableC1].[Col0])) | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[Col0])) | | | | | | |--Hash Match(Inner Join, HASH:([A].[Col11], [A].[Col0])=([TableC1].[Col11], [Expr1037]), RESIDUAL:([A].[Col11]=[TableC1].[Col11] AND [A].[Col0]=[Expr1037])) | | | | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableA].[PK_TableA] AS [A]), SEEK:([A].[Col14]=[@Col14]), WHERE:(((([A].[Col12]<>NULL AND [A].[Col17]<>NULL) AND [A].[Col13]=NULL) AND Convert([A].[Col6])=1) AND Convert([A].[Col5])=1) ORDERED FORWARD) | | | | | | | |--Compute Scalar(DEFINE:([Expr1037]=Convert([TableC1].[Col0]))) | | | | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableC1].[PK__TableC1]), SEEK:([TableC1].[Col14]=[@Col14]) ORDERED FORWARD) | | | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[BIC_EmpInfo].[PK_BIC_EmpInfo] AS [B]), SEEK:([B].[Col14]=[@Col14] AND [B].[Col0]=[A].[Col0]) ORDERED FORWARD) | | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableC1].[PK__TableC1]), SEEK:([TableC1].[Col14]=[@Col14] AND [TableC1].[Col0]=[TableC1].[Col0] AND [TableC1].[Col11]=[TableC1].[Col11]), WHERE:([TableC1].[Col21]=[TableC1].[Col21]-1) ORDERED FORWARD) | | | | |--Compute Scalar(DEFINE:([Expr1006]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=1) ORDERED FORWARD) | | | |--Compute Scalar(DEFINE:([Expr1009]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=2) ORDERED FORWARD) | | |--Compute Scalar(DEFINE:([Expr1012]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=3) ORDERED FORWARD) | |--Compute Scalar(DEFINE:([Expr1015]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=4) ORDERED FORWARD) |--Compute Scalar(DEFINE:([Expr1018]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=5) ORDERED FORWARD)Execution Tree: generated from query in procedure executed through QA--------------Compute Scalar(DEFINE:([Expr1021]=isnull('Med'+[Expr1006]+',', '')+isnull('Den'+[Expr1009]+',', '')+isnull('Dis'+[Expr1012]+',', '')+isnull('Ter'+[Expr1015]+',', '')+isnull('Vis'+[Expr1018]+',', ''))) |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TableC1].[Col10])) | | | | |--Filter(WHERE:(isnull([TableC1].[Col12], 'Jan 1 2000 12:00AM')<>isnull([TableC1].[Col12], 'Feb 2 2000 12:00AM') AND [TableC1].[Col12]=NULL)) | | | | | |--Nested Loops(Left Outer Join, WHERE:(([TableC1].[Col0]=[TableC1].[Col0] AND [TableC1].[Col11]=[TableC1].[Col11]) AND [TableC1].[Col21]=[TableC1].[Col21]-1)) | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[Col0])) | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([TableC1].[Col11], [TableC1].[Col0])) | | | | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableC1].[PK__TableC1]), SEEK:([TableC1].[Col14]=[@Col14]) ORDERED FORWARD) | | | | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableA].[PK_TableA] AS [A]), SEEK:([A].[Col14]=[@Col14] AND [A].[Col0]=Convert([TableC1].[Col0]) AND [A].[Col11]=[TableC1].[Col11]), WHERE:(((([A].[Col13]=NULL AND [A].[Col17]<>NULL) AND [A].[Col12]<>NULL) AND Convert([A].[Col5])=1) AND Convert([A].[Col6])=1) ORDERED FORWARD) | | | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[BIC_EmpInfo].[PK_BIC_EmpInfo] AS [B]), SEEK:([B].[Col14]=[@Col14] AND [B].[Col0]=[A].[Col0]) ORDERED FORWARD) | | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableC1].[PK__TableC1]), SEEK:([TableC1].[Col14]=[@Col14]) ORDERED FORWARD) | | | | |--Compute Scalar(DEFINE:([Expr1006]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) | | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=1) ORDERED FORWARD) | | | |--Compute Scalar(DEFINE:([Expr1009]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) | | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=2) ORDERED FORWARD) | | |--Compute Scalar(DEFINE:([Expr1012]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) | | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=3) ORDERED FORWARD) | |--Compute Scalar(DEFINE:([Expr1015]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) | |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=4) ORDERED FORWARD) |--Compute Scalar(DEFINE:([Expr1018]=If ([TableD].[Col19]=2) then 'O' else If ([TableD].[Col19]=3) then 'D' else If ([TableD].[Col19]=4) then 'F' else NULL)) |--Clustered Index Seek(OBJECT:([db].[dbo].[TableD].[PK_TableD]), SEEK:([TableD].[Col10]=[TableC1].[Col10] AND [TableD].[Col18]=5) ORDERED FORWARD) CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-12 : 09:12:06
|
The showcontig shows pretty bad fragmentation, so you should rebuild that index. |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-12 : 09:16:51
|
I had already done a check table with no improvement... do I need to rebuild all indexes or the clustered index?Could the fragmentation be caused by adding a new column to the table?CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-12 : 13:29:51
|
Ok... still in need of more suggestions.So far I have tried:- stripping derived tables- CheckTable- Reindex- Recreated PK- Create new clustered indexI still get significantly better results from code directly in QA vs. code in stored procedure being executed from QA.I would love more suggestions... the new showcontig is below:DBCC SHOWCONTIG scanning 'TableC1' table...Table: 'TableC1' (704929783); index ID: 1, database ID: 8TABLE level scan performed.- Pages Scanned................................: 7023- Extents Scanned..............................: 884- Extent Switches..............................: 883- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 99.32% [878:884]- Logical Scan Fragmentation ..................: 0.03%- Extent Scan Fragmentation ...................: 71.27%- Avg. Bytes Free per Page.....................: 33.7- Avg. Page Density (full).....................: 99.58%DBCC execution completed. If DBCC printed error messages, contact your system administrator. CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-12 : 15:03:02
|
It looks like the problem you are having is in how it handles the join in the view; one plan shows a hash join and the other (slower) shows a nested loop.It would be helpful if you posted the code for the view, and all the tables used in the query, including indexes and constraints. It would also be helpful if we had some idea how many rows there are in each table.CODO ERGO SUM |
|
|
X002548
Not Just a Number
15586 Posts |
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-12 : 16:38:26
|
quote: Originally posted by X002548 how ugly is the view?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
Its not bad... (i didn't think):Create View dbo.TableCAs Select A.Col1, A.Col2, A.Col3, A.Col5, A.Col6, Col7 = B.Col6, A.Col8, A.Col9, A.Col10, A.Col11, A.Col12, A.Col4 From TableC1 A Left Join TableC1 B On A.Col1 = B.Col1 and A.Col2 = B.Col2 and A.Col3 = B.Col3 and A.Col4 = B.Col4 - 1 Where isnull(A.Col6,'1/1/2000') <> isnull(B.Col6,'2/2/2000') CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-12 : 16:46:27
|
quote: Originally posted by Michael Valentine Jones It looks like the problem you are having is in how it handles the join in the view; one plan shows a hash join and the other (slower) shows a nested loop.It would be helpful if you posted the code for the view, and all the tables used in the query, including indexes and constraints. It would also be helpful if we had some idea how many rows there are in each table.CODO ERGO SUM
I am really trying to avoid posting actual column and table names... I'll give you counts for each of the tables... and I'll see what I can do about the constraints and indexes...Given that this is the same server, and the same code, why would the query plan be different. Is it reasonable to force the hash join? What other options might I have?Table Counts:TableA: 2107252 (where conditions filter to 100-7500 dependent on @parameterTableB: 2086483TableC (view): 786413 in source tableTableD: 2027CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-12 : 17:06:52
|
I have also tried forcing a hash join in the view... and it did speed up the slower one, but it also slowed down the faster one... so it really didn't solve the issue.I'll try to get a full set of indexes and constraints sometime soon.Thanks!CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-12 : 17:29:38
|
Your view definition does not look right. This code from the original query would not work, since these columns are not defined in the view.Select * From TableA Where Col14 = @Col14 and Col15 = 1 and Col16 = 1 and Col17 is not null and Col12 is not null and Col13 is null I think I'll bail out on this. I can understand that you might not want to post your queries, views, and table DDL, but I don't think I will be much help if I don't know what the actual query, view, tables, indexes, and constraints are.CODO ERGO SUM |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-13 : 10:29:56
|
Michael - I'll try to post a full set, but I really don't think that the query is the problem. I am trying to figure out why differentplans are being generated for the same piece of code. The performance of the query is very acceptable when I am running the code in QueryAnalyzer. Usually, I see an increase in speed for a query run in a stored procedure vs. running the code directly in QA (I guess because the procedures are compiled). But in this case, the performance is worse as a stored procedure. If the plan difference is really a code problem then I'll post the code, but I just don't get why it would be a code problem at this point.Thanks for your assistance so far!CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-13 : 23:42:11
|
Well, I was got the query working from the procedure... but I had to pull the view code into the main query AND use a join hint. Should all that really be necessary considering QueryAnalyzer generates the plan fine with the view?CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-15 : 13:25:44
|
I assume that @Col14 is a parameter in the stored procedure? And to run the query in Query Analyzer you created a variable with the same name?That would explain why the plans differ. When a plan is generated variable values are not known, but stored procedure parameter values are. As a result, the query optimizer guesses how many rows will be returned for a given variable value but it knows exactly how many rows will be returned for a given parameter value (if the statistics are up to date). Usually that means performance will be better in a stored procedure but in this case I guess not. Anyway - it explains why the plans can be different.See the topic titled "Unusable Statistics" in Chapter 14, Inside SQL Server 2000 by Kalen Delaney (currently available online here http://www.microsoft.com/technet/prodtechnol/sql/70/books/inside14.mspx).Also here if you have a SQL Server Magazine subscription http://www.sqlmag.com/Articles/Index.cfm?ArticleID=42801&DisplayTab=Article |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-15 : 13:37:44
|
snSQL - thanks for the explaination!! I'll definitely be checking out the links CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
|
|
|
|
|