Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to handle deleted data of a website Expand / Collapse
Author
Message
Posted Saturday, November 16, 2013 3:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 26, 2013 9:02 PM
Points: 99, Visits: 175
Hi,
Recently, I have started work on a database of a online product based website. We add thousands of products every month and after a specified time, we remove some products. For this purpose, we don't delete this product from database, we just change its flag from 1 to 0. Because we need these deleted products for reports, we don't delete from database. We just change the flag. And this process applies on each section (e.g. category, product etc).

Now problem is, now we have millions of these deleted records in several tables with several dependencies. So, each query contain a extra condition to check the flag for each table.

As database is growing, I am afraid because of this approach. I am not sure that this is correct approach to handle deleted data. I know, all websites use this type of functionality but how they manage their deleted (removed) data?
Can someone suggest me proper approach to handle this type of process?

thanks,
Anuj
Post #1514900
Posted Saturday, November 16, 2013 5:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 6,931, Visits: 12,653
One option would be to move the data into separate tables and performa a UNION ALL query für your reports.
You also might want to look into indexed filtered views if such a concept would be an option for you.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1514902
Posted Saturday, November 16, 2013 6:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 26, 2013 9:02 PM
Points: 99, Visits: 175
thanks for your reply.
Every social networking site face this situation. E.g. : when we delete any photo or or when we delete our profile etc.
They doesn't delete data physically. I am not sure, they also use this type of flag condition or they move data. Because this is an OLTP, so we have to minimize where clause. And this flag is essential condition.
Apart from this flag process, Is there any other way/approach to achieve this goal?
Post #1514906
Posted Saturday, November 16, 2013 6:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 6,931, Visits: 12,653
Apart from this flag process, Is there any other way/approach to achieve this goal?


See my previous post:
...to move the data into separate tables and performa a UNION ALL query für your reports...


Did you even bother to read it?




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1514907
Posted Saturday, November 16, 2013 10:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
What I might suggest is similar to Lutz.

Say you have this information in the Profile table. You can create an archive table called ProfileArchive. Copy all data here.

Next, you rename the existing table to ProfileCurrent. Obviously this during a maintenance time as you'll break the app. Now, you can create a view called Profile that does the union Lutz mentioned. Periodically you can move the data from Current to Archive as appropriate. You could also create a filtered index looking for active status' only.

That should minimize code changes.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1514933
Posted Saturday, November 16, 2013 9:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 26, 2013 9:02 PM
Points: 99, Visits: 175
Lutz, Steve,

thanks for your valuable suggestions.
I think, union all will be a solution for me.

thanks again.
Post #1514951
Posted Sunday, November 17, 2013 4:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 147, Visits: 580
we normally archive data either through table partition or we make use of filtered index.



Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com
Post #1514972
Posted Sunday, November 17, 2013 5:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 26, 2013 9:02 PM
Points: 99, Visits: 175
thanks praveen,

Do you archive data by using this type of flag column?
Post #1514974
Posted Sunday, November 17, 2013 7:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 147, Visits: 580
if you want to archive data by horizontally partitioning the table then you have to go for table partition, but for table partition you need to have good candidate key to partition like date column or id column also partitioned file group need to be placed on separate disk for better performance.
in your case i suppose filtered index is one solution, as mentioned by other experts other solution would be to move all deleted data to new table, this option would give you better performance if you place this table on different disk.




Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com
Post #1514982
Posted Sunday, November 17, 2013 7:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 26, 2013 9:02 PM
Points: 99, Visits: 175
thanks praveen !!
Post #1514983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse