Making The Code Run Faster Editing Records

Knightlite · **Posted:** Thu Mar 26, 2009 9:37 am

Hi,

I have an app that is a multiple choice test, in which the user selects an answer, and is scored on the answer depending which answer he selected. Once answered, the user can not answer the question and change the score again. In order to keep track that the question has already been answered, I have a database table that marks a field with an "X" . When the user exits out of the test, I have code that clears all the Xs out of all the records, so the next time a user starts a test, all the questions will be marked as unanswered. The code I have for doing so is below.

The problem is that it is incredably slow under Windows. It is not so bad on the Mac, but if you have 1800 questions in your recordset, it slows down to a crawl. I was wondering if anyone knew a way to speed up this code or know another way the problem can be handled. Any help would be appreciated.

Code:

  //Erases Already Answered Marks
  Dim VC As Integer
  Dim L as Integer

  
Dim wq as Recordset
Dim rs as Recordset
  
  VC=rs.RecordCount

  
  wq.MoveFirst
  
  For L = 1 to VC
    wq.Edit
    wq.field("XX").StringValue=""
    wq.Update
    Fire5.Commit
    wq.MoveNext
  Next

Goofie · **Posted:** Thu Mar 26, 2009 10:00 am

Isn't it possible to do this in a single sql-statement?
So you don't have to iterate all the records.
Syntax depends on what database-engine your using.

Code:

dim sqlstring as string="update  questionstable set questionstable.answered="" where questionstable.answered=""x"
db.sqlexectute(sqlstring)

Knightlite · **Posted:** Thu Mar 26, 2009 10:06 am

Hi Goofie,

That would be great if it could be just one sql statement. I am using the Realbasic database engine. Do you know what that syntax may be?

Jim

Goofie · **Posted:** Thu Mar 26, 2009 10:07 am

pasted some pseudocode in my edited message
Only have mysql-experience, but realbasic should do the same

Quote:

For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value. The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.

Knightlite · **Posted:** Thu Mar 26, 2009 10:41 am

Hi Goofie,

I am trying to take your code and apply it to my situation, but I don't think I have it right since I get an error that the sqlexecute does not exist.

My database name = Fire5
The Table Name = Mark
The Field Name =XX
The recordset - wq

This is the code:

Code:

dim sqlstring as string="update  Mark set XX="" where XX=""X"
Fire5.sqlexectute(sqlstring)

I also tried this in which I did not get any errors but it did do anything

Code:

  Dim sqlstring As String
  
  sqlstring="update  Mark set  XX="" where  XX=""X"
  
  wq=Fire5.SQLSelect(sqlstring)

Can anyone see what may be wrong? Thank you.

Jim

Goofie · **Posted:** Thu Mar 26, 2009 10:47 am

update Mark set Mark.XX="" where Mark.XX=""X""

You can also debug using the errorcode retrieved from your database

Code:

if db.error then
msgbox db.errormessage
end if

Knightlite · **Posted:** Thu Mar 26, 2009 10:57 am

I think I have it.

The correct code is :

Code:

  Dim sqlstring As String
  sqlstring = "update Mark set XX = '' where XX = 'X';"
  wq=Fire5.SQLSelect(sqlstring)

Thanks Goofie for pointing me in the right direction. I really appreciate the help.

Jim

Bessie · **Joined:** Tue Mar 17, 2009 8:53 am **Posts:** 751

Knightlite wrote:

I think I have it.

The correct code is :

Code:

  Dim sqlstring As String
  sqlstring = "update Mark set XX = '' where XX = 'X';"
  wq=Fire5.SQLSelect(sqlstring)

Thanks Goofie for pointing me in the right direction. I really appreciate the help.

Jim

I think it should be SQLExecute, not SQLSelect. Also, you don't need the where clause if you're just resetting all of them. So it's just this one statement:

Code:

Fire5.SQLExecute "update Mark set XX=''"

What was wq for?

Knightlite · **Posted:** Sun Mar 29, 2009 10:49 am

Thanks so much Bessie. That code is wicked fast, and worked great.

Thank you again.

Jim

REAL Software Forums

Making The Code Run Faster Editing Records

Who is online

	REAL Software Forums The forum for REAL Studio and other REAL Software products.
[ REAL Software Website \| Board Index ]