1

I keep hearing Sybase IQ works very badly with joins and in my current job main data model (Sybase IQ DB) is View-based with huge denormalized table underneath and still lots of hardcoded logic and number of joins and subqueries (going to 5 levels deep) - all within one huge view.

Am I mistaken, or am I right thinking that classic star schema would still perform better, as number of joins in any query would be limited (instead of computing whole crazy view logic with each query)? I did not have much experience outside Oracle Exadata, but from what I know columnar DBs like Sybase IQ still work fine with joins.

Anyone can advise on good practice around it?

marcinsow
  • 11
  • 2
  • If using IQ 15 maybe following performance rules can help: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00170.1540/doc/html/san1288042608221.html – João Feb 20 '22 at 11:08

1 Answers1

0

I think you are talking about IQ15.x -- An old version, while the latest is IQ16.1. In my memory, I really got some join performance issue on iq15.2, iq15.4 -- eg.-- create a view as "union all resulset from 3 tables" and then join this view with another small table to filter. It will execute the "union all" first-- usually generate a huge worktable and then join this worktable to that small table. But with newer version -- eg. IQ16.0 or IQ16.1 -- it will push down the small table to that 3 tables to join first then union the 3 small resultset to return the final resultset. It really improved a lot. Please have a try.

EisenWang
  • 189
  • 1
  • 10