Have Geek Brain

How can I reduce the storage time for a stored procedure to insert one record?

We wrote a stored procedure to select one record from the database at a time. Using the same stored procedure to read 2000 records, it takes 4 seconds. Is there a way to optimize it? (for example, one 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 one can save this stored procedure to read 2000 records instead of calling one for each item.

+2


a source to share


3 answers


SQL Query Analyzer

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

Optimizing a stored procedure

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

+1


a source


Please note that SQL Server (and all RDBMSs) perform best with set-based operations.

Consider changing your invocation (client) code to expect the recordset to be read.



Please post your stored procedure by reading or pasting it so we can find the best solution!

0


a source


Looking at your SQL I would say hit the following points:

  • Don't use syntax select *

    , just return the columns you want.
  • I'm assuming you don't have an index on the columns in the sentence where

    . Consider adding them.
  • If you need 2000 items, why not just bring them into one shot.
0


a source






More articles:


All Articles