January 26, 2012 at 5:27 am
Hi,
I am trying to run an SP which yields results after a long processing time due to a query within an inner join. Below is the script of the SP:
------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[KFR_CRA_Coding_Booking_Report]
WITH EXECUTE AS CALLER
--WITH RECOMPILE
AS
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
SELECT BOOKING_NUM, MB.SC_NUM AS [INVALID SC NUM], POL_LOCATION_CD, FIRST_POD_LOCATION_CD, PDL_LOCATION_CD, SERVICE_CD,
TRADE_CD, MB.CREATE_DT, [SHIPPER NAME],[BOOKING PARTY],[FORWARDER NAME], REEFER_FLG, USER_LAST_NAME
FROM MG_BOOKING MB
INNER JOIN
(
SELECT
BOOKING_ID,
MAX(CASE WHEN COMPANY_ROLE_CD = 'SH' THEN PARTY_NAME ELSE NULL END) AS [SHIPPER NAME],
MAX(CASE WHEN COMPANY_ROLE_CD = 'BK' THEN PARTY_NAME ELSE NULL END) AS [BOOKING PARTY],
MAX(CASE WHEN COMPANY_ROLE_CD = 'FW' THEN PARTY_NAME ELSE NULL END) AS [FORWARDER NAME]
FROM MG_BOOKING_PARTY
WHERE COMPANY_ROLE_CD IN ('SH','BK','FW')
GROUP BY BOOKING_ID
)MGP
ON MB.BOOKING_ID = MGP.BOOKING_ID
INNER JOIN MG_BOOKING_COMMODITY MBC
ON MB.BOOKING_ID = MBC.BOOKING_ID
INNER JOIN MG_ISEC_APPLICATION_USERS MIAU
ON MB.CREATE_USER_ID = MIAU.[USER_ID]
INNER JOIN MG_OFFICE MO
ON MB.BOOKING_OFFICE_CD = MO.OFFICE_CD
AND MO.ORGANIZATION_COMPANY_CD = 'KFR'
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD = 'F'
AND MB.CREATE_DT > = GETDATE() - 7
AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
OR MB.SC_NUM IS NULL)
SET ANSI_WARNINGS ON
-------------------------------------
does someone know if I can change some part of it to make it run more fast.
Thanks,
Paul
January 26, 2012 at 6:02 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
January 26, 2012 at 8:44 am
Posting a query plan would be helpful as well. Here's a video on how to do that if you don't know already.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply