Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 18 of 18 total |
Script - is there a faster way? |
Wed, Jun 18 2008 6:31 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jun 18 2008 6:32 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Wed, Jun 18 2008 6:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jun 18 2008 7:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jun 18 2008 2:07 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Jun 19 2008 2:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Jun 21 2008 12:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sat, Jun 21 2008 1:48 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |