Tuning advise needed. Any help is appreciated.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Tuning advise needed. Any help is appreciated.

Author

Topic

omnri
Starting Member

5 Posts

Posted - 2012-02-24 : 04:30:41

When I check on my DB health, I noticed this SQL statement below that is taking an awful long time to execute and sometimes causing a deadlock. I had asked my vendor but they have yet to reply. So I am seeking any expert advice here on how the query can be written in a better way.

quote:


SELECT DISTINCT VO.VESSEL_CODE, '2' AS APPROVAL_TYPE, 'RFQFSA' AS MODULE_NAME, '' AS APPROVAL_BY, P.PO_ITEM_ID, '' as INV_ITEM_ID, 
+P.PO_CODE + '-' + CONVERT(VARCHAR, P.REST_NO) + ' ' + P.PO_DESCRIPTION AS DESCRIPTION, V.VESSEL_ID,+ 
'<Quotation><ItemId>' P.PO_ITEM_ID +'</ItemId><ReqItemCode>' + P.PO_CODE + '-' + CONVERT(VARCHAR, P.REST_NO) + '</ReqItemCode><ItemCode>' + P.PO_CODE + '-' + CONVERT(VARCHAR, P.REST_NO) + '</ItemCode><ReqPort>' + ISNULL(P.PO_DESTPORTCODE,'') + '</ReqPort><ReqPortDescription>' + ISNULL(PORT.PORT_DESCRIPTION,'') + '</ReqPortDescription><POStatus>' + P.POSTATUS_ID + '</POStatus><OAQuoStatus>' + '10' + '</OAQuoStatus></Quotation>' AS KEY_VALUE,+ 
V.VESSEL_NAME 
FROM BP.BASS_POHEAD P 
LEFT JOIN DBO.BASS_REG_PORT PORT ON 
(
P.PO_DESTPORTCODE = PORT.PORT_CODE
), 
BP.BASS_POHEADSUPPLIER PS, DBO.BASS_VESSEL V, DBO.BASS_VESSEL_OWNER VO, 
(
SELECT V.USERID, V.VESSEL_ID, V.VESSELTRX_ID 
FROM 
(
SELECT U.USERID, U.OFFICE_CODE, U.APPROVAL_LEVEL, V.VESSEL_ID, V.VESSELTRX_ID 
FROM BP.BASS_OA_LEVEL L, BP.BASS_OA_USER U, BP.BASS_OA_VESSEL V 
WHERE L.ACTIVE_QUO = '1' AND U.OFFICE_CODE = L.OFFICE_CODE AND U.APPROVAL_LEVEL = L.LEVEL_CODE AND U.OFFICE_CODE = V.OFFICE_CODE 
AND U.APPROVAL_LEVEL = V.APPROVAL_LEVEL AND U.SEQ_KEY = V.APPROVAL_USER_SEQKEY AND (U.APPROVAL_LEVEL = 1 OR U.APPROVAL_LEVEL = 2) AND L.REC_DELETED = 0 
AND U.REC_DELETED = 0 AND V.REC_DELETED = 0
) V
) W 
WHERE P.VESSEL_ID = V.VESSEL_ID AND V.VESSELTRX_ID = VO.VESSELTRX_ID AND P.CATEGORY_ID = '2100' AND P.PO_ITEM_ID = PS.PO_ITEM_ID AND PS.OA_ADDRESS_STATUS_ID = '30' 
AND PS.REC_DELETED = 0 AND P.REC_DELETED = 0 AND VO.REC_DELETED = 0 AND V.REC_DELETED = 0 AND W.VESSEL_ID = V.VESSEL_ID AND W.VESSELTRX_ID = V.VESSELTRX_ID 
AND W.USERID = [TAG1]

I already have the Query Plan. It is *.sqlplan file format. If anyone need it to help me further, please give me a shoutout and I am more than happy to share it.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-24 : 05:10:06

Here's the sql with some formatting applied: (I've removed some of the concatenations) -- is this inside some dynamic sql?


