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:
- Do not use
select *
syntax, just bring back columns you need. - I am guessing you do not have an index on the columns in the
where
clause. Think about adding them. - If you need 2000 items, why not just bring them in one shot.
精彩评论