Re: Deleting Multiple Rows
From: Guadala Harry (GMan_at_NoSpam.com)
Date: 08/16/04
- Next message: Alex O: "RE: sql stored procedures"
- Previous message: Craig Kenisston: "How to NOT start a distributed transaction ?"
- In reply to: Joe Celko: "Re: Deleting Multiple Rows"
- Next in thread: Dandy WEYN: "Re: Deleting Multiple Rows"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 16 Aug 2004 16:14:12 -0700
Yes - AND - being *able* to put it in the WHERE clause is very different
than saying it *should* go there. My app has some rather highly performing
"in-memory" structures (a tree comprised of of hash tables and
ListDictionary collections) in which user preferences (i.e., inserts,
updates, and deletes) are cached. IF/When the user decides to save changes,
I'd like to ship off a list of rows to delete to the DB. In my case I'd
conclude that it's not realistic (although possible) to do it all in the DB
(we're very interested in minimizing number of db connections, db hits,
etc). A couple of years ago, the recommended strategy (per this very group)
was to create a loop in my application - wrap the loop in an ADO
transaction - and execute a stored procedure - once per loop - that would do
any inserts/updates/deletes. I never really liked that solution - but it was
the lesser evil relative to the old nasty ADO .UpdateBatch() method... Just
revisiting the issue now that I'm on a new system and now that our
technologies have evolved somewhat since ADO.
Thanks!
"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:OICl0y9gEHA.2812@tk2msftngp13.phx.gbl...
> >> The logic that determines which rows to delete has to stay in the
> application (can't realistically be moved to the db). <<
>
> Why? The only reason I can come up with is that a human being or
> external agent is making unpredictable numbers as they go along. If
> there was an algorithm, then you ought to be able to put it in the WHERE
> clause of a DELETE FROM statement.
>
> >> Too bad there's not an easy way to pass a varchar variable
> (containing a delimited list of the ints) to the WHERE clause of the
> DELETE statement... <<
>
> There are a bunch of such kludges posted here. They are all dangerous
> and slow thanks to dynamic SQL.
>
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
- Next message: Alex O: "RE: sql stored procedures"
- Previous message: Craig Kenisston: "How to NOT start a distributed transaction ?"
- In reply to: Joe Celko: "Re: Deleting Multiple Rows"
- Next in thread: Dandy WEYN: "Re: Deleting Multiple Rows"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
