Performance impact: Cartesian product comes to the rescue

re: Performance impact: Cartesian product comes to the rescue

GrumpyOldDBA — Thu, 15 Dec 2011 13:11:10 GMT

Very interesting, when tuning I often I use temp tables to replace parts of joins to achieve similar performance improvements - this is something I'd not considered and I will certainly look into it. Just to explain although I'm not working on a DW, many queries I attempt to tune contain over 16 joins, many of these are "business rules" , small tables joining against much larger tables - your approach may well work wonders - cool!

re: Performance impact: Cartesian product comes to the rescue

Whipper Snapper — Thu, 15 Dec 2011 13:51:42 GMT

Minor complaint. Your chart doesn't compare the total elapsed times, which should be 740 versus 249 (248+1) to include populating the temp table.

Cool concept though and brings into question when a star-schema is appropriate or not. It could be interesting to see where the star-schema makes more sense with lots of WHERE criteria on dimension fields, which should be appropriately indexed.

re: Performance impact: Cartesian product comes to the rescue

Wiseman82 — Thu, 15 Dec 2011 16:35:32 GMT

Interesting idea. :-) The point of the article was to demonstrate the use of a cartesian product, but my instinct from a performance point of view would be to aggregate the data in the fact table first then join to the dimension tables (assuming I'm not allowed to make any schema modifications).

This approach seems to work better for me on a small scale test (I didn't have the patience to load the full data set and I was running it on a slow client PC):

select server_config,

thread_name,

tran_type,

tran_status,

users,

cpus,

COUNT(*) as cnt

INTO #T

from tran_stats t1

group by server_config,

thread_name,

tran_type,

tran_status,

users,

cpus

SELECT id_server_config,

id_thread_name,

id_tran_type,

id_tran_status,

id_users,

id_cpus,

SUM(cnt) as cnt

FROM #T T1

join Dim_server_config t2 on t1.server_config = t2.server_config

join Dim_thread_name t3 on t1.thread_name = t3.thread_name

join Dim_tran_type t4 on t1.tran_type = t4.tran_type

join Dim_tran_status t5 on t1.tran_status = t5.tran_status

join Dim_users t6 on t1.users = t6.users

join Dim_cpus t7 on t1.cpus = t7.cpus

GROUP BY id_server_config,

id_thread_name,

id_tran_type,

id_tran_status,

id_users,

id_cpus

drop table #t

Cartesian products can be useful. They are usually a problem when they are unintentional...

re: Performance impact: Cartesian product comes to the rescue

Wiseman82 — Thu, 15 Dec 2011 16:37:40 GMT

This approach seems to work better for me on a small scale test (I didn't have the patience to load the full data set and I was running it on a slow client PC):

select server_config,

thread_name,

tran_type,

tran_status,

users,

cpus,

COUNT(*) as cnt

INTO #T

from tran_stats t1

group by server_config,

thread_name,

tran_type,

tran_status,

users,

cpus

SELECT id_server_config,

id_thread_name,

id_tran_type,

id_tran_status,

id_users,

id_cpus,

SUM(cnt) as cnt

FROM #T T1

join Dim_server_config t2 on t1.server_config = t2.server_config

join Dim_thread_name t3 on t1.thread_name = t3.thread_name

join Dim_tran_type t4 on t1.tran_type = t4.tran_type

join Dim_tran_status t5 on t1.tran_status = t5.tran_status

join Dim_users t6 on t1.users = t6.users

join Dim_cpus t7 on t1.cpus = t7.cpus

GROUP BY id_server_config,

id_thread_name,

id_tran_type,

id_tran_status,

id_users,

id_cpus

drop table #t

Cartesian products can be useful. They are usually a problem when they are unintentional...

re: Performance impact: Cartesian product comes to the rescue

Jason Kohlhoff — Thu, 15 Dec 2011 19:50:53 GMT

Linchi,

I've looked at your create table script. None of your tables have Clustered Indexes (PKs), and your tran_stats table does not have any Nonclustered Indexes on its foreign key columns.

I agree that a Cartesian product is actually useful (i.e. the "Report Writer's Magic Formula"), but all that you've successfully proven here is that heap (non-indexed) tables are a lot slower than indexed tables because of all the resulting tables scans and hash matches.

re: Performance impact: Cartesian product comes to the rescue

Linchi Shea — Thu, 15 Dec 2011 20:40:29 GMT

Hi Jason Kohlhoff;

The large table has a clustered index. The following statement is in one of the scripts:

create clustered index cix_tran_stats on tran_stats(tran_begin)

Correct, none of the dimension columns has an index. Not that the selectivities on these columns are extremely low for any index to be useful. The real world scenario that triggered this sample query is a very similar query that replaces the dimension values into their id's in the large table.

BTW, I actually tested with the large table being a heap, and that did not change the observation.

Performance impact: parallelism comes to the rescue

Linchi Shea — Thu, 15 Dec 2011 22:14:39 GMT

For the specific scenario discussed in my previous post ,  we observed that using an intermediate

Performance impact: diminishing marginal return on the degree of parallelism

Linchi Shea — Fri, 16 Dec 2011 17:24:40 GMT

In commenting on my previous post , Greg Linwood and GrumpyOldDBA raised questions about various implications

re: Performance impact: Cartesian product comes to the rescue

Alain Krikilion — Wed, 21 Dec 2011 08:02:45 GMT

Really interesting post.

But I have some questions.

1) Can creating a clustered index on the temptable speed the query up even more? I can't test it because I don't have the necessary hardware for it.

2) Can using a table variable instead of a temptable speed up the query. (I guess that, as always, "it depends" on the size of the resulting table).

3) Can using a CTE instead of a temptable speed up the query.

Performance impact: hyperthreading for reporting queries

Linchi Shea — Thu, 05 Jan 2012 05:36:51 GMT

There are a lot of questions on hyperthreading, but not a lot of answers.  There is no shortage