Re: Set explain Comments
In article <3qqa6b$fme@cssun.mathcs.emory.edu>
wayne@jones.mac.alt.na "Wayne Harlech-Jones" writes:
<snip>
> I was attempting to speed up a report (Fourgen standard), and used "set
> explain on" on our test database, getting very encouraging results (*3 in
> speed). Made the changes in the report, copied to the live env and timed
> the old version against the new. Guess what? Little or no time
> difference, in fact under certain conditions the new program was slower by
> a few minutes.
<snip>
In no special order:
Are the two databases EXACLTY the same in terms of indexes? Have you run
UPDATE STATISTICS on both databases? Are there 'significant' differences
in numbers of rows in the tables used in the queries? Have you tried'SET EXPLAIN ON' on the live database? Are there differences in the
distribution of values in the keys in the two databases (e.g. does the
live have an indexes column with lots of similar values and a few different
ones?)? Do the queries have self joins (or other joins) being done
many times over (as per 'WHERE NOT IN')?
I personally would go for 'UPDATE STATISTICS' followed by 'SET EXPLAIN ON'
to see what's happening on the live env, to get a starting point.
Regards
--
============================================================================
Sally Woolrich | This mail contains my personal
sally@excelsis.demon.co.uk | views not those of my employer!
============================================================================