Re: Slow Database Deletes

Peter, the reason it's slow is because of the transactions, RB makes a new
transaction for each rs.DeleteRecord you are preforming. so you should put

your_database_file.SQLExecute("Begin Transaction") before the SQLSelect
statement and your_database_file.SQLExecute("End Transaction") after the
Wend.

That said, letting RB delete the records for you is going to be slow no
matter what you do, so why not just talk to the database engine yourself.
Instead of all the code you have try this. It looks like you are trying to
delete everything from the database table so :

your_database_file.SQLExecute("Begin Transaction")

your_database_file.SQLExecute("Delete From Song")

your_database_file.SQLExecute("End Transaction")

-----------------------
If you only wanted to delete songs by INXS you'd do something like

your_database_file.SQLExecute("Delete From Song where Singer = 'INXS'")

------------------------------------------------

"Peter" <peter@xxxxxxxxxxxxxxxxxxxxxx*> wrote in message
news:peter-3A4CDA.15492115122005@xxxxxxxxxxxxxxxxxxxxxxxx
>
> I have been experimenting for the first time with the built in database
> in RB2005. While adding lots of records (1000) at once seems very quick,
> deleting all records (6000) takes about 30 seconds on my iBook 700MHz
> G3. Is this fairly normal for databases?
>
> here is the code I am using:
>
> rs = MusicLib.SQLSelect("SELECT rowid, * FROM SONG")
>
> while not rs.eof
> rs.DeleteRecord
> rs.MoveNext
> Wend
>
>
> I tried moving to the end of the record set and deleting backwards but
> that was worse and eventually hung:
>
> rs.MoveLast
> while not rs.eof
> rs.DeleteRecord
> rs.MovePrevious
> Wend
>
> Also I didnt call Commit after deleting yet the data was all gone. Does
> this mean Undoing record deletion is not really possible?
>
> Maybe it would be better 'flag' the records as deleted then slowly
> delete them in a thread?
>
> thanks!
> Peter
>
> www.fracturedSoftware.com
> zegsRuler - Add Rulers to your RB project in minutes!
> Rondo - Midi made easy.


.

Relevant Pages

  • Re: Capture Users IP in Access
    ... ' The user roster is exposed as a provider-specific schema rowset ... Wend ... > 'Private Sub Detail_Format ... > 'Output the list of all users in the current database. ...
    (microsoft.public.access.security)
  • Re: Alan Turings halting proof is incorrectly formed PT Herc
    ... thats just HTML you must have linux or some wierd reader. ... >> Isn't this exactly what Peter is saying? ... >> Does it stand up against the halting proof? ...
    (comp.theory)
  • Re: Alan Turings halting proof is incorrectly formed PT Herc
    ... thats just HTML you must have linux or some wierd reader. ... >> Isn't this exactly what Peter is saying? ... >> Does it stand up against the halting proof? ...
    (sci.math)
  • Re: Alan Turings halting proof is incorrectly formed PT Herc
    ... thats just HTML you must have linux or some wierd reader. ... >> Isn't this exactly what Peter is saying? ... >> Does it stand up against the halting proof? ...
    (sci.logic)
  • Slow Database Deletes
    ... I have been experimenting for the first time with the built in database ... Wend ... Also I didnt call Commit after deleting yet the data was all gone. ...
    (comp.lang.basic.realbasic)