PDA

View Full Version : PSQL massive slowdown



Dave &/or Wempy
13-Feb-2006, 05:41 PM
This is wierd.
Production is version 11.0. PSQL8.1, driver 2.x
Data on a netware 5.1 server.
Runs "fine".

I copied everything over to a nw6.5, psql8.7 driver 3.x server for
vdf11.1 development and the speed sucked, so I pointed the vdf11.1
back at the 5.1 database, it still sucked.
Tried the query program, and dbexplorer, and access was instantaneous.
Ditto access from the cm programs that don't use DDs.
Went back to the dev 11.0 version, same data as production. Still
sucked. Production still okay.

Okay then, put dd_debug into one of the offending programs. F4 on the
key field displays the popup instantly, though selecting a record
takes about 300,000 finds before displaying the correct one in the
original form. F9 on the keyfield dbform takes about 770,000 finds
before returning the correct record.

Direct PSQL tests on the database are fine, STAT is fine, the .int
file is correct, it's obviously the DD not optimising somehow but this
thing hasn't been changed for at least a month and afaik the
production .exe contains the same version of the dd package so why
does the production server not act up?

What am I missing?
Broken DDF?
500,000 brain cells?

Dave

Anders Öhrt
14-Feb-2006, 02:38 AM
> Okay then, put dd_debug into one of the offending programs. F4 on the
> key field displays the popup instantly, though selecting a record
> takes about 300,000 finds before displaying the correct one in the
> original form. F9 on the keyfield dbform takes about 770,000 finds
> before returning the correct record.

Sounds like it's using the wrong index. The main index of a field is
determined at runtime, so if that was somehow changed this could happen even
with the same .exe. Check in dbexplorer that the index for the field you are
finding is the right one.

// Anders

Dave &/or Wempy
14-Feb-2006, 08:12 AM
Thanks for responding so quickly, this drove me nuts now for 2 days...

>Sounds like it's using the wrong index. The main index of a field is
>determined at runtime, so if that was somehow changed this could happen even
>with the same .exe. Check in dbexplorer that the index for the field you are
>finding is the right one.
>
I agree that's what it sounds (and looks<g>) like, but the dd has a
'set ordering to 3' (the correct index) and dd_debug shows index.3 at
top right (though the 'find' area at bottom left shows index.0) - I've
not played with dd_debug before so I'm not familiar with it.

Because of the number of finds being made before the correct record is
detected it looks very like the recnum index is what's being used. And
yes... this is a df4btrv table with recnums.... should the .int file
contain something extra now?

My puzzle is that nothing is (supposed) to have changed..

Dave &/or Wempy
14-Feb-2006, 02:51 PM
Cracked it.

1. an alias DD was included that had been corrupted - dunno why it
compiled!
2. a child DD was included that had both the wrong index and the wrong
relationship (old fies)

Dunno which of these actually fixed the problem <g> but it seems now
to be gone.... thanks


On Tue, 14 Feb 2006 08:38:08 +0100, "Anders Öhrt"
<Anders.Ohrt@capslock.se> wrote:

>
>> Okay then, put dd_debug into one of the offending programs. F4 on the
>> key field displays the popup instantly, though selecting a record
>> takes about 300,000 finds before displaying the correct one in the
>> original form. F9 on the keyfield dbform takes about 770,000 finds
>> before returning the correct record.
>
>Sounds like it's using the wrong index. The main index of a field is
>determined at runtime, so if that was somehow changed this could happen even
>with the same .exe. Check in dbexplorer that the index for the field you are
>finding is the right one.
>
>// Anders
>