August 12, 2008 at 6:14 am
I recently upgraded to SQL Server 2005. Prior to upgrading, the query below runs every hour for 15 minutes.
Since I upgraded, the query now runs in 1 hour and 7 minutes. The query can be re-written or indexes could be added. I ran it through the analyzer and profiler and got no suggestions. It is already using clustered indexes.
I have since rebuilt all indexes, update usage amd also update statistics with a full scan.
Can anyone help please? I also included the Execution Plan as a zipped file attachment
SELECT
CTRLNUM = UPPER(BDTL.CTRL_NUM),
LINE_NO = BDTL.line_no,
PARENT_LINE_NO = BDTL.parent_line_no,
PARENTLINENUMBER = BDTL.ParentLineNumber,
SALESPERSONCODE = (CASE WHEN (len( LTRIM(RTRIM(BDTL.SALESPERSON_CODE))) = 0) THEN 'NotClassed'
ELSE ISNULL(UPPER(LTRIM(RTRIM(BDTL.SALESPERSON_CODE))), 'NotClassed') END),
SALESMGRCODE = (CASE WHEN (len( LTRIM(RTRIM(BDTL.SALES_MGR_CODE))) = 0) THEN 'OTHER'
ELSE ISNULL(UPPER(LTRIM(RTRIM(BDTL.SALES_MGR_CODE))), 'OTHER') END),
TERRITORYCODE = UPPER(LTRIM(RTRIM(BDTL.TERRITORY_CODE))),
ITEMCODE = UPPER(BDTL.PART_NO),
EDITION = (CASE WHEN (len(ltrim(rtrim(BDTL.EDITION))) = 0)
THEN 'NotClassed' ELSE BDTL.EDITION END),
UPGRADETYPE = (CASE WHEN (len( LTRIM(RTRIM(BDTL.UpgradeType))) = 0) THEN 'NotClassed'
ELSE ISNULL(LTRIM(RTRIM(BDTL.UpgradeType)), 'NotClassed') END),
PRODUCTMKT = ISNULL(UPPER(BDTL.PRODMKT),'C'),
PRODUCTTYPE = (CASE WHEN (len( LTRIM(RTRIM(BDTL.PRODTYPE))) = 0) THEN 'NotClassed'
ELSE ISNULL(LTRIM(RTRIM(BDTL.PRODTYPE)), 'NotClassed') END),
LICTYPE = UPPER(BDTL.LICTYPE),
LICTYPEDESC = (CASE
WHEN UPPER(BDTL.LICTYPE) in ('N')
and (UPPER(BDTL.EDITION) not like 'HOME%' and UPPER(BDTL.EDITION) not like 'IMP%'
and UPPER(BDTL.EDITION) not like 'EVA%' and UPPER(BDTL.EDITION) not like 'DEMO'
and UPPER(BDTL.EDITION) not like 'REPLA%') THEN 'NEW'
WHEN UPPER(BDTL.LICTYPE) in ('I')
and (UPPER(BDTL.EDITION) not like 'HOME%' and UPPER(BDTL.EDITION) not like 'IMP%'
and UPPER(BDTL.EDITION) not like 'EVA%' and UPPER(BDTL.EDITION) not like 'DEMO'
and UPPER(BDTL.EDITION) not like 'REPLA%') THEN 'INCR'
WHEN UPPER(BDTL.LICTYPE) = 'U' THEN 'UPG'
WHEN UPPER(BDTL.LICTYPE) = 'C' THEN 'CONV'
WHEN UPPER(BDTL.PRODTYPE) = 'SUB' THEN 'SUB'
WHEN UPPER(BDTL.PRODTYPE) = 'SUBREN' THEN 'SUBREN'
ELSE 'OTHER'
END),
QTYORDERED = BDTL.UNITS,
UNITPRICE = BDTL.UNITPRICE,
DISCOUNT = BDTL.DISCOUNT,
EXTAMT = BDTL.DOLLARS,
LICUNITS = (CASE WHEN UPPER(BDTL.LICTYPE) in ('N' , 'I')
and (UPPER(BDTL.EDITION) not like 'HOME%' and UPPER(BDTL.EDITION) not like 'IMP%'
and UPPER(BDTL.EDITION) not like 'EVA%' and UPPER(BDTL.EDITION) not like 'DEMO'
and UPPER(BDTL.EDITION) not like 'REPLA%')
THEN ROUND(ISNULL(BDTL.UNITS,0),2) ELSE 0 END),
LICDOLLARS = (CASE WHEN UPPER(BDTL.LICTYPE) in ('N' , 'I')
and (UPPER(BDTL.EDITION) not like 'HOME%' and UPPER(BDTL.EDITION) not like 'IMP%'
and UPPER(BDTL.EDITION) not like 'EVA%' and UPPER(BDTL.EDITION) not like 'DEMO'
and UPPER(BDTL.EDITION) not like 'REPLA%')
THEN ISNULL(BDTL.DOLLARS,0) ELSE 0 END),
LICDISCOUNTS = (CASE WHEN UPPER(BDTL.LICTYPE) in ('N' , 'I')
and (UPPER(BDTL.EDITION) not like 'HOME%' and UPPER(BDTL.EDITION) not like 'IMP%'
and UPPER(BDTL.EDITION) not like 'EVA%' and UPPER(BDTL.EDITION) not like 'DEMO'
and UPPER(BDTL.EDITION) not like 'REPLA%')
THEN ISNULL(BDTL.DISCOUNT,0) ELSE 0 END),
LICGROSS = (CASE WHEN UPPER(BDTL.LICTYPE) in ('N' , 'I')
and (UPPER(BDTL.EDITION) not like 'HOME%' and UPPER(BDTL.EDITION) not like 'IMP%'
and UPPER(BDTL.EDITION) not like 'EVA%' and UPPER(BDTL.EDITION) not like 'DEMO'
and UPPER(BDTL.EDITION) not like 'REPLA%')
THEN ROUND(ISNULL((BDTL.UNITS) * ABS(BDTL.UNITPRICE),0),2) ELSE 0 END),
UPGLICUNITS = (CASE WHEN UPPER(BDTL.LICTYPE) = 'U'
THEN ROUND(ISNULL(BDTL.UNITS,0),2) ELSE 0 END),
UPGLICDOLLARS = (CASE WHEN UPPER(BDTL.LICTYPE) = 'U'
THEN ISNULL(BDTL.DOLLARS,0) ELSE 0 END),
SUBUNITS = (CASE WHEN UPPER(BDTL.PRODTYPE) = 'SUB'
THEN ROUND(ISNULL(BDTL.UNITS,0),2) ELSE 0 END),
SUBDOLLARS = (CASE WHEN UPPER(BDTL.PRODTYPE) = 'SUB'
THEN ROUND(ISNULL(BDTL.DOLLARS,0),2) ELSE 0 END),
SUBRENUNITS = (CASE WHEN UPPER(BDTL.PRODTYPE) = 'SUBREN'
THEN ROUND(ISNULL(BDTL.UNITS,0),2) ELSE 0 END),
SUBRENDOLLARS = (CASE WHEN UPPER(BDTL.PRODTYPE) = 'SUBREN'
THEN ROUND(ISNULL(BDTL.DOLLARS,0),2) ELSE 0 END),
TOTALDOLLARS = ROUND(ISNULL(BDTL.DOLLARS,0),2),
PRODCATEGORY= UPPER(BDTL.prodcategory),
BDS.SERIALSEQUENCE,
BDS.UNITS,
BDS.SEATID,
BDS.SERIALNUMBER,
OLE.CONTRACTENDDATE,
OLE.CONTRACTSTARTDATE,
OLE.PARENTSEATID,
OLE.SIEBELASSETID,
OLE.PRODUCTTYPECODE
FROM SW_BOOKING_DETAIL BDTL
LEFT OUTER JOIN SW_BOOKING_HEADER BH ON UPPER(BDTL.CTRL_NUM) = UPPER(BH.CTRL_NUM)
LEFT OUTER JOIN SW_BOOKING_DETAIL_SEATS BDS ON (UPPER(BDTL.CTRL_NUM) = UPPER(BDS.CTRL_NUM) AND BDTL.LINE_NO = BDS.LINE_NO)
LEFT OUTER JOIN SW_ORD_LIST_EXT OLE ON (UPPER(BH.ORDER_NO) = UPPER(OLE.ORDER_NO) AND BDTL.LINE_NO = OLE.LINE_NO)
LEFT OUTER JOIN SW_Booking_Setup ON 1=1
WHERE BH.order_date >= SW_Booking_Setup.CurrPeriodStart - 30
August 12, 2008 at 7:36 am
You were running a query that moved 390 gb of data in 15 minutes? That's the estimated data size from the execution plan. Overall, the plan doesn't look too bad. You're getting an index scan on the sw_booking_detail table and a clustered scan on sw_booking_detail_seats table. Those seem to be because of the amount of data involved, there's not opportunity for an index seek, 1.8 million rows from the first and 1.7 million from the second.
Why do you need to move 390gb of data every 15 minutes?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2008 at 9:19 am
Just ran that same query in our old environemnt (SQL SERVER 2000) and it ran in 41 seconds returning 40,000+ rows. Same query plan, same data.
No need to include the Execution plan because it is literally the same thing, doing index scans on a clustered indexes for both SW_BOOKING_DETAIL and SW_BOOKING_DETAIL_SEATS.
August 12, 2008 at 11:34 am
What COLLATION are you using? Is it the same in both environments?
I ask because those UPPER functions on your joins are likely killing any chance of using the indexes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2008 at 11:39 am
Also, this does not make a lot of sense to me:
LEFT OUTER JOIN SW_Booking_Setup ON 1=1
WHERE BH.order_date >= SW_Booking_Setup.CurrPeriodStart - 30
Why make a CROSS JOIN, call it an OUTER JOIN and then turn it into an INNER JOIN with WHERE conditions?
This makes more sense to me:
INNER JOIN SW_Booking_Setup ON BH.order_date >= SW_Booking_Setup.CurrPeriodStart - 30
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2008 at 12:01 pm
Sort order is the same in both Latin1 General CIAS
Your modification changed my 41 seconds to 1 minute and 5 seconds. The table SW_Booking_Setup always has ONE row in it
August 12, 2008 at 12:04 pm
How do you save the execution plan in SQL server 2000 using Management Studio?
August 12, 2008 at 12:08 pm
Hopefully, its the same as Management Studio on 2005? Create the Query Plan output, then right-click in the output window and pick "Save As..."
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2008 at 12:11 pm
You can't do that in 2000.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2008 at 12:11 pm
I do that but the save part is grayed out. I am using 2005 Management Studio with SQL Server 2000.
And I am doing that because I installed teh development tools and Integration services on my dektop and that stops me from using Enterprise Manager.
August 12, 2008 at 12:16 pm
pino_daddy (8/12/2008)
Sort order is the same in both Latin1 General CIAS
Well that's case-insensitive (accent sensitive) , so you should be able to take the UPPER's off (though it is hard to tell without table defs or sample data).
Your modification changed my 41 seconds to 1 minute and 5 seconds. The table SW_Booking_Setup always has ONE row in it
Heh. OK, if it only has one row, and you are only using one column and you are only using it once, then you may want to extract the value from the table first and then use it as a parameter/variable in the WHERE condition.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2008 at 12:24 pm
Looking through the Execution Plan again, it does look like the UPPER() functions are the cause all those scans.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2008 at 12:25 pm
In SQL 2000, you can't save the graphical plan. An acceptable alternative is to run the query with SET SHOWPLAN_ALL ON, run to grid, select the entire grid and paste in Excel (make sure that the different columns appear in different cells), then zip the spreadsheet and attach it to a post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2008 at 12:27 pm
Yes I did that and it does work, but it does not save any time off the execution. I do thank everyone for their assistance. I hope I can get this resolved soon. I never thought by upgrading I would lose performance.
Yet the query still uses the clustered indexes, but takes almost 5 times longer than it did in SQL Server 2000. Strange
August 12, 2008 at 12:32 pm
pino_daddy (8/12/2008)
Yes I did that and it does work, but it does not save any time off the execution.
Can you post the execution plans of the query with the UPPER() functions removed?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply