February 22, 2008 at 7:48 am
Hello,
The sql statement below works perfectly until i notice that the performance is quite slow as compared to having no subquery. How can i improve this performance?
One more, why is it that the order result is different when i remove the subquery and put directly the criteria in IN clause.
[font="Courier New"]SELECT
TOP 500 MuniAnnuals.DOCID, MuniAnnuals.FYE, MuniAnnuals.DOCNAME, MuniAnnualsdocument.DocumentID,
MuniAnnuals.StateID, MuniAnnuals.pdfsid, MuniAnnuals.MuniAnnualsID
FROM
MuniAnnuals INNER JOIN
MuniAnnualsDocument ON MuniAnnuals.MuniAnnualsID = MuniAnnualsdocument.MuniAnnualsID
WHERE
(MuniAnnuals.ObligorID NOT IN (28111, 28109)) AND
(MuniAnnualsdocument.DocumentID IN (
SELECT doctypeid_int
FROM
ANNUALSQC_DOCTYPE
WHERE
annualsquery_doctype_int = 0 AND memberid_int = 2020
)
)
ORDER BY MuniAnnuals.FYE DESC[/font]
Pls help
February 22, 2008 at 11:31 am
Give this a try:
SELECT TOP 500
MuniAnnuals.DOCID,
MuniAnnuals.FYE,
MuniAnnuals.DOCNAME,
MuniAnnualsdocument.DocumentID,
MuniAnnuals.StateID,
MuniAnnuals.pdfsid,
MuniAnnuals.MuniAnnualsID
FROM
dbo.MuniAnnuals MuniAnnuals
INNER JOIN dbo.MuniAnnualsDocument MuniAnnualsDocument
ON (MuniAnnuals.MuniAnnualsID = MuniAnnualsdocument.MuniAnnualsID)
INNER JOIN dbo.ANNUALSQC_DOCTYPE ANNUALSQC_DOCTYPE
on (MuniAnnualsdocument.DocumentID = ANNUALSQC_DOCTYPE.doctypeid_int)
WHERE
(MuniAnnuals.ObligorID NOT IN (28111, 28109)) AND
(ANNUALSQC_DOCTYPE.annualsquery_doctype_int = 0
AND ANNUALSQC_DOCTYPE.memberid_int = 2020)
ORDER BY
MuniAnnuals.FYE DESC
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply