Design for scalability

Anon

Let's say you have 100 tables with identical structures each containing different numerical and textual information. All of these 100 tables get continuously modified, and new row entries get added to each table throughout the day.

If i want to see how many total changes have been made during the day, i calculate the amount of entries added to each table during the day and add up all of them up.. It is setup in such a way..

1 - Select all table names from a table which contains names of 100 database tables.

2 - Use a while statement, and run SELECT query on individual table, selecting rows for today. Add the number of rows returned to the number of rows returned from a previous table.. and so on. Continue until all 100 tables have been queried.

Now the way this is done, there's a query that gets repeated 100 times, and of course if i plan to expand to 1000 or 10000 tables, it will take forever to generate a report indicating changes for all 10000 tables throughout the day.

I'm wondering... If i want speed and optimal performance, is this the way it is supposed to be done? or is there any other way of generating such a report let's say.

Please don't say that i should create a table for a report, and add an sql query that increments a number of changes done for a certain day once a row is added to any of the 100 tables.

Anon

Why shouldn't we say that? It's a valid solution and will keep you from having to run some massive process at the end of the day to gather a count of new rows.

If you are using a database that has the ability to do stored procedures you could create one of these for each of your tables that will modify the report table, this way you don't have to change your current code.

If you can't do stored procedures, or you are dead set against having a report table, then you could probably build a single query that will count from all tables, but you'll have to take care to make sure you join the tables correctly so that your counts don't interfear with each other.