Hi there,
I have a model where one of the tables has 36 million rows (data since 2009). On any given report page my users will only select 5 years at a time.
Question: Considering the above (that is, we never display more than 5 years of data on a page), if I were to reduce the number of rows in my table by reducing the date range to start from 2014… would there be a performance improvement? Is there any benefit of doing this?
Short answer … yes. You should always strive to only have the minimum data in a report, by reducing the rows and columns as much as possible; you’ll have set the stage fir the best possible performance.
Greg
hi @michellepace,
I would consider only go back 5 years in information. The performance of the file would be better.
Keith
@michellepace It depends on the number of unique values each column has, if a column has only TRUE/FALSE then it doesn’t matter if you import a million rows or a billion rows.
Here is an example with 1 million TRUE/FALSE and 3 Million TRUE/FALSE
1 Million rows, file size 13.4 KB:
3 Million rows, file size 14 KB:
At end everything depends on the size of the dictionary created by the engine. if there are high number of unique values then the dictionary size would be large, but booleans are of only 2 type therefore dictionary has only 2 values.
If I import 1 million random unique values, then engine is smart enough to know that dictionary encoding is not useful therefore it stores the data as it is, that’s why the dictionary size is 120 and Encoding type is VALUE
File size 13.4 MB
Hi @michellepace, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Hi @michellepace, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Thank you, gentlemen. I am going to follow Keith’s and Greg’s advise irrespective of table size. And thank you, Antriksh, I completely forgot about that tool. I’m going to look for a little introduction on using it. I think I remember seeing it on SQLBi.