SELECT DISTINCT
	VO.VESSEL_CODE
	, '2' AS APPROVAL_TYPE
	, 'RFQFSA' AS MODULE_NAME
	, '' AS APPROVAL_BY
	, P.PO_ITEM_ID
	, '' as INV_ITEM_ID
	, P.PO_CODE + '-' + CONVERT(VARCHAR, P.REST_NO) + ' ' + P.PO_DESCRIPTION AS DESCRIPTION
	, V.VESSEL_ID
	,'<Quotation><ItemId>' P.PO_ITEM_ID +'</ItemId><ReqItemCode>' + P.PO_CODE + '-' + CONVERT(VARCHAR, P.REST_NO) + '</ReqItemCode><ItemCode>' + P.PO_CODE + '-' + CONVERT(VARCHAR, P.REST_NO) + '</ItemCode><ReqPort>' + ISNULL(P.PO_DESTPORTCODE,'') + '</ReqPort><ReqPortDescription>' + ISNULL(PORT.PORT_DESCRIPTION,'') + '</ReqPortDescription><POStatus>' + P.POSTATUS_ID + '</POStatus><OAQuoStatus>' + '10' + '</OAQuoStatus></Quotation>' AS KEY_VALUE
	, V.VESSEL_NAME 
FROM
	BP.BASS_POHEAD P 
	LEFT JOIN DBO.BASS_REG_PORT PORT ON (
		P.PO_DESTPORTCODE = PORT.PORT_CODE
		)
	, BP.BASS_POHEADSUPPLIER PS
	, DBO.BASS_VESSEL V
	, DBO.BASS_VESSEL_OWNER VO

	, (
		SELECT V.USERID, V.VESSEL_ID, V.VESSELTRX_ID 
		FROM 
			(
			SELECT
				U.USERID
				, U.OFFICE_CODE
				, U.APPROVAL_LEVEL
				, V.VESSEL_ID
				, V.VESSELTRX_ID 
			FROM
				BP.BASS_OA_LEVEL L
				, BP.BASS_OA_USER U
				, BP.BASS_OA_VESSEL V 
			WHERE
				L.ACTIVE_QUO = '1'
				AND U.OFFICE_CODE = L.OFFICE_CODE
				AND U.APPROVAL_LEVEL = L.LEVEL_CODE
				AND U.OFFICE_CODE = V.OFFICE_CODE 
				AND U.APPROVAL_LEVEL = V.APPROVAL_LEVEL
				AND U.SEQ_KEY = V.APPROVAL_USER_SEQKEY
				AND (U.APPROVAL_LEVEL = 1 OR U.APPROVAL_LEVEL = 2)
				AND L.REC_DELETED = 0 
				AND U.REC_DELETED = 0
				AND V.REC_DELETED = 0
			) V
		) W 
WHERE
	P.VESSEL_ID = V.VESSEL_ID
	AND V.VESSELTRX_ID = VO.VESSELTRX_ID
	AND P.CATEGORY_ID = '2100'
	AND P.PO_ITEM_ID = PS.PO_ITEM_ID
	AND PS.OA_ADDRESS_STATUS_ID = '30' 
	AND PS.REC_DELETED = 0
	AND P.REC_DELETED = 0
	AND VO.REC_DELETED = 0
	AND V.REC_DELETED = 0
	AND W.VESSEL_ID = V.VESSEL_ID
	AND W.VESSELTRX_ID = V.VESSELTRX_ID 
	AND W.USERID = [TAG1]

SELECT DISTINCT is always troubling. It generally means the author couldn't work out how to get unique values / the schema is a mess.

The joins can be cleaned up by rewriting into ANSI syntax.

My guess is there are out of date or missing indexes.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

omnri
Starting Member

5 Posts

Posted - 2012-02-24 : 05:25:15

Thanks Charlie. Sorry that I didn't format it earlier. I was trying to, but I am not familiar with the formatting tools available in this forum yet.

1. My guess is the same too on the missing indexes. But what index should I create?
2. When I check on the query plan, the left join is costing 26%. Is there any way that left join can be improved or re-write?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-24 : 05:45:48

you might want to download a tool called:

Sql Sentry Plan Explorer. It's a lot nicer to naviage than the inbuilt ssms viewer.
http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp

IT has better problem highlighting as well.

The percentages presented are quite often misleading. What you are looking for are table scans / index scans / Key lookups.

Can you post the plan?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

omnri
Starting Member

5 Posts

Posted - 2012-02-24 : 09:17:38

Hi Charlie,

How do I post the plan? It is in .sqlplan file format. Should I capture the screenshot and post it here?

I will download the tool when I am back in the office and use it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources