Re: Long-running queries

In article <3uk28e$ccg@lennon.cc.gatech.edu>, gukal@cc.gatech.edu says... > >Hi Folks >Part of my Ph.D. thesis deals with efficiently supporting long-running >queries (maybe Decision Support System queries) without affecting >concurrent transactions in an OLTP environment. I dont know how important >and/or frequent a problem this is in real applications. If you >have encountered similar situations, can you please let me >know the context you had the problem in and what your solution was. > We have a continous need to do long running queries in an OLTP environement. On databases of a few GB we generally have no problem with that. We are of course conserned what will happen when they grow larger. Although the "general consensus" is to set up two databases we are not very happy with that. People talk about different designs needed (denormalisation++). This is of course no problem. We add extra tables to the OLTP db for this (not destroy the OLTP design). The point is we realy want as current data as possible, and most machines will have lots of idle time that could be used for DSS or other long running transactions. Also we could have only one database to maintain and no replication, data loading or whatever would be needed. Some triggers, application logic or batch programs would often be needed though, to update the extra tables with denormalized and/or sumed up data. Our DSS queries also often has as their purpose to find data that shall eventually be put back into the database (in our case which customers should receive what direct mail or other marketing activity, but in other cases it might as well be products to be reordered or whatever). This must back into the OLTP database, and the whole design is simpler if it is all on one machine and in one database. If dirty reads are acceptable (which they very often are) Informix seems to me to be very near to solving this problem via the PDQ priority system. I am looking into that now, but they probably need some extensions. It seems to me that sofisticated prioritisation of queries could solve this, at least for many applications. If it had been possible to say that a query should be stoped temporarily, if there are any higher priority (OLTP type) queries pending, it seems to me we could do any type of queries at this lower priority without hurting OLTP response times significantly. I can see a problem in the number of checks that would have to be done inside the db engine to implement this might slow it down on all queries? If dirty reads are not acceptable, some form of versioning would probably be needed as well. I can see a lot of problems with that. The only reason for implementing it, that I can see, as opposed to using another database, is if the long queries are totaly dependent on very up to date data. A replicated database might still be better in this senario though, except possibly for running large batch jobs. Are there any db designers out there that can tell us if the prioritisation sceem might be possible, or is it totaly out of order? If prioritisation along these lines is possible we should expect Informix to implement it! Such expectations are very important for Informix when desiding what to develop. We can't have them though without knowing about them, and that they are (or at least might be) possible to implement. PS: Ken Block writes: >In the Sybase applications that I have put together, one table scanning >query can cause the entire OLTP system to block until the query finishes. This is a problem with SE also, but luckily we have a better option in the OnLine engine. It does at least NOT stop. Nils.Myklebust@ccmail.telemax.no NM-data, Dalsbergstien 7, N-0170 Oslo, Norway My opinions are those of my company