Robert's memory is correct: a single SQL statement per action. I've
requested (as have others probably) that this restriction be relieved
in Version 6.
I think another speed up is obtainable if you use database
replication, where inserts and updates are done on one database,
without indexes, and the tables are replicated to a second db used
for SELECTS, which does have indexes.
But I haven't tried this.
On Jul 27, 2007, at 10:08 AM, Robert Garcia wrote:
Yes, I do it all the time, and it is very efficient. As I and
others have said before, witango is not very efficient looping, and
when you throw something like a db insert in a loop, it makes it
even worse.
I even go a step further sometimes, like when I have an app
communicate to witango, I have the app build the extended mysql
insert statement in a big block, pass it in xml, and then witango
puts it in a direct dbms and it runs. If memory serves me, you
can't put more than one statement in a direct dbms, so for each ";"
you have to use a new direct dbms, but you can make a humongous
insert statement.
Some other tips, if you have to loop and do an insert in mysql, and
there is no other way, you can increase performance a lot, by doing
this:
directdbms: set foreign_key_checks=0;
directdbms: set autocommit=0;
witango loop goes here
insert your record
close loop
directdbms: commit;
directdbms: set foreign_key_checks=1;
directdbms: set autocommit=1;
This will cause your insert to be MUCH faster. This tells mysql, to
insert all your records, THEN on commit, do any updating of indexes
and keychecks at the end. Otherwise, it will do update of index on
each record you insert, which can take a while on a heavily indexed
or large table. This little tip can save you LOTS of time when
inserting many records at once, whether you are in witango or not.
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park Dr
Magalia, Ca 95954
ph: 530.645.4040 x222 fax: 530.645.4040
[EMAIL PROTECTED] - [EMAIL PROTECTED]
http://bighead.net/ - http://eventpix.com/
On Jul 27, 2007, at 9:31 AM, Fogelson, Steve wrote:
Robert,
I looked at the MySQL syntax and am wondering if this is what you
mean within the Direct DBMS:
INSERT INTO Country (
Country_ID ,
Country_Name ,
Country_Disabled
)
VALUES
<@rows array="EResults" scope="request">
(
<@var request$EResults[<@currow>,Country_ID]> ,
<@var request$EResults[<@currow>,Country_Name]> ,
<@var request$EResults[<@currow>,Country_Disabled]> ,
)
</@rows>
;
Thanks,
Steve
From: Robert Garcia [mailto:[EMAIL PROTECTED]
Sent: Friday, July 27, 2007 1:17 AM
To: [email protected]
Subject: Re: Witango-Talk: Inserting an array (multiple rows) into
a table
You definitely can with a direct dbms.
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park Dr
Magalia, Ca 95954
ph: 530.645.4040 x222 fax: 530.645.4040
[EMAIL PROTECTED] - [EMAIL PROTECTED]
http://bighead.net/ - http://eventpix.com/
On Jul 26, 2007, at 8:56 PM, Fogelson, Steve wrote:
Is there a way to insert an array (multiple rows) into a table
without using a loop?
The Witango action “Insert” implies that you can as it says
“Insert the following rows into the table”.
Thanks
Steve Fogelson
Internet Commerce Solutions
_____________________________________________________________________
___TO UNSUBSCRIBE: Go to http://www.witango.com/developer/
maillist.taf
_____________________________________________________________________
___ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/
maillist.taf
_____________________________________________________________________
___ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/
maillist.taf
______________________________________________________________________
__ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
