Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Best stored procdure to get one row with intensive requests

Posted on 2009-07-11
8
Medium Priority
?
205 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 2000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

885 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