SQL Query Performance Issue

SQL Query Performance Issue

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

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

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

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

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.