asked on

very slow query

the query below is working but takes forever. well over a minute to go through 300 records. surely there must be a more efficient alternative. the vwClientMembers is actually a query that looks across a network into an access database. I get the feeling it is going back and forth unnecessarily.

any ideas?

SELECT EmailAbsolute, Nom, Prenom, ContactID, Country, ClientLanguage, Prefix, EmailAbsolute AS NumOccurrences
FROM vwClientMembers AS q1
WHERE (NOT EXISTS
(SELECT NULL AS Expr1
FROM members
WHERE (active = 1) AND (q1.EmailAbsolute = email)))
AND q1.EmailAbsolute in
(SELECT EmailAbsolute FROM vwClientMembers GROUP BY EmailAbsolute
HAVING (COUNT(*) > 1))

danrosenthal

Give this a try...

SELECT	
	EmailAbsolute, Nom, Prenom, ContactID, Country, ClientLanguage
	, Prefix, EmailAbsolute AS NumOccurrences
FROM	
	vwClientMembers AS q1
WHERE
	q1.EmailAbsolute NOT IN ( 
		SELECT email
		FROM members
		WHERE active = 1
	) 
AND q1.EmailAbsolute IN (
		SELECT EmailAbsolute 
		FROM vwClientMembers 
		GROUP BY EmailAbsolute
		HAVING (COUNT(*) > 1)
	)

Open in new window

Shawn

ASKER

still well over a miniute. pretty much the same amount of time

Sharath S

try this

SELECT     EmailAbsolute, Nom, Prenom, ContactID, Country, ClientLanguage, Prefix, EmailAbsolute AS NumOccurrences
FROM         vwClientMembers AS q1
JOIN (SELECT EmailAbsolute FROM vwClientMembers GROUP BY EmailAbsolute 
HAVING (COUNT(*) > 1)) cm ON q1.EmailAbsolute = cm.EmailAbsolute
LEFT JOIN members m1 ON m1.active = 1 and q1.EmailAbsolute = m1.email
WHERE m1.email IS NULL

Open in new window

Shawn

ASKER

getting various errors here:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'EmailAbsolute'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'ContactID'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Country'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'EmailAbsolute'.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Olaf Doschke

Is there an index in each table on the email fields?

Bye, Olaf.

Shawn

ASKER

brilliant! lightning fast now.

thank you!