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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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 21 (of 21 total)

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