Load large fact table

Question

Load large fact table

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Rekonn SSChasing Mays Points: 619 More actions · Answer 1

I suspect this join condition is the problem. It has to perform the casting and + 1, and *100 on every single record in the sas_output table. The dimdate table is much smaller right? Probably better to rewrite the condition to perform all the conversions and such on B.IdDate instead of on A.Mois. Also, look up the word Sargeable.

B.IdDate = 100 * cast (A.Mois as int) + 1

Lidou123 Hall of Fame Points: 3186 More actions · Answer 2

Rekonn - Tuesday, February 12, 2019 9:44 PM
I suspect this join condition is the problem. It has to perform the casting and + 1, and *100 on every single record in the sas_output table. The dimdate table is much smaller right? Probably better to rewrite the condition to perform all the conversions and such on B.IdDate instead of on A.Mois. Also, look up the word Sargeable.
B.IdDate = 100 * cast (A.Mois as int) + 1

Thank you Rekonn.

I make the changes and I'll come back and tell you if it works.
Someone has an other suggestion?

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 3

Lidou123 - Wednesday, February 13, 2019 4:03 AM
Rekonn - Tuesday, February 12, 2019 9:44 PM
I suspect this join condition is the problem. It has to perform the casting and + 1, and *100 on every single record in the sas_output table. The dimdate table is much smaller right? Probably better to rewrite the condition to perform all the conversions and such on B.IdDate instead of on A.Mois. Also, look up the word Sargeable.
B.IdDate = 100 * cast (A.Mois as int) + 1
Thank you Rekonn.
I make the changes and I'll come back and tell you if it works.
Someone has an other suggestion?

You could create a temporary table from dwh.DimDate:
SELECT CAST((IdDate-1)/100 AS SameTypeOfColumnAsSasOutputMois) AS Mois, IdDate INTO #DimDate FROM [Dwh].[DimDate] ;

ALTER TABLE #DimDate ADD CONSTRAINT PK_#DimDate PRIMARY KEY CLUSTERED (Mois, IdDate)
Then in your join you'd just change it to this:LEFT JOIN #DimDate B ON B.Mois = A.Mois
It should be slightly faster.

salliven SSCrazy Points: 2644 More actions · Answer 4

And the best way if you show us the execution plan of this query.

Rekonn SSChasing Mays Points: 619 More actions · Answer 5

Lidou123 - Wednesday, February 13, 2019 4:03 AM
Rekonn - Tuesday, February 12, 2019 9:44 PM
I suspect this join condition is the problem. It has to perform the casting and + 1, and *100 on every single record in the sas_output table. The dimdate table is much smaller right? Probably better to rewrite the condition to perform all the conversions and such on B.IdDate instead of on A.Mois. Also, look up the word Sargeable.
B.IdDate = 100 * cast (A.Mois as int) + 1
Thank you Rekonn.
I make the changes and I'll come back and tell you if it works.
Someone has an other suggestion?

I'm curious, did that make a difference?

Lidou123 Hall of Fame Points: 3186 More actions · Answer 6

Rekonn - Thursday, February 14, 2019 10:40 PM
Lidou123 - Wednesday, February 13, 2019 4:03 AM
Rekonn - Tuesday, February 12, 2019 9:44 PM
I suspect this join condition is the problem. It has to perform the casting and + 1, and *100 on every single record in the sas_output table. The dimdate table is much smaller right? Probably better to rewrite the condition to perform all the conversions and such on B.IdDate instead of on A.Mois. Also, look up the word Sargeable.
B.IdDate = 100 * cast (A.Mois as int) + 1
Thank you Rekonn.
I make the changes and I'll come back and tell you if it works.
Someone has an other suggestion?
I'm curious, did that make a difference?

Hi
I corrected some things:
- I simplified the join with DimDate. (B.IdDate = A.IdDate)
- I also realized that I had duplicates in DimResponse, which multiplied my lines out.
By correcting these 2 points, I was able to populate my Fact table.

Thank you so much.