topic Re: 1 table or many tables in QlikView

1 table or many tables

cbushey1 — Sat, 16 Nov 2024 04:41:41 GMT

Hi all,

I wanted to get opinions and best practices around combining tables. Specifically, I have 8 tables currently in my data model that all share the same key field. Each of these tables represents facts about the key field so I was thinking about combining them all into a single table. Doing this would mean instead of say 20M rows in each of the 8 tables, I would have 20M rows in a single table, but that table would have around 150 fields. The key field is only listed once per table in each of the 8 tables so I am not worried about inflating rows.

Is there a point at which it makes more sense to keep these as separate tables?

Let me know what you think.

Re: 1 table or many tables

juraj_misina — Tue, 22 Jan 2019 16:46:48 GMT

Hi,
I remember seeing a recommendation to keep your tables rather narrow, so at first thought I'd go with separate tables in your case. But the answer is, of course, a bit more complex and depends on what else do you want to do with that data. Are there any other common dimensions? Maybe a date field in few of those tables for which you'd might like to create a common filter (so that end users are not overwhelmed with Order Year, Order Month, Order Date, Invoice Year, Invoice Month...). If yes, you should consider combining the tables , possibly - but not necessarily - into one.
Hope this helps.
Juraj

Re: 1 table or many tables

dplr-rn — Tue, 22 Jan 2019 16:53:26 GMT

theoretically single table would be best for performance but take more disk space. as juraj said it depends on scenario.
but if there is no other dimension tables which increases hops between tables when filtering i dont believe it will not make too much difference

Re: 1 table or many tables

cbushey1 — Tue, 22 Jan 2019 16:57:14 GMT

Juraj,

Thanks for the reply. I dont have any dates in these tables. Each table represents an algorithm that creates risk factors for that are then used to come up with a risk score for each key.

While I am creating a score, each risk factor is still needed in the final application and could be layered together to look at different groups of people.

I am thinking that I want to combine all these tables because my data model is already getting large with many tables and I am trying to increase the performance of the application as a whole.

Re: 1 table or many tables

juraj_misina — Tue, 22 Jan 2019 17:02:41 GMT

If complexity of data model is becoming an issue than do yourself a favour and combine the tables 😉

Re: 1 table or many tables

cbushey1 — Tue, 22 Jan 2019 17:27:55 GMT

It seems this might have been the answer I was looking for:

https://community.qlik.com/t5/Qlik-Design-Blog/To-Join-or-not-to-Join/ba-p/1463102

I guess I will need to evaluate if I need to combine them.

Thanks for all that responded.

Re: 1 table or many tables

swallace104 — Tue, 22 Jan 2019 18:06:06 GMT

So the memory engine stores a table by creating a unique list of values for a field and an index with pointers to those values. So the more unique values you have the more memory footprint your taking up, the more tables with more unique values in your model the more memory you're taking up.

I would consider a couple of things. One are all the fields actually necessary? If not, drop those that aren't from your applications. Is the unique key actually necessary, because it's unique it will inflate the size of the index and pointers so if you don't actually need it then combine the tables and drop that unique key after you combine them. While it may be a pretty wide table you may see a reduction in memory footprint because of the deduping. Basically anywhere you can alleviate unique values do so and you'll generally see your overall memory footprint reduce. If you have timestamps and don't need timestamps definitely drop those. If your date fields have timestamps in them, change it to only the date. If you still need timestamps break them out into an hour, minute, second field so that they have very low cardinality. Addresses, chop them up into their elements so that they have lower cardinality. Do the same with phone numbers.

The caveat here is that you might need seperate tables for certain measure calculations to work properly without tons of set analysis. You have to weight the tradeoff in set analysis performance vs. memory consumption.

Re: 1 table or many tables

cbushey1 — Tue, 22 Jan 2019 19:00:29 GMT

Thanks for the reply.

Most of what you spelled out I am already aware of and while I get where you are coming from to increase performance, those scenarios don't really apply here.

In its simplest form, I have 8 tables that each have a different number of fields. Most of the values in those fields are either Y/N or 1/0. Others are amounts or numbers. The key is required because it joins out to all my other tables that are needed in the application as it doesn't just join these 8 tables together.

My original thinking here was that if I combined these 8 tables it would make the data model simpler, and increase performance of the application as it would potentially reduce the number of hops between tables for some calculations.

My concern is that it won't be easy to put together all 8 tables especially those that have 10+ fields when I have rows into the many millions for data. It is possible that I might be able to get rid of some of these fields but the majority of them are used in the final application so they would be required in the final table.

Re: 1 table or many tables

dplr-rn — Tue, 22 Jan 2019 19:02:42 GMT

So your data model contains only 8 tables?
is it possible to share a picture?

Re: 1 table or many tables

swallace104 — Tue, 22 Jan 2019 19:13:24 GMT

Considering what you just mentioned about your data, I don't think combining them would add any significant performance impact vs. not having them combined. So if it simplifies things from a maintenance perspective you are likely better off just combining them. I would try it and verify there is no significant sign of performance degradation and move on.

Re: 1 table or many tables

cbushey1 — Tue, 22 Jan 2019 19:37:26 GMT

No there is definitely more than 8 tables, however I am looking to consolidate these 8 into 1.
Unfortunately I can't share a picture.

Re: 1 table or many tables

dplr-rn — Tue, 22 Jan 2019 19:40:49 GMT

Ok. if there are multiple hops (dimensions for each of these tables) involved i would definitely recommend consolidating these 8 into 1 fact table.

Re: 1 table or many tables

cbushey1 — Thu, 24 Jan 2019 15:41:16 GMT

I am concerned that doing a join on these tables will certainly cause bottlenecks in the reload. In the past, when I have done joins on large tables (10M rows) it would take around an hour. Certainly this was before trying to order the tables but I am still concerned that it might take more time than I want to sacrifice just to get all 8 tables into 1. If a table has 5 fields then I dont mind doing the Map/ApplyMap route.

Any suggestions for combing the large tables?

Re: 1 table or many tables

dplr-rn — Thu, 24 Jan 2019 15:54:59 GMT

You are right about the bottle neck piece. The question is to find the best fit for performance for end user and reload. Unfortunately will be a bit of trial and error for you. do yo have end user performance issue currently. if not i would probably leave it alone for now.

i have had apps with 100s of millions of rows and to optimze end user performance we created a singular fact table. reload take 3-4 hrs.
on the other hand i have seen at clients apps will 100s of millions of rows a lot of synthetic keys , but still without performance issue because the server is strong

Re: 1 table or many tables

dplr-rn — Thu, 24 Jan 2019 16:20:20 GMT

Missed to add if the app is still in development; i Would run benchmarks using scalability tool to asses response times