optimizing a query to delete duplicates

I have a DELETE statement that deletes duplicate data from a table. It
takes a long time to execute, so I thought I'd seek advice here. The
structure of the table is little funny. The following is NOT the table,
but the representation of the data in the table:

+----+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 123 |
+--+--+

As you can see, the data is tabular. This is how it is stored in the table:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 234 |
+--+----+----+

What I need is to delete all records having the same "Row" when there exists
the same set of records with a different (smaller, to be precise) "Row".
Using the example above, what I need to get is:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
+--+----+----+

A slow way of doing this seem to be:

DELETE FROM X
WHERE Row IN
(SELECT DISTINCT Row FROM X x1
WHERE EXISTS
(SELECT * FROM X x2
WHERE x2.Row < x1.Row
AND NOT EXISTS
(SELECT * FROM X x3
WHERE x3.Row = x2.Row
AND x3.FieldName = x2.FieldName
AND x3.FieldValue <> x1.FieldValue)))

Can this be done faster, better, and cheaper?my knee-jerk reaction is:

Why is it important to optimize it? I think you should delete the
duplicates, then create a constraint that prevents them from recurring.

If, for some reason, you are unable to fix the application that creates
these duplicates, and creating a constraint causes errors in the application
that you can't tolerate, then I suppose an alternative would be to create a
trigger that deletes them upon entry. Having a composite index on the
columns that are being duplicated would enable such a trigger to run
quickly.

But looking at your query, I find it strangely complex.

Why not just:

DELETE FROM X
WHERE EXISTS (SELECT * FROM X x2
WHERE x2.Row < x.Row
AND X.FieldName = x2.FieldName
AND X.FieldValue = x2.FieldValue)

Am I missing something? Your NOT EXISTS has me a bit confused... I think it
might delete data in situations other than described.

Also, NOT EXISTS is generally slow.|||On 2004-07-15, Aaron W. West <tallpeak@.hotmail.NO.SPAM> wrote:
> Why is it important to optimize it? I think you should delete the
> duplicates, then create a constraint that prevents them from recurring.

Such constraint may not be created. This table is a temporary table, where
data from an input file is loaded. Duplicate sets of records must be
deleted because the data then goes into permanent tables. Those table have
constraints against duplicates.

> But looking at your query, I find it strangely complex.

Me too. I'm trying to improve it. Its complexity seems to hinder its
performance.

> Why not just:
> DELETE FROM X
> WHERE EXISTS (SELECT * FROM X x2
> WHERE x2.Row < x.Row
> AND X.FieldName = x2.FieldName
> AND X.FieldValue = x2.FieldValue)

This would delete records that should not be deleted. Here's an example:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 666 |
+--+----+----+

Here the combination of values for "a" and "b" on every "Row" is
different. There are no duplicates here. The query that you proposed would
delete the second to last row

+--+----+----+
| 3 | a | 123 |
+--+----+----+

because it has the same FieldName and FieldValue as the first row.

Think of it the data this way:

+--+--+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 666 |
+--+--+

No duplicate rows here.|||Hi

You could try only selecting the correct data when you move it into the
permanent tables. But the following may work better:

DELETE FROM X1
FROM X X1 JOIN X X2
ON x2.Row < x1.Row
AND x1.Fieldvalue = x2.Fieldvalue
AND x1.FieldName = x2.FieldName

John

"Alexander Anderson" <no@.spam.com> wrote in message
news:slrncfe0ft.mk1.alex@.Toronto-HSE-ppp3682122.sympatico.ca...
> I have a DELETE statement that deletes duplicate data from a table. It
> takes a long time to execute, so I thought I'd seek advice here. The
> structure of the table is little funny. The following is NOT the table,
> but the representation of the data in the table:
> +----+
> | a | b |
> +--+--+
> | 123 | 234 |
> | 345 | 456 |
> | 123 | 123 |
> +--+--+
> As you can see, the data is tabular. This is how it is stored in the
table:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> | 3 | a | 123 |
> | 3 | b | 234 |
> +--+----+----+
> What I need is to delete all records having the same "Row" when there
exists
> the same set of records with a different (smaller, to be precise) "Row".
> Using the example above, what I need to get is:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> +--+----+----+
> A slow way of doing this seem to be:
> DELETE FROM X
> WHERE Row IN
> (SELECT DISTINCT Row FROM X x1
> WHERE EXISTS
> (SELECT * FROM X x2
> WHERE x2.Row < x1.Row
> AND NOT EXISTS
> (SELECT * FROM X x3
> WHERE x3.Row = x2.Row
> AND x3.FieldName = x2.FieldName
> AND x3.FieldValue <> x1.FieldValue)))
> Can this be done faster, better, and cheaper?

optimize query - how to make it an "Index seek"

create table t1(a varchar(50) , b varchar(50))

create index i1 on t1(a)
create index i2 on t1(b)

create view v1
as
select * from t1 where isnull(a,b) = 'test'

select * from v1

The above SQL "select * from v1" is doing a table scan.
What do I do to make it perform an index seek ??

TIA

- ForXLDBHi!

This query will make use of index i3:

create index i3 on t1(a,b)

Carsten|||Is it possible to have make an "Index seek" ??|||Just found the following behaviour:

When running:
select * from dbo.v1 => SQL Server uses an index scan

select * from dbo.v1 where a='test' => SQL Server uses an index seek

It's quite strange...|||I do use the following...
select * from dbo.v1 where a='test'

So, it works for me I guess.

Thanks !!|||quite strange...

How so?

There's no predicate...|||I'm not sure. But, it shows "Index Seek" in the plan !!|||Hi Brett,

what confused me is the point, that the DBMS changes the execution plan even if there is basically no difference in the constraint (in this case "a = 'test'").

The definition of v1 already contains this WHERE-clause. And that's why I expect SQL Server to generate the same execution plan.

Carsten

How so?

There's no predicate...|||create table t1(a datetime , b datetime)

create view v1
as
select a,b, isnull(a,b) as c from t1

create index i3 on t1(a,b)

select *
from v1
where c = '01/01/2004'

the above sql is using index scan.....can we make it to use index seek ?

--clean up
--drop table t1
--drop view v1
--drop index t1.i3|||One small thing is that you need to have a bunch of data in the table, before the optimizer thinks about the index. If the data portion of the table consists of a single page, then you will always get a table scan. This is not bad, as it is one read. If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.

A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.|||--Change view to:
alter view v1
as
select * from t1 where a = 'test'
union
select * from t1 where b = 'test'
go

--add 2 indexes and drop existing ones:
drop index t1.i1
drop index t1.i2
create index i3 on t1(a,b)
create index i4 on t1(b,a)|||...If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.This is not true when you deal with queries that do not address fields that are not part of an index.

...A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.Really? Try this using DDL changes from my previous post:

select * from v1 where isnull(a,b)='test'