August 28, 2018 at 5:46 pm
I'm looking into cleaning up a Large DB where ID and Timestamp are the fields in question and relevance. Its a logging Database and data accumulated since 2015, there are about 440 million records between 2018 and current date, 390 million in 2017 and 1 Billion in 2016.
'I'm planning to create three sub tables for 2018, 2017, 2016, 2015 move the records for those years and later purge the main table...so that selects can be faster.
Right now, cannot run selects on the table, cant check IX's cant create any .. Any suggestions welcome.
Thank you.
August 28, 2018 at 6:04 pm
So, what is the end game for the original table?
August 29, 2018 at 10:37 am
If you have a version that supports table partitioning I would read up on that.
August 29, 2018 at 11:50 am
If you don't need them, purge them. If they're queried rarely, partitioning might help with management and remove some blocking/locking.
August 29, 2018 at 12:32 pm
sqlguy80 - Tuesday, August 28, 2018 5:46 PMI'm looking into cleaning up a Large DB where ID and Timestamp are the fields in question and relevance. Its a logging Database and data accumulated since 2015, there are about 440 million records between 2018 and current date, 390 million in 2017 and 1 Billion in 2016.
'I'm planning to create three sub tables for 2018, 2017, 2016, 2015 move the records for those years and later purge the main table...so that selects can be faster.
Right now, cannot run selects on the table, cant check IX's cant create any .. Any suggestions welcome.
Thank you.
I've had a similar problem (now solved) where there are two tables with an FK relationship, the parent table has 150 million rows the child table has a billion rows.
I take it you have a primary key on the ID?
Are there any FK's on the tables that you want to move data?
August 29, 2018 at 1:38 pm
Jonathan AC Roberts - Wednesday, August 29, 2018 12:32 PMI've had a similar problem (now solved) where there are two tables with an FK relationship, the parent table has 150 million rows the child table has a billion rows.
I take it you have a primary key on the ID?
Are there any FK's on the tables that you want to move data?
Can you give me the solution you used? Yes , id pkey in one table is the fkey in the other table.this key value pair table is queried by a support team but the query just times out....due to the millions of rows.
August 29, 2018 at 2:20 pm
And what is the end goal of what you are trying to accomplish? Is that really that difficult a question to answer?
August 29, 2018 at 3:45 pm
the orig table will be truncated , after we get all the data out which is needed to be queried for analysis ..and those are logs..
hard part is since its so large, we cant run simple selects , indexes etc... i was thinking of using time stamp and pulling records into a new table and deleting the fkey values..but all I see is blocking or temp db getting filled up or out of memory, no luck.
August 29, 2018 at 3:59 pm
sqlguy80 - Wednesday, August 29, 2018 3:45 PMthe orig table will be truncated , after we get all the data out which is needed to be queried for analysis ..and those are logs..
hard part is since its so large, we cant run simple selects , indexes etc... i was thinking of using time stamp and pulling records into a new table and deleting the fkey values..but all I see is blocking or temp db getting filled up or out of memory, no luck.
Could you post the DDL (CREATE TABLE statement) for the table plus some sample data (not production data) that represents the problem domain, say a 3 or 4 rows for each year.
You say the original table will be truncated, does this mean not used or are you going to move data back in.
August 29, 2018 at 5:25 pm
sqlguy80 - Wednesday, August 29, 2018 1:38 PMCan you give me the solution you used? Yes , id pkey in one table is the fkey in the other table.this key value pair table is queried by a support team but the query just times out....due to the millions of rows.
Can you provide the DDL for both tables? Also, all indexes on the tables. And how many rows are on each table.
I wrote two versions for my archiving, one that required an index to be added to the details table and one that needed no indexes (other than the PKs).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply