Re: Long-running queries

olea@hal.nta.no (Ole J. Anfindsen) wrote: >In article <3uk28e$ccg@lennon.cc.gatech.edu>, gukal@cc.gatech.edu (Sreenivas Gukal) writes: >|> 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. >Most sites have lots of users who would love to run extensive queries against >the database if they were allowed to. As earlier replies to your posting have >pointed out (at comp.databases.theory, which is where I found it), >it's generally not considered a good idea to mix OLTP and DSS, and >one is therefore forced to use some less than optimal solution, e.g. multiple >databases. >The underlying problem, in my opinion, is the unability of the classical >transaction model to cope with DSS and OLTP coexistence. My own research is >focused on what I refer to as Application-Oriented Transaction Management >(AOTM). A paper of mine proposing transaction management mechanisms that >would enable DSS and OLTP coexistence will appear in the proceedings of the >2nd Internation Conference on Concurrent Engineering: Research and Applications >(to be held in McLean, Virginia, August 23-25, 1995). The paper is entitled >"Dynamic cooperation between database transactions by means of generalized >isolation levels". >A pointer to the web-server of the CE95-conference can be found at my WWW home >page (see below). By following the "Research material" pointer of my home page, >you can also find two technical reports upon which my CE95 paper is built, and >more. >Best regards >-------------------------------------------------------- >Ole J. Anfindsen >Telenor R&D >Postboks 83, Instituttveien 23, 2007 Kjeller, Norway > >Fax: (+47) 6381 0076 or 5190 >Internet: ole.anfindsen@fou.telenor.no >WWW: http://www.nta.no/brukere/olea >X400: G=ole;S=anfindsen;O=fou;P=telenor;A=telemax;C=no; Ole, If you consider the "transaction" model to be the problem then I suspect you are going down the wrong path. Perhaps by changing the transaction design somewhat you can chip away at the tip of the iceberg, but the iceberg tends to be in the nature of the workload and the tuning of the underlying operating system and middleware to efficiently use system resources (including processor cycles and I/O). Generally, you can tune a system to be highly efficient for classical transactions (e.g., measured in the 100s or 1000s per second), or you can tune it to handle long running queries, but the workloads are of a nature that tuning for one adversely impacts the other. You can also tune a system to be mediocre for both. I've spent over 25 years in the design and development of high end operating systems and middleware (e.g., transaction and database systems), and I would suggest that you can probably have much more of an impact on the performance by examining the organization of the system that the transactions and queries run on (where system is the hardware, operating system, and middleware).