CODEWITHSUNDEEP
sql-serverperformancestored-procedures
asharajay
asharajay

Reputation: 1193

I want SQL Server stored procedure performance help

I have a stored procedure 

name       : sp_select 
parameters : @tablename nvarchar(max), @where nvarchar(max)

I have approx. 20 tables.

This is single procedure to select data from every table.

Now my question is if this procedure executed multiple times can affect the performance on web...??

plz...

Upvotes: 0

Views: 103

Answers (3)

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

The simple answer is: yes -- it can, but doesn't have to.

But this depends on many factors:

  • How often your stored procedure is called.
  • How many records are in each table.
  • How many records (and columns) each query returns.
  • How actual query is called (in other words -- can SQL Server build plan for query).

But, based on declaration of stored procedure, I would afraid of performance problems -- even for a few users.

Upvotes: 1

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65391

It depends on the number of web requests.

It there is a single user on your site, then hitting 20 tables will take time but probably will not be critical. Assuming that the tables are not very large and you do not have indexes in place.

As the number of users increases you could get problems with locking and access to resources.

If you have to access 20 tables for every web request, there is probably a problem with your design.

Upvotes: 1

Kevin Ross
Kevin Ross

Reputation: 7215

It would depend on what table and what criteria you are using. I.e. doing a table scan on a massive table might give you trouble but a small query that uses indexes should be fine

Just out of interest why are you using a SP to do this and not a normal query? Seems a very odd way of doing things and might leave you open to a SQL injection attack

Upvotes: 2

Related Questions