Compacting Freezerworks Unlimited

Compacting Freezerworks Unlimited data file introduction – 00:00

Hello, and welcome to our class on compacting Freezerworks Unlimited data file.  If you've been running Freezerworks for an extended period of time and are experiencing some degradation and performance in which, for example, maybe your searches take longer to perform, you will find that compacting your data file will dramatically restore and improve performance.  So, in this class, we will compact Freezerworks Unlimited Server data file and show you the difference that you may find in performance. 

4d Tools – 00:35

Now, to compact a data file we use a utility called 4d Tools.  4d Tools is available at the 4d FTP site.  You can download it from your browser.  If you're going to compact your data file you might want to give yourself say 1-3 hours to do so, especially for larger data files, so find a time when people do not have to access Freezerworks Unlimited and you can compact the data file during that time.  

Renaming Data Files – 01:07

When you compact data files, what Freezerworks 4d Tools will do is compact and create a compacted copy of your data file.  So, for example, if your Freezerworks data file is named FreezerworksUL31.4dd (or FreezerworksUL31.data if it's Macintosh) then compacted data files will be named the default name: FreezerworksUL312.4dd. Also, data segments will be compacted as well, so unused data segments will be removed.  What we're going to do in this class is we're going to rename our data file something a dramatically more different than 312 and we'll talk about that a little bit later as we go about our class.

Installing and using 4d Tools – 02:00

So, the first thing we need to do is get 4d Tools and install it on our computer.  To do that, of course, we go to the FTP site.  So, what I'm going to do here is in my browser, I'm going to put in that address that we saw earlier on the PowerPoint presentation.  I'm going to go there and get the 4d Tools.  Now, after you access the FTP site with this address, you should get this dialog box to download and save 4d Tools.  Just select Save and you'll be able to download it.  In this class we won't do that, I have already downloaded it.  Let's go ahead instead and go into it and assume that we've downloaded and installed the unzipped 4d Tools to install it.   So, what I'm going to do is go to my explorer and find 4dtools. So, opening up Windows Explorer, we have 4d Tools down here.  We've downloaded 4d Tools 2004, which is the latest version of 4d Tools which is what we'll need in this situation since we're compacting Freezerworks 3.1 which is running under the 4d 2004 engine.  If you've got an older version of Freezerworks Unlimited (something older than 3.1), you will need an earlier version of 4d Tools.  If this is the case let us know and we can point you in the direction to get you the copy of 4d Tools that you need. So, here is 4dtools.exe.  Let me go ahead and double-click and go into it.  When you open 4d Tools, it will ask you for the data file, the structure program that you're using for Freezerworks Unlimited.  Down here in Files of type, select Compiled Database (*.4dc) (the default will say Structured File (.4db)).  Open it up and go to Compiled Database.  Then what we want to do is find the Freezerworks Server folder.  I am keeping that here.  I just go to the folder that's holding the server program and find the .4DC.  What you want to do is find the Freezerworks.4DC file and here it is.  This is the Compiled Database structure file.  So, we would go ahead and select it.  4d Tools will then look for the data file that is linked to the structure file (the 4d file).  When it finds the file, it will then ask you to go ahead and put in the username and password.  The username for 4d Tools is 4dtools the password is tools, and Connect.  Then we wait for 4d Tools and the next step in the process.  Now, if you have data segments, 4d Tools will now go ahead and ask you to locate the data segments.  What we do here, if you get this, is go ahead and select OK and find this data segment and select it.  That is this one right here.  Go ahead and select it and open it.  We'll then open it and then it asks for the next data segment, select OK again and find the next data segment.  As you can see, data segments will have the two named after it and that's why when we come back we're going to make it an easier simpler process and give this data file a brand new name.  So, let's go ahead and go on from here.  We've selected two data segments and we've got this message, “You cannot open this database because the data file (or one of the segments) is locked”.  If you get this, the reason is because Freezerworks Server is operating this data file right now.  What I need to do is exit out of Freezerworks Server.  Let me go ahead and do that.  I must select OK and I'm going to go and log out and close Freezerworks Server because the data file is currently open, so it's locked and I can't do it.  Something I should have done before. 

Examining the difference between an uncompacted and compacted data file – 07:27

However, before we do that, this is a good reminder that before I go any further, let’s open Freezerworks.  The reason for that is so that we can look at and see what kind of performance we're currently getting with our data file.  Then, we'll go back in and compact the data file, open the data file and run the same test and look at the difference in performance from the uncompacted data file to the current compacted data file.  So, let me go ahead and go into Freezerworks Client and we will do that now.  We're in the in the program, we can take a look here and see, here's our data file.  What I want to do now is run a process.  I've got a process here in Advanced Search I am going to search and pull up no more than 500 sample records in my database of 1 million records (this is a 1 million record database).  I want all these samples that have an Internal Barcode ID of greater than or equal to 300,000 and less than 300,500.  Let's run this query.  We'll run a little timer and we'll see how long this query will take.  So, what I'm going to do is put a little timer here and we'll kind of watch and see how this goes.  So, let me go ahead and execute the query.  You'll see the queries in progress at roughly 9 minutes after 5:00 pm and 15 seconds. We are about 10 seconds into this process.  Again, this is a 1 million record data file that is on a server that is pretty ancient actually, running Windows 2000, it's not even running XP.  It has 256 megabytes of RAM, so it's not the most powerful server, but it is running this million record data file and it's going to take roughly two to three minutes to do this.  As you can see, we're already about 45 seconds into the search.  The Query in progress… a thermometer is displayed and as you can see, it's taken a while to run through these million records to find the 500 that we want.  We're already one minute into it what I'm going to do is fast forward till we get to the end of this search.  Having moved ahead we're now about two minutes into our search and the query is still going on.  So, we can see we really want to increase and improve the performance in this data file.  We will do that by compacting it.  We're going to come up on roughly three minutes in this query, it's now 5:12 pm and moving on to the fourth minute.  We have now finished our query.  Freezerworks is now locating and at about three minutes and 20 seconds or so, the search is done and the display has taken place.  Here are our 408 samples that we requested.  So that's about what we're looking at, and we want to improve this of course.  Let's go ahead and do that.

Using 4d Tools to compact Freezerworks data file – 11:20

Let's leave Freezerworks and now, I do want to close the closest server (I'm going to go and shut down the server), come right back and we're going to compact that database data file.  I have shut down the server, now I'll go back into 4d Tools and let's go ahead and find the for DC file.  There it is, and we'll open it. 4dtools, Password: tools, and Connect.  Once again, we'll link up with our data segments so we get the complete data file and then we'll do our compacting. After we select those data segments, the data file and data segments will be listed here in 4d Tools under Data Segments.  We come down here, we see that under Administration, we get the message that the data does not need to be compacted. However, we are going to go ahead and compact.  So, we're going to go to Maintain.  At the bottom here, it says “Compacts the data file”; we go ahead and select Compact.  Again, as I said earlier, 4d Tools will default to a new name for this data file, it's the compacted data file putting a 2 at the end of the name here.  I think this is going to cause a little bit of confusion, so I believe it's a good idea to just give this a completely different name.  I'm going to call it FULcompact.4dd, this way there's no confusion between 4dd’s and 4ds’s and 1’s and 2’s.  We give this a name like this and select Save.

