Query problem

Hi
Some days ago I posted a problem with a query.
Thaks to Dave and John, I got a little closer to a solution.
Their suggestion was a solution to my problem as I stated it, but the real
problem is a bit more complicated, so I have to expand the original problem
a bit.
The query has to handle unknown number of departments, and a date interval.

I want:
1) For each day, for each department: a list of (from at_work table) all
employees at work.
2) In the same list I want listed (from emp table) all emplyees that belongs
to this department, but is not on work this date

Here are new scripts:
create table emp
(
empno int not null,
depno int not null
)
alter table emp add primary key (empno)

create table at_work
(
empno int not null,
depno int not null,
working_date int not null,
duration int not null
)
alter table at_work add primary key (empno, depno, working_date)
alter table at_work add constraint fk_at_work_emp foreign key (empno)
references emp (empno)

insert into emp (empno, depno) values (1,10)
insert into emp (empno, depno) values (2,10)
insert into emp (empno, depno) values (3,20)
insert into emp (empno, depno) values (4,20)

insert into at_work (empno, depno, working_date, duration) values
(1,10,'20031017',5)
insert into at_work (empno, depno, working_date, duration) values
(3,10,'20031017',4)
insert into at_work (empno, depno, working_date, duration) values
(1,10,'20031018',6)
insert into at_work (empno, depno, working_date, duration) values
(4,10,'20031018',7)
insert into at_work (empno, depno, working_date, duration) values
(1,20,'20031017',3)
insert into at_work (empno, depno, working_date, duration) values
(3,20,'20031017',5)
insert into at_work (empno, depno, working_date, duration) values
(2,20,'20031018',6)
insert into at_work (empno, depno, working_date, duration) values
(3,20,'20031018',7)
insert into at_work (empno, depno, working_date, duration) values
(4,20,'20031018',8)

The result set should now look like this:
empno depno working_date duration
--------------
1 10 '20031017' 5
3 10 '20031017' 4
2 10 '20031017' NULL
1 10 '20031018' 6
4 10 '20031018' 7
2 10 '20031018' NULL
1 20 '20031017' 3
3 20 '20031017' 5
4 20 '20031017' NULL
2 20 '20031018' 6
3 20 '20031018' 7
4 20 '20031018' 8

Could someone please help me?

Thanks in advance
Regards,
Gunnar Vyenli
EDB-konsulent as
NORWAY"Gunnar Vyenli" <gv@.edbkonsulent.no> wrote in message news:3f93e231$1@.news.broadpark.no...
> Hi
> Some days ago I posted a problem with a query.
> Thaks to Dave and John, I got a little closer to a solution.
> Their suggestion was a solution to my problem as I stated it, but the real
> problem is a bit more complicated, so I have to expand the original problem
> a bit.
> The query has to handle unknown number of departments, and a date interval.
> I want:
> 1) For each day, for each department: a list of (from at_work table) all
> employees at work.
> 2) In the same list I want listed (from emp table) all emplyees that belongs
> to this department, but is not on work this date
> Here are new scripts:
> create table emp
> (
> empno int not null,
> depno int not null
> )
> alter table emp add primary key (empno)
> create table at_work
> (
> empno int not null,
> depno int not null,
> working_date int not null,
> duration int not null
> )
> alter table at_work add primary key (empno, depno, working_date)
> alter table at_work add constraint fk_at_work_emp foreign key (empno)
> references emp (empno)
> insert into emp (empno, depno) values (1,10)
> insert into emp (empno, depno) values (2,10)
> insert into emp (empno, depno) values (3,20)
> insert into emp (empno, depno) values (4,20)
> insert into at_work (empno, depno, working_date, duration) values
> (1,10,'20031017',5)
> insert into at_work (empno, depno, working_date, duration) values
> (3,10,'20031017',4)
> insert into at_work (empno, depno, working_date, duration) values
> (1,10,'20031018',6)
> insert into at_work (empno, depno, working_date, duration) values
> (4,10,'20031018',7)
> insert into at_work (empno, depno, working_date, duration) values
> (1,20,'20031017',3)
> insert into at_work (empno, depno, working_date, duration) values
> (3,20,'20031017',5)
> insert into at_work (empno, depno, working_date, duration) values
> (2,20,'20031018',6)
> insert into at_work (empno, depno, working_date, duration) values
> (3,20,'20031018',7)
> insert into at_work (empno, depno, working_date, duration) values
> (4,20,'20031018',8)
> The result set should now look like this:
> empno depno working_date duration
> --------------
> 1 10 '20031017' 5
> 3 10 '20031017' 4
> 2 10 '20031017' NULL
> 1 10 '20031018' 6
> 4 10 '20031018' 7
> 2 10 '20031018' NULL
> 1 20 '20031017' 3
> 3 20 '20031017' 5
> 4 20 '20031017' NULL
> 2 20 '20031018' 6
> 3 20 '20031018' 7
> 4 20 '20031018' 8
> Could someone please help me?
> Thanks in advance
> Regards,
> Gunnar Vyenli
> EDB-konsulent as
> NORWAY

SELECT COALESCE(W.empno, E.empno) AS empno,
COALESCE(W.depno, E.depno) AS depno,
COALESCE(W.working_date, D.working_date) AS working_date,
W.duration
FROM Emp AS E
CROSS JOIN
(SELECT DISTINCT working_date FROM At_Work) AS D
FULL OUTER JOIN
At_Work AS W
ON E.empno = W.empno AND
E.depno = W.depno AND
D.working_date = W.working_date
ORDER BY depno, working_date, empno

empno depno working_date duration
1 10 20031017 5
2 10 20031017 NULL
3 10 20031017 4
1 10 20031018 6
2 10 20031018 NULL
4 10 20031018 7
1 20 20031017 3
3 20 20031017 5
4 20 20031017 NULL
2 20 20031018 6
3 20 20031018 7
4 20 20031018 8

Regards,
jag|||Since you want to report on dates which may or may not exist in your table,
best create a Calendar table:

CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY)

Populate with as many years as you need:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'20101231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Here's the query:

SELECT COALESCE(W.empno, E.empno) AS empno,
COALESCE(W.depno, E.depno) AS depno,
COALESCE(C.caldate, W.working_date) AS working_date,
W.duration
FROM Calendar AS C
CROSS JOIN Emp AS E
FULL JOIN At_Work AS W
ON E.empno=W.empno AND E.depno=W.depno AND C.caldate=W.working_date
WHERE C.caldate BETWEEN '20031017' AND '20031018'
OR C.caldate IS NULL

--
David Portas
----
Please reply only to the newsgroup
--

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:

> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
--
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
353
Date
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SB
I have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>
|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:

> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>

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'))

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'))