The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Creating tables daily

We have following requirement-we need to create a table eveyday,in which we will record data.At the end of day,that tables data will be archived,and table dropped.The table created might have name like tablename_date.What are the options to implement this requirement creation and recording of data,and then archiving?
Saturday, September 29, 2007
Sounds horrible. So ou use different SQL every day to access that table. Terrible, just terrible.
David Aldridge Send private email
Saturday, September 29, 2007
wow david, you are very quickly done with your conclusions. I don't see anything wrong with it yet because I don't know what kind of data are being stored. if you don't need cross-selects between different days and at the same time dataset is too big that it could affect overall performance if it's all in one table, then this is logical solution. SQL language doesn't have native support in most of the programming languages, so creating dynamic queries with variable table names is actually quite easy.
Saturday, September 29, 2007
While it is easy to create dynamic sql each day that doesn't mean one should. (It is easy to create dynamic sql every second, (where every statement is different) but it is usually not a good idea. 

Why can't you just have an additional column with the creation date for the record.  It would make things much simpler.  You could always delete the data for a day if you didn't need it.  It would make the whole I want to query the data and my query runs over until the next day problem.  (usually you can't drop a table that has an active query on it.  You usually have to get an exclusive lock on it.)
JimK Send private email
Saturday, September 29, 2007
I don't understand why we're avoiding the easy solution: just creating one table with a date column and an index on it.  Then all of your queries just select only records from today's date and, boom, problem solved.  Maybe there is a hidden requirement or database space issues that I don't know about.

But if we're wedded to the "Must have zero rows at midnight!" idea, make two tables, one for use and one for storage.  They have identical schema as your current table, with an (indexed) creation date column.  At midnight, select everything from table #1, insert it into table #2, then delete everything from table #1.
Patrick McKenzie (Bingo Card Creator) Send private email
Saturday, September 29, 2007
Why do you need to change the name of the Database table? When you export the data write it to tablename_date and truncate the table.

Why does it matter what the database table is called if you can create the archive file with the correct date?
Sunday, September 30, 2007
Another vote for keying a single table by creation date, and having an archiving process that moves them out to historical table(s) or files.
xampl Send private email
Sunday, September 30, 2007
If the data is really archived every night, why bother with different table names?

Just use a table called, for example, CurrentData, and copy it to archive and truncate it at the end of each day. That way, all your SQL can stay the same.
Entries of Confusion Send private email
Monday, October 01, 2007
Ok,we can go with one table.But,what if we have million transactions in one day to record.
Monday, October 01, 2007
There's two problems here:

1. You're getting a million rows a day and don't appear to have any experience in managing a database at that level.  Hire someone who has, and who can mentor you.

2. Ultimately, rows get stored in pages, which get written to disk.  If you're also querying on this data, you'll want fast results.  By minimizing the amount of data that you're querying against, you'll speed up results.  Thus, move unneeded data out of the active tables is a good thing. 

Also -
You may or may not want to compact the database occasionally, in conjunction with a disk defrag -- you'll need to think hard about this one -- it could make things worse, or make them much better, depending on your access patterns.
xampl Send private email
Monday, October 01, 2007
xample: are you talking about MS SQL server? the poster did not specify the database used.

p.s. Oracle does not work that way :)
Totally Agreeing
Monday, October 01, 2007
Yes, we used MS-SQLServer.

Oracle does work differently, you are correct.  Do they still support raw/native datafiles, or do they now depend on the OS to manage your datafiles?
xampl Send private email
Monday, October 01, 2007
While I agree with your points #1 and #2, I think a sensible indexing and/or partitioning scheme negates the impact of #2.

Having millions of transactions per day may be challenging, depending upon your implementation, but is certainly not difficult.

I always mention the case of telecoms carrier-class billing, where a million calls a day would be a really bad for business, and the database can easily grow to a billion rows in one table within a few months.

Any grown up database (SQL Server, Oracle, etc) can handle this amount of data with ease, as long as you have sensible hardware and sensible implementation.
Entries of Confusion Send private email
Tuesday, October 02, 2007
Look up database partitioning. Most enterprise level databases have this feature which sounds like it's pretty much what you need.
Tuesday, October 02, 2007
==> this is logical solution.

Umm ... no, it's not.

Millions of rows a day? pffft! I've got apps that do that in minutes. You need to size the hardware for the job.

Listen to these folks. Believe it or not they're trying to help, and they're right.
Tuesday, October 02, 2007
just truncate the table and reinsert.

no reason to drop it. has the same effect as drop accept you don't have to re-create the table.
Wednesday, October 03, 2007
Now,what we have decided is to take guidance from DBA.
Also,we are thinking to implement a prototype,using the idea of one currentdata table,and other archive table.Having a daily table seems not a good idea.
Its true,we are all programmers,and have no experience about how database will handle huge amount of data.
1)So,what you say,involve DBA?
2)Do you think a prototype will be a good idea,where we will do some stress testing.
3)Any links which guide us about how hardware improves performance?
We are using MSSQL 2005.Hardware is not an issue for us.
Friday, October 26, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz