MySQL insert function Bulk or not bulk?

[davidhs]

Hi all,

I have a function on a UX component (Version 12 AA)
The functions just copies some records from a sample table to another table and the puts a projectId to new records.
A the moment it makes about 120 insert calls to the MySQL, and now I'm not a sql specialist. But is this right to do it this way, or would it be better to do some kind of bulk insert. If so can any recommend a way to do the bulk. Here is my code as it looks today, of cause the connection to database is opened and all vars are dimmed to. So the code works fine, all records are copied correctly. It just takes some time to complete it.

cn.PortableSQLEnabled = .t.
sql = <<%sql%
SELECT * FROM checklistsample
%sql%

insertsql = <<%sql%
INSERT INTO projectschecklist (projectschecklistId, ProjektId, direktivId, checklistgroup, checklisttekst)
VALUES (:uuid, :ProjektId, :DirektivId, :checklistGroup, :checklistTekst)
%sql%

flagresult = cn.execute(sql)
rs = cn.ResultSet 
while (rs.nextrow())
	arg.Set("UUID", api_uuidcreate())
	arg.Set("DirektivId", rs.data("DirektivId"))
	arg.Set("checklistGroup", rs.data("checklistGroup"))
	arg.Set("checklistTekst", rs.data("checklistTekst"))
	if cn.execute(insertsql,arg) = .f. then
		error = .t.
	end if
end while

Kind regards David.

[Steve Workings]

Re: MySQL insert function Bulk or not bulk?

Some of the answer depends on your complete design and performance needs. But for a couple hundred inserts, I'd do what you've done. A bulk insert probably wouldn't save that much time or processing resources.

Bulk insert is terrific - I have a few places where I use it for inserts ranging from tens of thousands to millions. Using MS SQL I'm getting insert rates near 30,000 records/second which makes a major difference in total process time.

[Pat Bremkamp]

Re: MySQL insert function Bulk or not bulk?

David,

It takes time to read the record into Alpha, get the result set, then write it back out again. But MySQL has its own UUID() function, so in this case I'd use

insertsql = <<%sql%
INSERT INTO projectschecklist (projectschecklistId, ProjektId, direktivId, checklistgroup, checklisttekst)
SELECT UUID(), ProjektId, direktivId, checklistgroup, checklisttekst
FROM checklistsample
%sql%

flagresult = cn.execute(sql)

if flagresult = .f. then
error = .t.
end if

[davidhs]

Re: MySQL insert function Bulk or not bulk?

Hi Steve & Pat,

Thank you very much for your replies.

Pat I can see that this is a much quicker way to use, properly also better then bulk insert since mysql will do the work and not the alpha server.
If I where to use bulk insert, would the command then look like this code or am I wrong?

for i=0 to 10000[INDENT]sql = sql + crlf() + "INSERT INTO table (Field1, Field2) VALUES (:arg1, :arg2)"[/INDENT]
end for
cn.execute(sql,arg)

Kind regards David

[Pat Bremkamp]

Re: MySQL insert function Bulk or not bulk?

Have you tried what I sent?

If you feel like you really need to use arguments, then no, what you wrote will not work. What you want is more like

sql = "INSERT INTO table (Field1, Field2) Values"
for i=0 to 10000 step 2
sql = sql + "("+evaluate_string(":arg"+alltrim(str(i,5,0))+", "+evaluate_string(":arg"+alltrim(str(i+1,5,0))+"),"
end for
sql = left(sql, len(sql)-1)

cn.execute(sql,arg)

Then you have to name the args arg1, arg2, arg3,....arg10000

In other words, very messy.

[davidhs]

Re: MySQL insert function Bulk or not bulk?

Hi Pat,

Yes I am actually using your model, just changed it a little bit to make sure I don't make duplicates in the target table.
So the query looks like this now:

arg.Set("ProjektId", e.arguments.find("argProjektId").data)
sql = <<%sql%
INSERT INTO projectschecklist (projectschecklistId, projektId, direktivId, checklistgroup, checklisttekst)
SELECT UUID(), :projektId, DirektivId, checklistGroup, checklistTekst FROM checklistsample
WHERE checklistTekst NOT IN (SELECT checklistTekst FROM projectschecklist)
%sql%
flagresult = cn.execute(sql,arg)
cn.Close()

In the last post I didn't mean that I wanted to have 10000 of arguments. But If I don't have the option to use your sql method, and had to use some kind of bulk insert. How would the code look for building the query string for the sql? So my question is, would I for instance iterate over the number of insert statements I need and the build up the query that way? So something like this:

arg.Set("ProjektId", e.arguments.find("argProjektId").data)
for i = 0 to arr.size()[INDENT]arg.Set("LinkId", arr[i])
sql = sql + crlf() + "INSERT INTO projectLink (projektId, LinkId) VALUES(:ProjektId, :LinkId)"
[/INDENT]
next
cn.execute(sql,arg)

Would that be a way to make a bulk insert, and execute it in the end. I know that if the values are available in a not table and if I'm just copying templates/samples I might as well go with your example.
But if I really need to do some sort of calculation in alpha, and don't want to make 1000 single insert query's but would like a bulk insert query. Would the above example then be a good starting point?

Kind regards David.

[Pat Bremkamp]

Re: MySQL insert function Bulk or not bulk?

David, what you need to do is have the INSERT INTO ... Values statement in the your statement only once. The way you are doing it, if it runs at all, will create 1000 INSERT... statements.
So, create the INSERT INTO as a single statement and then add just the values (...) as many times as needed so in the end it looks like
INSERT INTO projectLink (projektId, LinkId) VALUES(:ProjektId, :LinkId),(:ProjektId, :LinkId), (.....

Another problem is at the end of your loop, when you execute, every :LinkId will be assigned the same value, the last value assigned to that argument.

[davidhs]

Re: MySQL insert function Bulk or not bulk?

Hi Pat,

Thank you, now I think I understand better. SQL is not my biggest force, so I'm learning. And then xbasic is also very new to me.
So if I understood correctly, I would need to do something like this instead:

arg.Set("ProjektId", e.arguments.find("argProjektId").data)
sql = "INSERT INTO projectLink (projektId, LinkId) VALUES"
for i = 0 to arr.size()[INDENT]sql = sql + crlf() + "(:ProjektId, " + arr[i] + ")"
if (i < arr.size()) then[INDENT]sql = sql + ","[/INDENT]
end if
[/INDENT]
next
cn.execute(sql,arg)

I not sure if the if statement will work as it is supposed to(making sure there is no comma after the last added line in the sql variable. But I hope I'm on the right track.
So thank you very much for explaining the errors for me, it helped me a lot. :-)

Kind regards David.