how to use index in stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to use index in stored procedure?

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-08 : 10:37:44

I have a stored procedure like this. It takes 4 seconds to execute it.


create procedure test
AS
BEGIN
select date, qty1, qty2, qty3 from table1
where field1 = 'xyz' and field2 = 123
END
GO
execute test

Now I'm trying to use index first time as below but it still takes 4 seconds to execute the same code.

So I wonder if I'm doing something wrong here.


create clustered index on dbo.table1 (field1,field2)
go
create procedure test
AS
BEGIN
select date, qty1, qty2, qty3 from table1
where field1 = 'xyz' and field2 = 123
END
GO
execute test

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-08 : 12:49:21

I must admit, that does not make much sense to me either. I would have expected that without the index, it would do a full table scan and with the index it would need to do only an index seek. That would mean much fewer reads and much faster execution time (unless the selectivity was so poor - for example, if every row had field1 = 'xyz' and field2 = 123).

Take a look at the execution plans with and without the index to see if it is a table scan without and index seek with the index. If you hover over the execution plan icon or right click and select properties, you can see how many reads and cpu time was used up in each case.

The only other possibility for why there is not a performance improvement is if the table is being locked by another process and the elapsed time is really the wait time.

Edit: I am of course assuming that the syntax error in the create index statement is just a typo and that you did successfully create the index on the table.

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-08 : 13:04:15

How many rows are in the table, and how many meet the criteria in the where clause?

-Chad

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-09-09 : 02:25:13

As suggested check the Execution Plan. Have you run the query multiple times?
Make sure to clear out the cache , everytime you start a fresh round of tests
http://www.sqlserver-dba.com/2010/05/query-baseline-testing.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-09 : 16:38:58

Thank @sunita, @chadmat and jackv

I have more than 5,00,000 data in the table and as it could execute it in 4 seconds so I have no problem with that sp.
But the real problem is coming for me in the following procedure which is taking 24 seconds to execute irrespective of whether index is there or not.


drop index firstindex on dbo.table1
go
create clustered index firstindex on dbo.table1 (field5, location, date)
go
create procedure procedurename
@startdate datetime, @enddate datetime, @location varchar(50), @field5 (varchar(50)
As
begin
select f.date,
sum(case when field2 = 'xyz' and field3 = '3' and field4 = 'am' Then qty1 else 0 end) as XYZ,
sum(case when field2 = 'abc' and field3 = '3' and field4 = 'tx' Then qty1 else 0 end) as ABC,
sum(case when field2 = 'def' and field3 = '3' and field4 = 'td' Then qty1 else 0 end) as DEF
from dbo.calendartable('2012010','20201231',0,0) as F
left join table1 t on t.date = f.date
and field5 = 'red' and location = 'central'
where f.date between @startdate and @enddate
end
go

Can you check if this index is correct or i need to change anything here?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-10 : 06:52:50

I would change the index to:

create clustered index firstindex on dbo.table1 (date, field5, location)

Also, the join and where clause probably should be one of these. the second is equivalent to what you already have, but makes the intent clearer.

left join table1 t on t.date = f.date
and field5 = 'red' and location = 'central'
AND f.date between @startdate and @enddate

-- OR

INNER join table1 t on t.date = f.date
and field5 = 'red' and location = 'central'
WHERE f.date between @startdate and @enddate

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-10 : 08:14:01

Thanks Sunitabeck...now it executes at 23 seconds...just 1 minute diff.

I just would like to ask few things regarding index here.
once i created such clustered index in a table, if I create any stored procedure based on the table, will all the stored procedures use the index automatically?? or I need to add any statement to make understand the sps to use the index?

If yes, for some stored procedure I may don't want to use the clustered index, is it possible?

quote:

INNER join table1 t on t.date = f.date
and field5 = 'red' and location = 'central'
WHERE f.date between @startdate and @enddate

@sunita, As to this, I want to display all missing dates also with zero values, so I can't use inner join if I'm not wrong. I will try your first option though. And I guess it(first option) also equivalent to my code.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-10 : 09:01:22

My experience has been that when you create a new index and if it is the appropriate index to use, the execution plan gets recalculated to use the index. However, I don't know if it is guaranteed to be so.

To be sure, you can use DBCC FREEPROCCACHE to remove the execution plan. If you use that command without any parameters, it would wipe out all the execution plans, which may not be desirable if you are on a production system.

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-11 : 12:36:02

Unless you have a nonclustered index that satisfies all of your search criteria AND contains all of the data needed for the query, then the clustered index will always be used in some capacity. It may be for a lookup, or it may be a CI Scan, but it will be used.

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-11 : 12:50:43

What datatypes are field5, location and date (In the table)?

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-11 : 13:26:34

@chadmat
All with varchar(50). And of course, I'm planning to change date to actual datetime but not now.

Do you mean I shall replace clustered with non-clustered?

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-11 : 13:36:12

That is a wide key for your clustered index. If you have any Nonclustered indexes, they will be very bloated.

No, I am just thinking you should have a different key for your clustered index, (Like an in, is there a primary key/identity column)?

Have you looked at the query plan? How many rows are returned from dbo.Calendartable? Why is everything varchar(50)?

-Chad

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-11 : 17:04:35

@chadmat
(1) I have an identity column but I'm not sure if this is a primary key or not. How I can check it?
I don't have any other primary key though.
(2) it returns 31 rows usually
(3) I don't have any index. This is the first time I tried index and it failed to work.
I may have to learn how to use non-clustered index and difference between clustered and non-clustered before I try.

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-11 : 17:29:48

Try putting the clustered index on just the Identity column, then create a Non Clustered index on: date, field5, location

and include columns Field2, Field3, Field4 and qty:

create clustered index firstindex on dbo.table1 (<theIDColumn>)
GO
create nonclustered index secondindex on dbo.table1(date, field5, location) INCLUDE (Field2, Field3, Field4, qty)

This may help, but without the query plan, we are just shooting in the dark.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources