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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Execution Speed

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


Corey

Co-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 = 5
where
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
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-12 : 00:04:03
You can start by rewriting the query like this

Select 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 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
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 = 5
Where 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.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-01-12 : 00:51:29
"it runs like crap"

Parameter sniffing (in the Sproc version)?

Kristen
Go to Top of Page

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't

MVJ & 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 red


DBCC SHOWCONTIG scanning 'TableC1' table...
Table: 'TableC1' (704929783); index ID: 1, database ID: 8
TABLE 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)




Corey

Co-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 ..."
Go to Top of Page

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.
Go to Top of Page

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?



Corey

Co-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 ..."
Go to Top of Page

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 index

I 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: 8
TABLE 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.


Corey

Co-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 ..."
Go to Top of Page

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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-12 : 15:23:41
how ugly is the view?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

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?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam







Its not bad... (i didn't think):


Create View dbo.TableC
As
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')


Corey

Co-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 ..."
Go to Top of Page

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 @parameter
TableB: 2086483
TableC (view): 786413 in source table
TableD: 2027

Corey

Co-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 ..."
Go to Top of Page

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!

Corey

Co-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 ..."
Go to Top of Page

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
Go to Top of Page

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 different
plans 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!

Corey

Co-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 ..."
Go to Top of Page

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?


Corey

Co-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 ..."
Go to Top of Page

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
Go to Top of Page

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

Corey

Co-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 ..."
Go to Top of Page
   

- Advertisement -