Click here to Skip to main content
14,271,663 members
Rate this:
Please Sign up or sign in to vote.
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...

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
Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
Comments
thatraja 6-Mar-12 9:45am
   
Right, 5!
Monjurul Habib 8-Apr-12 12:19pm
   
5!
Rate this:
Please Sign up or sign in to vote.

Solution 2

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 :)
   
Comments
thatraja 6-Mar-12 9:45am
   
Right, 5!
Monjurul Habib 8-Apr-12 12:19pm
   
5!
Rate this:
Please Sign up or sign in to vote.

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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100