Re: optimising the following query

Eckhart (n.kopalley@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)
Loading