Posted by Erland Sommarskog on 07/28/06 07:29

Eckhart (n.kopalley@gmail.com) writes:
> Dear All, Plz help me in optimising the following query,
> Reduce repeatable reads from the table via select ,ythe table sare not
> having referntial integrity constarints ,relations

I'm sorry, but you cannot just post a 180-line query to the newsgroup and
hope that someone will cast a magic spell. To do proper tuning requires
full knowledge of table and index, and also indication of data distribution.

I can give some quick general recommendations.

> from datalogs.dbo.translogs where transactiondate >= @ydate and
> transactiondate < @date and servicename in
> ('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV','QATAR2900','SIFY'))

For this query to perform well, the optimal would be a clusterd index
on (transctiondate, servicename) or (servicename, transactiodate). In which
order the columns should appear it's difficult to tell, because it
depends on which condition that have the best filter mechanism.

I would also recommend replacing @ydate with

dateadd(DAY, -1, @date)

Then the optimizer gets an idea of how long the interval is. Keep in mind
that the optimizer do not know the run-time value of variables.

> (SELECT CASE ua
> when 'unknown' then null
> else ua
> end) as ua,

I don't think it matters for performance, but using a sub-SELECT for this
adds additional noise. Better to simply write:

CASE ua WHEN 'unknown' THEN NULL ELSE ua END AS ua

Or even shorter:

nullif(ua, 'unknown') AS ua

> (select musiclabel from datalogs.dbo.cont_master where contentid =
> datalogs.dbo.translogs.contentid) as musiclable,

Here, on, the other hand is a potential performance stealer. In my
experiences nested queries in the SELECT list gives bad performance.
Better is to join to datalogs.dbo.cont_master in the main FROM clause
of the query. You have quite a few of these, and rewriting them into
joins, can very well make your day.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]