Announcement

Wednesday, 20 August 2014

SQL Query Performance Issue

SQL Query Performance Issue
SQL Query Performance
SQL Query Performance

As a developer we need to take care a lot of performance of the system that we develop. Recently in one of my project I got a task to check the performance of the query. I tried with three different ways using joins each returning the same result. BUT...



The FIRST way uses joins along with a left outer join with a nested query itself returning result using joins. A total of Five joins are used to get the final result.

The SECOND way uses Temp Table. It first creates a Temp Table and then inserts the data into the Temp Table using joins. It then again uses joins and a left outer join on the Temp Table to get the final result. A total of Five joins are used to get the final result including Left outer join.

The THIRD uses a Stored Procedure with Temp Table but uses less number of joins as compared to other two methods. A total of Four joins are used to get the final result. NOTE that no left outer join is used here.

The First Query goes here:




select schedule.fpcdate,
scheduleprogram.starttime,
programme_master.programme_name,
programme_master.FilmStarString,
programme_master.EPGDescription,
programme_master.year_of_production,
nested_query.Name
from schedule
join
scheduleprogram
on schedule.schedulecode=scheduleprogram.schedulecode
join
programme_master
on scheduleprogram.ProgramCode=programme_master.programme_code
left outer join
(
SELECT FilmStar.Name,
FilmCrew.programme_code,
programme_master.programme_name
FROM
FilmCrew 
INNER JOIN FilmStar 
ON FilmCrew.FilmStarCode = FilmStar.FilmStarCode 
INNER JOIN programme_master 
ON FilmCrew.programme_code = programme_master.programme_code
WHERE (FilmCrew.PersonCategoryCode = 'PC00000003')
) as nested_query
on programme_master.programme_code=nested_query.programme_code
where schedule.fpcdate>='1 jul 2014'
and schedule.fpcdate<='12 jul 2014' and schedule.channel_code='2' order by schedule.fpcdate,scheduleprogram.starttime

Now start the SQL Server Profiler, run the query and note the perfomance.

SQL Server Profiler
SQL Server Profiler

Performance of the first query as noted via SQL Server Profiler:

SQL Server Profiler Performance Query Result 1
SQL Server Profiler Performance Query Result 1


The Second Query goes here:




create table #Director
(
DirectorName varchar(100),
ProgramCode varchar(100),
ProgramName varchar(100)
)

insert into #Director
(
DirectorName
, ProgramCode
, ProgramName
)

SELECT FilmStar.Name,
FilmCrew.programme_code,
programme_master.programme_name
FROM
FilmCrew 
INNER JOIN FilmStar 
ON FilmCrew.FilmStarCode = FilmStar.FilmStarCode 
INNER JOIN programme_master 
ON FilmCrew.programme_code = programme_master.programme_code
WHERE (FilmCrew.PersonCategoryCode = 'PC00000003')

select schedule.fpcdate,
scheduleprogram.starttime,
programme_master.programme_name,
programme_master.FilmStarString,
programme_master.EPGDescription,
programme_master.year_of_production,
#Director.DirectorName
from schedule
join
scheduleprogram
on schedule.schedulecode=scheduleprogram.schedulecode
join
programme_master
on scheduleprogram.ProgramCode=programme_master.programme_code
left outer join #Director
on programme_master.programme_code=#Director.ProgramCode
where schedule.fpcdate>='1 jul 2014'
and schedule.fpcdate<='12 jul 2014' and schedule.channel_code='2' order by schedule.fpcdate,scheduleprogram.starttime drop table #Director

Performance of the second query as noted via SQL Server Profiler:

SQL Server Profiler Performance Query Result 2
SQL Server Profiler Performance Query Result 2

The Third Query goes here:




alter procedure EpgSpGetB4uUkDetails
(
@ChannelCode varchar(20)=null,
@StartDate varchar(20)=null,
@EndDate varchar(20)=null
)
as

declare @DirectorName varchar(100)

set @DirectorName=null

begin

select * into #Data from(
select schedule.fpcdate,
scheduleprogram.starttime,
programme_master.programme_name,
programme_master.programme_code,
programme_master.FilmStarString,
programme_master.EPGDescription,
programme_master.year_of_production,
DirectorName=@DirectorName
from schedule join
scheduleprogram
on schedule.schedulecode=scheduleprogram.schedulecode join
programme_master
on scheduleprogram.ProgramCode=programme_master.programme_code
where 
CAST( 
FLOOR(CAST(schedule.fpcdate AS FLOAT)) AS DATETIME) 
between convert(varchar(11),@startDate,106)
and 
convert(varchar(11),@endDate,106)
and schedule.channel_code=''+@ChannelCode+''
)
as DataTable

update #Data
set #Data.DirectorName=FilmStar.Name
from FilmCrew 
INNER JOIN FilmStar 
ON FilmCrew.FilmStarCode = FilmStar.FilmStarCode 
INNER JOIN #Data 
ON FilmCrew.programme_code = #Data.programme_code
WHERE FilmCrew.PersonCategoryCode = 'PC00000003'

select * from #Data order by #Data.starttime

drop table #Data

END


After executing the stored procedure the performance is noted via SQL Server Profiler:

SQL Server Profiler Performance Query Result 3
SQL Server Profiler Performance Query Result 3

CONCLUSION: The result from SQL Server Profiler clearly shows the performance result. Using Stored Procedure is always better. But Using SQL Server Profiler does not always return the right result. Infact the performance depends on the Database Optimizer. But, Using Stored Procedure is a better choice because Stored Procedure are precompiled and hence stored procedure will perform better rather than executing the normal query.

FEEDBACK: Please do comment if you have come across the same problem or if you have a perfect solution.

No comments: