February 24, 2005 at 12:09 pm
Hi,
This is my query which is having the perrformance issue:
SELECT distinct R.[student_id], ST.[first_name], ST.[last_name], ST.[grade], ST.[school_id],
ST.[address], ST.[city],
ST.[zipcode], ST.[birthdate] ,
S.[school_name],
S.[SASISitecode],
S.[SiteType],
S.[Address] as 'Address1', S.[City] as 'City1', S.[State] as 'State1', S.[ZipCode] as 'ZipCode1',
R.program_id,
(select distinct P.[program_name] from program P where p.[program_id]=R.program_id) as 'program_name',
(select distinct Pt.[program_type] from programtype pt,program P where p.[program_type_id]=pt.[program_type_id] and p.[program_id]=R.program_id ) as 'program_type',
(SELECT distinct [fund_list] FROM #ProgramFundsInfo PFI where PFI.[Program_id] = R.program_id ) as 'Grant',
(Select count(distinct(CONVERT(CHAR(10), session_start_time,101))) from Session SS,Attendance AA where SS.session_id=AA.session_id and AA.attendence_status=1 and SS.program_id=R.program_id and ss.session_is_active=1 and AA.Student_id=st.student_id) as 'AttendanceDays',
(SELECT sum(datediff(hh, [session_start_time], [session_end_time]) ) FROM [Session] ss where ss.program_id=R.program_id and session_id in (select session_id from attendance where student_id = St.student_id and attendence_status=1)) as 'Duration',
(Select ((select count(distinct(CONVERT(CHAR(10), session_start_time,101))) from Session SS,Attendance AA where SS.session_id=AA.session_id and AA.attendence_status=1 and SS.program_id=R.program_id and ss.session_is_active=1 and AA.Student_id=R.student_id)*100)/(select count(distinct(CONVERT(CHAR(10), session_start_time,101))) from Session SS,Attendance AA where SS.session_id=AA.session_id and SS.program_id=R.program_id and ss.session_is_active=1 and SS.program_provider_activity_id in (select RR.program_provider_activity_id from Registration RR where RR.Student_id=ST.student_id))
where (select count(distinct(CONVERT(CHAR(10), session_start_time,101))) from Session SS,Attendance AA where SS.session_id=AA.session_id and SS.program_id=R.program_id and ss.session_is_active=1 and SS.program_provider_activity_id in (select RR.program_provider_activity_id from Registration RR where RR.Student_id=ST.student_id))<>0) as 'AttendancePercentage'
FROM [School] S,
[Student] ST ,Registration R
where st.[school_id]=s.[school_id]
and R.student_id=st.student_id
and R.program_id in (select ProgramID from #ProgramList)
order by ST.[last_name]
The major performance problem is with the part marked in REd.it is actually a division where sometimes ther is divide by zero to avoid that i included a where clause which is adding to the performance problem.
is there any way I can avoid divide by zero and still have better performance?
The query takes 12 secs for 1 program,if i choose multiple programs it goes on for a very long time..
Thanks,
Sudha
SSM
February 24, 2005 at 12:28 pm
You are doing too many sub-queries in the SELECT portion (forcing them to be resolved for each row) instead of doing them in the FROM section as virtual tables, where they'd only be resolved once before joining.
Also, some of the sub-queries just plain don't make sense. Consider this:
(
select distinct P.[program_name] from program P
where p.[program_id] = R.program_id
\) as 'program_name'
Why have this in the SELECT (joined to every row), instead of just having table [Program] in the WHERE clause, joined to once ?
You have DISTINCT in the main SELECT statement, so use of DISTINCT all over the place in sub-queries is redundant and forces SQL server to perform the work of resolving Distinct too many times.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply