Optimization tip for the query

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 tip for the query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-09-24 : 19:49:32

Any Idea how to let optimize following query
Relation is like
ContainerSummaryRecord is parent of ContainerQuantityRecord, and ContainerQuantityRecord is parent of PackageQuantityRecord

I have to check particualr type of record of ContainerSummaryRecord should lie in both ContainerQuantityRecord and PackageQuantityRecord

SELECT fkSegmentID
,csmContainerID
,csmRecordRowNumber
FROM ContainerSummaryRecord
where ContainerSummaryRecord.fkJobID = @fkMAJobID
AND ContainerSummaryRecord.csmIsDeleted =0
and isnull(csmSiblingContainerIndicator,'') <> 'Y'
AND csmCSMRecordStatus in ('O', 'I', 'U')
AND csmContainerID not in (select parent.csmParentContainerReferenceID from ContainerSummaryRecord parent where parent.fkJobID = @fkMAJobID)
AND (
csmContainerID not in
(
select fkContainerID from ContainerQuantityRecord
where fkJobID = ContainerSummaryRecord.fkJobID
AND cqtIsDeleted = 0
AND cqtCQTRecordStatus NOT LIKE 'D'
)
OR
csmContainerID not in
(
select fkContainerID from ContainerQuantityRecord
where fkJobID = ContainerSummaryRecord.fkJobID
AND cqtIsDeleted = 0
AND cqtCQTRecordStatus NOT LIKE 'D'
AND cqtCQTDatabaseID In
(
select fkCQTDatabaseID from PackageQuantityRecord
where fkJobID = ContainerSummaryRecord.fkJobID
AND pqtIsDeleted = 0
AND pqtPQTRecordStatus NOT LIKE 'D'
)
)
)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-25 : 00:56:42

[code]SELECT csr.fkSegmentID
,csr.csmContainerID
,csr.csmRecordRowNumber
FROM ContainerSummaryRecord csr
LEFT JOIN ContainerSummaryRecord parent
ON parent.csmParentContainerReferenceID=csr.csmContainerID
AND parent.fkJobID = @fkMAJobID
LEFT JOIN ContainerQuantityRecord cqr
ON cqr.fkJobID = csr.fkJobID
AND cqr.fkContainerID =csm.csmContainerID
AND cqr.cqtIsDeleted = 0
AND cqr.cqtCQTRecordStatus NOT LIKE 'D'
LEFT JOIN PackageQuantityRecord pqr
ON pqr.fkJobID = csr.fkJobID
AND pqr.fkCQTDatabaseID = cqr.cqtCQTDatabaseID
AND pqr.pqtIsDeleted = 0
AND pqr.pqtPQTRecordStatus NOT LIKE 'D'
where csr.fkJobID = @fkMAJobID
AND csr.csmIsDeleted =0
and isnull(csr.csmSiblingContainerIndicator,'') <> 'Y'
AND csr.csmCSMRecordStatus in ('O', 'I', 'U')
AND parent.csmParentContainerReferenceID IS NULL
AND (pqr.fkCQTDatabaseID IS NULL
OR cqr.fkContainerID IS NULL )
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources