 Sybase Tips & Tricks by Rob Verschoor | |||
|
|
|
||||||||||||||||||||||||
|
This is derived from a
tool I once wrote to intercept end-user-written queries
which might be unacceptably heavy. Those end-users, while
technically not familiar of any SQL or RDBMS concepts,
were allowed to type in free SQL queries as they liked,
which could contain anything. This type of query, a.k.a. "Queries from Hell", often tend to include Cartesian Products, huge joins not using any indexes and other things you might not be happy with as a DBA, because they would put too heavy a load on the server. Basically, this tool examines the query without executing it, and tries to figure out if the query is likely to be very heavy, or does other things you don't want them to do. If so, the tool generates a message; based on this, you can give some gentle explanation to the user why you don't accept his query and possibly suggest some improvements. Should the query look harmless, you can then execute it and pass the results back to the user. Another possible application is to use it as part of some sanity checks on newly developed or modified SQL code which is kept in a repository. When a new version of a module is checked into the repository, you could run an automatic check using this filter, to see if there is anything in the code which shouldn't be there, for example forcing of indexes. This way, the filter could help enforcing SQL coding standards. Anyway, while this query filter is not perfect, it works surprisingly well. Give it a try... It's a Bourne Shell script, which was successfully tested on Digital Unix and Solaris with SQL Server 11.0. For technical reasons, it didn't run on ASE 11.5, and I never found the time to update to make it work with 11.9 or later versions (you could yourself to make it work in combination with ASE's "resource limits" feature, though). Note that this isn't a full-featured tool, but rather something that could serve as inspiration how to tackle cases like this. Download it from here & turn it into something that works for you... |
||||||||||||||||||||||||
|