CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
user3262364
user3262364

Reputation: 371

How indexing will affect the stored procedure performance

I am working with SQL Server 2008.

I have two tables like this:

Location_tbl structure is like this:

 Locid    int 
 LocName  varchar(150)

Locid is the primary key

TrTransaction_tbl structure is like this:

transactID    int
TBarcode      varchar(20)
Locid         int
PlateNo       varchar(20)
dtime         datetime
DelEcode      nvarchar(50)
Paydate       datetime
KeyRoomDate   datetime
DelDate       datetime
Status        int

In this table transactID is the primary key and locid is a foreign key.

In the transaction table I have more than 200 000 rows, daily increasing around 30 000 records.. I haven't implemented any indexing on my transaction table.. so my stored procedure execution takes a long time.

I added an index to my TrTransaction_tbl.locid like this:

create index transactlocid on transaction_tbl (Locid asc)

I want to know if this index will help my stored procedure run faster?

This will affect anything in the table or records?

This is my stored procedure:

ALTER procedure [dbo].[IBS_fetchreqVehicleinPodiumtestnew1]
   @locid INTEGER = NULL
AS BEGIN
   SET NOCOUNT ON

   DECLARE @TodayMinus7Days DATETIME
   Declare @krrt integer
   Declare @DT integer
   SET @TodayMinus7Days = getdate()-1

   SELECT  
       t.TBarcode, t.PlateNo, t.DelEcode,
       datediff(MINUTE, t.PayDate,
             CASE t.Status
                WHEN 3 THEN GETDATE()
                WHEN 4 THEN t.KeyRoomDate
                When 5 THEN  t.KeyRoomDate
                End) as KRRT,

             datediff(MINUTE,t.PayDate,
             CASE t.Status
              WHEN 3 THEN GETDATE()
              WHEN 4 THEN GETDATE()
             WHEN 5 THEN t.DelDate
             END) as DT

   FROM    
       dbo.Transaction_tbl t
   WHERE   
       ([status] IN (3,4) AND locid = @locid AND dtime >= @TodayMinus7Days)
       OR 
       ([status] = 5 AND DATEDIFF(n, CAST(DelDate AS DATETIME), GETDATE()) <= 3
         AND locid = @locid AND dtime >= @TodayMinus7Days)
   ORDER BY  
       paydate
       end

if i create index like this:

CREATE INDEX IX_TRANSACTION ON transaction_tbl
(
    Locid ASC, DTime ASC, Status ASC, DelDate ASC
)

this dtime means ,,,am taking the current time of saving each records,,if i give index to this,,my insertion will slow right??

Upvotes: 2

Views: 1033

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56725

I doubt very much that that index will help. It is really impossible to tell without a query plan, but you might try an index like this:

CREATE INDEX IX_TRANSACTION ON transaction_tbl
(
    Locid ASC, DTime ASC, Status ASC, DelDate ASC
)

This index will allow the WHERE clause conditions to be resolved entirely from the index. Ideally it will search for LocId, and DTime, and then predicate-resolve Status and DelDate. Then it will still have to go back and lookup the rest of the columns from the main table (so depending on the number of matches it expects, it could still skip this index, but I'd need the query plan to tell that).

As HLGEM points out in the comments, DelDate is already a DATETIME, so there's no need to convert it. So the following where clause should work better:

WHERE   locid = @locid
  AND   dtime >= @TodayMinus7Days
  AND   (( [status] IN (3,4) )
      OR 
         ([status] = 5 AND DATEDIFF(n, DelDate, GETDATE()) <= 3)
        )

Upvotes: 1

mwigdahl
mwigdahl

Reputation: 16578

Because you have added an index on locid and both your WHERE clause branches have a specific filter on locid, the optimizer should be able to significantly improve the performance of this query.

If it doesn't, I would suggest "factoring out" the locid = @locid clause out of the OR construct; that might help the optimizer if it is getting confused.

Certainly it would be a good idea to turn on the "show actual execution plan" option in SSMS and see whether it is generating a good plan or not. If you post the screenshot of the plan, we can probably see whether you are getting the improvement you want or if there is something else that could be done.

Upvotes: 0

Related Questions