[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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