I'm trying to use sql but have found that the code below is c100 times slower (1753ms) than the equivalent table version (16ms) when processing 5 records. I'm guessing its me so can anyone tell me how to speed it up?


procedure TEMailsForm.DoUpdateReadStatus(NewMB: string);
begin
{ UpdateReadStatus.Close;
if not UpdateReadStatus.Prepared then UpdateReadStatus.Prepare;
UpdateReadStatus.ParamByName('UserID').AsString := HHCommons.UserID;
UpdateReadStatus.ParamByName('EMailID').AsString := EMails_MsgNo.AsString;
UpdateReadStatus.ParamByName('NewBox').AsString := NewMB;
UpdateReadStatus.ExecScript;
UpdateReadStatus.Close;}
if emReadStatus.FindKey([EMails_MsgNo.AsInteger, HHCommons.UserID]) then emReadStatus.Delete;
if NewMB <> '' then begin
 emReadStatus.Insert;
 emReadStatus_fkUsers.AsString := HHCommons.UserID;
 emReadStatus_fkEMails.AsInteger := EMails_MsgNo.AsInteger;
 emReadStatus_fkMailBoxes.AsInteger := StrToInt(NewMB);
 try
  emReadStatus.Post;
 except
  emReadStatus.Cancel;
 end;
end;
end;


The script is

SCRIPT(IN EMailID VARCHAR, IN UserID VARCHAR, IN NewBox VARCHAR)
BEGIN
DECLARE Cmnd VARCHAR;
SET Cmnd = 'DELETE FROM emReadStatus WHERE emReadStatus._fkEMails = '+EMailID + ' AND _fkUsers = '''+UserID+'''';
EXECUTE IMMEDIATE Cmnd;
IF NewBox <> '' THEN
 BEGIN
   SET Cmnd = 'INSERT INTO emReadStatus (_fkEMails, _fkMailBoxes, _fkUsers) VALUES ('+ EMailID+', '+ NewBox+', '''+UserID+''')';
   EXECUTE IMMEDIATE Cmnd;
 END;
END IF;
END


Roy Lambert

Roy,

<< I'm trying to use sql but have found that the code below is c100 times
slower (1753ms) than the equivalent table version (16ms) when processing 5
records. I'm guessing its me so can anyone tell me how to speed it up? >>

Well, for starters use PREPARE in the script and parameterize the DELETE and
INSERT.  The second and subsequent times that you execute the script, it
will use the prepared versions and should be a lot faster.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

For some mentally deficient reason I didn't equate the internal PREPARE with a TEDBQuery external .prepare - rereading the OLH I should have done.

Is the external prepare still needed?


Roy Lambert

Tim

>Is the external prepare still needed?

Yes otherwise it won't recognise the parameters (I do like answering my own questions


Altered the script to

SCRIPT(IN EMailID INTEGER, IN UserID VARCHAR, IN NewBox INTEGER)
BEGIN
DECLARE Zapper CURSOR FOR zSQL;
DECLARE Inserter 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


    t1 := gettickcount;
    for Cntr := 1 to emList.RowCount - 1 do begin
     if EMails.Locate('_MsgNo', StrToIntDef(emList.Cells[cID, Cntr], 0), []) then begin
      if emList.Cells[cReadStatus, Cntr] <> '' then begin
       emList.Cells[cReadStatus, Cntr] := '';
       DoUpdateReadStatus(IntToStr(mbCurrent));
      end;
     end;
    end;
    t2 := gettickcount;

Loads of testing later

Using the script (now on 6 records) c 2096 ms roughly 50:50 for the two operations. Alter to use tables rather than the script and downto c10 ms


Roy Lambert

Roy,

<< Using the script (now on 6 records) c 2096 ms roughly 50:50 for the two
operations. Alter to use tables rather than the script and downto c10 ms >>

I'm not sure I understand your timings - are you saying that each execution
now takes *more* time than before ?  Are you still timing the first
execution or the subsequent executions ?  The first execution is always
going to take a while because everything needs to be prepared/compiled.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I'm timing a batch of 5 records so the time is for all 5 (until I tested deletion it was 6).

Bit more info before I start the day job:

Take out UpdateReadStatus.Close; ie don't close the script and it drops to c850ms

Move the Zapper & Inserter to a SENSITIVE cursor and the time goes up to c920ms

Split into two TEDBQuerys one for insert, one for delete and I get c500ms

Via table its <10ms

Roy Lambert

Roy,

<< I'm timing a batch of 5 records so the time is for all 5 (until I tested
deletion it was 6). >>

Yes, but is it the first execution or the subsequent executions ?

<<Take out UpdateReadStatus.Close; ie don't close the script and it drops to
c850ms >>

Well, you don't need to close it, so that's a nop unless you're using a
different version than you posted.  IOW, there's no cursor being returned
from the script, so there's nothing to close since nothing was opened.

<< Move the Zapper & Inserter to a SENSITIVE cursor and the time goes up to
c920ms >>

I didn't notice that you were doing that - you don't need cursors at all:

SCRIPT(IN EMailID INTEGER, IN UserID VARCHAR, IN NewBox INTEGER)
BEGIN
DECLARE zSQL STATEMENT;
DECLARE iSQL STATEMENT;
PREPARE zSQL FROM 'DELETE FROM emReadStatus WHERE emReadStatus._fkEMails = ?
AND _fkUsers = ?';
PREPARE iSQL FROM 'INSERT INTO emReadStatus (_fkEMails, _fkMailBoxes,
_fkUsers) VALUES (?,?,?)';
EXECUTE zSQL USING EMailID, UserID;
IF NewBox <> 0 THEN
EXECUTE iSQL USING EMailID, NewBox, UserID;
END IF;
END

<<  Via table its <10ms >>

Of course - it doesn't have to do any compilation, which is why I keep
asking you what the timings are for subsequent executions.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Sometimes I'm afraid you have to explain why you're asking so us morons understand how to answer the question.

Its basically part of the multi-user email part of my app. Not all mailboxes are going to be monitored for when the messages have been read and moving from a monitored box (eg the inbox)  to a long term storage box (eg yours to me go into DBISAM) may result in the unread status (think bold line) being removed if that box isn't also monitored.

The table which holds the status has three fields - _fkMailBoxes, _fkEMails, _fkUsers - indexed on _fkMailboxes, _fkUsers and PK _fkEMails, _fkUsers

So when I click on the "catch up" button my software loops through all (in this case 5) emails in the current mailbox and 1) deletes the current unread status and if the new mailbox is monitored adds new unread status. In this case new mailbox and current mailbox are the same but its done that way for ease of programming for drag'n'drop of a mailbox and sub-mailboxes to another site. I will always delete the read status info and sometimes add new info in.

So each time I press the button the script is run 5 times. The timings are for all five delete/insert operations. However, I'm also, without closing the app doing delete from emreadstatus in EDBManager to clear the read status, clicking from the inbox, to the outbox and back again and then clicking on catchup so:

1763ms 5 records first run after starting app
1732ms 5 records second run after starting app, clearing read status
1794ms 5 records third run after starting app, clearing read status

And just for you, a record by record version

Start app click catchup

390ms
421ms
359ms
390ms
421ms

Casting back to my earlier timings the insert and delete are about the same. As an extra piece of data the delete from takes c.29s to delete 5 records.

Back to the day job

Roy Lambert

Tim


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.

Roy Lambert

Tim

Yup - deleted records. Just tried it with c85k records in the emReadStatus table and its fine. Delete them and the nasty behaviour is back.

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


Roy Lambert