January 6, 2016 at 5:29 pm
I have two portioned tables, each partitioned by a different partition function/scheme. See examples below.
- Table MTable is small - it contains 10 partitions, each with about 15,000 records, for a total of 150,000 records.
- Table STable is larger - it contains 2,300 partitions, each with about 15,000 records, for a total of about 35 million records.
I have a function, FN_MyFunction, that given a date/time, it returns one record containing the partitions for the MTable and STable.
Local variable example:
I have a query set where I first put in local variables the results of the function FN_MyFunction, then query tables MTable and STable with an inner join using the local variables. A snippet of the example is below. This query takes about several seconds to run.
DECLARE @dt DATETIME2(0) = GETUTCDATE()
DECLARE @ps INT
DECLARE @pm INT
SELECT
@ps = PSID,
@pm = PMID
FROM FN_MyFunction ( @dt )
SELECT
[SID] = S.ID,
SName = S.Name,
MID = M.ID,
MName = M.Name
FROM
STable S INNER JOIN
MTable M ON
M.PartitionID = @pm AND
M.SIndexID = S.IndexID
WHERE
S.PartitionID = @ps
The performance of the query was unacceptable and I tried to store the function results in a temporary table, then join the temporary table with the MTable and STable. The performance improved by a factor of at least 10, but I have no idea why that would be the case.
In both cases, the execution plan was roughly the same, and it indicated it used the same indexes for the MTable and STable. The temporary table example is below:
DECLARE @dt DATETIME2(0) = GETUTCDATE()
CREATE TABLE #PTable
(
PSID INT,
PMID INT
)
INSERT #PTable ( PSID, PMID )
SELECT PSID, PMID
FROM FN_MyFunction ( @dt )
SELECT
[SID] = S.ID,
SName = S.Name,
MID = M.ID,
MName = M.Name
FROM
#PTable PT INNER JOIN
STable S ON
S.PartitionID = PT.PSID INNER JOIN
MTable M ON
M.PartitionID = PT.PMID AND
M.SIndexID = S.IndexID
We then replaced the temporary table with a table variable, and the performance was just as bad as with local variables. It seems to me that using local variables or table variables considerably degrades performance.
Is that a reason why, and is there something I should do to improve the performance of local and table variables? Any help is greatly appreciated.
January 6, 2016 at 11:06 pm
Hi,
could you give us your configuration of your tempDB? If you use only one file for the tempDB, it might by your bottleneck.
Kind regards,
Andreas
January 7, 2016 at 2:16 am
Won't be related to TempDB. If it was, the temp table one would have performed badly as well.
It probably comes down to row estimations. With local variables, the optimiser can't sniff the value and hence won't have an accurate estimation of the rows affected. Table variables don't have statistics, and hence the optimiser can't get an accurate estimation of the rows affected.
Try the local variable one and add the query hint OPTION(RECOMPILE), it should (emphasis *should*) end up as fast or faster than the temp table solution.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2016 at 8:06 am
Roughly the same execution plans really translates as, different execution plans. As Gail says, look to the row estimates to understand where the differences are coming from.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply