March 5, 2008 at 2:47 am
I have a query which is running slower in SQL 2005 SP2, syntax as mentioned below.
SELECT DISTINCT TOP 200 Table1.col1,Table2.col1,Table3.col1 FROM Table1(NOLOCK), Table1(NOLOCK), Table1(NOLOCK) WHERE and order by table3.col1
Please let me know if there is any known issue in sql 2005 with the order by clause because the same query runs faster in SQL 2004 SP4
Also the same query run slower in first run and then faster in the subsequent run in Sql 2005 but no issue in SQL 2000. This issue I am facing after upgrading SQL 2005 SP2 from SQL server 2000 SP4
Any help will be much appreciated
thanks in adavance
--SNT
March 5, 2008 at 2:55 am
just add index on the sort column, it will help ur querry and then it will be same on both the servers
March 5, 2008 at 3:30 am
Did you update statistics when you upgraded to SQL 2005?
Check out the execution plans on both servers and see if they're different (assuming you have access to the SQL 2000 server).
March 5, 2008 at 12:18 pm
Please post the actual query... there's no way the one you posted actually works.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 8:00 am
As for the reason why it runs quicker the second time through it's because the execution plan has been cached.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 6, 2008 at 11:01 am
Are you using join or not ???
March 6, 2008 at 11:47 am
Please post a full query and always use hint (NOLOCK) with 'WITH'.
table A WITH (NOLOCK) for example
Cheers,
Rishi
March 6, 2008 at 3:07 pm
There is a known issue if you are missing a join predicate in one of your join conditions. Refer to KB article:
http://support.microsoft.com/kb/942444/
We have seen several instances where queries with joins which are missing a join predicate perform much worse in SQL 2005 compared to SQL 2000. Examine the Query Plan to determine if there is a 'No Join Predicate' warning.
March 6, 2008 at 8:46 pm
That's useful... thank you very much.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2008 at 1:48 pm
sntiwary (3/5/2008)
I have a query which is running slower in SQL 2005 SP2, syntax as mentioned below.SELECT DISTINCT TOP 200 Table1.col1,Table2.col1,Table3.col1 FROM Table1(NOLOCK), Table1(NOLOCK), Table1(NOLOCK) WHERE and order by table3.col1
As mentioned this couldn't be the query.
Table1, Table1, Table1... Where ??? AND ??? Order by Table3.col1
Please try again. Fyi if this is even close.
I suspect you might be trying to do something like the following, but it would be nice to verify;
;WITH table1(col1) AS (
SELECT 1 col1
UNION ALL
SELECT 2 col1
UNION ALL
SELECT 3 col1
UNION ALL
SELECT 4 col1
UNION ALL
SELECT 5 col1
UNION ALL
SELECT 6 col1
)
SELECT DISTINCT TOP(200) Table1.col1,Table2.col1,Table3.col1
FROM table1,table1 TABLE2,table1 table3
ORDER BY table3.col1
Please read http://www.sqlservercentral.com/articles/Best+Practices/61537/">
http://www.sqlservercentral.com/articles/Best+Practices/61537/
and help us help you.
March 7, 2008 at 1:52 pm
Cliff Jones (3/6/2008)
There is a known issue if you are missing a join predicate in one of your join conditions. Refer to KB article:http://support.microsoft.com/kb/942444/
We have seen several instances where queries with joins which are missing a join predicate perform much worse in SQL 2005 compared to SQL 2000. Examine the Query Plan to determine if there is a 'No Join Predicate' warning.
Cliff - that's good to know - but it left me confused. Are they talking about not specifying INNER/OUTER, or skipping a join clause altogether and doing it in the WHERE clause?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 7, 2008 at 2:01 pm
No, the queries can have what looks like a perfectly formed join condition. But when you examine the query plan you will see a warning indicating 'No Join Predicate' indicating that 2 of the tables involved do not have a predicate (when joined). Adding a an Option (Force Order) to the query produces a completely different plan and the warning disappears (in some cases). That's how you know that this is the problem. Most of the queries that I have seen that perform better on SQL 2000 are exhibiting this problem. Cumulative update 4 to SP 2 is supposed to solve the problem.
March 7, 2008 at 2:12 pm
I guess I should have said 'a perfectly formed outer join', using a LEFT Join for example.
Cliff
March 7, 2008 at 2:12 pm
I have CU4 on all my systems, so I can't test this.
However as I read it, even though you have a valid join, it may interpret it wrong resulting in a Cartesian join (which of course would be slower).
March 7, 2008 at 2:19 pm
Yes, that is what I am seeing. I will have CU 4 installed this week-end so I can verify that it goes away afterwards. But if you can't reproduce it then there is hope that it will resolve the problem, however the KB arcticle states that you must have the 2 trace flags on to implement this feature.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply