Re: Summary: Controlling "ad hoc" queries

In article <1992Sep11.014224.19879_at_usage.csd.unsw.OZ.AU>, troy_at_cbme.unsw.EDU.AU (Troy Rollo) writes:
> A number of solutions are being used. The simplest, for dealing with
> overgrown queries, is both fascist and liberal limits being imposed
> on the tool (We do this by means of a client/server system, and impose
> the limits at the server, thus preventing wasted network traffic).
> The liberal limit informs the user periodically that the current
> query is really not at all simple and asks them if they really
> want you to do it. The fascist limit states in not so many words that
> the administrator thinks you are being unreasonable and you should
> go back and try it again.

I assume that you take the user's query and run it through EXPLAIN, then you examine the results of EXPLAIN to determine how the script will perform, and then you notify the users of the performance of the query before actually doing the query.

This would be nice but adds a slower startup and you still don't know how many rows are going to be returned OR processed.  

> These are both configurable limits based on number of bytes and/or rows.
> Should the limit be enforced, the server informs the database via a
> cancel message, and if the user is really desperate they can still use
> whatever they did get back.

The problem with adhoc querries is more the fact that the user processes thousands of records usually with many repeated full table scans to pull off a report of 10 or less rows.

SQL*ReportWriter already allows you to put a restriction on the size of the returned report but doesn't factor in the amount of work done by the kernel to get weed out the required rows and get them into the right order before actually sending them to the user.

> The second issue I think you were asking about is the issue of
> users not knowing the structure of the database and doing something
> which returned meaningless results (by, for example, misspecifying
> the joins). Without trying to get into an advertising blurb, our
> approach is, said briefly, to have the tool do the joins itself,
> thus eliminating the need for the user to do them. There are a
> lot of subtleties in doing that, which I won't go into here.

Having the tool actually formulate the script from the requirements as specified by the user is a safe way of ensuring the indexes are used correctly.

Oracle are doing this (I hope) with their new end-user tools.

-- 

Bruce...        pihlab_at_hhcs.gov.au
                 ^^
*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************