Problem with Table/Index Scan cost | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem with Table/Index Scan cost

Hello, I am trying to tune the following query –
————————————————————
SELECT top 80
CASE
WHEN (O.SEQ IN (SELECT DISTINCT ORIGSEQ
FROM #TERMTOSTRING
WHERE TERMSEQ < 0)) THEN (SELECT T.TERMSEQ – 1
FROM #TERMTOSTRING T
WHERE O.SEQ = T.TERMSEQ
AND T.TERMSEQ < 0)
ELSE – 1
END,
O.SEQ,
COUNT(DISTINCT RELID) * CASE
WHEN (O.ORIGINALSTRING IN (SELECT NOISE_WORD
FROM FTNOISEWORDS)) THEN – 5
ELSE 1
END,
CO.stringscore STRINGSCORE,
ORIGINALSTRING
FROM AGG_CONTAINSORIG CO,
AGG_ORIG O
WHERE CO.RELID IN (SELECT DISTINCT RELID
FROM AGG_CONTAINSORIG
WHERE ORIGSEQ IN (SELECT DISTINCT ORIGSEQ
FROM #TERMTOSTRING))

AND O.SEQ NOT IN (SELECT DISTINCT ORIGSEQ
FROM #TERMTOSTRING
WHERE TERMSEQ >= 0)
AND CO.ORIGSEQ = O.SEQ
AND (CO.FROMORIG + CO.TOORIG) > 0
GROUP BY O.SEQ,
O.ORIGINALSTRING,
HASFUNC,
HASPSEUDOSUBS,
HASSECONDSUBS,
HASSUBS,
stringscore –STRINGSCORE is a computed column calculated from HASFUNC,HAS…etc.
ORDER BY STRINGSCORE DESC
————————————————————
Currently its taking about 5-6 seconds and our aim is to bring it down to around < 1 sec. When executed through SQL Analyzer we found out that, the highest cost is due to the scanning of table "AGG_CONTAINSORIG". The table contains around 2.2 million records but the record size is small. Due to the structure of query, this table is being scanned twice and taking most of the time of total query execution.. Proper indexes are present on the table. Even if the index is scanned or table is scanned maximum percentage of total time spent in scanning of index/table. I was just wondering, if there is any way by which I could reduce this scan time. Thanks and Regards,
Shailesh


I assume you have indexed ( clustered ) table AGG_CONTAINSORIG on column ORIGSEQ.
If not create one.
Also in the subquery try avoiding distinct and rather use exists clause like this — WHERE
exists ( SELECT 1
FROM AGG_CONTAINSORIG a
WHERE exists
( SELECT 1 FROM #TERMTOSTRING a where a.origseq = b.origseq)
and a.relid = co.relid
) Your temp table #TERMTOSTRING usage does not seem correct performance wise I think. Because in the select clause it is used in similar way twice.
Try this… I rewrote little bit here … SELECT top 80
/*
CASE
WHEN (O.SEQ IN (SELECT DISTINCT ORIGSEQ FROM #TERMTOSTRING WHERE TERMSEQ < 0))
THEN (SELECT T.TERMSEQ – 1 FROM #TERMTOSTRING T WHERE O.SEQ = T.TERMSEQ AND T.TERMSEQ < 0)
ELSE – 1
END,*/
(isnull(T.TERMSEQ, 0 ) – 1 ),
O.SEQ,
COUNT(DISTINCT RELID) * CASE WHEN (O.ORIGINALSTRING IN (SELECT NOISE_WORD FROM FTNOISEWORDS)) THEN – 5
ELSE 1 END,
CO.stringscore STRINGSCORE,
ORIGINALSTRING
FROM
AGG_CONTAINSORIG CO
inner join AGG_ORIG O
on CO.ORIGSEQ = O.SEQ and
(CO.FROMORIG + CO.TOORIG) > 0
left join#termtostring t
ONT.TERMSEQ = O.SEQ and
T.TERMSEQ < 0
/*
WHERE
CO.RELID IN (SELECT DISTINCT RELID FROM AGG_CONTAINSORIG
WHERE ORIGSEQ IN (SELECT DISTINCT ORIGSEQ FROM #TERMTOSTRING))
*/
WHERE
exists ( SELECT 1
FROM AGG_CONTAINSORIG a
WHERE exists ( SELECT 1 FROM #TERMTOSTRING b where a.origseq = b.origseq)
and a.relid = co.relid )
–AND O.SEQ NOT IN (SELECT DISTINCT ORIGSEQ FROM #TERMTOSTRING WHERE TERMSEQ >= 0)
and not exists ( select 1 from #TERMTOSTRING t1 where t1.TERMSEQ >= 0 and t1.ORIGSEQ = O.SEQ ) GROUP BY O.SEQ,
O.ORIGINALSTRING,
HASFUNC,
HASPSEUDOSUBS,
HASSECONDSUBS,
HASSUBS,
stringscore –STRINGSCORE is a computed column calculated from HASFUNC,HAS…etc.
ORDER BY
STRINGSCORE DESC

Hi, Actually ORIGSEQ column did not have clustered index. After creating one, it gave very good performance. Temporary table is also causing a problem, so I created a permenant table. And it worked fine. The timing came down from 6 secs to 2 secs. But (isnull(T.TERMSEQ, 0 ) – 1 ) part could not be used in select, since its not present in group by clause. Thanks a lot for ur help. I had been working on this for last 3-4 days but couldnt find out the solution. Thanks again. Cheers,
Shailesh

]]>