Tim


>The repair completely rebuilds the indexes (and the table, for that matter)
>from scratch, so I can't see how it could "miss" anything.

So there must be a difference between dropping the index and adding it back and rebuilding it with repair.

Roy Lambert

Tim


Bit more on this. I wanted to get the same table (EMails) across from the live to the development database again so I could check something out. I set up a session in EDBManager to access both databases, deleted everything from DEV.EMails and ran

INSERT INTO DEV.EMails SELECT * FROM LIVE.EMails

Fine but there were 71 records in mailbox -8 when there should have been 3. Check LIVE.EMails and it only shows 3!

The extra 68 were all records that had been deleted.

To get rid of the ones that shouldn't have been there I had to optimise LIVE.EMails

Don't know if you can make anything from this but I'm just baffled.

Roy Lambert

Roy,

<< Bit more on this. I wanted to get the same table (EMails) across from the
live to the development database again so I could check something out. I set
up a session in EDBManager to access both databases, deleted everything from
DEV.EMails and ran

INSERT INTO DEV.EMails SELECT * FROM LIVE.EMails

Fine but there were 71 records in mailbox -8 when there should have been 3.
Check LIVE.EMails and it only shows 3!

The extra 68 were all records that had been deleted. >>

I'll check it out.  There's been some changes in 2.03 to the underlying
cursors used for the SQL, so that may be the issue.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

I've snuck a fix for this into 2.03 B4.   It was what I thought - the change
in the cursors caused the issue.  The SQL in EDB uses certain low-level
table cursors that don't necessarily filter out deleted rows, and the INSERT
statements were changed to use this type of cursor without taking into
account the deleted rows.  I'm slowly but surely trying to tighten up the
SQL syntax so that ORDER BY statements are only used with client cursors,
and not for sub-queries and other intra-SQL operations.  It makes the SQL
execution much, much faster.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I've snuck a fix for this into 2.03 B4. It was what I thought - the change
>in the cursors caused the issue. The SQL in EDB uses certain low-level
>table cursors that don't necessarily filter out deleted rows, and the INSERT
>statements were changed to use this type of cursor without taking into
>account the deleted rows. I'm slowly but surely trying to tighten up the
>SQL syntax so that ORDER BY statements are only used with client cursors,
>and not for sub-queries and other intra-SQL operations. It makes the SQL
>execution much, much faster.

I did wonder why there were ORDER BYs in the subselects. I thought you had a reason and now I see its the infamous put in slow downs so you can take them out later and make the product seem soooo much better

I'm please to give you another test for the suite.

Roy Lambert