July 8, 2015 at 4:58 pm
Query 1
---------
Business Key in Both Tables so join on Business Key
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT
DIM_MATERIAL.MATNR,
DIM_MATERIAL.MAKTX,
sum(F_BILLING.NETWR_1)
FROM
DIM_MATERIAL INNER JOIN F_BILLING ON (DIM_MATERIAL.MATNR=F_BILLING.MATNR)
GROUP BY
DIM_MATERIAL.MATNR,
DIM_MATERIAL.MAKTX
order by DIM_MATERIAL.MATNR
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 12 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 119 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 10 ms.
(21513 row(s) affected)
Table 'F_BILLING'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 14540, lob physical reads 1, lob read-ahead reads 7889.
Table 'DIM_MATERIAL'. Scan count 9, logical reads 2992, physical reads 2, read-ahead reads 2712, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 345 ms, elapsed time = 491 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Query 2
---------
Surrogate Key in Both Tables so join on Surrogate ID
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT A.MATNR, A.MAKTX, SUM(B.[NETWR_1])
FROM [dbo].[DIM2_MATERIAL] A INNER JOIN [dbo].[F_BILLING2] B ON A.MATNR_ID=B.MATNR_ID
GROUP BY A.MATNR, A.MAKTX
order by A.MATNR
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 15 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 113 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 8 ms.
(21514 row(s) affected)
Table 'F_BILLING2'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 8547, lob physical reads 3, lob read-ahead reads 4173.
Table 'DIM2_MATERIAL'. Scan count 9, logical reads 837, physical reads 3, read-ahead reads 764, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 174 ms, elapsed time = 28495 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Both Fact tables are Clustered Column Store.
July 8, 2015 at 7:30 pm
I am assuming that you aren't using SQL Server 2005 since you are mentioning Column Store Indexes.
July 9, 2015 at 5:58 am
2014 enterprise edition. Posted in wrong location.
July 10, 2015 at 2:46 am
These are two completely different queries:
SELECT PT.created_dttm Created_Datetime,
PT.proj_task_id Project_Task_Id,
CONVERT(VARCHAR(2000), text, 0) text
FROM MetrixDatamart_T.dbo.project_text PT
WHERE text_line_code = @1;
and
SELECT DISTINCT "project_text20"."created_dttm" AS "Created_Datetime",
"project_text20"."proj_task_id" AS "Project_Task_Id",
"project_text20"."text" AS "Text"
FROM
(
SELECT "project_text_id",
"created_dttm",
"created_id",
"proj_task_id",
"project_id",
"text_id",
"text_line_code",
"billing_status",
"internal_use_only",
CAST( "text" AS VARCHAR( 2000 ) ) AS "text"
FROM "MetrixDatamart_T"."dbo"."project_text"
WHERE "text_line_code" = 'RESO'
)
"project_text20"
Those are from the execution plans. One is parameterized and the other has hard coded values. you could be looking at issues around parameter sniffing. You could be looking at other issues related to using DISTINCT. I don't see GROUPING or ordering in either of these, so I'm not at all sure what your question is.
"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
July 10, 2015 at 6:16 am
You are looking at wrong sql plan. I guess you looked at another post and commented in this one b
July 10, 2015 at 6:44 am
Oops. yes. Another query1, query2, not your query1, query2. Sorry about that.
First most likely issue, the two different tables in the query, DIM_MATERIAL vs. DIM2_MATERIAL are showing different statistics right from the start. The cardinality for one is 82000 the other is 81999. That's a miniscule difference, but if that statistic is different, that might explain why the two plans are different. All the other estimates are a little off. The principal difference is in where the Compute Scalar operation is located. I'd suggest getting the statistics up to date as a starting point.
The second plan timed out during compile, so that might explain the difference too. Again, possibly caused by the differences in structures, possibly relating to statistics or something else around DIM2_MATERIAL.
"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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply