Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 18 total |
Script - is there a faster way? |
Sun, Jun 15 2008 11:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Jun 16 2008 9:39 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Jun 16 2008 10:58 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Jun 16 2008 3:15 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Jun 16 2008 6:23 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Jun 17 2008 2:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Jun 17 2008 9:12 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Jun 17 2008 9:49 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jun 18 2008 2:30 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jun 18 2008 3:15 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Saturday, September 21, 2024 at 09:49 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |