QEP and printqry

On Tue, 10 Sep 1996, Barry Smith wrote:

> I have a C/ESQL Program with many SQL statements within it. Rather than
> tease out each 'Select' statement and run a QEP on each one I would
> like to run QEP on the whole program and view the results to find the
> inefficient 'Select' statements.
> 
>  1) Can this be done?  

Yes.  

>  2) If so - How?  

Just SET QEP and don't turn it off.

>  3) If not - can anyone suggest a way of finding inefficient statements 
>     within programs without running each SQL statement individually 
>     via QEP?

You may want to consider tackling this a different way.  You are looking at
this from the programmer's perspective and asking, "How well is this query
doing compared with what it should theoretically be capable of?"  That is a 
very engaging problem and a lot of fun to diddle around with, but from the
user's perspective it may be the wrong approach entirely.

The user is more concerned with how long it takes to accomplish a
specific task.  The user couldn't care less that a query is using 100
times more CPU time and disc I/O than it should, provided it is still
executing in the elapsed time the user can allow it.  The user is much more
worried about the query that takes 10 seconds when they really need it
to be nearly instant.  Looking at QEPs first won't necessarily direct you
to the queries that are a high-priority problem for the users.

Instead, try setting the environment variable II_EMBED_SET to 'printqry' on 
the client to log the query submitted, and the response time.  This should
direct you to where you will get the biggest opportunities to reduce response
time.

Once you've found these slow queries, THEN you want to look at the QEP.

Just my .02 Canadian pesos worth.  Feel free to take it or leave it. :-)

========================================================================
Roy Hann                                   rhann@tnc.com
BKB Engineering Ltd.,                      NAIUA Member
11211-76th Avenue,                 
Edmonton, Alberta                          http://www.tnc.com/bkb
T6G 0K2                                    Tel: (403)497-2409
Canada                                     FAX: (403)436-6055
========================================================================
Ingres Q & A
To William's Home Page
Email William