SQL question

[Parker, Ron]

> -----Original Message-----
> From: Brian Densmore [mailto:DensmoreB at ctbsonline.com]
> Sent: Friday, August 08, 2003 4:34 PM
> To: KCLUG (E-mail)
> Subject: SQL question

> 1) why should it take 18 hours to run (the delete query should
> have deleted about 1/3 of the data in the database),
> 2) is there anyway to have a single delete query statement
> do periodic commits (as really dangerous as it is)?
> 
> basically what I have is this:
> 
> table bad_marks (uid,mark)
> table messages  (indice,mark,...)
> table msg_elements (id,indice,...)
> 
> where bad_marks.mark 1->M/1->0 messages.mark, uid = PK
>       messages.indice 1->M msg_element.indice, message.indice 
> = PK, message.mark = indexed
>       msg_element id = PK, indice = indexed
> and there is no referential integrity turned on (yet).

Off the top of my head I would guess the DBE is doing some sort of N^2
operation to find the records to delete.  What SQL server are you using?
What's your actual delete query look like?  Sometimes changing a query from
something naive to something a little more sophisticated will avoid the N^2
problem.  I had this sort of thing happening all the time with Informix
years ago.