November 3, 2008 at 6:38 am
I've got a query selecting data from views that reference a linked server. Total data returned is 3,558 rows, total number of rows in tables used isn't much more. In 2000, it returns in 1 second or less, in 2005 it runs for 6+ minutes. All oject references in the query below are views. I know it's the order by that's causing it, but I don't understand why it's causing the issue. I've tried adding an additional index on comm_name, but it didn't help.
Can anyone help explain this to me? Both servers are similar in build, both are located at the same physical location, both reference the same source for the views.
SELECTc.comm_id AS [ID],
c.comm_name AS [Committee Name],
c.state AS [State],
l.comm_desc AS [Committee Type],
c_1.comm_name AS [Parent Committee]
FROMCommittee c
LEFT OUTER JOIN COMMITTEE c_1
on c.parent_id=c_1.comm_id
INNER JOIN legislative_type l
ON c.type_fk=l.type_code
WHEREc.comm_name IS NOT NULL
AND c.STATE IN ('US', 'AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT',
'DE', 'DC', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL',
'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA',
'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH',
'NJ', 'NM', 'NY', 'NC', 'ND', 'MP', 'OH', 'OK',
'OR', 'PW', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN',
'TX', 'VI', 'UN', 'UT', 'VT', 'VA', 'WA', 'WV',
'WI', 'WY')
AND c.TYPE_FK IN ( 'FS', 'FH', 'FJ', 'SS', 'SH', 'SJ', 'OT')
ORDER BYc.comm_name
November 3, 2008 at 6:47 am
in my experience, when the same query doesn't run right on two different servers, i check two items first thing: statistics and parameter sniffing.
statistics on one server might be up to date, but whacked out on the other server. So The "other' server builds a poor plan that is really really slow.
parameter sniffing is where the server buidls a plan based on one parameters passed to a stored procedure, but that plan is way off when other vbalues are used.
Since I don't see this is a stored proc with a parameter, I think a simple update on the statistics is the thing to do.
Offhand, your query doesn't look overly complex, so try that first.
Lowell
November 3, 2008 at 6:49 am
I'd double check indexes as well. I've seen people assuming they're the same when they aren't.
November 3, 2008 at 7:05 am
- Was the sql2000 query also remote and using linked servers ?
- are the linked server settings equal ?
- do the databases use the same ansi settings ?
- I guess it materializes the result in local tempdb, then performs the order by , then returns the data.
Chances are the materialization is on a per object (in your case 'view') basis.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 3, 2008 at 8:51 am
ALZDBA (11/3/2008)
- Was the sql2000 query also remote and using linked servers ?- are the linked server settings equal ?
- do the databases use the same ansi settings ?
- I guess it materializes the result in local tempdb, then performs the order by , then returns the data.
Chances are the materialization is on a per object (in your case 'view') basis.
Yes, there are three servers involved here: the 2005 box, the 2000 box and another 2000 box where the tables actually reside
Yes the linked server settings are equal
Yes, all of the ansi settings are the same
November 3, 2008 at 8:53 am
Steve Jones - Editor (11/3/2008)
I'd double check indexes as well. I've seen people assuming they're the same when they aren't.
No indexes to compare, the source tables are the same set of tables, on the same server, in the same database. The views are not indexed.
November 3, 2008 at 8:54 am
Lowell (11/3/2008)
in my experience, when the same query doesn't run right on two different servers, i check two items first thing: statistics and parameter sniffing.statistics on one server might be up to date, but whacked out on the other server. So The "other' server builds a poor plan that is really really slow.
parameter sniffing is where the server buidls a plan based on one parameters passed to a stored procedure, but that plan is way off when other vbalues are used.
Since I don't see this is a stored proc with a parameter, I think a simple update on the statistics is the thing to do.
Offhand, your query doesn't look overly complex, so try that first.
statistics were updated, along with rebuilding all of the indexes just to be safe. No improvement.
November 3, 2008 at 1:15 pm
I know it's the order by that's causing it, but I don't understand why it's causing the issue.
So if you remove the Order By, it functions the same on the new server?
November 3, 2008 at 3:15 pm
Garadin (11/3/2008)
I know it's the order by that's causing it, but I don't understand why it's causing the issue.
So if you remove the Order By, it functions the same on the new server?
yes, that's what's confusing me.
November 3, 2008 at 9:28 pm
November 4, 2008 at 12:17 am
Could you perform this query just as a test:
(=your query but used as a nested table expression)
Select *
from
(SELECT c.comm_id AS [ID],
c.comm_name AS [Committee Name],
c.state AS [State],
l.comm_desc AS [Committee Type],
c_1.comm_name AS [Parent Committee]
FROM Committee c
LEFT OUTER JOIN COMMITTEE c_1
on c.parent_id=c_1.comm_id
INNER JOIN legislative_type l
ON c.type_fk=l.type_code
WHERE c.comm_name IS NOT NULL
AND c.STATE IN ('US', 'AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT',
'DE', 'DC', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL',
'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA',
'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH',
'NJ', 'NM', 'NY', 'NC', 'ND', 'MP', 'OH', 'OK',
'OR', 'PW', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN',
'TX', 'VI', 'UN', 'UT', 'VT', 'VA', 'WA', 'WV',
'WI', 'WY')
AND c.TYPE_FK IN ( 'FS', 'FH', 'FJ', 'SS', 'SH', 'SJ', 'OT')
) NestSel
ORDER BY [Committee Name];
Might not seem that much a difference, but since it will materialize the NestSel prior to ordering it, it may (or may not :ermm: ) enhance the overall performance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 4, 2008 at 9:46 am
Neither of the last two suggestions helped. Any other suggestions?
Also, I appreciate the help, this issue is driving me nuts.
November 4, 2008 at 10:14 am
Try this:
with ParentCommittee (
CommID,
CommName
) as (
select
comm_id,
comm_name
from
Committee
)
SELECT
c.comm_id AS [ID],
c.comm_name AS [Committee Name],
c.state AS [State],
l.comm_desc AS [Committee Type],
c_1.CommName AS [Parent Committee]
FROM
Committee c
LEFT OUTER JOIN ParentCommittee c_1
on c.parent_id=c_1.CommID
INNER JOIN legislative_type l
ON c.type_fk=l.type_code
WHERE
c.comm_name IS NOT NULL
AND c.STATE IN ('US', 'AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT',
'DE', 'DC', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL',
'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA',
'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH',
'NJ', 'NM', 'NY', 'NC', 'ND', 'MP', 'OH', 'OK',
'OR', 'PW', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN',
'TX', 'VI', 'UN', 'UT', 'VT', 'VA', 'WA', 'WV',
'WI', 'WY')
AND c.TYPE_FK IN ( 'FS', 'FH', 'FJ', 'SS', 'SH', 'SJ', 'OT')
ORDER BY
c.comm_name
November 4, 2008 at 1:54 pm
Lynn Pettis (11/4/2008)
Try this:
with ParentCommittee (
CommID,
CommName
) as (
select
comm_id,
comm_name
from
Committee
)
SELECT
c.comm_id AS [ID],
c.comm_name AS [Committee Name],
c.state AS [State],
l.comm_desc AS [Committee Type],
c_1.CommName AS [Parent Committee]
FROM
Committee c
LEFT OUTER JOIN ParentCommittee c_1
on c.parent_id=c_1.CommID
INNER JOIN legislative_type l
ON c.type_fk=l.type_code
WHERE
c.comm_name IS NOT NULL
AND c.STATE IN ('US', 'AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT',
'DE', 'DC', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL',
'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA',
'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH',
'NJ', 'NM', 'NY', 'NC', 'ND', 'MP', 'OH', 'OK',
'OR', 'PW', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN',
'TX', 'VI', 'UN', 'UT', 'VT', 'VA', 'WA', 'WV',
'WI', 'WY')
AND c.TYPE_FK IN ( 'FS', 'FH', 'FJ', 'SS', 'SH', 'SJ', 'OT')
ORDER BY
c.comm_name
It helped, cut it down to a couple minutes, but can you (or anyone else) point me into the right direction as to why the query would perform so drastically different between versions?
November 4, 2008 at 2:12 pm
best to do is compare the execution plans.
With sql2005 SSMS you can even save and post the .sqlplan if you want to.
using set statistics io on and set statistics time on, you may also get some valuable info for comparison.
I now it is "simple", but it's a start.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply