[Dev-sig] Query question for SQL gurus...

[Emerson, Tom (*IC)]

> -----Original Message----- Of John Riehl
> Emerson, Tom (*IC) wrote:
> > 
> > *Is this valid:*
> > 
> > *[... deleting from MULTIPLE tables allowed?]*
> 
> All the rdbmses that I have seen only allow 1 table name.
> 
> > 
> > *Barring that, I would (ordinarily) do this:*
> > 
> > *   delete from extension*
> > *    where pri-key in (select pri-key from header where pi-key = 
> > <whatever>)*
> > [but problems occur if list >= 256 elements]
> 
> From your description, it appears as though when the database has a 
> list of > 255 items, it goes into a serial mode.  This might 
> be a smart algorithm on the database's part (calculating page
> hit rate), or it could be just some sloppy algorithmic ("no one
> would try to [select] delete more than x at a time") assumptions by
> the database designer, ...

Actually, the underlying database is HP's "TurboIMAGE" database, which
isn't specificially "relational", however using "Allbase", they've
"bolted on" SQL compliance, (to a degree), so the internal
decision/limit was probably more the latter (but could have been
hampered by memory constraints -- i.e., allowing for more items in the
list would have used more memory than the designers were willing to
waste...)

> If you break up your delete into multiple statements with fewer 
> than 256 elements per, is the aggregate speed better, or 
> about the same vs 1 big delete?

In this particular case, I don't know -- I DO know that where this came
up before (having more that 256 elements in the "in()" list), the time
difference between 254 and 257 was on the order of 8-10 minutes [with
<256 taking about 10 seconds...]

> The other extreme is to iterate through your list of 
> prikeys, giving individual delete commands.  (I dont endorse 
> it, but it might be something to try).   What is your hit rate?

Again, "for this case", this is exactly what I ended up doing [mainly
because I needed to iterate through the primary table ANYWAY]

After thinking about it last night, though, I realized there is one
other way I could do this: enforce "referential integrity" and enable
cascading deletes -- when the "primary" table entry gets deleted, the DB
engine takes care of the secondary set on it's own -- or [just thought
of now...] write a "trigger" that deletes the secondary entries when the
primary gets deleted.