July 11, 2018 at 4:35 am
Hello guys, I currently have a query comprising of joins across multiple tables to process data for 10 years and load it in single table. This process takes around 3-4 hours.
All I want to do is (a) break down the processing year wise (in parallel) into multiple temp tables and (b) finally load then all in one go to Target table.
How should I achieve the b part ? Can the Target table be a partitioned table so that I can just add those tables to Target table as partition ? This will always be a trunate load process.
July 11, 2018 at 10:59 am
I'd be curious to see if each of the 10 separate years of data could be constructed as inserts in SQL tasks inside a Data Flow within SSIS, and to a partitioned target table, to see if that would be fastest...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2018 at 1:55 pm
I want to do it using t-sql and not SSIS. trying to do it using partition switch from 10 non partitioned tables to one partitioned table. Getting some roadblocks. Does anyone has any example ?
July 11, 2018 at 3:28 pm
sqlenthu 89358 - Wednesday, July 11, 2018 1:55 PMI want to do it using t-sql and not SSIS. trying to do it using partition switch from 10 non partitioned tables to one partitioned table. Getting some roadblocks. Does anyone has any example ?
I don't have examples of that, but I was curious about setting up an SSIS package is because all 10 inserts could be running concurrently, which might have the potential to speed it up quite a bit, especially for a partitioned target table. Plus, the tasks can be tuned with buffer size and thread count.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2018 at 4:29 pm
have a look at
https://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/
and http://www.dbdelta.com/table-partitioning-best-practices/
for some examples and explanations.
this may also be of help on the partition aspect of it.
July 12, 2018 at 4:33 am
I am getting following error while switching partition:
Msg 4972, Level 16, State 1, Line 2
ALTER TAVLT SWITCH statement failed. Check constraints or partition function of source table 'source_table_name' allows values that are not allowed by check constraints or partition function on Target table 'target_table_name'.
I have following check constraint on one of the source table:
Alter table source_table_name add constraint constraint_year_2010 check (business_date >= '01-01-2010' and business_date < '01-01-2011')
Target table has following partition function:
Create partition function f_targettbl (datetime) as range right for values('01-01-2010', '01-01-2011', so on till '01-01-2018')
No of columns and datatypes are same. Partitioned columns are not null in both source and target tables. Source table contains data for only year 2010.
July 12, 2018 at 6:34 am
sqlenthu 89358 - Thursday, July 12, 2018 4:33 AMI am getting following error while switching partition:Msg 4972, Level 16, State 1, Line 2
ALTER TAVLT SWITCH statement failed. Check constraints or partition function of source table 'source_table_name' allows values that are not allowed by check constraints or partition function on Target table 'target_table_name'.I have following check constraint on one of the source table:
Alter table source_table_name add constraint constraint_year_2010 check (business_date >= '01-01-2010' and business_date < '01-01-2011')
Target table has following partition function:
Create partition function f_targettbl (datetime) as range right for values('01-01-2010', '01-01-2011', so on till '01-01-2018')No of columns and datatypes are same. Partitioned columns are not null in both source and target tables. Source table contains data for only year 2010.
Why would you want a table constraint restricting the data to just one year when you are looking to partition by year ? That makes no sense... You don't want that constraint on the source table.... After all, it has to BECOME a part of the target table, and the partition function has to run the show, so the constraint has to go.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 12, 2018 at 6:40 am
sgmunson - Thursday, July 12, 2018 6:34 AMsqlenthu 89358 - Thursday, July 12, 2018 4:33 AMI am getting following error while switching partition:Msg 4972, Level 16, State 1, Line 2
ALTER TAVLT SWITCH statement failed. Check constraints or partition function of source table 'source_table_name' allows values that are not allowed by check constraints or partition function on Target table 'target_table_name'.I have following check constraint on one of the source table:
Alter table source_table_name add constraint constraint_year_2010 check (business_date >= '01-01-2010' and business_date < '01-01-2011')
Target table has following partition function:
Create partition function f_targettbl (datetime) as range right for values('01-01-2010', '01-01-2011', so on till '01-01-2018')No of columns and datatypes are same. Partitioned columns are not null in both source and target tables. Source table contains data for only year 2010.
Why would you want a table constraint restricting the data to just one year when you are looking to partition by year ? That makes no sense... You don't want that constraint on the source table.... After all, it has to BECOME a part of the target table, and the partition function has to run the show, so the constraint has to go.
Steve, I gave example of just one source table with 2010 data. There will be multiple tables carrying particular yearsy data.
July 12, 2018 at 6:53 am
sqlenthu 89358 - Thursday, July 12, 2018 6:40 AMsgmunson - Thursday, July 12, 2018 6:34 AMsqlenthu 89358 - Thursday, July 12, 2018 4:33 AMI am getting following error while switching partition:Msg 4972, Level 16, State 1, Line 2
ALTER TAVLT SWITCH statement failed. Check constraints or partition function of source table 'source_table_name' allows values that are not allowed by check constraints or partition function on Target table 'target_table_name'.I have following check constraint on one of the source table:
Alter table source_table_name add constraint constraint_year_2010 check (business_date >= '01-01-2010' and business_date < '01-01-2011')
Target table has following partition function:
Create partition function f_targettbl (datetime) as range right for values('01-01-2010', '01-01-2011', so on till '01-01-2018')No of columns and datatypes are same. Partitioned columns are not null in both source and target tables. Source table contains data for only year 2010.
Why would you want a table constraint restricting the data to just one year when you are looking to partition by year ? That makes no sense... You don't want that constraint on the source table.... After all, it has to BECOME a part of the target table, and the partition function has to run the show, so the constraint has to go.
Steve, I gave example of just one source table with 2010 data. There will be multiple tables carrying particular yearsy data.
And any similar constraints on those other source tables will have to be removed as well. Remember, these tables are about to become partitions in an entirely different table, and will have to play by a different set of rules. You can't have a table constraint hanging around that doesn't apply to the entire table, and that's what you are asking SQL Server to do by leaving that constraint in place. Just think about whether or not SQL Server would allow you to restrict the entire table to only 2010 data, and then further restrict the entire table to only 2011 data, and so on.... you'd never have any data that could meet even 2 of those constraints simultaneously. That's the role of the partition function, so table constraints of that nature can't coexist.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 12, 2018 at 7:07 am
sgmunson - Thursday, July 12, 2018 6:53 AMsqlenthu 89358 - Thursday, July 12, 2018 6:40 AMsgmunson - Thursday, July 12, 2018 6:34 AMsqlenthu 89358 - Thursday, July 12, 2018 4:33 AMI am getting following error while switching partition:Msg 4972, Level 16, State 1, Line 2
ALTER TAVLT SWITCH statement failed. Check constraints or partition function of source table 'source_table_name' allows values that are not allowed by check constraints or partition function on Target table 'target_table_name'.I have following check constraint on one of the source table:
Alter table source_table_name add constraint constraint_year_2010 check (business_date >= '01-01-2010' and business_date < '01-01-2011')
Target table has following partition function:
Create partition function f_targettbl (datetime) as range right for values('01-01-2010', '01-01-2011', so on till '01-01-2018')No of columns and datatypes are same. Partitioned columns are not null in both source and target tables. Source table contains data for only year 2010.
Why would you want a table constraint restricting the data to just one year when you are looking to partition by year ? That makes no sense... You don't want that constraint on the source table.... After all, it has to BECOME a part of the target table, and the partition function has to run the show, so the constraint has to go.
Steve, I gave example of just one source table with 2010 data. There will be multiple tables carrying particular yearsy data.
And any similar constraints on those other source tables will have to be removed as well. Remember, these tables are about to become partitions in an entirely different table, and will have to play by a different set of rules. You can't have a table constraint hanging around that doesn't apply to the entire table, and that's what you are asking SQL Server to do by leaving that constraint in place. Just think about whether or not SQL Server would allow you to restrict the entire table to only 2010 data, and then further restrict the entire table to only 2011 data, and so on.... you'd never have any data that could meet even 2 of those constraints simultaneously. That's the role of the partition function, so table constraints of that nature can't coexist.
But as per my understanding for partition switching, there should be same validation constraint on both source and target tables. In this example, partition function will be taking care of data boundary while source table will need check constraint to implement the same.
July 12, 2018 at 7:21 am
sqlenthu 89358 - Thursday, July 12, 2018 7:07 AMsgmunson - Thursday, July 12, 2018 6:53 AMsqlenthu 89358 - Thursday, July 12, 2018 6:40 AMsgmunson - Thursday, July 12, 2018 6:34 AMsqlenthu 89358 - Thursday, July 12, 2018 4:33 AMI am getting following error while switching partition:Msg 4972, Level 16, State 1, Line 2
ALTER TAVLT SWITCH statement failed. Check constraints or partition function of source table 'source_table_name' allows values that are not allowed by check constraints or partition function on Target table 'target_table_name'.I have following check constraint on one of the source table:
Alter table source_table_name add constraint constraint_year_2010 check (business_date >= '01-01-2010' and business_date < '01-01-2011')
Target table has following partition function:
Create partition function f_targettbl (datetime) as range right for values('01-01-2010', '01-01-2011', so on till '01-01-2018')No of columns and datatypes are same. Partitioned columns are not null in both source and target tables. Source table contains data for only year 2010.
Why would you want a table constraint restricting the data to just one year when you are looking to partition by year ? That makes no sense... You don't want that constraint on the source table.... After all, it has to BECOME a part of the target table, and the partition function has to run the show, so the constraint has to go.
Steve, I gave example of just one source table with 2010 data. There will be multiple tables carrying particular yearsy data.
And any similar constraints on those other source tables will have to be removed as well. Remember, these tables are about to become partitions in an entirely different table, and will have to play by a different set of rules. You can't have a table constraint hanging around that doesn't apply to the entire table, and that's what you are asking SQL Server to do by leaving that constraint in place. Just think about whether or not SQL Server would allow you to restrict the entire table to only 2010 data, and then further restrict the entire table to only 2011 data, and so on.... you'd never have any data that could meet even 2 of those constraints simultaneously. That's the role of the partition function, so table constraints of that nature can't coexist.
But as per my understanding for partition switching, there should be same validation constraint on both source and target tables. In this example, partition function will be taking care of data boundary while source table will need check constraint to implement the same.
Then I'll guess that your understanding is not quite correct. You can't have a table constraint that conflicts with the partition function. And you most certainly can't have table constraints that conflict with each other, which is what would happen if you could actually implement all of those separately constrained tables as the new partitions in the target table. With a partition function in place, why would you need any additional table constraints that do the same thing? I'll have to go look up the meaning of RANGE RIGHT to validate what I think I remember about it and see what it's exact meaning is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 12, 2018 at 7:36 am
Take a look here: https://www.sqlskills.com/blogs/kimberly/clarifying-left-and-right-in-the-defintion-of-a-partition-function-in-sql-server-2005/
The article is old, but the concepts have probably not changed much. If anyone knows otherwise, please correct me, as I'm not a partitioning expert. RANGE RIGHT does mean what I think it does, in that the boundary values you specify in your partition function are lower boundaries. I'm still sure that even if you could successfully maintain that existing table constraint, you'd have trouble again trying to bring in a 2nd partition that had a similar constraint that conflicts with the first at the table level. These are TABLE constraints, and don't suddenly become something else after partitioning goes into place.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 12, 2018 at 9:45 am
I got it. The issue was that the check constraint defined on source target tables was not defined with "with check" condition. Once done it worked fine.
July 12, 2018 at 9:46 am
sqlenthu 89358 - Thursday, July 12, 2018 9:45 AMI got it. The issue was that the check constraint defined on source target tables was not defined with "with check" condition. Once done it worked fine.
Thanks all for your valuable suggestions and discussion. Appreciate.
July 12, 2018 at 2:54 pm
sqlenthu 89358 - Thursday, July 12, 2018 9:45 AMI got it. The issue was that the check constraint defined on source target tables was not defined with "with check" condition. Once done it worked fine.
This is how I learn.. Most interesting....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply