?
Solved

SQL Server stored procedure

Posted on 2011-09-07
3
Medium Priority
?
230 Views
Last Modified: 2012-08-14
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
Comment
Question by:Sandeepiii
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Easwaran Paramasivam earned 1500 total points
ID: 36500649
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
 

Author Comment

by:Sandeepiii
ID: 36500823
Thanks ,any particular reason to use Like operator here.
0
 

Author Closing Comment

by:Sandeepiii
ID: 36535123
It was helpful.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question