On Sep 22, 23:32, Brian Wilkins wrote:

> I wish I knew some easy way of telling where the bottlenecks are, just by
> looking at a table or running some utility. We have been going through
> our system running queries, looking at the query execution plans and
> timing the queries, to see which are particularly slow.
> ...
>>One useful trick is to turn on printqry for a while (say a day or two),
>>run the system normally, and munge the output to pick out the queries
>>with the longest response time

I have found that the easiest way to spot slow-running queries in applications
is to set the II_EMBED_SET environment variable (or logical) to "printqry".  
The query output with query timings is placed into a file called iiprtqry.log.

[ William: You can change the file that printqry points to by using:

	setenv II_EMBED_SET "printqry; qryfile xxx"

where xxx is the filename - this is a C-shell example 

        However, II_EMBED_SET doesn't seem to handle trace points or qeps
        to a file (let me know if I'm wrong here!). So you could use ING_SET
        instead (which is interactive), but you need to log the output. eg:
 
                script
                setenv ING_SET "set printqry; set qep; set trace point qe90"
                run_application_here
                unsetenv ING_SET
                ^D              (control-D to get out of the script command)
                vi typescript   (resultant script file)

        This file can take a while to generate because you have to sit through
        all the messages, but it works.
]


Compare the Query Send Time and the Query Response Time.  Start with 
those that run the longest and check the qep.

This helps you focus on those queries that are causing the most problems.

Bill Hoelzer
SCT Manufacturing & Distribution Systems
bhoelzer@sctcorp.com

Ingres Q & A

To William's Home Page

© William Yuan 2000

Email William