Compacting the .4dd file – 13:30

The screen will go blank like this for a little bit, just give it some time.  4d Tools is preparing itself for the compacting process.  Once it's ready to go, you will see that the first thermometer will be compacting aliquots.  As you can see, the thermometer is moving quite slowly.  There is going to be a number of tables within Freezerworks Unlimited that will need to be compacted. We’ll first compact the aliquots, the samples will need to be compacted, Audit Trail, and a number of indexes.  Like I said earlier, this could take anywhere from one to three hours.  So, let's go ahead and let it run its course and I'm going to fast forward so that we don't have to wait a whole hour here or so.  I'm going to fast-forward this process and let it go about its work.  When the compacting is over and we have gone about, not quite two hours into the compacting, it ran through about 400 indexes. Compacting is done.  You'll know it is done when you see the 4d Tool screen again.  We can now go ahead and quit 4d Tools.

Opening our compacted data file – 15:05

Let's go and find our data file, and this would be down here.  I have it located here.  FreezerworksUL31Server and I will go into the Server Database folder.  We see here our old data file and our new compacted data file.  Now, as you can see, FULcompact.4DD did compact everything, including the data file and the segments.  We had that two segmented files in FreezerworksFUL31.4DD, we do not have any segmented files in FULcompaact.4dd.  The entire data file was compacted into one data file, both into FULcompact.4dd.  What we're going to do now is go ahead and fire up the server again and load FULcompact onto it.  Then, we'll go and run that same search and see how much time we saved after compacting a data file.

Comparing Search times to compacted vs. uncompacted data files – 16:40

I have restarted the server, linked it up to FULcompact, and now I’m going to go into Client and open it up.  Log in to Freezerworks Unlimited.  Let's take a look at the About and see that we do have FULcompact.4DD linked up.  Let's now run that search that took about three minutes in the uncompacted data file, and run that search in the new compacted data file.  Again, samples between 300,000 and 300,500. Execute the query, and you can see that within a matter of seconds Freezerworks has located the 408 samples that took three minutes in the uncompacted data file.  We didn't have to wait through the query, and thermometers didn't show up.  So, you can see there is a dramatic difference when you compact your data file.  

Changing data files – 18:00

We're going to keep this data file.  We're going to want to then, of course, go back to our data file Server Database.  We now have two data files and this could cause some confusion.  There are a couple things you can do, you'll want to, of course, remove the old file.  Now, what you may want to do is rename the FULcompact after we've removed the 4DD files and the associated segmented files.  We don't need the 4dr anymore; we have a new 4dr, so we want to remove these.  We'll just go ahead and put them into the into the recycle bin.  We're now going to use FULcompact.4DD. Change the name.  Be sure to reset your backup and log file procedures, so that so that you are backing up your new data file.  If you need help with that, go ahead and contact us anytime, we can walk you through that as well.

Conclusion – 19:30

So, this is the end of the class, and thank you for spending some time with us to look at compact new data file.  As you can see, you can get dramatic improvements by doing this. Again, remember that the 4d Tool is located here, at this address in the 4d website FTP site.  The name and password, when you download install it, is 4dtools and then the password: tools.  Again, if you have any questions, or need any help, contact Dataworks Development and we can certainly help you through this and answer any questions about anything you need to know about compacting.  Thank you again and we'll see you later, bye-bye.