Re: Summary: Controlling "ad hoc" queries

> Are "user query tools" a dead end, due to inadequate protection against
> "incorrect" queries?
 .
 .
> writing tools...)?. If not, how are they best used (only with heavy
> training for users, small databases only, on extracted "reporting"
> databases...)?

No, they're not really a dead end.

I was hoping to see a little more discussion before I threw my $.02 in. Before I start, I should mention that anything I say may well be biased because I head up a team developing a Windows based end user query and report writing tool, with ad hoc reports.

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.

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.

Some clients prefer to take summary tables on a daily basis in the early hours when nobody is accessing the database, and then allow the users access to only those summary tables, or to those summary tables together with a subset of source tables.

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.

--
__________________________________________________________________________
troy_at_cbme.unsw.EDU.AU	Overworked, overcommited and always multitasking.
        Fine, so I'm an FOFH. Now shut up and don't complain.