| Author |
Topic |
|
Lec11
Starting Member
3 Posts |
 Posted - 2010-01-25 : 08:13:22
|
Hello,First let me say that I don't have much experience with SQL. I am a Navision developer and from time to time a make stored procedures and run them through ADO.I made a stored procedure which worked just fine on SQL Server 2000 until we migrated to SQL Server 2005. Now, it seems that it never ends.The procedure uses many views and I started to compare the running time of each view in 2000 vs 2005.So I stumbled upon the following query which is taking 6 seconds to show results in 2000 and it seems to never end in 2005.quote:
SELECT UUVEs_VE_Capacity.[Source No_], UUVEs_VE_Capacity.No_, SUM(UUVEs_VE_Capacity.InvoicedQuantityKg_Order) AS InvoicedQuantityKg_Order, SUM(UUVEs_VE_Capacity.InvoicedQuantity_Order) AS InvoicedQuantity_Order, SUM(UUVEs_VE_Capacity.ActualCapacityCostPerUnitBase * UUVEs_VE_Capacity.InvoicedQuantity_Order) / OutSN.InvoicedQuantity AS [Actual Capacity Cost (Base UOM)], SUM(UUVEs_VE_Capacity.ActualCapacityCostPerKg * UUVEs_VE_Capacity.InvoicedQuantityKg_Order) / OutSN.InvoicedQuantityKg AS [Actual Capacity Cost/Kg], UUVEs_VE_Capacity.UnitsPerKg, OutSN.InvoicedQuantity, OutSN.InvoicedQuantityKg, SUM(UUVEs_VE_Capacity.[Cost Posted to G_L]) AS CostPostedToG_LFROM dbo.UUVEs_ValueEntryCapacity1 UUVEs_VE_Capacity INNER JOIN dbo.UUVEs_Output_SourceNo OutSN ON UUVEs_VE_Capacity.[Source No_] = OutSN.[Source No_]GROUP BY UUVEs_VE_Capacity.[Source No_], UUVEs_VE_Capacity.No_, UUVEs_VE_Capacity.UnitsPerKg, OutSN.InvoicedQuantity, OutSN.InvoicedQuantityKg
Do you have any advice on this issue?Regards,Edy |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-25 : 08:14:42
|
| are indexes are same in both servers? also are statistics updated? |
 |
|
|
Lec11
Starting Member
3 Posts |
Posted - 2010-01-25 : 08:16:23
|
| Yes to both questions |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-25 : 08:33:26
|
| Are there any logic tests (WHERE clause or JOINs - including in any VIEWs) that make implicit casts of datatype? (e.g. compare a DATETIME with a String Date, or an INT with a string number, and so on).If so try putting an explicit Cast in the code instead.When you moved to SQL 2005 did you:Run Upgrade Advisor to see if it found any issues with your codeChange compatibility level (to SQL 2005)Rebuild all indexesUpdate statistics WITH FULL SCANMore info on this link (refers to SQL 2008, but most of it is applicable to SQL 2005 upgrades)http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-25 : 08:34:22
|
| P.S. worth looking at the Query Plan for that on both SQL 2000 and SQL 2005 and seeing what looks different. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-25 : 08:50:06
|
Also might want to check for blocking processes (spwho2 while the process is running).The query itself doesn't at first glance have any big gotchas in it. Here it is formatted a littleSELECT UUVEs_VE_Capacity.[Source No_] , UUVEs_VE_Capacity.No_ , SUM(UUVEs_VE_Capacity.InvoicedQuantityKg_Order) AS InvoicedQuantityKg_Order , SUM(UUVEs_VE_Capacity.InvoicedQuantity_Order) AS InvoicedQuantity_Order , SUM(UUVEs_VE_Capacity.ActualCapacityCostPerUnitBase * UUVEs_VE_Capacity.InvoicedQuantity_Order) / OutSN.InvoicedQuantity AS [Actual Capacity Cost (Base UOM)] , SUM(UUVEs_VE_Capacity.ActualCapacityCostPerKg * UUVEs_VE_Capacity.InvoicedQuantityKg_Order) / OutSN.InvoicedQuantityKg AS [Actual Capacity Cost/Kg] , UUVEs_VE_Capacity.UnitsPerKg , OutSN.InvoicedQuantity , OutSN.InvoicedQuantityKg , SUM(UUVEs_VE_Capacity.[Cost Posted to G_L]) AS CostPostedToG_LFROM dbo.UUVEs_ValueEntryCapacity1 UUVEs_VE_Capacity INNER JOIN dbo.UUVEs_Output_SourceNo OutSN ON UUVEs_VE_Capacity.[Source No_] = OutSN.[Source No_]GROUP BY UUVEs_VE_Capacity.[Source No_] , UUVEs_VE_Capacity.No_ , UUVEs_VE_Capacity.UnitsPerKg , OutSN.InvoicedQuantity , OutSN.InvoicedQuantityKg How big are the tables concerned/Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-25 : 09:52:17
|
| "The query itself doesn't at first glance have any big gotchas in it... no telling what might be in the definitions of the VIEWs though! |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-25 : 10:05:53
|
| Ah true. I assumed they were base tables but... you know what they say about assumeCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-25 : 10:11:41
|
| U leave ME out of it! |
|
|
|
Lec11
Starting Member
3 Posts |
Posted - 2010-01-25 : 11:35:14
|
| Thanks to all for your responses!The operations regarding compatibility, indexes or statistics have all been done.We have several stored procedures used of several reports and all are working better now than before (as expected). Except this one.The funny thing about this query (or at least it is funny for me) that I forgot to mention is that if I make a SELECT from each of the two views it ends, running pretty well actually. So could be something wrong with the sums or maybe with the join? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Query fast in 2000, slow in 2005