Using Month Year Table

[Zero.NULL]

Hi,

We are using Month-year tables to keep the history of long transaction
of our application. For example:

We capture the details of a certain action in table
"TransDtls<Curr Month><CurrYear >" (this month: TransDtls072005 ).
This way tables keep growing. every month a new table gets created. We
have done it because we estimated that every month year table will
carry around 2 - 3 Lac records and most of the time the operations will
work on current month year table.

Avoiding this way and carrying on with single table instead of "Month
year" table might lead us system performance issues.

But now we are a bit confused on the way we are heading and also facing
the implementation issues like manipulating data from different
"month-year" tables. Could anyone please help us to make our vision
clear on this?

Looking for your valuable comments.
Thanks.

[David Portas]

Take a look at partitioned views in Books Online.

--
David Portas
SQL Server MVP
--

[Zero.NULL]

I would really like appreciate that you have got my problem clearly and
want to be thankful that you tried to point the would be solution of
this issue,
however I should say here that the rules applied on "Partitione d Views"
would trouble even more as there are situation and logic is developed
accordingly where the data grows unlimitedly in a single table, whereas
primary key column in partitioned tables require prefixed range set.

This is the first doubt about this solution in mind. Would revert back
with more.

[Erland Sommarskog]

Zero.NULL (ma******@gmail .com) writes:

I would really like appreciate that you have got my problem clearly and
want to be thankful that you tried to point the would be solution of
this issue,
however I should say here that the rules applied on "Partitione d Views"
would trouble even more as there are situation and logic is developed
accordingly where the data grows unlimitedly in a single table, whereas
primary key column in partitioned tables require prefixed range set.
Huh? Could you clarify what you mean?

Since you create YYYYMM tables every now and then, you would have to
recreate the view every you do this, but that's not a big deal.

In your original post you said:
We capture the details of a certain action in table
"TransDtls<Curr Month><CurrYear >" (this month: TransDtls072005 ).
This way tables keep growing. every month a new table gets created. We
have done it because we estimated that every month year table will
carry around 2 - 3 Lac records and most of the time the operations will
work on current month year table.

"Lac" is a unit that is unknown to me. Could you explain?

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

The name of this design flaw is attribute splitting. That means you
take waht should have been one table, find an attribute and use the
values of the attribute to make extra tables that shoudl not exist. If
you had split "Personnel" into "MalePerson nel" and "FemalePersonne l"
you would see the fallacy immediately.

What you have re-invented is the old IBM mag tape file system labels
that had a "yymmm" numbering. You even say "records" because you do
not know why a row is not a field and a column is nothing like a
record.

Avoiding this way and carrying on with single table instead of "Month year" table might lead us system performance issues. <<

Why don't you worry about data integrity and correct design first?
Hey, if nothing has to be right, I can make it run really fast -- the
answer is always 42!! Get the design then and only then, worry about
tuning.

[Zero.NULL]

Erland, I apologize to use a regional unit here. Lac is equal to 0.1
Million

Now let me clear the implementation, when a new batch transaction takes
place, the data for this transaction get stored in the current Month
year table (for now XYZMast072005) and when we update this batch it
insert updated data in the same table (this is being done to keep the
History of previous data). Now the issue is the batch in the previous
Month Year table (for now XYZMast062005) can also be updated, hence
data grows in the same table. (We have managed the Primary key IDs, and
use a base id for each and every batch). So this is why it is not
possible to supply a fixed range on Primary key column on these tables.

I hope this will clear my stand.

Celko,
I always respect wisdom. and I can feel the a great source of
information in you as you talk about something like "design flaw" and
"old IBM mag tape file system labels" from which I am unaware of. Still
your comments sounds criticism, whereas I am looking for some help and
direction. Still thankful to you that you have provided atleast few
keywords, and my background processes are actively working on these.

Thanks for all of your typing efforts.

[Erland Sommarskog]

Zero.NULL (ma******@gmail .com) writes:

Erland, I apologize to use a regional unit here. Lac is equal to 0.1
Million

Now let me clear the implementation, when a new batch transaction takes
place, the data for this transaction get stored in the current Month
year table (for now XYZMast072005) and when we update this batch it
insert updated data in the same table (this is being done to keep the
History of previous data). Now the issue is the batch in the previous
Month Year table (for now XYZMast062005) can also be updated, hence
data grows in the same table. (We have managed the Primary key IDs, and
use a base id for each and every batch). So this is why it is not
possible to supply a fixed range on Primary key column on these tables.

I don't think so. It is not clear to me what your primary key is, but
it appears that it is (batchid, runningnumber). But you have fooled
yourself. Because in fact the primary key is (yearmonth, batchid,
runningnumber). You have hidden that first component of the key in
the table name. Thus, you need to add a column with yearmonth (that is
'200507', not '072005'!), then you can partition on that column.

However, now that I know what a Lac is, I would suggest that you should
leave this yearmonth-table business entirely. You get 200000-400000 rows
a month. That is not a threating size, and having one table per month
is definintely going to buy you more headache than it will save you from.
One table per year possibly, but not even 48 milliion rows really call
for a partition. It depends a little on what requirements for how long
you have to save the data.

I reckon that if most operations are against current month, it could
still be worthwhile to have an area for the current month. This can
be achieved in two ways. One is to have two tables, currentmonth and
archive. By the end of the month you move over the data from the
currentmonth table to the archive. You could unify the tables with a
partitioned view, assuming that you put a date first in the PK.

The other alternative would be to have all data in one table, but then
have an indexed view which is defined to hold the values of the current
month. Queries that are for current month only could go directly against
that view. Queries that are unlimited would go against the base table.
In this case, you would need a monthly job that drops the view and
recreates it with a new defintion. One thing which is appealing here is
that since the view would always be empty initially, this would not
require any data to be moved. (Not that moving half a million rows is
daunting.)

I should add, that all these designs I discuss here requires proper
indexing. Then again, if you were to search for data in your current
design, and you don't know which month to look in, you would have a hard
time to find what you are looking for.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Zero.NULL]

Thanks Erland, for providing such piece of information. I find these
designs really useful, as I was looking for a better design for it.

What i feel here was the lacking of proper knowledge of capabilities of
SQL server

As you say here:
However, now that I know what a Lac is, I would suggest that you should

leave this yearmonth-table business entirely. You get 200000-400000
rows
a month. That is not a threating size, and having one table per month
is definintely going to buy you more headache than it will save you
from.
One table per year possibly, but not even 48 milliion rows really call
for a partition. It depends a little on what requirements for how long
you have to save the data.

can you please guide me where can I get the white papers on SQL Server
capabilities (i.e. storage capacity of table)

thanks once again for your concerned and detailed mails on this issue.

[Erland Sommarskog]

Zero.NULL (ma******@gmail .com) writes:

can you please guide me where can I get the white papers on SQL Server
capabilities (i.e. storage capacity of table)

I will have to admit that I can't point directly to any such white
paper, but you may find something useful at
http://www.microsoft.com/technet/pro...l/default.mspx.

But as a general guidance, recall that SQL Server is designed to be
an enterprise solution. There are SQL Server databases out there with
over 10 TB of data. It goes without saying that an engine that is
capable to handle that amount of data, should not have any problem with
a couple of million rows.

That is, and this can not be stressed enough, proivided that you have
proper indexing.

I should also add that the number of rows is not really the crucial
part, but more the size in megabytes. If you have ten million rows, ir
makes quite a difference if your average row size is ten bytes or
7500 bytes.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp