1

I have a web project in asp.net/C#/Mysql where the data can be up to some 30,000 rows of data to process. Its a reporting tool, and I have to show statistics like counts and sum at several levels.

I want to know which would be the better way to go around this.

I can filter my data to limited columns though which I can query.

  1. Now, Is is a good way to get the data (all rows) to my application on load and whenever user queries I can filter that data and do my calculations in the code and show my statistics.

  2. or I can have a stored procedure do all my calculations and every time user queries I can call the stored procedure and get my statistics.

Thanks

Community
  • 1
  • 1
Neelam
  • 1,028
  • 12
  • 25
  • 1
    There are a few questions about code/stored procedures: http://stackoverflow.com/questions/22907/which-is-better-ad-hoc-queries-or-stored-procedures http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code?lq=1 – nmat Apr 17 '13 at 09:11
  • i dont plan for ad hoc queires.My plan is to get the data on load in a datatable,than maybe maintain it in session and query data from my code.Here i don't have to connect to my db everytime. – Neelam Apr 17 '13 at 09:16

2 Answers2

2

Databases are optimized to do this kind of data manipulation. And since you reduce network load as well i would vote for the second option.

Another possibility is to consider some kind of OLAP solution where transactional data is already consolidated into smaller chunks of data which in turn can be easily queried.

PrinceCharles
  • 101
  • 9
1

I would definetly go with the second option. You are talking about a web application, if You want to get all the data at load, then you must store it somewhere to preserve it during postbacks. If you chose to store it in a session state, you will end up consuming the Web server memory, since you have more than one user accessing your site.

If you store it in view state, then you will end up in a very big client response,which will make your page very slow to load on the client side, and will cause network traffic.

Option 2 is the best, because stored procedures are precompiled, which means they are much better in terms of performance. You will also reduce network traffic.

Hassan Mokdad
  • 5,832
  • 18
  • 55
  • 90