limit of data in select stored procedure

Question

limit of data in select stored procedure

sanjeev.k

Valued Member

Points: 53
More actions
February 5, 2010 at 10:03 pm

#386262

Hi, I want to know if there is any limit of data with in one select stored procedure. Is it right to bring all tables in one Stored procedure or should I use multiple SPs in case of large amount of data. Is there any performance issues. Is it depends upon Network capability or any other issues. please help about this topic.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Since your question is vague, my answer will be too: it depends.

Usually, the number of tables or number of rows that a reaffected has not really an influence if there will be one procedure or more.

If the proc deals with data on the same server, Network issues won't matter.

If you need help on a specific scenario, please describe it more detailed.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sanjeev.k Valued Member Points: 53 More actions · Answer 2

Hi lmu92, I have same server and having suppose more than 1000 tables and each table can have millions of records. I've to show these records in my .NET applications.

What is exactly right, should I use one SP for this or more than one, in performance point of view.

If I use one SP to do so will network bandwidth effect it.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The answer to that question is: It Depends.

You will more than likely need numerous queries to support the various different aspects of your application. Some queries should require the joining of multiple tables into the proc, some will not.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 4

sanjeev.k (2/6/2010)
Hi lmu92, I have same server and having suppose more than 1000 tables and each table can have millions of records. I've to show these records in my .NET applications.
What is exactly right, should I use one SP for this or more than one, in performance point of view.
If I use one SP to do so will network bandwidth effect it.

I'd question the business case in general!

Exactly right would be NOT to show thousands of tables with millions of rows each!

You should think about what data your application really needs, define some business rules that will define the details and work from there.

Moving that amount of data across the network to transform it with a .NET app seems the wrong way to go to me....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sanjeev.k (2/6/2010)
I have same server and having suppose more than 1000 tables and each table can have millions of records. I've to show these records in my .NET applications.

Who is going to read thousands of millions of rows?

Seriously, this is not a SQL Server capacity issue. It's a common sense issue.

While you can use a single stored proc to send thousands of millions of rows to a single client you will likely kill any network in existence by doing so, you will bring just about any client PC to its knees (memory usage) and no user in their right mind will read all of that, even if their machine could handle it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

I believe there might be a bit of a language barrier problem here... I believe the OP is saying that, yes, he has thousands of tables and millions of rows, but the OP probably only needs to show 1 set of information based on criteria at a time. I don't believe he actually wants to display millions of rows all at the same time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)