Select/Join query execution time too long

[cossiboon]

Okay, I hope you guys can keep up with this... Here's my dilema: I have a user table, a list table and a list/user index table. Our interface allows people to create lists and I need to do a check to verify if there are duplicate email addresses in the same list. So I wrote the query below. The execution time on this query is 3 mins on a list of 5000 and 38 duplicates.
The user_tbl.email_address_vc is a varchar(300), which I think is causing the delay.

SELECT u_tbl.*
FROM user_tbl as u_tbl

INNER JOIN list_user_index_tbl as lui_tbl
ON u_tbl.[user_id] = lui_tbl.[user_id]

WHERE (lui_tbl.list_id = @nmListID) AND (u_tbl.email_address_vc IN
(SELECT user_tbl.email_address_vc
FROM user_tbl

INNER JOIN list_user_index_tbl
ON user_tbl.[user_id] = list_user_index_tbl.[user_id]

WHERE (list_user_index_tbl.list_id = @nmListID)
GROUP BY user_tbl.email_address_vc
HAVING (COUNT(user_tbl.email_address_vc) > 1)
)
)


Any ideas? New query samples???

ANYBODY??? Please ?

cossiboon

 

[royjimenez]

can u write the primary keys ?
do u have indexes in the columns ?

in the IN u could return the primary instead of email field.

 

[cossiboon]

No indexes.
user_tbl
user_id primary key

list_id is a primary key in the list_tbl not the list_user_index_tbl

Could you give me an example of getting the user_id instead of the email_address_vc field?

Because this doesn't work, email_address_vc needs to be in the select for the Group by to function:

SELECT u_tbl.*
FROM user_tbl as u_tbl

INNER JOIN list_user_index_tbl as lui_tbl
ON u_tbl.[user_id] = lui_tbl.[user_id]

WHERE (lui_tbl.list_id = @nmListID) AND (u_tbl.[user_id] IN
(SELECT user_tbl.[user_id]
FROM user_tbl

INNER JOIN list_user_index_tbl
ON user_tbl.[user_id] = list_user_index_tbl.[user_id]

WHERE (list_user_index_tbl.list_id = @nmListID)
GROUP BY user_tbl.email_address_vc
HAVING (COUNT(user_tbl.email_address_vc) > 1)
)
)