optimising the following query

[Eckhart]

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

CREATE proc Rolex136Sync
as
DECLARE @date varchar(50),@yd ate varchar(50)
print CONVERT(char(11 ),(GETDATE()-1),100)
SET @date =
substring(CONVE RT(char(11),(GE TDATE()),100),5 ,2)+'-'+substring(CON VERT(char(11),( GETDATE()),100) ,1,3)+'-'+substring(CON VERT(char(11),( GETDATE()),100) ,8,4)
SET @ydate =
substring(CONVE RT(char(11),(GE TDATE()-1),100),5,2)+'-'+substring(CON VERT(char(11),( GETDATE()-1),100),1,3)+'-'+substring(CON VERT(char(11),( GETDATE()-1),100),8,4)
Print @date
Print @ydate
insert into
biiod.dbo.data_ trans_currentda y_test(MobileNo ,UA,MessageID,C ontentID,Descri ption,MusicLabe l,CPID,CPName,C ontentType,Cate gory,SubCategor y,TransactionDa te,Units,Unitpr ice,Shortcode,S ervicecode,Oper atorID,CatID,Su bCatID,SpecialP ackage,Royaltie s,
Operator,Circle ,OPGPName)
(select mobileno,
(SELECT CASE ua
when 'unknown' then null
else ua
end) as ua,
(select case remarks
when 'unknown' then null
else remarks
end) as remarks,
contentid,
(select case description
when 'unknown' then null
else description
end) as description,
(select musiclabel from datalogs.dbo.co nt_master where contentid =
datalogs.dbo.tr anslogs.content id) as musiclable,
(select cpid from datalogs.dbo.co ntentprovider where cpname =
datalogs.dbo.tr anslogs.cpname) as cpid,
cpname,
contenttype,
(select catname from datalogs.dbo.co nt_Catg where catid in (select
catid from cont_master where contentid =
datalogs.dbo.tr anslogs.content id)) as category,
(select subcatname from datalogs.dbo.co nt_subCatg where subcatid in
(select subcatid from cont_master where contentid =
datalogs.dbo.tr anslogs.content id)) as subcategory,
transactiondate ,1 as Units, price,
(select case servicename
when 'AIRTELIVE' then remarks
when 'ALCOMBOPACKREG ' then remarks
when 'HINDI' then remarks
when 'NOKIAGAL' then remarks
when 'SUDOKU' then remarks
when 'SUDOKU_APP' then remarks
else NULL
end) as SHORTCODE,
servicename,
(select case servicename
when 'TSTTNEWS' THEN 600
when 'TSTTWAP' THEN 600
when 'TSTT_MMS' THEN 600
when 'AKTEL' THEN 300
when 'TELEMOVIL' THEN 700
when 'COMCEL' THEN 701
when 'QATAR2900' THEN 1
ELSE
(select operatorid from datalogs.dbo.op erator where phoneseries =
substring(datal ogs.dbo.translo gs.mobileno,1,l en(phoneseries) ))
end) as operatorid,
(select catid from datalogs.dbo.co nt_master where contentid =
datalogs.dbo.tr anslogs.content id) as catid,
(select subcatid from datalogs.dbo.co nt_master where contentid =
datalogs.dbo.tr anslogs.content id) as subcatid,
(select specialpackage from datalogs.dbo.co nt_master where contentid =
datalogs.dbo.tr anslogs.content id) as specialpackage,
(select Royalties from datalogs.dbo.co nt_master where contentid =
datalogs.dbo.tr anslogs.content id) as Royalties,
(select case servicename
when 'AKTEL' then 'Aktel'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'TeleMovil'
when 'COMCEL' THEN 'COMCEL'
when 'TSTTNEWS' then 'TSTT'
when 'TSTTWAP' then 'TSTT'
when 'TSTT_MMS' then 'TSTT'
when 'ALCLICKWIN6464 ' then 'Airtel'
when 'ALMMSPORTAL' then 'Airtel'
when 'ALMMSSMSDWN' then 'Airtel'
when 'ALMYALBUM646' then 'Airtel'
when 'HINDU6397' then
substring(remar ks,1,PATINDEX(' %.6397.%',remar ks)-1)
else
(select OPname from datalogs.dbo.op erator where phoneseries =
substring(datal ogs.dbo.translo gs.mobileno,1,l en(phoneseries) ))
end) as Operator,
(select case servicename
when 'AKTEL' then 'Bangladesh'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'El Salvador'
when 'COMCEL' THEN 'Gautemala'
when 'TSTTNEWS' then 'Trinidad'
when 'TSTTWAP' then 'Trinidad'
when 'TSTT_MMS' then 'Trinidad'
when 'HINDU6397' then substring(remar ks,PATINDEX('%. 6397.%',remarks ) +
6,len(remarks)-PATINDEX('%-%',remarks))
else
(select Circlename from datalogs.dbo.op erator where phoneseries =
substring(datal ogs.dbo.translo gs.mobileno,1,l en(phoneseries) ))
end) as Circle,
(select case servicename
when 'AKTEL' then 'Aktel'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'TeleMovil'
when 'COMCEL' THEN 'COMCEL'
when 'TSTTNEWS' then 'TSTT'
when 'TSTTWAP' then 'TSTT'
when 'TSTT_MMS' then 'TSTT MMS'
when 'ALCLICKWIN6464 ' then 'Airtel Click Win 646'
when 'ALMMSPORTAL' then 'Airtel MMS'
when 'ALMMSSMSDWN' then 'Airtel MMS SMS'
when 'ALMYALBUM646' then 'Airtel My Album'
when 'HINDU6397' then 'Hindu 6397'
else
(select OPname from datalogs.dbo.op erator where phoneseries =
substring(datal ogs.dbo.translo gs.mobileno,1,l en(phoneseries) ))
end) as OPGPName
from datalogs.dbo.tr anslogs where transactiondate >= @ydate and
transactiondate < @date and servicename in
('AIRTELMMS_SUB ','ALMYALBUM646 ','HINDU6397',' MTV','QATAR2900 ','SIFY'))
go

[Erland Sommarskog]

Re: optimising the following query

Eckhart (n.kopalley@gma il.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.tr anslogs 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.co nt_master where contentid =
datalogs.dbo.tr anslogs.content id) 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.co nt_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@sommarsk og.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

[--CELKO--]

Re: optimising the following query

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

CREATE PROCEDURE Rolex136Sync
-- proc names are "<verb><object> " in ISO 11179
AS
DECLARE
@date VARCHAR(50), -- reserved word, non-temporal data type and
toooooooo long
@ydate VARCHAR (50) -- non-temporal data type and toooooooo long
PRINT CONVERT(CHARr(1 1), (GETDATE()-1), 100);
-- print is for diagnostics
-- CONVERT() is proprietary
--GETDATE() is proprietary and should be CURRENT_TIMESTA MP
-- 1950's style COBOL strings for dates !!
...
(SELECT CASE ua
WHEN 'unknown' THEN NULL ELSE ua END ) AS ua,
-- why did you put a SELECT in this?
-- did you use CASE instead if NULLIF()?

...
CASE servicename
WHEN 'aktel' THEN 'aktel'
WHEN 'qatar2900' THEN 'star multimedia 2900'
WHEN 'telemovil' THEN 'telemovil'
WHEN 'comcel' THEN 'comcel'
WHEN 'tsttnews' THEN 'tstt'
WHEN 'tsttwap' THEN 'tstt'
WHEN 'tstt_mms' THEN 'tstt mms'
WHEN 'alclickwin6464 ' THEN 'airtel click win 646'
WHEN 'almmsportal' THEN 'airtel mms'
WHEN 'almmssmsdwn' THEN 'airtel mms sms'
WHEN 'almyalbum646' THEN 'airtel my album'
WHEN 'hindu6397' THEN 'hindu 6397'
ELSE
(SELECT opname FROM datalogs.dbo.op erator WHERE phoneseries ..)
END

-- why isn't this an auxiliary look-up table? Why only one operator in
that table?

Your code is basically 1950's COBOL written in SQL and is awful. You
are treating temporal data as insanely loooooonng strings and doing
display work in the database and not the application. You even use
PRINT in production code!

Singular table names is another COBOL thing. That language (and others
that are 40+ years old and non-relational) did record-at-a-time process
so you did bring one Operator into the program each loop. But SQL is
set-oriented and we bring many operators into the program without a
loop.