Query taking too long...

[betachristopher]

I am running the following using SQL Query Analyzer. I keep canceling it after 30 minutes. As of right now, there is only one record in my database that it should return, so it shouldn't take that long. Can anyone offer suggestions to speed up this query?

SELECT RTRIM(TEST.EXTACCESSION) AS 'External Acc#', ACCN.OURACCN AS 'Our Acc#',
RTRIM(TEST.VISITDESC) AS 'Visit Name', SITE.CODE AS 'Site#', TEST.PATALLOC AS 'Subject#', TEST.INITIALS AS 'Patient Initials', REPLACE(CONVERT(VARCHAR(11), TEST.DOB, 106), ' ', '-') AS [DD-Mon-YYYY],
TEST.SEX AS 'Gender', REPLACE(CONVERT(VARCHAR(11), TEST.COLLECT_DT, 106), ' ', '-') AS [DD-Mon-YYYY], TEST.COLLECT_TM AS 'Collection Time', SPEC_TYPE.DSCR AS 'Spec Location',
LINKED05.DSCR AS 'DX1-Adequacy', LINKED06.DSCR AS 'DX2-Surface', LINKED07.DSCR AS 'DX3-Matrix', LINKED08.DSCR AS 'DX4-Cell Distrubution', DX1.DSCR AS 'DX5-Cell Pop Viabilility',
DX2.DSCR AS 'DX6-Subchondral Bone', DX3.DSCR AS 'DX7-Cartilage Mineralization', DX4.DSCR AS 'DX8-Toluidine Blue Stain Comment', DX5.DSCR AS 'DX9-Trichrome Stain Comment', RECOMEND.DSCR AS 'DX10-Control Stain Comment',
'OUR MEDICAL LABORATORIES, INC., 123 DEFAULT RD, SUITE 123' AS 'Our Name and Address'
FROM ACCN, TEST, SITE, SPEC_TYPE, LINKED05, LINKED06, LINKED07, LINKED08, DX1, DX2, DX3, DX4, DX5, RECOMEND
WHERE TEST.NO = ACCN.NO
AND TEST.SITE = SITE.NO
AND TEST.SPECTYPE = SPEC_TYPE.NO
AND TEST.LINKED05 = LINKED05.NO
AND TEST.LINKED06 = LINKED06.NO
AND TEST.LINKED07 = LINKED07.NO
AND TEST.LINKED08 = LINKED08.NO
AND TEST.DX1 = DX1.NO
AND TEST.DX2 = DX2.NO
AND TEST.DX3 = DX3.NO
AND TEST.DX4 = DX4.NO
AND TEST.DX5 = DX5.NO
AND TEST.RECOMEND = RECOMEND.NO
AND TEST.PROJECT = 692
AND TEST.SIGNOUT_DT BETWEEN (GETDATE()-1) AND GETDATE()

 

[Crowley16]

firstly, this should go in the programming sql server forum instead of here...

secondly, you should be using ansi standard join syntax instead of doing it in the where...

thirdly, query speed depends on multiple factors, not just the number of records you return, so check your tables, indexes, locks...

--------------------
Procrastinate Now!

 

[betachristopher]

I'm sorry for the wrong location.
Do you mean like this?

SELECT RTRIM(TEST.EXTACCESSION) AS 'External Acc#', ACCN.OURACCN AS 'OUR Acc#',
RTRIM(TEST.VISITDESC) AS 'Visit Name', SITE.CODE AS 'Site#', TEST.PATALLOC AS 'Subject#', TEST.INITIALS AS 'Patient Initials', REPLACE(CONVERT(VARCHAR(11), TEST.DOB, 106), ' ', '-') AS [DD-Mon-YYYY],
TEST.SEX AS 'Gender', REPLACE(CONVERT(VARCHAR(11), TEST.COLLECT_DT, 106), ' ', '-') AS [DD-Mon-YYYY], TEST.COLLECT_TM AS 'Collection Time', SPEC_TYPE.DSCR AS 'Spec Location',
LINKED05.DSCR AS 'DX1-Adequacy', LINKED06.DSCR AS 'DX2-Surface', LINKED07.DSCR AS 'DX3-Matrix', LINKED08.DSCR AS 'DX4-Cell Distrubution', DX1.DSCR AS 'DX5-Cell Pop Viabilility',
DX2.DSCR AS 'DX6-Subchondral Bone', DX3.DSCR AS 'DX7-Cartilage Mineralization', DX4.DSCR AS 'DX8-Toluidine Blue Stain Comment', DX5.DSCR AS 'DX9-Trichrome Stain Comment', RECOMEND.DSCR AS 'DX10-Control Stain Comment',
'OUR MEDICAL LABORATORIES, INC., 123 DEFAULT RD, SUITE 123' AS 'OUR Name and Address'
FROM ACCN JOIN TEST ON ACCN.NO = TEST.NO
JOIN SITE ON TEST.SITE = SITE.NO
JOIN SPEC_TYPE ON SPEC_TYPE.NO = TEST.SPECTYPE
JOIN LINKED05 ON LINKED05.NO = TEST.LINKED05
JOIN LINKED06 ON LINKED06.NO = TEST.LINKED06
JOIN LINKED07 ON LINKED07.NO = TEST.LINKED07
JOIN LINKED08 ON LINKED08.NO = TEST.LINKED08
JOIN DX1 ON DX1.NO = TEST.DX1
JOIN DX2 ON DX2.NO = TEST.DX2
JOIN DX3 ON DX3.NO = TEST.DX3
JOIN DX4 ON DX4.NO = TEST.DX4
JOIN DX5 ON DX5.NO = TEST.DX5
JOIN RECOMEND ON RECOMEND.NO = TEST.RECOMEND
WHERE
TEST.PROJECT = 692
AND TEST.SIGNOUT_DT BETWEEN (GETDATE()-1) AND GETDATE()