Hi Everyone,

just wanted to give an update on the approach we ended up going with:

Further analysis of write behavior of different tables showed that our largest and most resource consuming...

Yes, if you return 80-100% of the table every time, columnstore would likely be better.  That makes it seem very likely that Power BI is likely reading (almost) all...

They are exlusively Nonclustered Columnstores.

Hi ScottPletcher, I have not seriously considered page...

Hi ScottPletcher, I have not seriously considered page compression instead of (N)CCI's as not using any columnstores would require us creating B-Tree indexes to keep query performance sane. In our...

• This reply was modified 3 years, 4 months ago by  MadOwl.

I initially figured the nonclustered index would help with the updates/deletes - it didn't. I also just ran a test without the nonclustered index with only a minor improvement.

Yeah, Niko...

• This reply was modified 3 years, 4 months ago by  MadOwl.

It seems the issue ended up being unixODBC version 2.3.1. Upgrading to 2.3.7 resolved the problem for me.

Issue is somewhat resolved. There seems to be bug in how pyodbc handles duplicate primary keys on tables with the IGNORE_DUP_KEY = ON option.
Github issue here: https://github.com/mkleehammer/pyodbc/issues/476