How to make fast Stored Procedure ?

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

, +

Hi,
I am using the following query in store procedure and after execution I get only 27 records. But the procedure will take 5+ minute for execution, what I do for its fast execution?......... Titlefee tabel having approx 62,000,00 records...

SQL

SELECT  u.sname
        , u.idUsr
        , count(distinct o.idord) as Orders
        , premiums = sum(IsNULL(tf.curAmount, 0))

 FROM  ord o (nolock)
       Inner Join usr u (nolock) on o.idUsrTO = u.idusr
       Inner Join titlefee tf (nolock) on  tf.idord = o.idOrd
       Inner Join feetype ft (nolock) on ft.idFeetype = tf.idFeetype
 WHERE
       ft.bTOCommission = 1
       AND tf.dtIssue between @dateFrom and DATEADD(DAY, 1, @dateTo)
       AND o.idBusUnit in (select idBusUnit from @BusUnit)
       AND o.idOrdType = (Case when @idOrdType = 0 or @idOrdType Is Null then o.idOrdType else @idOrdType End)
       AND o.idUsrTo =  (Case when @idTo <=0 or @idTo is null then o.idUsrTo Else @idTo End)
       AND o.idOrdStatusCurrent = 3

 group by
       u.sName
       , u.idUsr
 order by
       u.sname
       , u.idUsr

Posted 6-Mar-12 1:42am

bhupendra.ramani

Add a Solution

4 solutions

Solution 3

Check this bunch

Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)[^]
Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and denormalization)[^]
Top 10 steps to optimize data access in SQL Server: Part IV (Diagnose database performance problems)[^]
Top 10 steps to optimize data access in SQL Server: Part V (Optimize database files and apply partitioning)[^]

Posted 6-Mar-12 3:48am

thatraja

Comments

Monjurul Habib 8-Apr-12 12:18pm

5!

Solution 4

Declare @idOrd Table (idOrd int Primary Key,idUsrTO int)
insert into @idOrd (idOrd,idUsrTO)
select idord,
idUsrTO
from
ord o(nolock)
where
o.idOrdStatusCurrent = 3
And o.idBusUnit in (select idBusUnit from @BusUnit)
AND o.idOrdType = (Case when @idOrdType = 0 or @idOrdType Is Null then o.idOrdType else @idOrdType End)
AND o.idUsrTo = (Case when @idTo <=0 or @idTo is null then o.idUsrTo Else @idTo End)
and o.curARBalance is Not Null

Declare @TblidOrds table (idOrd int,curAmount MONEY,dtIssue datetime)
insert into @TblidOrds (idOrd,curAmount,dtIssue)
Select
tf.idOrd,
ISNull(tf.CurAmount,0),
tf.dtIssue

from
titlefee tf (nolock)
Inner join FeeType Ft on Ft.idFeeType = tf.idFeeType
Inner Join @idOrd o on o.idOrd = tf.idOrd
where
Ft.bTOCommission=1
and curAmount is not null
and tf.dtIssue between @dateFrom and DATEADD(DAY, 1, @dateTo)

SELECT u.sname
, u.idUsr
, count(distinct o.idord) as Orders
, sum(tf1.curAmount) as premiums

FROM
@idOrd o
Inner Join @TblidOrds tf1 on tf1.idOrd=o.idOrd
Inner Join usr u WITH (NOLOCK) on o.idUsrTO = u.idusr

group by
u.sName
, u.idUsr
order by
u.sname
, u.idUsr

Posted 2-May-12 21:10pm

bhupendra.ramani

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Accepted Answer · 2012-03-06T02:00:00

The first thing you should do is run that statement through EXPLAIN to see what your query is doing.

In SSMS, make sure you have the database selected and open a new query window. Paste your SQL into the window, then choose Query -> display estimated execution plan (Ctrl + L)

The query plan is displayed in the window at the bottom. Look at the plan, it will tell you where things are taking time (percentage relative to batch) If you are unfamiliar with plans, have a look here...

http://www.simple-talk.com/sql/performance/execution-plan-basics/[^]

You could also run your query through the tuning advisor to see if any indexes are recommended. However, if table titlefee is the table with the most records, you need to consider the operations you are performing against it.

1) You are limiting the results from this table by field dtIssue. Is this field part of an index, or is it causing a table scan (look in your execution plan)

2) You are summarising by field curAmount - this could possibly be indexed.

Using the execution plan + query tuning advisor, you should be able to index your tables for optimal performance.

However, by introducing indexes, you have to weigh that up against how often the table is updated \ the process that updates the data (e.g user application? overnight ETL?). It can be a bit of a dark art to get the correct balance of READ against WRITE performance :)

ZurdoDev · Accepted Answer · 2012-03-06T01:50:00

You need to add indexes to your tables. You can run the Database Engine Tuning Advisor, which is under the Tools menu in SQL Management Studio. It can suggest indexes or statistics to add to help improve performance.

You can also run your query showing the Actual Execution Plan or the Estimated Execution Plan (both under the Query) menu in SMS and see what particular step is taking so long.

How to make fast Stored Procedure ?

4 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0