SQL Server stored procedure

Hi experts,
Iam enclosing two strored procedure , the reason i would like to know whether
1. these stored procedure can be optimised for better results
2in case there is scope of improvement please suggest any necessary changes.
As i would be putting them into production.
thanks in advance.
Script-1.sql
Script-2.sql
SandeepiiiAsked:
Who is Participating?
 
Easwaran ParamasivamCommented:
In from clause more tables are used. This will make caresian product. Consider using JOINs instead such as INNER JOIN, LEFT OUTER JOIN based on your needs.

In where clause in is used to verify one value. Use like operator instead.

Order by will slow your performance.

Select all required fields and place it in a table variable. Then apply order by on the table variable. This will improve performance much.

In second script, cursor is used. Avoid that. Instead try to use CTE (Common table expresson). That will improve the performance.

Most importantly, verify whether the tables to be searched have clustured indexes. If not try to create it. That will increase select operation faster.

I sum up important points. Please google the topics that I highlighted and apply in your sp.


 
0
 
SandeepiiiAuthor Commented:
Thanks ,any particular reason to use Like operator here.
0
 
SandeepiiiAuthor Commented:
It was helpful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.