Optimizing SQL queries

Question

Optimizing SQL queries

Imke Cronje

SSCarpal Tunnel

Points: 4822
More actions
December 17, 2010 at 1:11 am

#230890

Hi All,
I am sitting with a situation where I have a table that is 27 MB in size and a query that runs against it that possibly needs to be optimized. The query takes less than a sec to bring back the data however I have run that estimated execution plan and it shows that in the query there is a Clustered Index Scan of 74%. See query below.
SELECT
*,
table2.column1 AS <columnname>
FROM
table1
LEFT OUTER JOIN table2
ON
table1.columnn2 = table2.column2
JOIN [PARAMETER]
ON
[PARAMETER] = 'ColumnData'
AND column3> (GETDATE() - CAST(PARAMETERVALUE AS INT))
AND column4 = 'columndata'
WHERE
table1.column2 = 5000
ORDER BY
column3 DESC
The number of records that is returned amounts to 100. The is a clustered index on the primary key. I have added an non-clustered index to table1.column2. The query plan is still showing that a clustered index scan is at 75% cost. Can anyone please assist. Does this query need to be optimized any further?
Regards
IC

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Imke Cronje SSCarpal Tunnel Points: 4822 More actions · Answer 2

I can't post that information:

See statistics results:

Table 'Table1'. Scan count 1, logical reads 3512, physical reads 3, read-ahead reads 3508, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'table2'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 39 ms.

Imke Cronje SSCarpal Tunnel Points: 4822 More actions · Answer 3

Hi All,

I create the missing index as the estimated query plan suggested which improves statistics on the query.

Thanks!!

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Imke Cronje (12/17/2010)
I can't post that information:

It's very hard (near impossible) to provide correct advice on performance problems without seeing what I asked for. Without knowing what the table looks like and what indexes are already there, any suggestions can be completely wrong.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass