Internal Query Processor Error: The query processor could not produce a query pl

  • YSLGuru

    SSC-Insane

    Points: 21519

    Is there a standard method for troubleshooting the below error?

    Internal Query Processor Error: The query processor could not produce a query plan

    I know its probably the most popular reply to posts like this but I'm not allowed to post the DDL to recreate the involved tables.  I could provide a facsimile if that's the only way to troubleshoot this but it will take time to create.  I can post a copy of the query with the table and column names changed .

    I ran the below query last night many items (its contained in a report our accounting software uses) and every single time the result was this error msg. Today I re-ran those same reports requests and every one ran without any error so as of today I'm unable to produce the error when running the same query as last night.

      SELECT P.ID, 
    T.ID,
    TS.Status,
    C.ID,
    A.ID
    CT.ID

    FROM TABLEP P Join TABLET T ON P.ID = T.P_ID
    Join TABLETS TS ON T.Status = TS.Status
    Join TABLEC C ON T.Person = C.TS_Person
    Join TABLEA A ON A.ID = C.A_ID
    Left Outer Join TABLECT CT ON C.CT_ID = CT.ID

    WHERE 1 = 1
    AND C.Type = 7
    AND P.ID IN(1,2,3,4,5,6,7,8,9)
    AND C.Date BETWEEN '2020/10/01' AND '2020/10/01'

    When I was getting this error last night with the query I found I could get the query to run if I made one small change to one of the table joins as shown below between TABLEP and TABLEC.  By adding a conditioning to the join between TABLEC and TABLEP the query ran without any error and very quickly.  TABLEC has a Foreign Key column that links back to the Primary Key for TABLEP. That is there because there are times when you'd pull data from TABLEP and TABLEC without going thru TABLET and TABLETS.

    ID is a Primary Key column and P_ID is the foreign key.

      SELECT P.ID, 
    T.ID,
    TS.Status,
    C.ID,
    A.ID
    CT.ID

    FROM TABLEP P Join TABLET T ON P.ID = T.P_ID
    Join TABLETS TS ON T.Status = TS.Status
    Join TABLEC C ON T.Person = C.TS_Person AND C.P_ID = P.ID /*<<<THIS CHANGE*/
    Join TABLEA A ON A.ID = C.A_ID
    Left Outer Join TABLECT CT ON C.CT_ID = CT.ID

    WHERE 1 = 1
    AND C.Type = 7
    AND P.ID IN(1,2,3,4,5,6,7,8,9)
    AND C.Date BETWEEN '2020/10/01' AND '2020/10/01'

    Kindest Regards,

    Just say No to Facebook!
  • Sue_H

    SSC Guru

    Points: 90814

    You don't have the error anymore so it's hard to say. I have seen it come up when statistics were out of date and that would be my first guess of what may have happened in your case. I don't know that there is a standard way to troubleshoot the error other than going through the query, make sure the logic makes sense, remove any hints (since sometimes they don't make sense and wouldn't apply) and update the statistics.

    Sue

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply