Combine Large files by Create Date

[LaRetta]

Hi Everyone,

I am pulling in the last pieces of a conversion and have what may seem like a silly question ...

Does the sequence of the data make any difference in speed of access? Example: I have Invoices table 230,000 records (dating back 5 years). Owner now presented older invoices (he wants to keep 7 years in all tables).

I imported the records but they are out of logical sequence (both by Invoice Number and by creation date). So, in my wisdom (or lack of), I sorted them by CreationDate/InvoiceNumber and exported the entire table as .mer. My plan was to delete all existing invoices and import them (in logical creation sequence); thinking it would make a difference in speed, calculations which look for Last() etc.

But now while deleting these 230,000, it's taking an hour and not even half done and I still have LineItems to repeat this process with and it's 15-times as large!! I know in general, sequence doesn't matter but it seemed strange to have records created in 1999 after my 2005 records. Am I wasting my time or does this make sense to others as well? I know I can always sort by relationship or portal but I keep thinking it would be better to have them right - and NOW ... before it gets larger and that the natural creation order of the unsorted table would be best; particularly since I sometimes use a serial to self-join and 'look back' at prior records. Am I nuts?

Why in the world does deleting take so long? Exporting, finding and everything else I do to these records doesn't!! It amazes me how long deletions take.

LaRetta

[LaRetta]

Hi Everyone,

I am pulling in the last pieces of a conversion and have what may seem like a silly question ...

Does the sequence of the data make any difference in speed of access? Example: I have Invoices table 230,000 records (dating back 5 years). Owner now presented older invoices (he wants to keep 7 years in all tables).

I imported the records but they are out of logical sequence (both by Invoice Number and by creation date). So, in my wisdom (or lack of), I sorted them by CreationDate/InvoiceNumber and exported the entire table as .mer. My plan was to delete all existing invoices and import them (in logical creation sequence); thinking it would make a difference in speed, calculations which look for Last() etc.

But now while deleting these 230,000, it's taking an hour and not even half done and I still have LineItems to repeat this process with and it's 15-times as large!! I know in general, sequence doesn't matter but it seemed strange to have records created in 1999 after my 2005 records. Am I wasting my time or does this make sense to others as well? I know I can always sort by relationship or portal but I keep thinking it would be better to have them right - and NOW ... before it gets larger and that the natural creation order of the unsorted table would be best; particularly since I sometimes use a serial to self-join and 'look back' at prior records. Am I nuts?

Why in the world does deleting take so long? Exporting, finding and everything else I do to these records doesn't!! It amazes me how long deletions take.

LaRetta

[LaRetta]

Hi Everyone,

I am pulling in the last pieces of a conversion and have what may seem like a silly question ...

Does the sequence of the data make any difference in speed of access? Example: I have Invoices table 230,000 records (dating back 5 years). Owner now presented older invoices (he wants to keep 7 years in all tables).

I imported the records but they are out of logical sequence (both by Invoice Number and by creation date). So, in my wisdom (or lack of), I sorted them by CreationDate/InvoiceNumber and exported the entire table as .mer. My plan was to delete all existing invoices and import them (in logical creation sequence); thinking it would make a difference in speed, calculations which look for Last() etc.

But now while deleting these 230,000, it's taking an hour and not even half done and I still have LineItems to repeat this process with and it's 15-times as large!! I know in general, sequence doesn't matter but it seemed strange to have records created in 1999 after my 2005 records. Am I wasting my time or does this make sense to others as well? I know I can always sort by relationship or portal but I keep thinking it would be better to have them right - and NOW ... before it gets larger and that the natural creation order of the unsorted table would be best; particularly since I sometimes use a serial to self-join and 'look back' at prior records. Am I nuts?

Why in the world does deleting take so long? Exporting, finding and everything else I do to these records doesn't!! It amazes me how long deletions take.

LaRetta

[LaRetta]

HAH!

Well one reason it was taking so long is that I had 'Allow Deletion of Related' to my LineItems. So now I won't have to delete my LineItems also! Nonetheless, deletions always take far too long. Anyone else notice this?

Good thing I always back up immediately before I do these types of things! My question remains although it's been altered to WAS this a dumb idea?

And to think ... normal people are off playing on a Saturday. They simply don't know the fun they're missing.

[LaRetta]

HAH!

Well one reason it was taking so long is that I had 'Allow Deletion of Related' to my LineItems. So now I won't have to delete my LineItems also! Nonetheless, deletions always take far too long. Anyone else notice this?

Good thing I always back up immediately before I do these types of things! My question remains although it's been altered to WAS this a dumb idea?

And to think ... normal people are off playing on a Saturday. They simply don't know the fun they're missing.

[LaRetta]

HAH!

Well one reason it was taking so long is that I had 'Allow Deletion of Related' to my LineItems. So now I won't have to delete my LineItems also! Nonetheless, deletions always take far too long. Anyone else notice this?

Good thing I always back up immediately before I do these types of things! My question remains although it's been altered to WAS this a dumb idea?

And to think ... normal people are off playing on a Saturday. They simply don't know the fun they're missing.

[Ender]

When deleting a lot of records is called for, I always clone the file and import the records I want to keep instead.

[Ender]

When deleting a lot of records is called for, I always clone the file and import the records I want to keep instead.

[Ender]

When deleting a lot of records is called for, I always clone the file and import the records I want to keep instead.

[LaRetta]

Thanks Mike!

Makes sense. I sure wish I could have kept the other file instead, except the one with 1999-2005 was my existing solution - ONE file with 39 tables and the other was a single lonely FM table. Although sometimes I feel like trashing it and starting with one simple file/table again.

So then you HAVE done something like this - combine the two files because of the sequence? Good to know if true. I felt a bit silly doing it.

I suppose I could have just attached the lonely file as a File Reference and imported into it - directing all my connections to it but what about all my calculations? Too bad we can't clone tables. I guess this is where FM Robot would have helped, huh?

LaRetta

[LaRetta]

Thanks Mike!

Makes sense. I sure wish I could have kept the other file instead, except the one with 1999-2005 was my existing solution - ONE file with 39 tables and the other was a single lonely FM table. Although sometimes I feel like trashing it and starting with one simple file/table again.

So then you HAVE done something like this - combine the two files because of the sequence? Good to know if true. I felt a bit silly doing it.

I suppose I could have just attached the lonely file as a File Reference and imported into it - directing all my connections to it but what about all my calculations? Too bad we can't clone tables. I guess this is where FM Robot would have helped, huh?

LaRetta

[LaRetta]

Thanks Mike!

Makes sense. I sure wish I could have kept the other file instead, except the one with 1999-2005 was my existing solution - ONE file with 39 tables and the other was a single lonely FM table. Although sometimes I feel like trashing it and starting with one simple file/table again.

So then you HAVE done something like this - combine the two files because of the sequence? Good to know if true. I felt a bit silly doing it.

I suppose I could have just attached the lonely file as a File Reference and imported into it - directing all my connections to it but what about all my calculations? Too bad we can't clone tables. I guess this is where FM Robot would have helped, huh?

LaRetta

[Ender]

For my FM7 solution, I'm keeping large data tables as separate files to make purging easier. One reason why combining everything into one file is not necessarily the best way to go.

So then you HAVE done something like this - combine the two files because of the sequence?

I'm not sure what you mean. I thought your original question was about performance of sorted records vs. unsorted. I don't have any comparisons to offer about that. It's not something I've thought much about.

[Ender]

For my FM7 solution, I'm keeping large data tables as separate files to make purging easier. One reason why combining everything into one file is not necessarily the best way to go.

So then you HAVE done something like this - combine the two files because of the sequence?

I'm not sure what you mean. I thought your original question was about performance of sorted records vs. unsorted. I don't have any comparisons to offer about that. It's not something I've thought much about.

[Ender]

For my FM7 solution, I'm keeping large data tables as separate files to make purging easier. One reason why combining everything into one file is not necessarily the best way to go.

So then you HAVE done something like this - combine the two files because of the sequence?

I'm not sure what you mean. I thought your original question was about performance of sorted records vs. unsorted. I don't have any comparisons to offer about that. It's not something I've thought much about.

[LaRetta]

Certainly ... another reason for the Separation Model but not my current reality, I'm afraid; at least not yet.

I thought your original question was about performance of sorted records vs. unsorted.

Sort of. It was about performance issues (and logic) when records are no longer in their normal creation sequence. The records could remain unsorted if they were in creation date order (in most instances); but would continually have to be sorted if the 7-year-old records were at the bottom (out of proper creation order). Also, the new serial could no longer identify the prior (created) record via self-join (for isolating last of any specific record).

Hmmm, if you haven't given it much thought then maybe I shouldn't have given it ANY thought and left them alone. Sometimes I think I must be nuts.

[LaRetta]

Certainly ... another reason for the Separation Model but not my current reality, I'm afraid; at least not yet.

I thought your original question was about performance of sorted records vs. unsorted.

Sort of. It was about performance issues (and logic) when records are no longer in their normal creation sequence. The records could remain unsorted if they were in creation date order (in most instances); but would continually have to be sorted if the 7-year-old records were at the bottom (out of proper creation order). Also, the new serial could no longer identify the prior (created) record via self-join (for isolating last of any specific record).

Hmmm, if you haven't given it much thought then maybe I shouldn't have given it ANY thought and left them alone. Sometimes I think I must be nuts.

[LaRetta]

Certainly ... another reason for the Separation Model but not my current reality, I'm afraid; at least not yet.

I thought your original question was about performance of sorted records vs. unsorted.

Sort of. It was about performance issues (and logic) when records are no longer in their normal creation sequence. The records could remain unsorted if they were in creation date order (in most instances); but would continually have to be sorted if the 7-year-old records were at the bottom (out of proper creation order). Also, the new serial could no longer identify the prior (created) record via self-join (for isolating last of any specific record).

Hmmm, if you haven't given it much thought then maybe I shouldn't have given it ANY thought and left them alone. Sometimes I think I must be nuts.

[LaRetta]

I cannot keep from wondering about this scenario. Can I get other people's opinions on what they would have done? Situation:

You have 230,000 invoices in a table (unsorted by creation order, of course). You are given 80,000 invoices dated (created) prior to your existing table set. We can't slide a set of records at the beginning of a table!! So, not only do your invoice numbers now list out of sequence, ie, when viewing the last entered record it displays Invoice# 072422 instead of the current number of 201376 but the record creation dates are 1998. The same applies to your LineItems (their Invoice # will be greatly misaligned as well).

I believe that leaving this kind of mis-sequence (incorrect creation order) will cause problems down the road. One small example: Client lookup related Invoice to find first order for the Client (first related record in unsorted relationship). One would continually have to sort the Invoice & LineItems either via relationship, portal or sort script (for reports) - and that takes time and resources, does it not? I had never considered it before but I think the pure natural creation sequence of table records is indeed important.

I've never polled before; I want to know if I just wasted 20 hours resequencing them unnecessarily. I would appreciate your participation and/or additional comments as well.

LaRetta

Edit - seems I can't change a poll once created and it doesn't appear to accept votes until tomorrow. Oh brother ...

[LaRetta]

I cannot keep from wondering about this scenario. Can I get other people's opinions on what they would have done? Situation:

You have 230,000 invoices in a table (unsorted by creation order, of course). You are given 80,000 invoices dated (created) prior to your existing table set. We can't slide a set of records at the beginning of a table!! So, not only do your invoice numbers now list out of sequence, ie, when viewing the last entered record it displays Invoice# 072422 instead of the current number of 201376 but the record creation dates are 1998. The same applies to your LineItems (their Invoice # will be greatly misaligned as well).

I believe that leaving this kind of mis-sequence (incorrect creation order) will cause problems down the road. One small example: Client lookup related Invoice to find first order for the Client (first related record in unsorted relationship). One would continually have to sort the Invoice & LineItems either via relationship, portal or sort script (for reports) - and that takes time and resources, does it not? I had never considered it before but I think the pure natural creation sequence of table records is indeed important.

I've never polled before; I want to know if I just wasted 20 hours resequencing them unnecessarily. I would appreciate your participation and/or additional comments as well.

LaRetta

Edit - seems I can't change a poll once created and it doesn't appear to accept votes until tomorrow. Oh brother ...

[Oldfogey]

LaRetta, I have run into a similar problem but nothing like 230,000 records!

(I use unsorted order so often in sorting out user messes!) I actually went to the trouble of creating a clone, importing the old stuff and then the new stuff. This gives you the correct creation order. I also gave them new (additional) serials.

Why would you bother having to do, possible many, lo-o-ong sorts when you can have the 'natural' order so easily ... comparatively easily, anyway?

(I admit to bias. I do a lot of work for a customer, dealing with employee timesheets. Analysing creation dates, employee-supplied dates, and creation order throw up some amazing discrepancies.)

[Oldfogey]

LaRetta, I have run into a similar problem but nothing like 230,000 records!

(I use unsorted order so often in sorting out user messes!) I actually went to the trouble of creating a clone, importing the old stuff and then the new stuff. This gives you the correct creation order. I also gave them new (additional) serials.

Why would you bother having to do, possible many, lo-o-ong sorts when you can have the 'natural' order so easily ... comparatively easily, anyway?

(I admit to bias. I do a lot of work for a customer, dealing with employee timesheets. Analysing creation dates, employee-supplied dates, and creation order throw up some amazing discrepancies.)

[NYPoke]

I have not used Version 7 yet, but I am well trained in Relational Databases (Oracle, Informix, SQL Server, plus numereous other smaller databases).

In reality, the physical order shouldn't matter much. If it does, you should look at the scripts/processes causing the problem, NOT the physical order of the data.

The important fields should be indexed, thus giving you the order you want. You can only have one physical order, so it really does not matter, in that you are concerned about both Creation Date & Invoice Number, probably others at times.

Your Indexing will provide you the order you want. You can easily sort the records, even as a default when the file opens.

Deleting takes a long time, because not only do you delete the record, you also have to update each Index. I'm not sure that the deletion is the key, as much as the number of records you are dealing with.

If this process of deleting & reordering makes you feel more comfortable, then it may be worth it. But, if it is a real pain in the back-side, you don't really need to do it.

Way to approach the problem:

1) Is this order adversely affecting operations?

A) If no, then you don't need to do anything.

If yes,

a) Consider building in a Sort. If this works, you save a lot of work

: If the Sort does not solve your problems, then rebuild the file (like you are doing)

[NYPoke]

I have not used Version 7 yet, but I am well trained in Relational Databases (Oracle, Informix, SQL Server, plus numereous other smaller databases).

In reality, the physical order shouldn't matter much. If it does, you should look at the scripts/processes causing the problem, NOT the physical order of the data.

The important fields should be indexed, thus giving you the order you want. You can only have one physical order, so it really does not matter, in that you are concerned about both Creation Date & Invoice Number, probably others at times.

Your Indexing will provide you the order you want. You can easily sort the records, even as a default when the file opens.

Deleting takes a long time, because not only do you delete the record, you also have to update each Index. I'm not sure that the deletion is the key, as much as the number of records you are dealing with.

If this process of deleting & reordering makes you feel more comfortable, then it may be worth it. But, if it is a real pain in the back-side, you don't really need to do it.

Way to approach the problem:

1) Is this order adversely affecting operations?

A) If no, then you don't need to do anything.

If yes,

a) Consider building in a Sort. If this works, you save a lot of work

: If the Sort does not solve your problems, then rebuild the file (like you are doing)

[Vaughan]

Before doing any big data changes like this, I go through and remove the "delete related records" option off all relationships. It really speeds things up. But you've gotta remember to put them back on again later.

Alternatively, delete all the related records first. Not as fast because the parent still searches for related records to delete.

The "where do we put this new bunch of old records" is a real challenge. I'd be sorely tempted to dump all records and import them in order, but in a relational file thast could mean re-keyin all related records -- a huge and risky situation, and time consuming. Not something I'd like to do on a production system, and not something I'd like to do with a tight schedule to keep either.