How to reduce time for taken by stored Procedure for inserting a single record?

Question

We wrote a stored procedure for selecting a single record from DB at a time. Using the same stored procedure to read 2000 records it takes 4 seconds. Is there any way to optimize it? (like single stored procedure for 2000 records)

Thanks & Regards Padma

Answer 1

SQL Query Analyzer

http://www.developer.com/db/article.php/3418031/Query-Analyzer-Tips-and-Tricks.htm

Stored Procedure Optimization

http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm

How-to-Optimize-Queries

http://www.serverwatch.com/tutorials/article.php/2175621/How-to-Optimize-Queries-Theory-an-Practice.htm

last but not least Index, Index, Index...

Answer 2

Consider that SQL Server (and all RDBMS) work best with set-based operations.

Consider changing your calling code (the client) to expect a set of records to read.

Please post your stored procedure, either reading or inserting, so we can help find the best solution!

Answer 3

Here is my Stored procedure:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go

ALTER PROCEDURE [dbo].[GetItemValue](@ItemName VARCHAR(200),@TimeStamp as DATETIME)

AS

select * from EIItemData

where ItemName=@ItemName AND TimeStamp=@TimeStamp

Can anyone tell me how can this stored procedure can be optimized to read 2000 records instead of calling once for each item.

Thanks & Regards

Padma

Answer 4

Looking at your SQL, I'd say, hit up the following points:

1. Do not use select * syntax, just bring back columns you need.
2. I am guessing you do not have an index on the columns in the where clause. Think about adding them.
3. If you need 2000 items, why not just bring them in one shot.