Solved

Best stored procdure to get one row with intensive requests

Posted on 2009-07-11
8
153 Views
Last Modified: 2012-05-07
Hello,

I need a very optimized stored procedure that gets one row from 500,000 records with primary key index.

The stored procedure will be used with asp.net DataReader. Number on concurrrent request at a time may reach 1 million per minutes.

So I need the most optimized Stored Procure that will handle it properly notice that the return row will contrain 5 fields

Thanks

Zaki
0
Comment
Question by:zaki100
  • 3
  • 3
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24830600
without more specifics, we can't tell.  are you searching based on the primary key index?
0
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 500 total points
ID: 24830793
A stored procedure like that is quite straight forward. You can use with(nolock) to make the query less sensetive to deadlocks, but other than that it's just as simple as it can get:

create procedure GetLine
   @Id int
as
select Some, Fields, From, The, Table
from SomeTable with(nolock)
where Id = @Id

An alternative would be to use output parameters instead of returning a result set:

create procedure GetLine
   @Id int,
   @Some int,
   @Fields int,
   @From int,
   @The int,
   @Table int
as
set nocount on
select @Some = Some, @Fields = Fields, @From = From, @The = The, @Table = Table
from SomeTable with(nolock)
where Id = @Id

You would have to make a comparison under high load to determine if there is any real performance difference.

Have you considered caching the results to reduce the number of database queries?
0
 

Author Comment

by:zaki100
ID: 24831659
Hello,

My Search is based on Primary key as parameter in stored procedure.

Regarding caching I not considering that because it will consume memory.

Any more advise or revised one?

Thanks

Zaki
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:Göran Andersson
ID: 24831699
Make sure that the primary key has an index.

Usually when you create a primary key, the index is created automatically, but it's possible to create a primary key without creating the index.
0
 

Author Comment

by:zaki100
ID: 24831718
Last question if you please

Is there a difference if there is duplicate and I make index  or the primary key  with index and no duplicates has better performance?

Thanks
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 24831898
For performance there isn't a very big difference if the index is unique or not. The database may be able to optimise the use of a unique index slightly better, because it knows that it can never return more than one match.

If you use a unique index or not should mainly be based on whether you need the values to be unique or not.
0
 

Author Comment

by:zaki100
ID: 25006314
Sorry for the delay.

Please note my acceptance for the first answer.


Regards

Zaki
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 33
crm development 2 37
How can I use this extension method? 8 36
encyps queries mssql 15 27
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now