July 25, 2017 at 8:41 am
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!
July 25, 2017 at 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 25, 2017 at 9:34 am
Lynn Pettis - Tuesday, July 25, 2017 8:38 AMChange 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.
July 25, 2017 at 9:36 am
Phil Parkin - Tuesday, July 25, 2017 8:49 AMAnother 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 runningSELECT 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.
July 25, 2017 at 10:56 am
Antares686 - Tuesday, July 25, 2017 9:36 AMPhil Parkin - Tuesday, July 25, 2017 8:49 AMAnother 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 runningSELECT 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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 25, 2017 at 11:19 am
Phil Parkin - Tuesday, July 25, 2017 10:56 AMAntares686 - Tuesday, July 25, 2017 9:36 AMPhil Parkin - Tuesday, July 25, 2017 8:49 AMAnother 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 runningSELECT 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