TSQL Optimization help required 1

[CrystalQB]

Can Anyone help me with this?? Running SQL Server 2000 -- I have this query, a fairly simple one...but hits several tables
and has several case statements and LEFT OUTER JOINS. I know this is not the best way to go about this, because it takes over 45 second to run for just one PO_NUMBER. Not Good. But I just don't know how to get around all this. Can anyone show me how I might make this a little better / quicker? Any ideas?

Thanks in advance.
Crystal QB

SELECT DISTINCT
PO.PO_NUMBER,
PO.COMPANY,
PO.PO_RELEASE,
PO.PO_CODE,
PO.PO_DATE,
PO.PO_REVISION,
PO.TERM_CODE,
PO.PO_USER_FLD_5+' '+PO.PO_USER_FLD_3 as REQ_NBR,
CASE
WHEN PO.FREIGHT_TERMS = 'P'
THEN 'PREPAID'
WHEN PO.FREIGHT_TERMS = 'C'
THEN 'COLLECT'
WHEN PO.FREIGHT_TERMS = 'A'
THEN 'PPD & ADD'
ELSE PO.FREIGHT_TERMS
END FREIGHT_TERMS,
PO.SHIP_VIA,
CASE
WHEN PO.FOB_CODE = 'SP'
THEN 'SHIPPING POINT'
WHEN PO.FOB_CODE = 'DS'
THEN 'DESTINATION'
ELSE PO.FOB_CODE
END SHIP_TERMS,
PO.DFLT_DL_DATE,
CASE
WHEN PO.CURRENCY_CODE = ' '
THEN 'USD'
ELSE PO.CURRENCY_CODE
END CURRENCY,
BUYER.[NAME] as BUYER_NAME,
BUYER.PHONE_PREFIX AS Buyer_PhnPrefx,
BUYER.PHONE AS Buyer_Phone,
POL.LINE_NBR,
POL.ITEM,
POL.[DESCRIPTION],
POL.ENT_BUY_UOM,
POL.ENT_UNIT_CST,
POL.ITEM_TYPE,
POL.QUANTITY,
POL.CXL_QTY,
POL.REC_QTY,
POL.EARLY_DL_DATE,
POL.BUYER_CODE,
POL.TAX_CODE,
POL.EXTENDED_AMT,
rtrim(ltrim(POL.PO_USER_FLD_4)) + ' ' + rtrim(ltrim(POL.PO_USER_FLD_6)) as POLineUserCodes,
CASE
WHEN POL.USER_DATE_3 > '12/31/1753'
THEN POL.USER_DATE_3
END OnHireDate,
CASE
WHEN POL.USER_DATE_3 > '12/31/1753'
THEN POL.USER_DATE_4
END OffHireDate,
ICL.PO_NAME as LNbr_ShipTo_Name,
ICL.PO_ADDR1 as LNbr_ShipTo_Addr1,
ICL.PO_ADDR2 as LNbr_ShipTo_Addr2,
ICL.PO_ADDR3 as LNbr_ShipTo_Addr3,
ICL.PO_ADDR4 as LNbr_ShipTo_Addr4,
ICL.PO_CITY_ADDR5 as LNbr_ShipTo_City,
ICL.PO_STATE_PROV as LNbr_ShipTo_State,
ICL.PO_POSTAL_CD as LNbr_ShipTo_Zip,
ICL.PO_COUNTRY as LNbr_ShipTo_Country,
MMDIST.SYSTEM_CD,
MMDIST.DOC_TYPE,
MMDIST.COMPONENT_SEQ,
MMDIST.AOC_CODE,
MMDIST.ACCT_UNIT,
MMDIST.ACCOUNT,
MMDIST.DIST_COMPANY,
MMDIST.ACTIVITY,
MMDIST.ACCT_CATEGORY,
CASE
WHEN SUBSTRING(L_HICO.OBJECT,6,1) = 'A'
THEN SUBSTRING(L_HICO.OBJECT,(L_HICO.HEADSIZE+1),LEN(L_HICO.OBJECT))
END IMComment
FROM
dbo.PURCHORDER PO
LEFT OUTER JOIN
dbo.POLINE POL
ON PO.COMPANY = POL.COMPANY
AND PO.PO_NUMBER = POL.PO_NUMBER
AND PO.PO_RELEASE = POL.PO_RELEASE
AND PO.PO_CODE = POL.PO_CODE
LEFT OUTER JOIN
dbo.ICLOCATION ICL
ON POL.COMPANY = ICL.COMPANY
AND POL.LOCATION = ICL.LOCATION
AND POL.LOCATION <> PO.LOCATION
LEFT OUTER JOIN
dbo.APVENMAST VENMASTR
ON PO.VENDOR = VENMASTR.VENDOR
LEFT OUTER JOIN
dbo.BUYER BUYER
ON PO.BUYER_CODE = BUYER.BUYER_CODE
AND POL.PROCURE_GROUP = BUYER.PROCURE_GROUP
LEFT OUTER JOIN
dbo.MMDIST MMDIST
ON POL.PO_NUMBER = MMDIST.DOC_NUMBER
AND POL.COMPANY = MMDIST.COMPANY
AND POL.PO_RELEASE = MMDIST.DOC_NBR_NUM
AND POL.PO_CODE = MMDIST.PO_CODE
AND POL.LINE_NBR = MMDIST.LINE_NBR
AND POL.LOCATION = MMDIST.LOCATION
LEFT OUTER JOIN
dbo.ITCOMMENT ITCOMMENT
ON POL.ITEM = ITCOMMENT.ITEM
LEFT OUTER JOIN
dbo.L_HICO L_HICO
ON POL.L_INDEX = L_HICO.L_INDEX
WHERE
rtrim(ltrim(PO.PO_NUMBER)) = '100207489'

 

