Help With Query - Index issue with OR statement ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help With Query - Index issue with OR statement ?

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2014-06-05 : 05:06:43

Hey I have the following Query..

SELECT pca.ADDRESS_POINT_ID AS address_point_id_1
FROM pca.v_assignment_chars AS pca INNER JOIN
pca.v_assignment_chars AS other_pca ON other_pca.ROUTING_KEY = pca.ROUTING_KEY AND (
other_pca.XX__ = pca.suffix OR
other_pca._XX_ = pca.suffix
)

AND other_pca.ADDRESS_POINT_ID IN
(SELECT others_in_group.pad_address_point_id
FROM (SELECT UNIQUE_GROUP_ID
FROM for_scrum_2.GROUPS_2 AS g
WHERE (pad_address_point_id = pca.ADDRESS_POINT_ID) AND (GROUP_TYPE IN (3))) AS in_groups INNER JOIN
for_scrum_2.GROUPS_2 AS others_in_group ON others_in_group.UNIQUE_GROUP_ID = in_groups.UNIQUE_GROUP_ID AND
NOT (others_in_group.pad_address_point_id = pca.ADDRESS_POINT_ID))

The problem is with this bit making the query very slow

AND (
other_pca.XX__ = pca.postcode_suffix OR
other_pca._XX_ = pca.postcode_suffix
)

If I just have either of those statements i.e.

AND (
other_pca.XX__ = pca.postcode_suffix
)

The query returns in seconds...but when I add the OR it takes hours and hours.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-05 : 08:09:24

First off, please post your code as #91;code#93; and use indentation to make it easy to follow the subqueries.

Second, what does the query plan show? Seeks or Scans? Generate a QP for the fast and slow variants and examine them for the differences. That should give you a clue.

BTW, you can write that OR clause more succinctly as:


pca.postcode_suffix  in (other_pca.XX__, other_pca._XX_)

Doing so eliminates an extra level of parentheses which makes the whole thing more readable.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-05 : 09:05:57

without looking for any other stuff...

eliminate the OR like this:

SELECT 
pca.ADDRESS_POINT_ID AS address_point_id_1
FROM pca.v_assignment_chars AS pca 
INNER JOIN pca.v_assignment_chars AS other_pca 
ON other_pca.ROUTING_KEY = pca.ROUTING_KEY AND other_pca._XX_ = pca.suffix
AND other_pca.ADDRESS_POINT_ID IN
 (SELECT others_in_group.pad_address_point_id
 FROM (SELECT UNIQUE_GROUP_ID
 FROM for_scrum_2.GROUPS_2 AS g
 WHERE (pad_address_point_id = pca.ADDRESS_POINT_ID) AND (GROUP_TYPE IN (3))) AS in_groups INNER JOIN
 for_scrum_2.GROUPS_2 AS others_in_group ON others_in_group.UNIQUE_GROUP_ID = in_groups.UNIQUE_GROUP_ID AND 
 NOT (others_in_group.pad_address_point_id = pca.ADDRESS_POINT_ID))

union

SELECT 
pca.ADDRESS_POINT_ID AS address_point_id_1
FROM pca.v_assignment_chars AS pca 
INNER JOIN pca.v_assignment_chars AS other_pca 
ON other_pca.ROUTING_KEY = pca.ROUTING_KEY AND other_pca.XX__ = pca.suffix 
AND other_pca.ADDRESS_POINT_ID IN
 (SELECT others_in_group.pad_address_point_id
 FROM (SELECT UNIQUE_GROUP_ID
 FROM for_scrum_2.GROUPS_2 AS g
 WHERE (pad_address_point_id = pca.ADDRESS_POINT_ID) AND (GROUP_TYPE IN (3))) AS in_groups INNER JOIN
 for_scrum_2.GROUPS_2 AS others_in_group ON others_in_group.UNIQUE_GROUP_ID = in_groups.UNIQUE_GROUP_ID AND 
 NOT (others_in_group.pad_address_point_id = pca.ADDRESS_POINT_ID))

Too old to Rock'n'Roll too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-05 : 11:51:15

You might try re-arranging your query. I suspect that the "AND other_pca.ADDRESS_POINT_ID IN ..." Might also be causing you issues. Without knowing data volume, indexing, etc.. it's hard to guide you, but when I see that kind of correlated sub query as part of a join clause, red flags go up fast. I doubt this works, but maybe this will help you see how to do that query without a correlated sub query:

SELECT 
	pca.ADDRESS_POINT_ID AS address_point_id_1
FROM 
	pca.v_assignment_chars AS pca 
INNER JOIN
	for_scrum_2.GROUPS_2 AS others_in_group 
	ON others_in_group.pad_address_point_id <> pca.ADDRESS_POINT_ID
INNER JOIN
	for_scrum_2.GROUPS_2 AS in_groups 
	ON others_in_group.UNIQUE_GROUP_ID = in_groups.UNIQUE_GROUP_ID 
	AND in_groups.pad_address_point_id = pca.ADDRESS_POINT_ID 
	AND in_groups.GROUP_TYPE = 3
INNER JOIN
	pca.v_assignment_chars AS other_pca 
	ON other_pca.ROUTING_KEY = pca.ROUTING_KEY 
	AND other_pca.ADDRESS_POINT_ID = others_in_group.pad_address_point_id
	AND 
	(
		other_pca.XX__ = pca.suffix OR
		other_pca._XX_ = pca.suffix 
	)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources