...

View Full Version : More Efficient Query Needed



sitechooser
03-07-2013, 03:35 PM
Hi All

Does anyone have an idea how to run this query...




SELECT *
FROM [TableName]
WHERE [ColumnName] IN
(
SELECT [ColumnName]
FROM [TableName]
GROUP BY [ColumnName]
HAVING COUNT(*) > 1
)
ORDER BY [ColumnName]



without the sub query?

The sub query option seems to be taking ages to run. Is there a more efficient query that does the same thing?

Thanks in advance for any help.

sunfighter
03-07-2013, 04:32 PM
SELECT [ColumnName]
FROM [TableName]
GROUP BY [ColumnName] <= Not needed because that is the only thing your getting
HAVING COUNT(*) > 1 <= This makes no sense to me because count() tells you the number of rows. Do you mean where the value of the ColumnName column is large then 1?

sitechooser
03-07-2013, 08:55 PM
Sorry, it would help if I explained what I'm trying to do!

The query is supposed to search a column (name) for duplicate values and then return all data in the table which has duplicate values in that column

Any ideas?

Old Pedant
03-07-2013, 09:24 PM
Nope, I think you have it right.

Though doing it like this *MIGHT* be more efficient:


SELECT T1.*
FROM table AS T1,
( SELECT column, COUNT(column) AS cnt
FROM table
GROUP BY column
HAVING cnt > 1
) AS T2
WHERE T1.column = T2.column
ORDER BY T1.column

Or even possibly (though I doubt it)


SELECT T1.*
FROM table AS T1,
( SELECT column, COUNT(column) AS cnt
FROM table
GROUP BY column
) AS T2
WHERE T1.column = T2.column AND T2.cnt > 1
ORDER BY T1.column

May not matter, but you could try it.

Old Pedant
03-07-2013, 09:25 PM
It will, of course, make a *HUGE* difference if column is indexed.

sitechooser
03-07-2013, 11:21 PM
Magic!

I ran the query on an 80MB table and it returned in 6.5 minutes. I then made the column concerned an index, ran the same query and it returned in 7.6 seconds! Bit of a difference:thumbsup:

Thanks a million for your help, got a critical meeting tomorrow morning and you'll make me look like superman.:D

Old Pedant
03-08-2013, 12:26 AM
LOL! The cardinal rule of database design: When it's slow, index it.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum