Odd Behavior by Instance

  • Again, all of these attempted changes are blind guesses until you know why the query takes the time it does.

    You need to collect information about that session when the query is running to see why it takes the time it does (while not perfect, this could be as simple as querying the DMVs or sysprocesses and manually eyeballing wait stats, IO, CPU, etc.). Once you know that, we can start to make some more pointed investigations.

    Randomly changing aspects of the query is an incredibly inefficient way of troubleshooting something like this.

    Could you at least post the execution plans for the original query from each of the servers?

    Cheers!

  • Another thing you could try, if you are unable to post the detailed information being requested, is breaking down the queries into multiple steps and using well designed temp tables to store intermediate results.
    Eg, create an indexed temp table containing the results of running
       SELECT ACCT
        FROM dbo.TABLEB
        WHERE
           FSCD IN ('BIQ', 'BCU')

    and then you can modify your subsequent query to use the temp table.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Lynn Pettis - Tuesday, July 25, 2017 8:38 AM

    Change this:

    AND A.[ACCT] NOT IN
                (
                  SELECT
                    [ACCT]
                  FROM
                    [dbo].[TABLEB]
                  WHERE
                    [FSCD] IN (
                                'BIQ'
                                , 'BCU'
                              )
                )

    to this and see if there is a difference:

    AND NOT EXISTS
                (
                  SELECT
                    [ACCT]
                  FROM
                    [dbo].[TABLEB] [tb]
                  WHERE
                    [tb].[ACCT] = [A].[ACCT] AND
                    [tb].[FSCD] IN (
                                      'BIQ'
                                      , 'BCU'
                                    )
                )

    did not make a difference, but thanks for the input.

  • Phil Parkin - Tuesday, July 25, 2017 8:49 AM

    Another thing you could try, if you are unable to post the detailed information being requested, is breaking down the queries into multiple steps and using well designed temp tables to store intermediate results.
    Eg, create an indexed temp table containing the results of running
       SELECT ACCT
        FROM dbo.TABLEB
        WHERE
           FSCD IN ('BIQ', 'BCU')

    and then you can modify your subsequent query to use the temp table.

    That's what we ended up doing to resolve it. Using a Table Variable in place of the query and it is more than happy with that. But this behavior difference is a mystery.

  • Antares686 - Tuesday, July 25, 2017 9:36 AM

    Phil Parkin - Tuesday, July 25, 2017 8:49 AM

    Another thing you could try, if you are unable to post the detailed information being requested, is breaking down the queries into multiple steps and using well designed temp tables to store intermediate results.
    Eg, create an indexed temp table containing the results of running
       SELECT ACCT
        FROM dbo.TABLEB
        WHERE
           FSCD IN ('BIQ', 'BCU')

    and then you can modify your subsequent query to use the temp table.

    That's what we ended up doing to resolve it. Using a Table Variable in place of the query and it is more than happy with that. But this behavior difference is a mystery.

    I suggest that you try using temp tables rather than table variables. They often perform better and rarely perform worse.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil Parkin - Tuesday, July 25, 2017 10:56 AM

    Antares686 - Tuesday, July 25, 2017 9:36 AM

    Phil Parkin - Tuesday, July 25, 2017 8:49 AM

    Another thing you could try, if you are unable to post the detailed information being requested, is breaking down the queries into multiple steps and using well designed temp tables to store intermediate results.
    Eg, create an indexed temp table containing the results of running
       SELECT ACCT
        FROM dbo.TABLEB
        WHERE
           FSCD IN ('BIQ', 'BCU')

    and then you can modify your subsequent query to use the temp table.

    That's what we ended up doing to resolve it. Using a Table Variable in place of the query and it is more than happy with that. But this behavior difference is a mystery.

    I suggest that you try using temp tables rather than table variables. They often perform better and rarely perform worse.

    I agree when the table reaches a certain point. But the data is not significant in this case, nor will it ever be.

Viewing 6 posts - 16 through 20 (of 20 total)

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