[gmmastros]

Having case statements in the SELECT clause will not slow down a query too much. That's not your problem.

I have some queries that join more tables than you are and have performance in the millisecond range, so that might not be your problem either.

You biggest problem is probably the where clause. When you use a function in a where clause, it becomes non-sargable. Do a google search on sargable to see what I mean.

I would recommend that you run a query to LTRIM/RTRIM your PO_NUMBER once. Then, when data is inserted, LTRIM/RTRIM it then. That way you can remove the LTRIM & RTRIM from the where clause.

Also.... you need to look at your indexes. Are you getting table scans? Index Scans? Ideally, you should be getting index seeks for this query. To see whether you are getting index scans or seeks... Press CTRL-M in query analyzer and then run the query. There will be a new execution plan tab at the bottom of the window. Look for scans.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

why are you trimming the ponumber? It is best not to use functions inthe where clause if you don't have to.

What does your execution plan say?

What indexes do you have and is it using them?

"NOTHING is more important in a database than integrity." ESquared

 

[CrystalQB]

Sorry, guess I'm a little new to this. I don't know how to get an "execution plan".
I tried "Press CTRL-M in query analyzer and then run the query" but nothing happened. Not sure I know where to be when I do CTRL+M. Sorry for my ignorance.

I see what you mean about the not doing the rtrim(ltrim)) thing, I forgot about that. But the PO_NUMBER is stored as a 14 digit field with leading spaces where the digits don't equal 14. Any suggestions to get around this?

~CrystalQB

 

[CrystalQB]

Yes that is most definitely the problem. Just ran it without that and it came back in 4 seconds.

I'm so dumb, I can't beleive I forgot that rule.

But again...how do I get around this so the people running a crystal report does not have to count out spaces whenevr they enter a PO_NUMBER?

 

[gmmastros]

CTRL-M will display the actual execution plan in SQL Server Mangement Studio (SQL2005). For SQL 2000/Query Analyzer, you need to use CTRL-K. Sorry about that.

Leading spaces... Hmmm..... You should realize that it is important to not use a function on a column in a where clause. As such, there are ways you can resolve this problem. I would suggest that you pad the search value with spaces and then do a 'simple' search, like this...

WHERE
        PO.PO_NUMBER = Right(Space(14) + '100207489', 14)

See the difference. There is still a function in the where clause, but it only involves constants, so you should still be able to use an index on the PO_NUMBER column. If there is not an index on this column, then you should make one.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CrystalQB]

It does make sense, thank you.

That definitely did the trick...thank you so much. And I'm looking into the indexing thing now.

Being just a contractor, I'm normally not allowed to touch thte table structure...but I'll look into what I can do.

again...thank you tons! This was so helpful!

Sincerely,
CrystalQB