September 12, 2008 at 10:00 am
I migrated from SQL 2000 to SQL 2005. The query below runs in 3-4 seconds. After I migrated from 2000 to 2005 the query now takes over 10 minutes.
SELECT dbo.neb_invoice_detail.vendor, dbo.neb_vendor_main.vend_name, SUM(dbo.neb_invoice_detail.exten_price) AS SALES,
SUM(dbo.neb_invoice_detail.profit) AS PROFIT,
sum(case when year(neb_invoice_detail.inv_date) = year(dateadd(yyyy, -2, dateadd(yyyy, datediff(yyyy, 0, getdate()), 0))) then dbo.neb_invoice_detail.exten_price else 0 end) as TotalSales1,
sum(case when year(neb_invoice_detail.inv_date) = year(dateadd(yyyy, -1, dateadd(yyyy, datediff(yyyy, 0, getdate()), 0))) then dbo.neb_invoice_detail.exten_price else 0 end) as TotalSales2,
sum(case when year(neb_invoice_detail.inv_date) = year(dateadd(yyyy, -1, dateadd(yyyy, datediff(yyyy, 0, getdate()), 0))) and neb_invoice_detail.inv_date < dateadd(yyyy, -1, dateadd(dd,datediff(dd,0,getdate()),0)) then dbo.neb_invoice_detail.exten_price else 0 end) as SYTD1,
sum(case when year(neb_invoice_detail.inv_date) = year(dateadd(yyyy, 0, dateadd(yyyy, datediff(yyyy, 0, getdate()), 0))) and neb_invoice_detail.inv_date < dateadd(dd,datediff(dd,0,getdate()),0) then dbo.neb_invoice_detail.exten_price else 0 end) as SYTD2 FROM dbo.neb_invoice_detail LEFT OUTER JOIN neb_customer_main ON neb_invoice_detail.custno = neb_customer_main.custno LEFT OUTER JOIN
dbo.neb_vendor_main ON dbo.neb_invoice_detail.vendor = dbo.neb_vendor_main.vendorno WHERE neb_customer_main.shipto = 0 AND neb_invoice_detail.inv_date between dateadd(yyyy, -2, dateadd(yyyy, datediff(yyyy, 0, getdate()), 0)) and dateadd(dd,datediff(dd,0,getdate()),0)
GROUP BY dbo.neb_invoice_detail.vendor, dbo.neb_vendor_main.vend_name ORDER BY dbo.neb_invoice_detail.vendor
September 12, 2008 at 11:11 am
Start with the basics: did you update statistics after upgrading?
September 12, 2008 at 12:00 pm
Do you mean "reindex" ? then yes.
September 12, 2008 at 12:08 pm
September 12, 2008 at 12:13 pm
Well.... i found the problem..somewhat. I created indexes on the tables in SQL2005 and cut processing from 10+ minutes to 17 secs. However there where no indexes on the old server. The old server still process at 4 secs. I should note that the query I displayed above is a stored procedure that is building he SQL statement dyncamically. AND we move from a 32bit 2000 SQL to 64bit SQL 2005.
Thanks for you posts. I truely appreciate it.
September 12, 2008 at 12:42 pm
If there were a number of the normalization guru's out there seeing this post they'd tell you that you really didn't have a table cause there were no indexes on it, instead you had a heap.
Unless there's a very specific business reason, you should always have appropriate indexes on your table otherwise the optimiser won't really know what to do with your data and you will end up with a table scan.
September 12, 2008 at 1:08 pm
We have been given very limited information here, but...
There is no reason that SQL 2005 x64 should take 4x as long to complete that query, if you are on similar hardware. It is reasonable to assume that there was hardware upgrade with the software change, which even further indicates you still have a performance problem.
If you are interested in more feedback, post up your execution plans and your hardware config before/after the upgrade.
September 14, 2008 at 7:36 am
There can be a number of reasons for this query to go slow.
Check out execution plans. See if there is some parallellism gone bad on x64 server.
N 56°04'39.16"
E 12°55'05.25"
September 14, 2008 at 7:56 am
- Did you switch your db to db-level 90 (sql2005) or did you leave it on 80 after the restore ?
- You need to rebuild all indexes.
- you need to sp_updatestats.
- you need to dbcc updateusage (0) with coun rows.
- Was that table a pinned table on sql2000 ? (i.e. kept in memory after the initial load) That is no longer supported with sql2005.
- There should be a clustering index on every table by default (unless proven it hurts performance on a case by case basis)
- There should be non clustering indexes on a table if needed (to support FK, queries, order by , group by ,...) to reduce io needed to obtain the results you need
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply