On 7/27/07, William M Conlon <[EMAIL PROTECTED]> wrote: > 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've executed multiple SQL statements in 1 DirecDBMS action. I did it with the JDBC connector to MySQL. I did have to set a config parameter on the JDBC driver though to get it to work. (allowMultiple...) or something like that. the ConnectorJ documentation explains it nicely. /John > 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 > > -- /John ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
