Optimization hint for a query - SQL Server Forums

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)

Optimization hint for a query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-12-21 : 02:50:40

Please give me hint to optimize this query (in sql server 2008)

declare @JobID VARCHAR(8)

set @JobID = '11000492'

SELECT JobID,
-1 AS TransactionID,
'-1' AS TransactionType,
CreatedDate,
importjob.fkBranchID,
importjob.fkCompanyID,
fkUserID,
fkJobProcessingStatusID,
fkJobValidationStatusID,
imjOriginalJobID,
imjSubmissionDate,
imjTotalFiles,
imjJobSize,
Isnull((SELECT Count(*)
FROM HEADERRECORD
WHERE fkjobID = @JobID), 0) AS HeaderRecordCount,
SEGFileStatus,
hdrModifiedBy = Isnull(up16.usrUserName, 'N/A'),
SEGModifiedBy = Isnull(up1.usrUserName, 'N/A'),
MPUModifiedBy = Isnull(up2.usrUserName, 'N/A'),
MCRModifiedBy = Isnull(up5.usrUserName, 'N/A'),
ImportJob.hdrModifiedDate,
PBCModifiedDate,
PARModifiedDate,
IMRModifiedDate,
OCIModifiedDate,
PSRModifiedDate,
CCRModifiedDate,
PSRCreatedDate,
CCRCreatedDate,
Isnull(ociIsPOValid, 0) AS ociIsPOValid,
Isnull(cptIsPOValid, 0) AS cptIsPOValid,
Isnull(ccrIsPOValid, 0) AS ccrIsPOValid,
fkPostalOneValidationStatusId,
HeaderRecord.hdrMaildatPresentationCategory,
imjPostalOneValidationDuration,
imjPostalOneValidationLastRunAt
FROM ImportJob
INNER JOIN HeaderRecord
ON HeaderRecord.fkJobID = ImportJob.JobID
AND HeaderRecord.hdrHistoryStatus = 'C'
LEFT JOIN UserProfile up1
ON up1.UserID = ImportJob.SEGModifiedBy
LEFT JOIN UserProfile up2
ON up2.UserID = ImportJob.MPUModifiedBy
LEFT JOIN UserProfile up3
ON up3.UserID = ImportJob.MPAModifiedBy
LEFT JOIN UserProfile up4
ON up4.UserID = ImportJob.CPTModifiedBy
LEFT JOIN UserProfile up5
ON up5.UserID = ImportJob.MCRModifiedBy
LEFT JOIN UserProfile up16
ON up16.UserID = ImportJob.imjModifiedBy
LEFT JOIN UserProfile up17
ON up17.UserID = ImportJob.PBCModifiedBy
LEFT JOIN UserProfile up18
ON up18.UserID = ImportJob.CCRModifiedBy
WHERE JobID = @JobID

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-12-21 : 03:54:49

What does your query plan tell you?

What size record sets are we talking?

What indexes do you currently have?

We really need more information. I'm sure thee are a few ways to make this faster, but really no point unless we figure out where your current bottleneck is.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-21 : 04:14:19

Hi,

I modified your query. I have calulated count and assigned to a variable then that variable used in the query.

declare @JobID VARCHAR(8)
declare @HeaderRecordCount INT
set @JobID = '11000492'

SELECT @HeaderRecordCount=Count(*)
FROM HEADERRECORD
WHERE fkjobID = @JobID

SELECT JobID,
-1 AS TransactionID,
'-1' AS TransactionType,
CreatedDate,
importjob.fkBranchID,
importjob.fkCompanyID,
fkUserID,
fkJobProcessingStatusID,
fkJobValidationStatusID,
imjOriginalJobID,
imjSubmissionDate,
imjTotalFiles,
imjJobSize,
Isnull(@HeaderRecordCount, 0) as HeaderRecordCount,
SEGFileStatus,
hdrModifiedBy = Isnull(up16.usrUserName, 'N/A'),
SEGModifiedBy = Isnull(up1.usrUserName, 'N/A'),
MPUModifiedBy = Isnull(up2.usrUserName, 'N/A'),
MCRModifiedBy = Isnull(up5.usrUserName, 'N/A'),
ImportJob.hdrModifiedDate,
PBCModifiedDate,
PARModifiedDate,
IMRModifiedDate,
OCIModifiedDate,
PSRModifiedDate,
CCRModifiedDate,
PSRCreatedDate,
CCRCreatedDate,
Isnull(ociIsPOValid, 0) AS ociIsPOValid,
Isnull(cptIsPOValid, 0) AS cptIsPOValid,
Isnull(ccrIsPOValid, 0) AS ccrIsPOValid,
fkPostalOneValidationStatusId,
HeaderRecord.hdrMaildatPresentationCategory,
imjPostalOneValidationDuration,
imjPostalOneValidationLastRunAt
FROM ImportJob
INNER JOIN HeaderRecord
ON HeaderRecord.fkJobID = ImportJob.JobID
AND HeaderRecord.hdrHistoryStatus = 'C'
LEFT JOIN UserProfile up1
ON up1.UserID = ImportJob.SEGModifiedBy
LEFT JOIN UserProfile up2
ON up2.UserID = ImportJob.MPUModifiedBy
LEFT JOIN UserProfile up3
ON up3.UserID = ImportJob.MPAModifiedBy
LEFT JOIN UserProfile up4
ON up4.UserID = ImportJob.CPTModifiedBy
LEFT JOIN UserProfile up5
ON up5.UserID = ImportJob.MCRModifiedBy
LEFT JOIN UserProfile up16
ON up16.UserID = ImportJob.imjModifiedBy
LEFT JOIN UserProfile up17
ON up17.UserID = ImportJob.PBCModifiedBy
LEFT JOIN UserProfile up18
ON up18.UserID = ImportJob.CCRModifiedBy
WHERE JobID = @JobID

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-21 : 04:25:56

Post table and index definitions and please upload the execution plan somewhere.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources