Re: Deleting Multiple Rows

From: Dandy WEYN (dandy_at_STOPSPAM_dandyman.net)
Date: 08/16/04 

Date: Mon, 16 Aug 2004 23:04:38 GMT

I prefer my syntax especially if you make the table a memory table for short
number of records.
Going dynamic sql is mostly the last option if I can't make it with standard
transact-sql

Relational databases love to work with datasets.

Executing the stored procedure to pass single parameters will cause them to
perform multiple index seeks or scans retrieving the affected records.

Therefor a temporary table (or eventually table datatype) might be the best
solution. 

-- 
Dandy  Weyn, Belgium
MCSE, MCSA,MCDST,MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Guadala Harry" <GMan@NoSpam.com> wrote in message 
news:uTr5cS9gEHA.3016@tk2msftngp13.phx.gbl...
> Suppose I have a very simple table (just one column of integers, not null,
> for the sake of simplicity).
>
> I have an application that interacts with this table (in a SS2K database)
> via ADO.NET.
>
> There will periodically be the need to arbitrarily delete 300-500 out of
> 150,000 rows from this table (arbitrarily - meaning that the integers to 
> be
> deleted do not necessarily fall in a range).
>
> My application has the logic that determines which of the rows need to be
> deleted.
>
> What is an efficiant and recommended way to get rid of these rows?
>
> Should the app execute a stored procedure 300 times - once for each row to
> be deleted? I'd hate to do that if I don't have to (transaction or not). 
> Is
> there a way to pass to the stored procedure the list of 300 integers that
> need to be deleted and then the stored procedure executes only one DELETE
> statement that does the job? I can pass the list to the SP - but how to 
> get
> the DELETE statement to use it? Is there a better way altogether?
>
> Thanks!
>
> -G
>
>
>
>
>

Relevant Pages

  • Re: Pass-thru SQL performance vs Stored Proc
    ... >> are going to have to create a lot of dynamic SQL based on user selects ... > stored procedure per permutation of parameters, ... > ms per execution, and you are executing it 100 times an hour, no problem, a ... Right now all our code is in stored procs. ...
    (microsoft.public.sqlserver.programming)
  • SQL command parameters Advantages of?
    ... a quick ADO.NET question in regards to the command object. ... executing a stored procedure over just calling the stored procedure via ... // Pseudo code below using dynamic SQL ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: Use variable in table name?
    ... You can create the temp table before executing your dynamic SQL. ... I have one Stored procedure that use a global temporary table, ... of a SQL in Linked server: ...
    (microsoft.public.sqlserver.programming)
  • Re: Poor performance when executing stored procedure
    ... If SQL Server ... has to re-compile the SP, it takes shorter time to compile a smaller stored ... Poor performance when executing stored procedure ...
    (microsoft.public.sqlserver.programming)
  • Stored procedure does not complete until result set is retrieved from ODBC
    ... I have a SQL Server Stored procedure that I am executing via ODBC. ... -- Start Code without cursor ...
    (microsoft.public.sqlserver.odbc)
Loading