• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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
0
Sandeepiii
Asked:
Sandeepiii
  • 2
1 Solution
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now