1

Currently I'm working on a legacy system that does partitioning at the application level. A line item table with a million rows (for example) have be split into two tables so that each table gets 500,000 thousand rows. I don't have experience with data partitioning so I'm not sure if this is the right way to do it. I have two questions regarding this: If this is an acceptable way to do this, how would it work with foreign key relationships? If this is not an acceptable way to do this, how would one have partition logic at the application level.

if (LineItemID < 5000000) {
    GetLineItemFromTableA();
} else {
    GetLineItemFromTableB();
}

Update - The data is retrieved through a simple stored procedure (Select column1, 2, 3 From TableName). The data is used for reports and a web application.

user3587180
  • 1,317
  • 11
  • 23
  • We may need to know how you retrieve the data, and what you going to do after retrieve the data. – Prisoner Sep 22 '16 at 02:44
  • Added more information for you... – user3587180 Sep 22 '16 at 03:12
  • Thanks, I would like to know is the system retrieve all 1m records at the end? Or just need to process 500K records? And you retrieve data from SP, with `ADO.NET`? Why you need to handle the `foreign keys` in application layer? IMO, if you retrieve the data for reports (no sorting, filtering in application layer), I will recommend you to do the paging in SP (http://stackoverflow.com/a/8938957/1050927) – Prisoner Sep 22 '16 at 03:24

0 Answers0