开发者

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

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开发者_JAVA百科 takes 4 seconds. Is there any way to optimize it? (like single stored procedure for 2000 records)

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.


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...


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!


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.
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