I have just began my first development project for a relatively sized company that needs to manage lots of data. A typical database is 6gb (from what I have seen to date). Among the jobs is confirming. What it is done presently is -
Information is duplicated and moved onto an information warehouse. After that, all of the data needed for the report is collected (1000's of rows and a lot of tables) and aggregated to some reviews database within the warehouse. This really is all completed with saved methods.
Whenever a report is asked for, a saved procedure is invoked which copies the information onto a reviews database which PHP reads from to show the information.
I am not really a large fan of saved procs whatsoever. However the people I have spoken to insist that saved methods are your best option, as queries directly from the data using a programming language are really slow (think 30 mins?). Security is another concern.
So my real question is - are saved methods needed if you have a really large data set? Do queries really take that lengthy on such a lot of data or it is possible to trouble with either the DB servers or the way the information is arranged (and indexed?). I have got a sense that something is wrong.
The reasoning behind utilizing a saved procedure would be that the execution plan that's produced to be able to execute your procedure is cached by SQL Server within an section of memory referred to as Plan Cache. When the process is then subsequently re-run at another time, the execution plan has the potential of being re-used.
A saved procedure won't run any faster compared to same query, performed like a batch of T-SQL. It's the execution plans re-use that create a performance improvement. The query cost would be the same for that actual T-SQL.
Offloading data to some confirming database is really a typical pursuit however you may want to take a look at indexing strategy around the confirming database because it will probably have to be not the same as those of your OLTP platform for instance.
You may even want using SQL Server Analysis Services to be able to service your confirming needs because it seems like your reviews contain plenty of data aggregations. Storing and processing data with regards to fast counts and statistics is what SSAS is about. It may sound like the time is right for the business to appear as creating a data warehouse.
I really hope this can help but don't hesitate to request further particulars.