Solved

Best stored procdure to get one row with intensive requests

Posted on 2009-07-11
8
172 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
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)

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

778 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