Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?
If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>
Showing posts with label facing. Show all posts
Showing posts with label facing. Show all posts
Friday, March 23, 2012
query performance with large tables
Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexe
s
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>sql
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexe
s
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>sql
query performance with large tables
Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>
Friday, March 9, 2012
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()),1
00),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(cha
r(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),1
00),8,4)
Print @.date
Print @.ydate
insert into
biiod.dbo. data_trans_currentday_test(MobileNo,UA,M
essageID,ContentID,Descrip
tion,MusicLabel,CPID,CPName,ContentType,
Category,SubCategory,TransactionDate
,Units,Unitprice,Shortcode,Servicecode,O
peratorID,CatID,SubCatID,SpecialPack
age,Royalties,Operator,Circ
le,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','HINDU63
97','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 - u
se
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()),1
00),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(ch
ar(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,Mu
sicLabel,CPID,CPName,ContentType,Categor
y,SubCategory,TransactionDate,Units,Unit
pric
e,Shortcode,Servicecode,OperatorID,CatID
,SubCatID,SpecialPackage,Royalties,Opera
tor,
Cir
cle,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','HINDU63
97','MTV','QATAR2900','SIFY'))
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()),1
00),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(cha
r(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),1
00),8,4)
Print @.date
Print @.ydate
insert into
biiod.dbo. data_trans_currentday_test(MobileNo,UA,M
essageID,ContentID,Descrip
tion,MusicLabel,CPID,CPName,ContentType,
Category,SubCategory,TransactionDate
,Units,Unitprice,Shortcode,Servicecode,O
peratorID,CatID,SubCatID,SpecialPack
age,Royalties,Operator,Circ
le,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','HINDU63
97','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 - u
se
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()),1
00),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(ch
ar(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,Mu
sicLabel,CPID,CPName,ContentType,Categor
y,SubCategory,TransactionDate,Units,Unit
pric
e,Shortcode,Servicecode,OperatorID,CatID
,SubCatID,SpecialPackage,Royalties,Opera
tor,
Cir
cle,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','HINDU63
97','MTV','QATAR2900','SIFY'))
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'))
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'))
Subscribe to:
Posts (Atom)