Need an opinion of a pro SQL Dev

[DeathFox]

My current project is maintaining/upgrading an e-commerce site with a very huge number of items. Currently the items in their database are currently around 2,165,001 million records with around 100 records added everyday. And around 1,096,394 are rejected irrelevant (dili masearch sa customers) and along with 33,728,311 tags/keywords attached to those rejected records.

Now my question is will purging them and moving them to another table would make it a lot more efficient? I know the answer is yes, but how MUCH more effecient will it be if I do this. Thanks

[MarkCuering]

What is your SQL Server? It happens to me before on a mailserver, messages no longer appear on the console, and various datas on our generated reports are no longer shown, we figured out and that cause was we are about to hit the 4GB size limit.

But since you didn't mentioned specific server and size, and concluded that customers are not able to search this 1,096,394 items. Do you have any access on how they implement the searching method? on how they used up these tags and keywords.

Have you checked already how hey configure the SQL Agent? This gives notifications and various automations, the tasks also can be manage strategically. you mentioned about Purging, this can be another solution, but think which items should be purge first? you better start working on the history the oldest date you can find. I don't find efficient of you move them to another table, you better take some extra machine, test on that, make some search and delete those searchable items and repeat the process just to verify which is not searchable items, It can be a server side problem too, with million's of records and hundred of searches at the same time is really different than searching thousands of records with 20 users on X machine.


by the way, how this customer hit 2,165,001 million records? when there's only 100 records added a day? tha'st almost 60 years worth of data

[dodie]

I can feel you, I also handle millions of records in my previous projects... long time ago.

Talking about maintaining any databse system is tricky. Sometimes you have to concern about the relating tables or foreign keys. That might affect other important tasks.

On the otherhand, opensource system is designed to make users/developers easily purge the data.
If the table is isolated itself and with no other interruptions of other relating data, that would be easy to copy all the data or move it to the identical table with different name. Just be careful or make back-ups.

by the way, how this customer hit 2,165,001 million records? when there's only 100 records added a day? tha'st almost 60 years worth of data

It's possible to achieve it for weeks or days. If you have a proper sources and "tools" to that. Way back then...., I had thousands of records a day for the online selling items, the numbers are not the same. We're talking programming here, it's not like we input it one by one. LOLS!!!!!

[MarkCuering]

I'm not taken onto the point where we have to input 1 by 1 , even if you have a thousands of records a day, but wait, what record is this? how many tables? if you have 1000 records per day or items since it is selling, then you can have approx. 365,000 items within a year minus the sold items, or depreciated items or items that were cancelled out. these 365,000 will reduced, but hitting 1 million items, is something to seat back and think... hey, how did this happen? Am I selling a million of different kinds of items here


This vary on how they design their database at first stage. The problem mentioned is unsearchable items from customers side. Not to any other related tables. if you have thousands of items it can have another thousands of related table, but when you do searching it involves related datas and certain criteria that can help the system generate eg. product_info, product_type, product_category, and thus these tables are linked using id's and foreign keys that you've mentioned. So therefore, you aren't searching the real numbers of your items, coz its been reduce base on the criteria of search done by customer. But if these records and other related tables are poorly design, you have to reconstruct or table then.

what opensource system is designed to make users/developers easily purge the data purge is just a straight-forward command in SQL server... maybe what you mean to say is detecting unuse table, defective links etc... which actually already included in SQL servers.

[DeathFox]

Their using SQL Server 2005, and their host is rackspace. Almost 20k USD per month ang gasto nila tanan sa host.
And its currently in the hundreds per day now coz their business is in a slump. It was already on the 2 million mark when we got the project and there were thousands of ***** being uploaded way back then. Plus they have been around for a few years already. Anyways, out of the 2 million, only 700k is the only relevant data in there. This is badly due to bad Database Design from their very first programmers.

[DeathFox]

I'm not taken onto the point where we have to input 1 by 1 , even if you have a thousands of records a day, but wait, what record is this? how many tables? if you have 1000 records per day or items since it is selling, then you can have approx. 365,000 items within a year minus the sold items, or depreciated items or items that were cancelled out. these 365,000 will reduced, but hitting 1 million items, is something to seat back and think... hey, how did this happen? Am I selling a million of different kinds of items here

Well, it is a microstock website, so they are selling x amount of different kinds of items.

[MarkCuering]

Their using SQL Server 2005, and their host is rackspace. Almost 20k USD per month ang gasto nila tanan sa host.
And its currently in the hundreds per day now coz their business is in a slump. It was already on the 2 million mark when we got the project and there were thousands of ***** being uploaded way back then. Plus they have been around for a few years already. Anyways, out of the 2 million, only 700k is the only relevant data in there. This is badly due to bad Database Design from their very first programmers.

It's quite a number yet it is still manageable. If agreed on both parties, better to reconstruct the database structure. X members selling Y number of items is quite unpredictable, you can construct your database, like how ebay or msn does, There are some books focuses and explains how ebay's implement RDBMS, remember that software does not scale... architecture does, so if you are preparing for scalable system, then the company might invest another hardware that will act as their subserver, same like as binary tree implementation.

There is no such thing as no limits, rules must be applied. Let say X member can sell only around Y number of items, and Z period of TIME, from that you can maintain, coz there is a concrete information you can assume...this is the time you need to play around on the SQL ServerAgent, and on the Database Maintenance Plans.


if it happens that you are at pick of 80% Usage capability of the system its either you expand the architecture or revise your policies.

[dodie]

it's been years already? hhhmm... I guess it's time to revise if that's the case.
I think the other suggestions are also mentioned by MarCuering.

On the otherhand, the system is very solid and reliable, perhaps only need to revise only that part. Just for the sake of improving your system. Anything is possible for you to handle.
It would be pain in the butt sitting all day recreating a system from scratch.

[uchihacirxe]

My current project is maintaining/upgrading an e-commerce site with a very huge number of items. Currently the items in their database are currently around 2,165,001 million records with around 100 records added everyday. And around 1,096,394 are rejected irrelevant (dili masearch sa customers) and along with 33,728,311 tags/keywords attached to those rejected records.

Now my question is will purging them and moving them to another table would make it a lot more efficient? I know the answer is yes, but how MUCH more effecient will it be if I do this. Thanks

What kind of machine is the database server residing?
What is the OS version, service packs and patches?
What is the SQL Server version, service packs and patches?
Do you have a Development Database you can test the "purging" on?