T-SQL Performance Optimization (3/6)

As a continue for what started in the second part of Ugly coding parts, we could jump into other ones as below:

3- Joins + where conditions used with Views:

As a generic concept , inner join with an object hasn’t any sufficient indexing will result of much index Scan or perhaps might be Table Scan ….

Also the same apply for views where joins or where conditions could result in :

  • Dramatic performance degradation particularly more for huge data entity  
  • Unbelievable performance degradations even for small criteria  scope of where conditions

 

Since coding algorithm go then  in improper way :

  • Selecting all Data entity of the view >>>then filter this data to match up with where conditions.
  • Much complies/sec are there more than if we used SPs

 

So what is the possible appropriate coding solutions to supersede views:

  1. We could pass directly those select statements inside view to SP.

 

  1. Then we could apply the needed where conditions to them or  join with them

 

  1. Also , you could use CTEs (Common Table Expressions) with apply the same where conditions to them or join with them also.

 

  1. Also ,it might be possible to use SPs Output function but this is particularly more for single values not grid of data

 

Workshop Examples:

 

1 –Schema design :

<strong><span style="text-decoration: underline"> </span></strong>
 
CREATE TABLE [dbo].[Students](
 
      [Student_ID] [int] NOT NULL,
 
      [Student_Name] [nvarchar](150) NULL,
 
      [Telephone] [nvarchar](10) NULL,
 
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
 
(
 
      [Student_ID] ASC
 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 
) ON [PRIMARY]
 
GO
 
CREATE TABLE [dbo].[Certificates](
 
      [Student_ID] [int] NOT NULL,
 
      [Certificate] [nvarchar](100) NULL,
 
      [Certificates_Date] [date] NULL,
 
      [Place] [nvarchar](50) NULL
 
) ON [PRIMARY]
 
GO
 
CREATE VIEW [dbo].[Certificates_count]
 
AS
 
SELECT     COUNT (Certificate)AS count, Student_ID
 
FROM         dbo.Certificates GROUP BY Student_ID

 

2-     Original SP using views

 

create procedure [dbo].[Students_Statistics_Old] ( @student_id int)
 
as
 
begin
 
select T.* from Students T INNER JOIN Certificates_count S ON T.student_id=S.student_id where S.count &gt;1 and T.Student_id =@student_id
 
return
 
end

3-     New SP using SP output function

 

 

--First SP having outout function which could replace views for single values
 
ALTER procedure  [dbo].[certificates_Details] (@student_ID INT =NULL, @Certificates_count int output)
 
as
 
begin
 
set nocount on;
 
select @Certificates_count = COUNT (*)  from Certificates where student_id=@student_ID
 
return
 
end
 
--Main SP
 
ALTER procedure [dbo].[Students_Statistics_New] ( @student_id int )
 
as
 
begin
 
declare  @Certificates_count int
 
exec dbo.certificates_Details @student_id=@student_id,@Certificates_count=@Certificates_count output
 
select * from Students T where  (T.Student_id =@student_id ) and @Certificates_count &gt;1
 
RETURN
 
end

4-     New SP using inner joins with select statements

 

 

<strong><span style="text-decoration: underline"> </span></strong>
 
ALTER procedure [dbo].[Students_Statistics_New1] ( @student_id int)
 
as
 
begin
 
select T.* from Students T INNER JOIN (select COUNT (Certificate)AS count, Student_ID
 
FROM         dbo.Certificates GROUP BY Student_ID )S
 
ON T.student_id=S.student_id where S.count &gt;1 and (T.Student_id =@student_id OR @student_id IS NULL)
 
RETURN
 
end

5-     New SP using CTE (Common table expression)

 

ALTER procedure [dbo].[Students_Statistics_New2] ( @student_id int)
 
as
 
begin
 
;
 
with Temptable as (select COUNT (Certificate)AS count, Student_ID
 
FROM         dbo.Certificates GROUP BY Student_ID )
 
select T.* from Students T INNER JOIN Temptable S
 
ON T.student_id=S.student_id where S.count &gt;1 and T.Student_id =@student_id
 
RETURN
 
End

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>