Hello
just found something interesting, it is about insert-ing and add-ing records into MySQL table (using ODBC Clarion driver and ABC templates).
This code:
loop L = 1 to 50000
yield()
clear(m:record)
m:name = 'some name '&L
m:userId = 1
clear(sql:record)
sql_file{prop:sql} = 'select max(id)+1 from materials where userId = 1'
next(sql_file)
m:id = sql:f1
add(materials)
end
works almost 10 times faster as this one
loop L = 1 to 50000
yield()
clear(m:record)
m:name = 'some name '&L
m:userId = 1
access:materials.insert()
end
At a guess, because youâre missing info, Iâd say that the table is doing an auto inc, and the .insert adds a blank record, checks itâs ok then updates. Which is all missing from your first example.
Essentially the Insert is more robust, the select and add could have someone sneak in between the 2 statements.
If you look at the InsertServer method in the FielManager class I think the answer is fairly obvious. When you use Insert, then after every record, assuming you are not using stream/flush at all â you donât have them in your code â Clarion will wait to see if the server returns an error, and will act on it if it does. Waiting for the server to respond âYes I added thatâ, or âI didnât add that, and here is the errorâ, takes time.
In your Add loop you are not checking for any errors, and if you encounter them you are just going to keep on trying to add more records.
If you are worried about time, firstly stream/flush would speed things up. Secondly, if you actually had a real-world need to do something like that, then this code (this is the Oracle version, but MySQL you could do something similar) will probably work a hundred times or more faster than your ADD loop (you could write a prop:SQL):
with maxid as
(select max(id) id
from materials
where UserID = 1)
, idrange as
(select 'some name' || to_char(maxid + level) addname
from dual
connect by level <= 50000
)
insert into materials(userID,name)
select 1,addname
from idrange
This is also getting rid of the need to get max(ID) after every insert. Downside would be that if someone else tried to enter a name in the range that you have mapped out your whole insert would fail.
@mriffey here is table declared in the DCT (not sure what is DDL?)
materials FILE,DRIVER('ODBC'),OWNER(glo:owner),NAME('materials'),PRE(m),BINDABLE,THREAD !
idKey KEY(m:id),NOCASE,OPT,PRIMARY !
nameKey KEY(m:name),DUP,NOCASE,OPT !
userIdKey KEY(m:userId),DUP,NOCASE,OPT !
ordersKey KEY(m:p_id,m:userid,m:orders),DUP,NOCASE,OPT !
pUpKey KEY(m:p_id,m:up_id),DUP,NOCASE,OPT !
Record RECORD,PRE()
id LONG !
name CSTRING(255) !
jm CSTRING(50) !
price DECIMAL(14,4) !
userId LONG !
orders LONG !
mycode CSTRING(100) !
p_id LONG !
up_id LONG !
END
END
@seanh I assumed too that the insert was a better and safer option, but it is 10 times slower. Itâs not about a single table - the whole application is designed to work as an insert (I have a lot of hand code).
@JonW I just checked Stream/Flush and Logout/Commit - no difference. The help says this: "On some filesystems, STREAM and FLUSH are simply ignored when a file is opened for shared access,'" and my files are opened for shared access. Additionally, logout/commit (and access:materials.insert()`, too), create a âdeadlockâ when running code in the same thread.
ADD is just a function, its got no errorcode handling, no client side auto numbering and client side shadow buffer checking.
Insert is a class method which is carrying out errorcode checking, client side auto numbering (where applicable - (dct) driver switch can off load this back to the SQL Server/ODBC source) and client side shadow buffer management.
The extra code in the class simply takes more time to process.
Watch the ABC class in action by stepping through the debugger. Set your breakpoint on the Access:Materials.Insert() line and step through the code, youâll see what I mean then about its got more code to process.
@RchdR Thanks for the details, I got your point but I donât need all bells and whistleâs, just simple add to database.
Adding the basic error check
if errorcode()
message(fileerrorcode()&' : '&fileerror())
.
does not slow down ADD-ing at all (adding 6000 records is done by 1 or 2 second using MySQL table and ODBC driver). Using Insert(), it take 10-12 seconds.
If you need the fastest possible method to import records, in MS-SQL so if the equivalent exists in MySQL, doing a batch import is the fastest way to get millions/billions of records into the db.
The indexing et al will take place as a background process, so calling the batch import via prop:sql and importing all the records from a txt file is also an option besides using clarion code.
@Max thanks for idea but I already have +30000 lines of code and I have no idea where I use just added ID (on the client side) so it can be dangerous to to this now.
Same for your other suggestions (adding by SQL).
Thanks anyway.
@RchdR there will be adding max 5000-10000 records at time.
Now I found another problem - when adding 5000 records from one command and then from another, it give me âAttempts to automatically number this record have failedâ error.
Tables are declared using THREAD attribute, so I have no idea why it display that error?
That might be a transaction isolation problem. Adding many records to SQL by many options fast has problems making sure that each doesnât stomp on the other. You might be a victim of that. Itâs also one of the reasons that the Insert method takes care in doing its thing.
Perhaps giving up a little time to ensure everything is ok might be worth it.
I was surprised to find that you were right about logout and stream/flush making no difference. Thatâs with ODBC access against Oracle. My results for adding 5000 over a WAN were an abysmal 500+ seconds whether or not I included an if error() and stream/flush or a logout/commit wrapped around the full 5000. Thatâs ADDs, not filemanager inserts.
The PROP:SQL version, which I got a little wrong, plus an adjustment to match the other tests, ran in 0.32 seconds.
insertspeed{PROP:SQL} = 'insert into insertspeed(ID,descrip) ' & |
âwith idrange as (select level id,ââtestââ || to_char(level) descrip from dual connect by level <= 5000 ) â & |
âselect id,descrip from idrangeâ
Basic rule is to reduce the number of roundtrips to the server. Wrapping logout/commit around the loop reduces the burden on the server, but clearly that was insignificant compared to the time for sending data over and checking that it was added OK.
That is likely to be problem code. The FileErrorCode() is only set for a few errors, IIRC 90 (driver error) and 47 (invalid file def). So any other error codes would just show a Colon, e.g. Duplicate Key or File Not Open.
FileErrorCode() returns a STRING not a Number so you would check for it = Blank.
Hereâs the way I would code:
if errorcode() then
message('Add "record id" failed '& Err4Msg())
end
Using this function to easily get all the RTL Error info:
Err4Msg PROCEDURE(Byte NoCRLF=0)!,STRING
!Example: IF ERRORCODE() THEN STOP('Failed ADD(xxx)' & Err4Msg()).
!Note: Return starts '<13,10><13,10>Error Code:' so no need to put in the Message()
CODE
IF ~ERRORCODE() THEN RETURN ''.
IF ~NoCRLF THEN
RETURN '<13,10><13,10>Error Code: ' & ERRORCODE()&' '&ERROR() & |
CHOOSE(~FILEERRORCODE(),'','<13,10>Driver Error: ' & FILEERRORCODE()&' '&FILEERROR() ) & |
CHOOSE(~ERRORFILE(),'','<13,10>File Name: ' & ERRORFILE() )
END
!NoCRLF<>0 is 1 line format for use by logging
RETURN ERRORCODE()&' '&ERROR() & | ! {148}
CHOOSE(~FILEERRORCODE(),'',' [Driver ' & FILEERRORCODE()&' '&FILEERROR() &']' ) & |
CHOOSE(~ERRORFILE(),'',' {{' & ERRORFILE() & '}' )
@JonW I understand, SQL is best option for adding lot of records but I try to avoid that because of SQL injection stuffâŚ
Iâm happy with the ADD-ing of records (I do not work over WAN, everything is on server and 1-2 sec for 6000 records is pretty good result for me).
I still have the problem by add records (or ABC insert) in the same time. Attached is sample app and dct with 4 buttons. Just compile and press âRun MyProcâ and second later, click on Run MyProc 2".
You will see error by inserting a record. I try with CriticalSection (it is commented inside a code) and it works OK (you can click as many times you want on each button) but the problem is when you run 2 instances of exe file - in that case error raises again
Any idea how to add records using other ways or is the ServerAutoInc only option for ODBC drivers? test.zip (563.4 KB)
Thank you @CarlBarnes for details regarding error code. Will implement this as soon I find a way of how to add records into same table (in the same time).
Currently, I know what happens without detailed error description
