2

I have a very long query which uses distinct intersect, when the query grows too long and I execute it it throws above exception 

 SELECT DISTINCT RTypeId FROM dbo.User_Res WHERE UserId = '1749' 
 INTERSECT SELECT DISTINCT RTypeId FROM dbo.User_Res WHERE UserId = '424' 
 INTERSECT SELECT DISTINCT RTypeId FROM dbo.User_Res WHERE UserId = '1906' 
 INTERSECT SELECT DISTINCT RTypeId FROM dbo.User_Res WHERE UserId = '725' 
 INTERSECT SELECT DISTINCT RTypeId FROM dbo.User_Res WHERE UserId = '1596'

Can you please help me with an alternative to this query?

Error msg:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
G--
  • 497
  • 1
  • 8
  • 25
  • 2
    `INTERSECT` is supposed to show `DISTINCT` values. Why do you explicitly use `DISTINCT` in the sub queries? Also, wouldn't it be faster to write something along the lines of 'SELECT DISTINCT TYpeID FROM dbo.User_Res WHERE UserID IN ('1749', '424', '1906', '725', '1596')`? – SchmitzIT Dec 05 '13 at 10:44
  • AFAIR we had this same error after upgrading up from SQLS2005. We had insane amounts of repeated constants in queries, userid's like you and accountids etc. We had lots of continuous ranges and no will to rewrite view logic so I build a "compacter" that built "between" clauses. Do not recommend even though it solved that particular case. – Pasi Savolainen Dec 05 '13 at 10:56
  • @SchmitzIT:i have to intersect the result set obtained from previous users with next set of records..and i thought this would be the solution..now i am thinking abt the point you mentioned.thanks for pointing out! – G-- Dec 06 '13 at 03:46

1 Answers1

2

So essentially what you want is to get RTypeIds' that are common to all the users?

You could build the same query in this way:

with userrt (rid, uid)
as (select distinct rtypeid, userid from User_Res where UserId in (1749, 424, 1906 ...)
select rid, count(uid) as cuid
from userrt
group by rid

Now only those result rows that have cuid == amount of userid's in limiting clause are the ones you're interested in (since they've been there for all users and are therefore common to all).

Pasi Savolainen
  • 2,460
  • 1
  • 22
  • 35