Hi,

Since I have come across MANY smart people on this forum and they pretty much have helped me out with every db2 question I've had so far.. I was curious to know how they would go about tackling another question I've been wondering about --- any thoughts / ideas will be appreciated!


This is how our system is setup -- basically, we have equipment out in the field that monitors certain kinds of data and dumps this information into one table ( let's call it Data table). The frequency of the data insertion is different for each device that is out there.. some values are inserted every 5 mins, others 15mins, etc.. so as you can tell we have a big Data table that is growing every day. We have customers who view this data on a daily basis via graphs and reports. The graphing program was creating a long time ago by someone else and it was done in java. Our customers can select different intervals to view the data on the fly.. we are looking into other tools / programs to replace the current one.. I've been told that the current charts look a little old school and that something 'fancier' would be nice. I'm looking into php charts (we have another app that's done in php but no charting in that app just simple queries against 'light' tables) ,etc to see if I can find something better.. the question is about the back end...am trying to figure out what strategy to implement to make the data query return results super fast.. the users can select a date range, interval, and the id of what they want to look at.. based on the level selected, we have to retrieve values for individual points and add / average them up.. do you think stored procedures are the way to go? Or summary tables? How would you design the back end when you know for sure that you need to manipulate the data on the fly?

Have you ever looked at the charts on the google finance page.. they come up super fast and you can do pretty big date ranges :

http://www.google.com/finance?q=SHA:000001

Thanks a ton and sorry for the long message!

Stored procedures are very efficient in most cases. Just make sure you tables are indexed properly and that you have plenty of real memory (not virtual) allocated for bufferpools.

The most efficient way of accessing DB2 is by means of static SQL. To accomplish that in a web-app...... I'd go for java-applets with sqlj.
Not a very common approach, not well known outside the mainframe world. But still: the most accurate answer to your question imho

SQL Stored Procedures use static SQL, and multiple SQL statements can be executed at once on the server without going back and forth between client and server. SQL Stored Procedures also run inside the DB2 address space, which makes them even faster.

You are right, I tripped over my own mainframe roots again. But, I've never seen an application consisting of Stored Procedures only. Mostly simple selects + SP's for the actual transaction, right? Those simple selects could be static as well := best of 2 worlds.

I have seen applications that use stored procedures for selects. It is usually more than one at a time since logic can be built-in to the stored procedure. But I have also seen SP's with just one "simple" select. I don't know of any reason not to do that.

Also don't forget the control you get with stored procedures. You can revoke all privileges from the tables/views from all users and just grant them EXECUTE privileges on the respective stored procedures. Thus, no user can send queries that may hog the whole system because you have encapsulated all possible queries in stored procedures - and you can optimize those queries specifically.

I don't think there is a simple answer to your questions. Application performance is a result of many things, including:
- data model that is appropriate for the task;
- efficient physical design of the database;
- scalable server and network hardware;
- correct separation of duties between the database, application server, and client-side programs;
- etc.

You could use stored procedures or summary tables, or both, or neither, in addition to many other elements of the puzzle. If you look at your own example, Google Finance, you will notice that data for the graph are only retrieved when you change the graph scale; all other manipulations are performed locally by javascript and Flash.

Thanks for all the replies everyone! Nick -- you're right, performance is dependent on a lot of factors with the database being only one of them. I think summary tables and sprocs definitely won't hurt...we definitely need to re-evaluate our current system and come up with a better strategy for fast data retrieval. I know this is a db forum but do any of you have any experience with graphing tools that work well with db2 as a backend?...thanks!

Just one more thing on the SPs: It is stupid to think just because you move your code from the application to a stored procedure will make it run faster. If you have a Java app, which doesn't do much in terms of accessing the database or is badly coded, the same code will just run as badly in a stored proc. (I'm just mentioning this because such an evaluation was done a while ago at some university and the folks were surprised by the obvious results.)

I would not want graphing tools to access DB2 (or any database, for that matter) directly, especially in a highly interactive application. There should be another layer that prefetches some of the data and caches it for the graphing tool to use.