Query Optimisation

Dear All,
I am facing problem with this procedures,the multiselect queries are
taking lot of time ,is there any other solution apart from indexes
Regards
Eckhart
CREATE proc Rolexi36Sync
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,MessageID,ContentID,Description,MusicLabel,CPID,CPName,ContentType,Category,SubCategory,TransactionDate,Units,Unitprice,Shortcode,Servicecode,OperatorID,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(phoneseries)))
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(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(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(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.operator where phoneseries = substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
end) as OPGPName
from datalogs.dbo.translogs where transactiondate >= @.ydate and
transactiondate < @.date and servicename in
('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV','QATAR2900','SIFY'))Eckhart wrote:
> Dear All,
> I am facing problem with this procedures,the multiselect queries are
> taking lot of time ,is there any other solution apart from indexes
Try rewriting your query, replacing subselect to inner join (if
applicable) or left join
,(select musiclabel from dbo.cont_master where contentid
=dbo.translogs.contentid) as musiclable
--
select ...
,m1.MusicLabel as MusicLabel
...
from dbo.translogs l [left] join dbo.cont_master M1 on
l.contentid=m1.contentid
....
if {translog.contentid} - {int NOT null} use inner join, else - use
left join.|||On 28 Jul 2006 00:27:31 -0700, "Eckhart" <n.kopalley@.gmail.com> wrote:
>I am facing problem with this procedures,the multiselect queries are
>taking lot of time ,is there any other solution apart from indexes
Numbers, numbers, please!
How much data, how much time?
What do you get from "set statistics io on"? What does the plan look
like?
The only thing to watch out for is if the plan somehow decides to do
the joins for all records before selecting, so you might try selecting
out of the main translog first into a #temp, then using that as the
source for the complex select. If that's still slow, either there's
something wrong with one of your lookup tables, or, well, I don't
know?!
Josh
>Regards
>Eckhart
>CREATE proc Rolexi36Sync
>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,MessageID,ContentID,Description,MusicLabel,CPID,CPName,ContentType,Category,SubCategory,TransactionDate,Units,Unitprice,Shortcode,Servicecode,OperatorID,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(phoneseries)))
>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(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(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(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.operator where phoneseries =>substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
>end) as OPGPName
>from datalogs.dbo.translogs where transactiondate >= @.ydate and
>transactiondate < @.date and servicename in
>('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV','QATAR2900','SIFY'))