Need help in optimizing query.[too long to comlet]

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)

Need help in optimizing query.[too long to comlet]

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-09-03 : 01:14:08

Need help in optimizing following query.
I am having long delay if i am opening the commented lines in either of inner or outer query.

SELECT X.*
FROM
(
SELECT CON_SHIPPER_CONTENT_ID
,CON_SA_CREATE_DATETIME
,CON_PK_JOB_ID
,CON_PK_DATABASE_CONTAINER_ID
,CON_MAILCLASS
,CON_PROCESSINGCATEGORY
,CON_ENTRY_POINT_PHYSICAL
,VerificationLocation
,CON_ZONE_SKIPPING_INDICATOR
,PriorityMailIndicator
,CON_NEWS_INDICATOR
,CON_DESTINATION_DISCOUNT_INDICATOR
,CON_RATETYPE
,CON_SCHEDULED_INDUCTION_DATE
,CON_INHOME_START_DATETIME
FROM MDB_CONTENT_INFO
WHERE ((CON_CONSIGNEE_ID IS NULL) OR (CON_CONSIGNEE_ID = 'N/A'))
AND ((CON_FAST_CONTENT_ID IS NULL) OR (CON_FAST_CONTENT_ID = 'N/A'))
AND (SAC_IsLinked is null or SAC_IsLinked = 'False' )
AND (SAC_SchedulerContentID is null)
AND ShipperApptRequestID is null
AND (CON_SHIPPER_CONTENT_ID not in (SELECT items FROM dbo.GetCommaSplitValue(@InputVariable,',')))
AND (@MailClass = '-1-' OR CON_MailClass = @MailClass)
And (@MailShape = '-1-' OR CON_ProcessingCategory = @MailShape)
And (@InductionDate = '-1-' OR CON_SCHEDULED_INDUCTION_DATE = @InductionDate)
And (@VerificationLocation = '-1-' OR VerificationLocation like '%' + @VerificationLocation+ '%')
And (@LocaleKey = '-1-' OR CON_ENTRY_POINT_PHYSICAL like '%' + @LocaleKey+ '%')
And (@IsPriorityMail = '-1-' OR ( PriorityMailIndicator =
CASE WHEN @IsPriorityMail = 'True' THEN 1
WHEN @IsPriorityMail ='False' THEN 0
END
))
And (@IsZoneSkip = '-1-' OR ( CON_ZONE_SKIPPING_INDICATOR =
CASE WHEN @IsZoneSkip = 'True' THEN 1
WHEN @IsZoneSkip ='False' THEN 0
END
))
And (@IsNews = '-1-' OR ( CON_NEWS_INDICATOR =
CASE WHEN @IsNews = 'True' THEN 1
WHEN @IsNews ='False' THEN 0
END
))
And (@IsDestinationDiscount = '-1-' OR ( CON_DESTINATION_DISCOUNT_INDICATOR =
CASE WHEN @IsDestinationDiscount = 'True' THEN 1
WHEN @IsDestinationDiscount ='False' THEN 0
END
))
AND ISDATE(CON_SCHEDULED_INDUCTION_DATE) = 1
And ISDATE(CON_INHOME_START_DATETIME) = 1
AND CON_ENTRY_POINT_PHYSICAL is not null

) X
WHERE convert(datetime, X.CON_SCHEDULED_INDUCTION_DATE,112) >= GetDate()
And convert(datetime, X.CON_INHOME_START_DATETIME,112) >= GetDate()
/*AND CON_ENTRY_POINT_PHYSICAL is not null
And LEN(CON_ENTRY_POINT_PHYSICAL) > 6
AND Right(CON_ENTRY_POINT_PHYSICAL, 6) in
(
Select RIGHT(Rtrim(Ltrim(RAL_DROPSITE_KEY)), 6)
FROM REF_ADDRESS_LIST_FILE
Where RAL_DROPSITE_KEY IS NOT NULL
AND RAL_DS_DLVYADD_STATEABBRV IS NOT NULL
AND RTRIM(LTRIM(RAL_DS_DLVYADD_STATEABBRV))
in (
SELECT RTRIM(LTRIM(RAL_DS_PHYSADD_STATEABBRV))
FROM REF_ADDRESS_LIST_FILE
INNER JOIN
(
SELECT DISTRICTS.DIS_PK_ZIP_CODE
FROM DISTRICTS
WHERE DISTRICTS.DIS_PK_DISTRICT_ID in
(
SELECT UDR_FK_DISTRICT_ID = CAST(UDR_FK_DISTRICT_ID as int)
FROM USER_DISTRICT
WHERE UDR_FK_LOGIN_ID = @UserLoginID
)
)x ON x.DIS_PK_ZIP_CODE = LEFT(RAL_DS_PHYSADD_ZIP4CODE,3)
)
)
*/
Order by CON_PK_JOB_ID, CON_PK_DATABASE_CONTAINER_ID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-03 : 01:36:31

You haven't really posted enough information.

Please post the entire query, the indexes that support the query, the estimated execution plan, the statistics of the query including reads, etc...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-09-03 : 02:15:26

updated original post

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-03 : 02:26:46

You've only posted the code. Please post the other things, plus some sample data and a description of what the query is supposed to be doing.

It is very hard to look at code, especially without formatting, and not have additional information and be able to optimize it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-03 : 03:44:31

Also asked and answered here http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/32604047-54d5-4f15-9e4c-4ebc11c40f08
and here http://www.sqlservercentral.com/Forums/Topic980038-392-1.aspx

N 56°04'39.26"
E 12°55'05.63"

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-09-03 : 04:58:56

Thanks peso

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources