By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,836 Members | 2,097 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,836 IT Pros & Developers. It's quick & easy.

optimising the following query

P: n/a
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),@ydate varchar(50)
print CONVERT(char(11),(GETDATE()-1),100)
SET @date =
substring(CONVERT(char(11),(GETDATE()),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()),100),1,3) +'-'+substring(CONVERT(char(11),(GETDATE()),100),8,4)
SET @ydate =
substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),8,4)
Print @date
Print @ydate
insert into
biiod.dbo.data_trans_currentday_test(MobileNo,UA,M essageID,ContentID,Description,MusicLabel,CPID,CPN ame,ContentType,Category,SubCategory,TransactionDa te,Units,Unitprice,Shortcode,Servicecode,OperatorI D,CatID,SubCatID,SpecialPackage,Royalties,
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.cont_master where contentid =
datalogs.dbo.translogs.contentid) as musiclable,
(select cpid from datalogs.dbo.contentprovider where cpname =
datalogs.dbo.translogs.cpname) as cpid,
cpname,
contenttype,
(select catname from datalogs.dbo.cont_Catg where catid in (select
catid from cont_master where contentid =
datalogs.dbo.translogs.contentid)) as category,
(select subcatname from datalogs.dbo.cont_subCatg where subcatid in
(select subcatid from cont_master where contentid =
datalogs.dbo.translogs.contentid)) 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.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))
end) as operatorid,
(select catid from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as catid,
(select subcatid from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as subcatid,
(select specialpackage from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as specialpackage,
(select Royalties from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) 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(remarks,1,PATINDEX('%.6397.%',remarks)-1)
else
(select OPname from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))
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(remarks,PATINDEX('%.6397.%',remarks) +
6,len(remarks)-PATINDEX('%-%',remarks))
else
(select Circlename from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))
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.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))
end) as OPGPName
from datalogs.dbo.translogs where transactiondate >= @ydate and
transactiondate < @date and servicename in
('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV', 'QATAR2900','SIFY'))
go

Jul 28 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Eckhart (n.********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 28 '06 #2

P: n/a
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(11), (GETDATE()-1), 100);
-- print is for diagnostics
-- CONVERT() is proprietary
--GETDATE() is proprietary and should be CURRENT_TIMESTAMP
-- 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.operator 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.

Jul 31 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.