Inserting / Deleting one at a time vs. dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Inserting / Deleting one at a time vs. dynamic SQL

Author

Topic

rasmasyean
Starting Member

22 Posts

Posted - 2009-07-22 : 07:15:07

What’s better to do?

If you do a dynamic SQL string based on the input records, it has to compile the SQL run time right?

And if you have an individual record command, it will compile it once during build time right?

So what’s better to do? Dynamically make your SQL statement, or loop through your records and execute one at a time?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-22 : 07:29:32

Why not just bulk insert?

It really would depend on the situation, as you have specified no situation, my above comment stands.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-22 : 08:59:54

I too like to go for option 3 (something else)

Post some specifics and you'll get better responses.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rasmasyean
Starting Member

22 Posts

Posted - 2009-07-22 : 11:51:38

Oh sorry, I mean like this.

command_string = “DELETE…WHERE”
foreach (record)
{
concatenate command_string with record key
}
execute

vs.

foreach(record)
{
command_string = “DELETE…WHERE” + record key
execute
}

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-22 : 12:04:16

Neither is a good approach. You want to issue a set based operation so you only want to use 1 delete statement. (unless you are deleting lots of rows from a very big table where you may want to used a page delete)

Using IN


DELETE FROM x
WHERE
    x.[key] IN (
        <comma separated list>
        )

Using JOIN


DELETE x
FROM
    tabX x
    JOIN deleteVals d ON d.[key] = x.[key]

Examples

DECLARE @foo TABLE (
    [Id] INT
    , [value] NVARCHAR(255)
    )

INSERT @foo ([ID], [value])
      SELECT 1, 'a'
UNION SELECT 2, 'b'
UNION SELECT 3, 'c'
UNION SELECT 4, 'd'
UNION SELECT 5, 'e'
UNION SELECT 6, 'f'
UNION SELECT 7, 'g'

SELECT * FROM @foo

-- Delete using (IN)
DELETE f
FROM
	@foo f
WHERE
	f.[ID] IN (
		1
		, 5
		, 7
	)

SELECT * FROM @foo

-- Delete using JOIN
DECLARE @delList TABLE ([ID] INT)
INSERT @delList ([ID])
      SELECT 2
UNION SELECT 4

DELETE f
FROM
	@foo f
	JOIN @delList d ON d.[Id] = f.[ID]

SELECT * FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-22 : 12:06:33

Also -- the syntax you posted isn't Microsoft SQLSERVER syntax. Is that mysql or an IBM variant you are using?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rasmasyean
Starting Member

22 Posts

Posted - 2009-07-22 : 19:33:05

Oh that “syntax” was pseudo code. I was just trying to illustrate a concept. I have experience with DB2 for a number of years so maybe the structure is different?

Thanks for the info.

Someone once told me that if you have a dynamic SQL, the statement had to be compiled or something during run-time for each execution. And if you have a static SQL (with field parameters), the compiler will build the statement and it will just execute without having to recompile. So I thought that if you had a variable list of keys, it might make the SQL compile all the time and take a performance hit.

Can the IN statement handle multiply field keys?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-23 : 03:45:13

You should read this:
http://www.sommarskog.se/dynamic_sql.html
It's a fantastic resource for dynamic sql

The short version is that -- nowadays you can store cache plans for dynamic sql so performance is on a par with regular sql.

For parametrised queries if you use the EXEC sp_executeSql method then you can still reuse the query plan.

why don't you post the table structure and the choices for keys you want to delete from?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-23 : 03:53:02

Also try to explain how you are doing the delete, is it a front end app and the user gets to delete the records? If so, are you 100% sure you want to really delete these records? What happens if the user accidently deletes a record etc..

rickh
Starting Member

2 Posts

Posted - 2009-07-23 : 04:05:21

If you are using SQL Server 2008 you can pass in a table variable. that way ou could use a sytax similar to this.

DELETE t
FROM MyTable t
WHERE EXISTS (SELECT * FROM MyTableVariable tv WHERE tv.Key = t.Key)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources