Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

Internal SQL Server Error - Subquery and join

Hi - I know this is a bit of big question but I am really mystified.

I have a query which should be based on a view (I have put the view's query inside for this example) which needs to have the result put into a variable. Running this example causes error 8624 - 'Internal SQL Server Error'. It does not cause an error when: you remove the inner join to the view; OR you just run the query without passing it into a variable; OR you remove the WHERE clause with the subqueries.

Here is the example:

declare @var int

select @var = (

select count(*) from (
--**** following would be a view
SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
where
     r2.[sequence] =
     CASE WHEN NOT EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                     WHERE      r3.request_id = r1.request_id AND
                         r3.event_type = 'RELEAS' AND
                          r3.[sequence] > r1.[sequence]
                     )
          THEN ( SELECT MAX(r4.[sequence]) FROM request_event r4
                 WHERE  r4.request_id = r1.request_id AND
                     r4.event_type = 'COMPLT' AND
                     r4.[sequence] > r1.[sequence]
                )
          ELSE      ( SELECT MIN(r5.[sequence]) FROM request_event r5
                 WHERE  r5.request_id = r1.request_id AND
                     r5.event_type = 'RELEAS' AND
                     r5.[sequence] > r1.[sequence]
                )
          END
--***** end of view
) re
inner join request r on re.request_id = r.request_id
)

select @var
0
griffinj01
Asked:
griffinj01
1 Solution
 
BobSnyderCommented:
Two suggestions
Use sp_configure to check the query governor cost limit, it should be 0 to remove limits.
If any join column is NULLABLE, try converting its values to non null like
ISNULL(r3.event_type, '') = 'RELEAS'

The query optimizer goes through a lot of extra work if a column allows nulls.
0
 
griffinj01Author Commented:
Thanks for the ideas but query govenor cost limit is 0 and all columns are not nullable.
0
 
falconewCommented:
Hi,

When you run it without passing it into variable what is the output?
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
griffinj01Author Commented:
The output is just a normal count.

eg.            
-----------
379

(1 row(s) affected)

 
0
 
spcmnspffCommented:
Try this instead . . . .

declare @var int

select @var = count(*) from (
--**** following would be a view
SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
where
    r2.[sequence] =
    CASE WHEN NOT EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                    WHERE      r3.request_id = r1.request_id AND
                        r3.event_type = 'RELEAS' AND
                         r3.[sequence] > r1.[sequence]
                    )
         THEN ( SELECT MAX(r4.[sequence]) FROM request_event r4
                WHERE  r4.request_id = r1.request_id AND
                    r4.event_type = 'COMPLT' AND
                    r4.[sequence] > r1.[sequence]
               )
         ELSE      ( SELECT MIN(r5.[sequence]) FROM request_event r5
                WHERE  r5.request_id = r1.request_id AND
                    r5.event_type = 'RELEAS' AND
                    r5.[sequence] > r1.[sequence]
               )
         END
--***** end of view
) re
inner join request r on re.request_id = r.request_id


select @var
0
 
griffinj01Author Commented:
Excellent! Thanks spcmnspff. Do you want the points?

Why would that work and not the way I tried?
0
 
spcmnspffCommented:
Yeah I would like the points =).  Uhhh - My guess is that there were too many selects and the optimizer got confused, but really, who knows . . . .  One other thing, I was working on rewriting your view.  It would probably be faster with a union and a not exists rather than a cross join and a case.  Hold on I'll send you that too . . . .
0
 
spcmnspffCommented:
griffinj01:

I was thinking of something like this for th view:

SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
Where r2.[sequence] =  ( SELECT MIN(r5.[sequence]) FROM request_event r5
                   WHERE  r5.request_id = r1.request_id AND
                   r5.event_type = 'RELEAS' AND
                   r5.[sequence] > r1.[sequence]

    AND EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                  WHERE      r3.request_id = r1.request_id AND
                  r3.event_type = 'RELEAS' AND
                  r3.[sequence] > r1.[sequence])
   
UNION ALL
                   
SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
where
   r2.[sequence] = ( SELECT MAX(r4.[sequence]) FROM request_event r4
                     WHERE  r4.request_id = r1.request_id AND
                     r4.event_type = 'COMPLT' AND
                     r4.[sequence] > r1.[sequence]
              )  
   
   AND NOT EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                   WHERE      r3.request_id = r1.request_id AND
                       r3.event_type = 'RELEAS' AND
                        r3.[sequence] > r1.[sequence]

It actually turns out to bo not much different than yours, but see if the reads improve with this one  . .  .
0
 
griffinj01Author Commented:
Cool - I'll give that a go.  Thanks very much.
0
 
Scott PletcherSenior DBACommented:
Here is another possibility for the view.  I am not able to test it, but you might want to try it just in case:

SELECT r1.request_id, r1.event_type, r1.event_dt
FROM request_event r1
INNER JOIN
(
SELECT r2.request_id, MAX(r2.[sequence]) AS maxSeq, MIN(r2.[sequence]) AS minSeq
FROM request_event r2
WHERE r2.request_id = r1.request_id AND
((r2.event_type = 'RELEAS' AND maxSeq > r1.[sequence]) OR
(r2.event_type = 'COMPLT' AND minSeq > r1.[sequence]))
GROUP BY r2.request_id
) AS r2e ON r1.request_id = r2e.request_id

You will need index(es) on request_id (very likely already present) and [sequence] (probably present as well).  For the purposes of this query, it would help if you added event_type as an additional column in (one of) the index(es); that is, if it's a large table.  Then the query will be able to complete using only the index, without accessing the primary table at all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now