Roy,

<< The problem is deleted records. Initially this table would have been set
up with 50k+ records. I then decided only to monitor selected mailboxes and
emptied the file. I've just tried optimising it (after a bit of swearing at
yankee spelling) and the time drops to a more acceptable 15ms to process the
5 records. When I get a chance later on I'll build the table up again and
see if just the number of records makes a different or it is only deleted
records. >>

Is the DELETE completely optimised (notice no z ?  Deleted rows should
only affect un-optimised conditions or (sometimes) conditions that use the
inequality operator (<>).

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< Supplementary question. Is V2 still reusing deleted record space? I
remember something about it may not when replication arrived. >>

That was another nice thing about the final design that I was able to come
up with - that little item disappeared and rows can be re-used whether a
table is published or not.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>That was another nice thing about the final design that I was able to come
>up with - that little item disappeared and rows can be re-used whether a
>table is published or not.

Good.

Roy Lambert

Tim

>Is the DELETE completely optimised (notice no z ? Deleted rows should
>only affect un-optimised conditions or (sometimes) conditions that use the
>inequality operator (<>).

Errm woops. With all the testing I was doing yesterday I left the DELETE part of the sql commented out as you can see below. But yes as far as I can tell its optimized (see I too can talk yankee). I removed the comments and with c85k records it took 31ms, delete but not optimised c3000ms delete and optimsed c16ms.

 EXECUTE IMMEDIATE 'CREATE TABLE "emReadStatus"
                    (
                    "_fkMailBoxes" INTEGER,
                    "_fkEMails" INTEGER,
                    "_fkUsers" VARCHAR(5) COLLATE "ANSI_CI",
                    CONSTRAINT "PK" PRIMARY KEY ("_fkEMails", "_fkUsers")
                    )
                    VERSION 1
                    UNENCRYPTED
                    INDEX PAGE SIZE 4096
                    BLOB BLOCK SIZE 512
                    MAX ROW BUFFER SIZE 32768
                    MAX INDEX BUFFER SIZE 65536
                    MAX BLOB BUFFER SIZE 32768';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "User" ON "emReadStatus"
                    ("_fkUsers" COLLATE "ANSI_CI")';
 
  EXECUTE IMMEDIATE 'CREATE INDEX "MailBox" ON "emReadStatus"
                    ("_fkMailBoxes")';


SCRIPT(IN EMailID INTEGER, IN UserID VARCHAR, IN NewBox INTEGER)
BEGIN
DECLARE Zapper SENSITIVE  CURSOR FOR zSQL;
DECLARE Inserter SENSITIVE CURSOR FOR iSQL;

/*
PREPARE zSQL FROM 'DELETE FROM emReadStatus WHERE emReadStatus._fkEMails = ? AND _fkUsers = ?';
*/
PREPARE iSQL FROM 'INSERT INTO emReadStatus (_fkEMails, _fkMailBoxes, _fkUsers) VALUES (?,?,?)';
/*
OPEN Zapper USING EMailID, UserID;
*/
IF NewBox <> 0 THEN
 OPEN Inserter USING EMailID, NewBox, UserID;
END IF;

END

Roy,

<< Errm woops. With all the testing I was doing yesterday I left the DELETE
part of the sql commented out as you can see below. But yes as far as I can
tell its optimized (see I too can talk yankee). I removed the comments and
with c85k records it took 31ms, delete but not optimised c3000ms delete and
optimsed c16ms. >>

Could you send me the database catalog and table that you're using ?  I'm
going to have to track this down against the live data in order to see what
the optimizer is doing for the DELETE.  Also, I need to know exactly what
rows you're deleting before you run the script the second time and it takes
3000ms.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Could you send me the database catalog and table that you're using ? I'm
>going to have to track this down against the live data in order to see what
>the optimizer is doing for the DELETE.


Naturally I'll do this, but I think you missed a little point. When I tried it day before yesterday I'd commented out the DELETE part of the script and I still had lousy performance with JUST the INSERT part.

>Also, I need to know exactly what
>rows you're deleting before you run the script the second time and it takes
>3000ms.

Easy all of them.

I'll email the stuff in a while - I'm just rebuilding the table now.

Roy Lambert

Roy,

<< I'll email the stuff in a while - I'm just rebuilding the table now. >>

Okay, I found out the problem with this and fixed it.  EDB was navigating
through all 85k rows looking for a non-deleted row when it didn't actually
have to.  It was effectively scanning all 85k rows *twice* for each INSERT,
so I guess it's a testament to the speed of EDB that it wasn't even slower.
 A fix will be in 2.00 B3.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Brill. Having read this post I just realised I'm lying in another one - I'm not using the latest and greatest, I'm a version behind. I downloaded b2 but forgot to install. I'll do that tomorrow and check the other problems out and see if they're still there. If not I'll post an apology.

Roy Lambert