Best stored procdure to get one row with intensive requests

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
zaki100Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
without more specifics, we can't tell.  are you searching based on the primary key index?
0
Göran AnderssonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zaki100Author Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Göran AnderssonCommented:
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
zaki100Author Commented:
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
Göran AnderssonCommented:
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
zaki100Author Commented:
Sorry for the delay.

Please note my acceptance for the first answer.


Regards

Zaki
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.