SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Gary Stout on January 04, 2013, 01:39:56 pm

Title: Any way to spped this code up?
Post by: Gary Stout on January 04, 2013, 01:39:56 pm
I have the following snippet which makes changes to the Contribution table based on a first or last name being changed in the Members table. Right now the code takes maybe as long as 7-8 minutes to make all of the updates to Contributions. If I make the change in SQLite Expert, the change is almost immediate, so I am sure that there is a better way to do it...I just don't know the best way to do it.

Thanks for your help,
Gary


'Update all contributions with head of house name change
slSel "Select * FROM Contributions WHERE FamilyID = '" & Trim$(CurrentFamilyID$) & "'", %rs1

Do While slGetRow(%rs1)
   slExe "Begin Immediate"
   SQL$ = "UPDATE Contributions SET Last = '" & Trim$(NewLast$) & "', First = '" & Trim$(NewFirst$) & "' WHERE FamilyID = '" & Trim$(CurrentFamilyID$) & "'"
   slExe Sql$
   slExe "End"
Loop
Title: Re: Any way to spped this code up?
Post by: Bern Ertl on January 04, 2013, 02:34:34 pm
I don't understand why you are using a select statement and a loop.  You should be able to execute the UPDATE statement as written one time and it will update all records where the FamilyID index = CurrentFamilyID

Assuming the new names are stored in another table with the CurrentFamilyID as an index, there really shouldn't be any need to keep a duplicate copy of that data in the Contributions table.
Title: Re: Any way to spped this code up?
Post by: Gary Stout on January 04, 2013, 03:36:04 pm
Thanks, Bern

Quote from: Bern Ertl on January 04, 2013, 02:34:34 pm
I don't understand why you are using a select statement and a loop.

Cause I don't know what I am doing LOL!

Quote
  You should be able to execute the UPDATE statement as written one time and it will update all records where the FamilyID index = CurrentFamilyID


If that is the case, then that is probably why it is so slow. It is updating approx. 400 records in one of my test, but I assumed I needed to loop through all records that needed changed. If I can do that with one call, I like that a whole lot better. Sounds like my code may be looping through 400 records 400 times possibly.

I will give this a try.

Thanks for your help,
Gary
Title: Re: Any way to spped this code up?
Post by: Bern Ertl on January 04, 2013, 06:03:12 pm
lol.  Sorry Gary.  I totally grok your situation.  I'm often fumbling around myself.  That's what these discussion forums are for.  Cheers.
Title: Re: Any way to spped this code up?
Post by: Gary Stout on January 05, 2013, 03:13:17 am
Bern,

I tried removing the SELECT statement and the loop and just used the UPDATE statement and all is working as fast as possible!

Thanks for pointing me in the right direction.

Gary