October 29, 2012 at 8:37 am
hello i have one task
in that , i prepare query to do data extract, but i takes longer time so i need to do something that will execute faster
here is my logic. so please help me to improve query performance
CREATE TABLE #x1(
[cStudentId] [char](9) NOT NULL,
[iSchoolCode] [int] NOT NULL,
[dtEnrollmentDate] [datetime] NOT NULL,
[cGradeCode] [char](2) NOT NULL,
[cAbsenceCode] [char](1) NOT NULL,
[dtEntryDate] [datetime] NOT NULL,
[DaySeq] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE #x2(
[cAbsenceCode] [char](1) NOT NULL,
[cAbsenceDeseption] [char](20) NOT NULL
) ON [PRIMARY]
GO
insert into #x1 values ('003757137',319,'2012-08-30','12','P','2012-10-29',1)
insert into #x1 values ('003757137',319,'2012-08-31','12','E','2012-10-29',2)
insert into #x1 values ('003757137',319,'2012-09-04','12','P','2012-10-29',3)
insert into #x1 values ('003757137',319,'2012-09-05','12','P','2012-10-29',4)
insert into #x1 values ('003757137',319,'2012-09-06','12','P','2012-10-29',5)
insert into #x1 values ('003757137',319,'2012-09-07','12','P','2012-10-29',6)
insert into #x1 values ('003757137',319,'2012-09-08','12','E','2012-10-29',7)
insert into #x1 values ('003757137',319,'2012-09-11','12','P','2012-10-29',8)
insert into #x1 values ('003757137',319,'2012-09-12','12','P','2012-10-29',9)
insert into #x1 values ('003757137',319,'2012-09-13','12','P','2012-10-29',10)
insert into #x1 values ('003757137',319,'2012-09-14','12','U','2012-10-29',11)
insert into #x1 values ('003757137',319,'2012-09-15','12','P','2012-10-29',12)
insert into #x1 values ('003757137',319,'2012-09-18','12','P','2012-10-29',13)
insert into #x1 values ('003757137',319,'2012-09-19','12','P','2012-10-29',14)
insert into #x1 values ('003757137',319,'2012-09-20','12','U','2012-10-29',15)
insert into #x1 values ('003757137',319,'2012-09-21','12','P','2012-10-29',16)
insert into #x1 values ('003757137',319,'2012-09-22','12','P','2012-10-29',17)
insert into #x1 values ('003757137',319,'2012-09-23','12','P','2012-10-29',18)
insert into #x2 values ('P','Present')
insert into #x2 values ('E','Excused')
insert into #x2 values ('U','UnExcused')
SELECTtemp.cStudentId,
temp.iSchoolCode,
temp.cGradeCode,
COUNT(1) AS iMemb,
SUM(CASE WHEN temp.cAbsenceDeseption = 'Excused' THEN 1 ELSE 0 END) AS iExc,
SUM(CASE WHEN temp.cAbsenceDeseption = 'Unexcused' THEN 1 ELSE 0 END) AS iUnExc
FROM (
select a.cStudentId,
a.cAbsenceCode,
a.cGradeCode,
a.DaySeq,
a.iSchoolCode,
a.dtEnrollmentDate,
b.cAbsenceDeseption from
(select * from #x1
where DaySeq between 8 and 18)
a join #x2 b
on a.cAbsenceCode = b.cAbsenceCode) temp
group by temp.cStudentId,
temp.iSchoolCode,
temp.cGradeCode
so this is logic that i apply to my query. i will good for one student but if there re 30K students with many records then i will take longer time.
so please help me to improve performance of this query.
Thanks
October 29, 2012 at 8:58 am
Thanks for the ddl and sample data. It makes understanding what you are trying to do a lot easier and it makes helping possible.
I really can't quite wrap my head around why your original query is so complicated. I think this will do the same thing.
select cStudentID, iSchoolCode, cGradeCode, COUNT(*) as iMemb, SUM(CASE WHEN y.cAbsenceDeseption = 'Excused' THEN 1 ELSE 0 END) AS iExc,
SUM(CASE WHEN y.cAbsenceDeseption = 'Unexcused' THEN 1 ELSE 0 END) AS iUnExc
from #x1 x
join #x2 y on x.cAbsenceCode = y.cAbsenceCode
where x.DaySeq >= 8
and x.DaySeq <= 18
group by cStudentID, iSchoolCode, cGradeCode
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply