December 12, 2008 at 3:24 am
Hi!
I am working on an application processing requests from families to get subvention for their kids in school. For this purpose I need a query which returns for each family data for their first ,second, .. up to fifth kid.
The query should correctly handle twins (same birth date) and the order of the ID's does not always match the order of the birth dates.
Now I am using the following query (I limited it to 3 kids here!):
select R.ID, R.NAme,R.Address,
K1.Name, K1.Birth_date, -- First child
K2.Name, K2.Birth_date, -- Second child
K3.Name, K3.Birth_date -- Third child
from #REQUESTS R
inner join #KIDS K1
on R.ID = K1.REQ_ID
and K1.ID = (SELECT TOP 1 ID FROM #KIDS
WHERE (REQ_ID=R.ID)
ORDER BY Birth_date)
left outer join #KIDS K2
on R.ID = K2.REQ_ID
and K2.ID = (SELECT TOP 1 ID FROM #KIDS
WHERE (REQ_ID = R.ID) AND (ID <> K1.ID)
ORDER BY Birth_date)
left outer join #KIDS K3
on R.ID = K3.REQ_ID
and K3.ID = (SELECT TOP 1 ID FROM #KIDS
WHERE (REQ_ID = R.ID) AND (ID NOT IN(K1.ID, K2.ID))
ORDER BY Birth_date)
It works nice, but since I have yearly about 12000 requests and even more KID records, it is terrible slow. 🙁
I tried some other approaches, but without success. Any idea how to speed up this query??
Below is my test data:
Thanks,
Herman
IF OBJECT_ID('TempDB..#KIDS','U') IS NOT NULL
DROP TABLE #REQUESTS
IF OBJECT_ID('TempDB..#KIDS','U') IS NOT NULL
DROP TABLE #KIDS
CREATE TABLE #REQUESTS
(ID int PRIMARY KEY CLUSTERED,
Name varchar(30),
Address varchar(30))
CREATE TABLE #KIDS
(ID int PRIMARY KEY CLUSTERED,
REQ_ID int NOT NULL,
Name varchar(34) NOT NULL,
Birth_DAte datetime NOT NULL
)
SET DATEFORMAT YMD
INSERT INTO #REQUESTS (ID, NAme, Address)
SELECT 1,'Family 1','Main road 1' UNION ALL
SELECT 2,'Family 2','Main road 2' UNION ALL
SELECT 3,'Family 3','Main road 3' UNION ALL
SELECT 4,'Family 4','Main road 4' UNION ALL
SELECT 5,'Family 5','Main road 5'
INSERT INTO #KIDS(ID, REQ_ID, NAme, Birth_DAte)
SELECT 1,1,'Harry', '20010101' UNION ALL
SELECT 2,1,'Betty', '20020101' UNION ALL
SELECT 3,2,'Hank', '19991111' UNION ALL
SELECT 4,3,'Laura', '20020101' UNION ALL --ID order does not match birth order
SELECT 5,3,'Mike', '20010101' UNION ALL
SELECT 6,3,'Erik', '20030101' UNION ALL
SELECT 7,4,'Lina', '20030101' UNION ALL --Twins!
SELECT 8,4,'Lana', '20030101' UNION ALL --Twins!
SELECT 9,5,'Andrej', '20030101' UNION ALL
SELECT 10,5,'Katy', '20030101'
December 12, 2008 at 5:31 am
Hi Herman
Here's a few more kids to play with:
INSERT INTO #KIDS(ID, REQ_ID, NAme, Birth_DAte)
SELECT 1,1,'Harry', '20010101' UNION ALL
SELECT 2,1,'Betty', '20020101' UNION ALL
SELECT 3,2,'Hank', '19991111' UNION ALL
SELECT 4,3,'Laura', '20020101' UNION ALL --ID order does not match birth order
SELECT 5,3,'Mike', '20010101' UNION ALL
SELECT 6,3,'Erik', '20030101' UNION ALL
SELECT 7,4,'Lina', '20030101' UNION ALL --Twins!
SELECT 8,4,'Lana', '20030101' UNION ALL --Twins!
SELECT 9,4,'Jane', '20020101' UNION ALL
SELECT 10,4,'Jude', '20021001' UNION ALL
SELECT 11,4,'Shel', '20040101' UNION ALL
SELECT 12,4,'Pooky', '20050101' UNION ALL
SELECT 13,5,'Andrej', '20030101' UNION ALL
SELECT 14,5,'Katy', '20030101'
Here's a query similar to yours which may run a little faster:
[font="Courier New"]SELECT R.ID, R.NAme,R.Address,
K1.Name, K1.Birth_date, -- First child
CASE WHEN K2.ID <> K1.ID THEN K2.Name ELSE NULL END AS Name, CASE WHEN K2.ID <> K1.ID THEN K2.Birth_date ELSE NULL END AS Birth_date, -- Second child
CASE WHEN K3.ID <> K2.ID THEN K3.Name ELSE NULL END AS Name, CASE WHEN K3.ID <> K2.ID THEN K3.Birth_date ELSE NULL END AS Birth_date, -- Third child
CASE WHEN K4.ID <> K3.ID THEN K4.Name ELSE NULL END AS Name, CASE WHEN K4.ID <> K3.ID THEN K4.Birth_date ELSE NULL END AS Birth_date, -- Fourth child
CASE WHEN K5.ID <> K4.ID THEN K5.Name ELSE NULL END AS Name, CASE WHEN K5.ID <> K4.ID THEN K5.Birth_date ELSE NULL END AS Birth_date -- Fifth child
FROM #REQUESTS R, #KIDS K1, #KIDS K2, #KIDS K3, #KIDS K4, #KIDS K5
WHERE K1.ID = (SELECT TOP 1 ID FROM #KIDS WHERE REQ_ID = R.ID ORDER BY Birth_DAte, ID)
AND K2.ID = (SELECT TOP 1 ID FROM (SELECT TOP 2 * FROM #KIDS WHERE REQ_ID = R.ID ORDER BY Birth_DAte, ID) d ORDER BY Birth_DAte DESC, ID DESC)
AND K3.ID = (SELECT TOP 1 ID FROM (SELECT TOP 3 * FROM #KIDS WHERE REQ_ID = R.ID ORDER BY Birth_DAte, ID) d ORDER BY Birth_DAte DESC, ID DESC)
AND K4.ID = (SELECT TOP 1 ID FROM (SELECT TOP 4 * FROM #KIDS WHERE REQ_ID = R.ID ORDER BY Birth_DAte, ID) d ORDER BY Birth_DAte DESC, ID DESC)
AND K5.ID = (SELECT TOP 1 ID FROM (SELECT TOP 5 * FROM #KIDS WHERE REQ_ID = R.ID ORDER BY Birth_DAte, ID) d ORDER BY Birth_DAte DESC, ID DESC)
[/font]
Here's a completely different method which pre-numbers the kids by DOB, rather than fudging it with SELECT TOP n:
[font="Courier New"]SELECT R.ID, R.NAme AS FamilyName, R.Address, K.Name, K.Birth_date, CAST(NULL AS INT) AS KidNo
INTO #temp
FROM #REQUESTS R INNER JOIN #KIDS K ON K.REQ_ID = R.ID ORDER BY R.ID, K.Birth_DAte, K.ID
DECLARE @KidNo INT, @ID INT
SET @KidNo = 0
UPDATE #temp SET @KidNo = KidNo = CASE WHEN @ID = ID THEN @KidNo+1 ELSE 1 END, @ID = ID
SELECT ID, FamilyName, Address,
MAX(CASE KidNo WHEN 1 THEN (Name) ELSE NULL END) AS [Name], MAX(CASE KidNo WHEN 1 THEN (Birth_date) ELSE NULL END) AS [Birth_date],
MAX(CASE KidNo WHEN 2 THEN (Name) ELSE NULL END) AS [Name], MAX(CASE KidNo WHEN 2 THEN (Birth_date) ELSE NULL END) AS [Birth_date],
MAX(CASE KidNo WHEN 3 THEN (Name) ELSE NULL END) AS [Name], MAX(CASE KidNo WHEN 3 THEN (Birth_date) ELSE NULL END) AS [Birth_date],
MAX(CASE KidNo WHEN 4 THEN (Name) ELSE NULL END) AS [Name], MAX(CASE KidNo WHEN 4 THEN (Birth_date) ELSE NULL END) AS [Birth_date],
MAX(CASE KidNo WHEN 5 THEN (Name) ELSE NULL END) AS [Name], MAX(CASE KidNo WHEN 5 THEN (Birth_date) ELSE NULL END) AS [Birth_date]
FROM #temp
GROUP BY ID, FamilyName, Address[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 12, 2008 at 6:17 am
Here's a "dynamic" solution.
Primitive, but effective
It requests your data for all children with a total of 5
but it includes twins, triplets, ...
If you have 3 children and then are blessed with a triplet, this proc will
deliver the data for all 6 children
Correct ?
btw: it is primitive and may need some extra optimization.
/*
http://www.sqlservercentral.com/Forums/Topic618464-65-1.aspx
*/
IF OBJECT_ID('TempDB..#KIDS','U') IS NOT NULL
DROP TABLE #REQUESTS
IF OBJECT_ID('TempDB..#KIDS','U') IS NOT NULL
DROP TABLE #KIDS
IF OBJECT_ID('TempDB..#ReqKidsWRK','U') IS NOT NULL
DROP TABLE #ReqKidsWRK
IF OBJECT_ID('TempDB..#ListKidsWRK','U') IS NOT NULL
DROP TABLE #ListKidsWRK
Declare @DebugNY char(1)
Set @DebugNY = 'N'
CREATE TABLE #REQUESTS
(ID int PRIMARY KEY CLUSTERED,
Name varchar(30),
Address varchar(30))
CREATE TABLE #KIDS
(ID int PRIMARY KEY CLUSTERED,
REQ_ID int NOT NULL,
Name varchar(34) NOT NULL,
Birth_DAte datetime NOT NULL
)
-- SET DATEFORMAT YMD
INSERT INTO #REQUESTS (ID, NAme, Address)
SELECT 1,'Family 1','Main road 1' UNION ALL
SELECT 2,'Family 2','Main road 2' UNION ALL
SELECT 3,'Family 3','Main road 3' UNION ALL
SELECT 4,'Family 4','Main road 4' UNION ALL
SELECT 5,'Family 5','Main road 5' UNION ALL
SELECT 6,'Family 6','Main road 6' UNION ALL
SELECT 7,'Family 7','Main road 7'
INSERT INTO #KIDS(ID, REQ_ID, NAme, Birth_DAte)
SELECT 1,1,'Harry', '20010101' UNION ALL
SELECT 2,1,'Betty', '20020101' UNION ALL
SELECT 3,2,'Hank', '19991111' UNION ALL
SELECT 4,3,'Laura', '20020101' UNION ALL --ID order does not match birth order
SELECT 5,3,'Mike', '20010101' UNION ALL
SELECT 6,3,'Erik', '20030101' UNION ALL
SELECT 7,4,'Lina', '20030101' UNION ALL --Twins!
SELECT 8,4,'Lana', '20030101' UNION ALL --Twins!
SELECT 9,5,'Andrej', '20030101' UNION ALL
SELECT 10,5,'Katy', '20030101' UNION ALL
SELECT 11,6,'No1', '20010101' UNION ALL
SELECT 12,6,'No2', '20020101' UNION ALL
SELECT 13,6,'No3', '20030101' UNION ALL
SELECT 14,6,'No4_1', '20040101' UNION ALL
SELECT 15,6,'No4_2', '20040101' UNION ALL
SELECT 16,6,'No4_3', '20040101' UNION ALL
SELECT 17,7,'No1', '20010101' UNION ALL
SELECT 18,7,'No2', '20020101' UNION ALL
SELECT 19,7,'No3', '20030101' UNION ALL
SELECT 20,7,'No4', '20040101' UNION ALL
SELECT 21,7,'No5', '20050101' UNION ALL
SELECT 22,7,'No6', '20060101'
If @DebugNY = 'Y'
begin
select R.ID, R.NAme,R.Address,
K1.Name, K1.Birth_date, -- First child
K2.Name, K2.Birth_date, -- Second child
K3.Name, K3.Birth_date -- Third child
from #REQUESTS R
inner join #KIDS K1
on R.ID = K1.REQ_ID
and K1.ID = (SELECT TOP 1 ID FROM #KIDS
WHERE (REQ_ID=R.ID)
ORDER BY Birth_date)
left outer join #KIDS K2
on R.ID = K2.REQ_ID
and K2.ID = (SELECT TOP 1 ID FROM #KIDS
WHERE (REQ_ID = R.ID) AND (ID <> K1.ID)
ORDER BY Birth_date)
left outer join #KIDS K3
on R.ID = K3.REQ_ID
and K3.ID = (SELECT TOP 1 ID FROM #KIDS
WHERE (REQ_ID = R.ID) AND (ID NOT IN(K1.ID, K2.ID))
ORDER BY Birth_date)
end
Select K.REQ_ID
, K.Birth_DAte
, count(distinct K.ID) as DatePersonsCount
, count(distinct K1.ID) as RunningPersonsCount
, case when count(distinct K1.ID) >= 5 then 1 else 0 end as ReEvaluate
into #ReqKidsWRK
from #KIDS K
inner join #KIDS K1
on K1.REQ_ID =K.REQ_ID
and K1.Birth_DAte <= K.Birth_DAte
Group by K.Req_Id, K.Birth_Date
Order by K.Req_Id, K.Birth_Date
/* drop Childeren where childcount > 5 and birthdate > the referencedate */
Delete W
from #ReqKidsWRK W
Inner join (Select REQ_ID
, min( Birth_Date ) as Ref_Birth_Date
from #ReqKidsWRK
where ReEvaluate = 1
group by REQ_ID ) R
on W.REQ_ID = R.REQ_ID
and W.Birth_Date > R.Ref_Birth_Date
Create table #ListKidsWRK
(ID int ,
REQ_ID int NOT NULL,
Name varchar(34) NOT NULL,
Birth_DAte datetime NOT NULL,
Seq_No int not null default 0
);
Insert into #ListKidsWRK (ID, REQ_ID, Name, Birth_DAte )
Select distinct K.*
from #Kids K
Where exists ( select *
from #ReqKidsWRK W
where W.REQ_ID = K.REQ_ID
and W.Birth_Date = K.Birth_Date ) ;
Create unique clustered index x_ListKidsWRK on #ListKidsWRK (REQ_ID, Birth_DAte, ID) ;
Declare @SQLSeqNo varchar(4000)
Select @SQLSeqNo =
'DECLARE @PrevGrpCnt INT
SET @PrevGrpCnt = 0
DECLARE @PrevReqId INT
SET @PrevReqId = 0
UPDATE #ListKidsWRK
SET @PrevGrpCnt = Seq_No = CASE
WHEN REQ_ID = @PrevReqId
THEN @PrevGrpCnt + 1
ELSE 1
END,
@PrevReqId = REQ_ID
FROM #ListKidsWRK WITH (INDEX(x_ListKidsWRK),TABLOCKX)
'
Exec (@SQLSeqNo)
If @DebugNY = 'Y'
begin
Select *
from #ListKidsWRK
Order by REQ_ID, Seq_No
end
/* Build dynamic sql for all kids */
Declare @SQLSel varchar(1000)
Declare @SQLSelKid varchar(1000)
Declare @SQLSeljoin varchar(1000)
Declare @SQLSelGroup varchar(1000)
Declare @SQLSelorderby varchar(1000)
Select @SQLSel = 'select R.ID, R.NAme,R.Address'
, @SQLSelKid = ''
, @SQLSeljoin = ' from #REQUESTS R inner join #ListKidsWRK K on R.ID = K.REQ_ID '
, @SQLSelGroup = ' group by R.ID, R.NAme,R.Address'
, @SQLSelorderby = ' Order by R.ID ;' ;
Select @SQLSelKid = @SQLSelKid + ', min( case when K.Seq_No = ' + cast(T.N as varchar(15)) + ' then K.Name else null end) as Name, min( case when K.Seq_No = ' + cast(T.N as varchar(15)) + ' then K.Birth_date else null end) as Birth_date'
from (Select max (Seq_No) as Max_Seq_No
from #ListKidsWRK ) W
inner join tempdb.dbo.Tally T
on T.N <= W.Max_Seq_No
If @DebugNY = 'Y'
begin
Print @SQLSel
Print @SQLSelKid
Print @SQLSeljoin
Print @SQLSelGroup
Print @SQLSelorderby
end
Exec ( @SQLSel + @SQLSelKid + @SQLSeljoin + @SQLSelGroup + @SQLSelorderby )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 12, 2008 at 6:24 am
Wow,:w00t:
Thanks Chris. Your query is much faster then mine: 3 seconds compared to more than a minute on my database.
I had been thinking about your second suggestion as well, but it requires me to change the application. Anyway, with the response time of your query there is no need!
Thanks very much
Herman
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply