SELECT	DISTINCT ContactID
FROM	crmContact
WHERE	ContactID IN	(
				SELECT	ContactID 
				FROM	lstRunFilter
				WHERE	RunID = 100
					AND ListFilterID IN (93, 98, 99, 100, 101, 102, 103)
			)
	AND NOT ContactID IN	(
					SELECT	ContactID
					FROM	lstRunFilter
					WHERE	RunID = 100
						AND ListFilterID = 94
				)

quote:

---

Originally posted by vallis

Nice one Peso. So that means there will only ever be a maximum of 3 logical operations on the data, and that will only happen when one list uses AND, OR and NOT on at least one plugin each. That should speed it up nicely, i'll post some times later when I rewrite the code generator.

Many thanks! :)

Any more suggestions or improvements are still gratefully receieved!

---

SELECT	DISTINCT ContactID
FROM	crmContact
WHERE	ContactID IN	(
				SELECT	ContactID
				FROM	lstRunFilter
				WHERE	RunID = 124
				AND ListFilterID IN (98, 99, 100, 101, 103)
			)
	AND ContactID IN	(
					SELECT	ContactID
					FROM	lstRunFilter
					WHERE	RunID = 124
						AND ListFilterID IN (102)
				)
	AND NOT ContactID NOT IN	(
						SELECT	ContactID
						FROM	lstRunFilter
						WHERE	RunID = 124
							AND ListFilterID IN (94)
					)

quote:

---

Originally posted by vallis

The above statement (which is operating on about 30000 records in lstRunFilter) now takes about half a second to execute, compared to 40-60 seconds before. (It's a slightly different statement but its close).

---

quote:

---

Originally posted by spirit1

so all those IN's and NOT IN's....
why don't you replace them with a single EXISTS?

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

---

SELECT	DISTINCT ContactID
FROM	crmContact t1
WHERE	exists (SELECT	*
		FROM	lstRunFilter
		WHERE	
			ContactId = t1.ContactID
			RunID = 124
			AND ListFilterID IN (98, 99, 100, 101, 103, 104, 94)			

select * from table1 t1
where exists (select * from table2 where t1.id = t2.id anb t1.someDate > t2.someDate) -- or any other condition

quote:

---

Originally posted by spirit1

Indeed exists return True or False
so If the condition specified is true it will return 1 and the row will get selected else it won't.

SELECT	DISTINCT ContactID
FROM	crmContact t1
WHERE	exists (SELECT	*
		FROM	lstRunFilter
		WHERE	
			ContactId = t1.ContactID
			RunID = 124
			AND ListFilterID IN (98, 99, 100, 101, 103, 104, 94)			

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

---

SELECT	DISTINCT ContactID
FROM	crmContact t1
WHERE	exists (SELECT	*
		FROM	lstRunFilter
		WHERE	
			ContactId = t1.ContactID
			AND RunID = 124
			AND ListFilterID IN (98, 99, 100, 101, 103, 104)
			AND ListFilterID NOT IN (94)

quote:

---

Originally posted by spirit1

SELECT	DISTINCT ContactID
FROM	crmContact t1
WHERE	exists (SELECT	*
		FROM	lstRunFilter
		WHERE	
			ContactId = t1.ContactID
			AND RunID = 124
			AND ListFilterID IN (98, 99, 100, 101, 103, 104)
			AND ListFilterID NOT IN (94)

---

ContactID  RunID  ListFilterID
---------  -----  ------------
123         124             98
123         124             94
124         124             99
125         124            100
126         124            101
127         124            103
128         124            104

SELECT	DISTINCT ContactID
FROM	crmContact
WHERE	ContactID IN	(
				SELECT	ContactID
				FROM	lstRunFilter
				WHERE	RunID = 124
				AND ListFilterID IN (98, 99, 100, 101, 103, 104)
			)
	AND ContactID NOT IN	(
						SELECT	ContactID
						FROM	lstRunFilter
						WHERE	RunID = 124
							AND ListFilterID IN (94)
					)

SELECT	DISTINCT ContactID
FROM	crmContact t1
WHERE	EXISTS (SELECT	*
		FROM	lstRunFilter
		WHERE	
			ContactId = t1.ContactID
			AND RunID = 124
			AND ListFilterID IN (98, 99, 100, 101, 103, 104))
	AND NOT EXISTS (SELECT	*
		FROM	lstRunFilter
		WHERE	
			ContactId = t1.ContactID
			AND RunID = 124
			AND ListFilterID IN (94))

quote:

---

Originally posted by vallis

Peso is correct. You would need to do something like this...

Which looks almost identical to the original IN based method, but with the INs replaced with EXISTS instead.

---