August 17, 2006 at 3:48 pm
I'm trying to migrate a db from SQL2000 to SQL2005 and ran into a performance issue with this query:
SELECT L.TransNumber, L.MatchTransNumber, L.MatchTransDate, L.VendorName, L.Amount * -1 AS 'Amount', 'Fund' =
(SELECT MAX(L2.Fund) -- Runs in 1 Seconds
--SELECT TOP 1 L2.Fund -- Takes over 5 minutes to run
FROM MRP_LEDGER L2
WHERE L2.TransNumber = RIGHT(RTRIM(L.MatchTransNumber), LEN(L.MatchTransNumber) - 2)
AND L2.TransCode = 'PV'
AND L2.Fund <> '001'
AND L2.BSAccount NOT IN ('1100', '1101', '1200', '1201', '1500')
)
FROM MRP_LEDGER L
WHERE L.Fund = '001'
AND L.TransCode = 'AD'
-- Parameter: Need to pass in
AND L.TransNumber BETWEEN '00000519942' AND '00000520251'
AND L.DebitCredit = 'C'
AND L.BudgetFY = '2007'
I can't figure out why using the "SELECT TOP 1 L2.Fund" clause is so slow on SQL2005. The same query in SQL2000 takes only 1 second to run, while on SQL2005 takes well over 5 minutes. These two databases are exactly the same. The indexes and schemas are identical. All the other queries run as expected, except for the above query. This is the only query that uses a TOP clause, since we rarely have a need for it.
The SQL2005 is on a new server that is at least 5 times more powerful than the one on SQL2000. Yet, it takes forever to run the above query on it's 8 CPU, 32GB RAM, etc... The old server with SQL2000 only takes 1 second. The tables involve only has about 500,000 records total.
If I use "SELECT MAX(L2.Fund)", the query will once again run in one second. Is there a bug with the "SELECT TOP" statement in SQL2005?
August 17, 2006 at 4:25 pm
See the execution plan on both the server and see if you can see any difference
Amit Lohia
August 18, 2006 at 3:44 pm
Doesn't TOP need an ORDER BY clause? (Otherwise, how does the server know the meaning of TOP?) Try adding one to your subquery.
Dan
August 18, 2006 at 6:37 pm
Adding an ORDER BY has no effect. It still takes over 5 minutes.
The execution plan using TOP shows that it's not using an index for the "TransNumber" field in table "MRP_LEDGER", whereas the MAX() code is using it. I don't see how 1 index can make such a huge difference in terms of performance in this case. We're talking about 1 sec and 5 minutes.
As a comparison, the longest and most complex query we had in the SQL2000 server took about 21 minutes to run. The same query on the SQL2005 takes 13-14 seconds. The SQL2005 is sitting on a very powerful server that costs a lot of money. It executes everything almost instantly, except for this one query. This query is simple and only deals with small (less than 500K records) tables compared to some of the others where there are millions of records.
I did some experimenting and indexed the "TransCode" field in table "MRP_LEDGER". And guess what...this query takes 1 second to run using the TOP 1 code. The MAX() code always takes 1 second. This is very puzzling.
FYI. I'm running SQL2005 Standard 64-bit on Win2003 R2 Ent X64. That's the only program on this server. It's a dedicated DB box.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply