September 23, 2018 at 10:28 am
Hi,
In a DataMart analysis for a CRM system, about 10 Dim tables are joined with FactTable to see if the account is also in the table (inner join). This takes a long time (even with an index on KeyCol) for large amounts of data (1 million rows per DIM). If it were not better, I would prepare a table with 10 columns to look up in this table by lookup or join, if the account exists.
Is the idea good - or are there better solutions?
Thanks
Regards
Nicole 🙂
September 23, 2018 at 10:48 am
info 58414 - Sunday, September 23, 2018 10:28 AMHi,
In a DataMart analysis for a CRM system, about 10 Dim tables are joined with FactTable to see if the account is also in the table (inner join). This takes a long time (even with an index on KeyCol) for large amounts of data (1 million rows per DIM). If it were not better, I would prepare a table with 10 columns to look up in this table by lookup or join, if the account exists.
Is the idea good - or are there better solutions?
Thanks
Regards
Nicole 🙂
We don't have enough information to help you. I'll also say that a million rows per DIM shouldn't be a problem... the problem will be in the code that uses them.
My recommendation would be to see the 2nd link in my signature line below under "Helpful Links" and post what is requested along with an adequate description of what the code is supposed to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2018 at 1:00 pm
Hi, here is the Execution-Plan.
Thanks
September 23, 2018 at 1:03 pm
Hi, here is the Execution-Plan.
Thanks
Jeff Moden - Sunday, September 23, 2018 10:48 AMinfo 58414 - Sunday, September 23, 2018 10:28 AMHi,
In a DataMart analysis for a CRM system, about 10 Dim tables are joined with FactTable to see if the account is also in the table (inner join). This takes a long time (even with an index on KeyCol) for large amounts of data (1 million rows per DIM). If it were not better, I would prepare a table with 10 columns to look up in this table by lookup or join, if the account exists.
Is the idea good - or are there better solutions?
Thanks
Regards
Nicole 🙂We don't have enough information to help you. I'll also say that a million rows per DIM shouldn't be a problem... the problem will be in the code that uses them.
My recommendation would be to see the 2nd link in my signature line below under "Helpful Links" and post what is requested along with an adequate description of what the code is supposed to do.
Hi, the Queyplan is online ...thanks
September 24, 2018 at 3:33 pm
info 58414 - Sunday, September 23, 2018 1:03 PMHi, here is the Execution-Plan.
ThanksJeff Moden - Sunday, September 23, 2018 10:48 AMinfo 58414 - Sunday, September 23, 2018 10:28 AMHi,
In a DataMart analysis for a CRM system, about 10 Dim tables are joined with FactTable to see if the account is also in the table (inner join). This takes a long time (even with an index on KeyCol) for large amounts of data (1 million rows per DIM). If it were not better, I would prepare a table with 10 columns to look up in this table by lookup or join, if the account exists.
Is the idea good - or are there better solutions?
Thanks
Regards
Nicole 🙂We don't have enough information to help you. I'll also say that a million rows per DIM shouldn't be a problem... the problem will be in the code that uses them.
My recommendation would be to see the 2nd link in my signature line below under "Helpful Links" and post what is requested along with an adequate description of what the code is supposed to do.
Hi, the Queyplan is online ...thanks
You'll need to explain the WHY behind the JOINs to the 10 DIM tables. An execution plan is certainly helpful, but it can't give us the WHY, which is often critical to providing useful information.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply