Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Best stored procdure to get one row with intensive requests

Posted on 2009-07-11
8
Medium Priority
?
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

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.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 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