My database is set up with what we call our staging tables and our live tables. Currently for we have about 2 million rows for a table called products. Our customers we call manufacturers load their data into the staging tables and when they are done updating, adding, deleting data we then push that data into our live tables.
These live tables feed a web service for a mobile app and websites. The pushing of data from staging to live consists of deleting all the data in the live tables and inserting all of the data from the staging tables. All of this is segregated by a column called
ManufacturerID that exists in every table.
Some manufacturers have 500 products, some have 75,000. Depending on this we sometimes have REALLY REALLY slow web service responses because of paging through all of the 2 million records. Deleting the data from the live tables also seems to be getting ridiculously slow.
Would partitioning my products table by
ManufacturerID help this situation? From what I have read this would basically mean when I am querying my products I would only be querying a small subset of the database on that
ManufacturerID and therefore see a huge improvement in overall response time.