• Welcome, Guest. Please login.
 
September 17, 2019, 09:59:16 am

News:

Welcome to the SQLitening support forums!


Any way to spped this code up?

Started by Gary Stout, January 04, 2013, 01:39:56 pm

Previous topic - Next topic

Gary Stout

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
Gary Stout
gary@sce4u.com

Bern Ertl

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.

Gary Stout

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
Gary Stout
gary@sce4u.com

Bern Ertl

lol.  Sorry Gary.  I totally grok your situation.  I'm often fumbling around myself.  That's what these discussion forums are for.  Cheers.

Gary Stout

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
Gary Stout
gary@sce4u.com