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:
- We could pass directly those select statements inside view to SP.
- Then we could apply the needed where conditions to them or join with them
- Also , you could use CTEs (Common Table Expressions) with apply the same where conditions to them or join with them also.
- 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 >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 >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 >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 >1 and T.Student_id =@student_id RETURN End |
Recent Comments