Network Performance

[LadyDi]

I have a question for you. I think I know the answer to the question, but my co-workers have asked me to ask the "experts" I know on the internet. With the assistance of several people on this forum, I created a database that would go out on a network drive and unzip over 2,000 files to a person's hard drive and then collect the data from those files. This database has since been handed off to an associate in another building. The database is working great. The only problem is that the internet connection in this person's building is really slow. The only time the database interacts with the network is to initially unzip the files. Other than that everything is done on the hard drive. The person running the database now complains a lot about the database being slow. If she isn't complaining, she is impatiently clicking buttons when she shouldn't be. I don't think there is anything I can do because the problem is the connection speed, not the database. Do you have any suggestions?

 

[CJ_London]

Have you considered copying the zipped files from the network to the users drive and then unzipping from there? Once unzipped the copied files can be deleted.

I'm not sure without testing whether it would be quicker but the other possibility is to copy a zipped file and then unzip, then copy the next file and unzip etc. From a network perspective you would be breaking the transfers into smaller 'bites', the users computer would be waiting less since the copy and unzip can happen concurrently and you could update a screen display between each file copied with a progress bar.

 

[LadyDi]

I would like to try the suggestion you gave about copying one file at a time and unzipping it. Unfortunately, I'm not real sure how to accomplish this. I feel very comfortable with writing VBA that deals with items within the database. However, when it comes to getting the database to run other programs or copy and paste files in another location, I'm afraid I'm lost. I am currently creating a table that lists the paths to the files that need to be unzipped and am looping through those one at a time. Below is the module I am using. What do I need to add to get it to copy and paste the zip file and then unzip it?

Const DecompressZIP As Long = &H214
 
Public Function UnZipFile(ByVal ZipFile As String, ByVal DestFolder As String, _
Optional AllFiles As Boolean = False, Optional Fname As String = "") As Boolean
Dim fld As Shell32.Folder
Dim fld2 As Shell32.Folder
Dim objShell As Shell32.Shell
Dim oItems As Shell32.FolderItems
Dim oItem As Shell32.FolderItem
 
If Left(DestFolder, 4) = ".zip" Then
    GoTo EXITME
End If
Set objShell = New Shell32.Shell
Set fld = objShell.Namespace(ZipFile)
Set fld2 = objShell.Namespace(DestFolder)
If AllFiles Then
    Set oItems = fld.Items
    fld2.CopyHere oItems, DecompressZIP
Else
    If Fname = "" Then 'If filename is blank and AllFiles not selected don't know what to extract
        GoTo EXITME
    End If
    
    For Each oItem In fld.Items
        If oItem.Name Like Fname Then
            fld2.CopyHere oItem, DecompressZIP
        End If
    Next oItem
    
End If
UnZipFile = True
EXITME:
Set oItem = Nothing
Set oItems = Nothing
Set fld = Nothing
Set fld2 = Nothing
Set objShell = Nothing
End Function

Then I call this module through a loop statement:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Files")
Do Until rs.EOF
UnZipFile (rs("FPath")) & (rs("FName")), "C:\Report_Data\Accounts\PNC\Data\Log Parser", True
rs.MoveNext
Loop

Set rs = Nothing

"Files" is the name of my table where the file paths are stored. Thank you so much for your assistance, I really appreciate it.

 

[CJ_London]

Well, you are unzipping a file at a time, but across the network. So the only change you need to make is to copy it first and then unzip.

Create a temporary directory on the C drive before you start your loop, then during the loop, copy the file to the temporary directory and then delete it once the loop is completed - note the change to the call to UnZipFile:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Files")
[COLOR=red]on error resume next[/COLOR] [COLOR=seagreen]'in case the directory already exists[/COLOR]
[COLOR=red]mkdir "C:\Tempzip" [/COLOR][COLOR=seagreen]'create a temporary directory, will generate an error if directory already exists[/COLOR]
Do Until rs.EOF
    [COLOR=red]FileCopy rs("FPath") & rs("FName"), "C:\Tempzip\" & rs("FName") [/COLOR][COLOR=seagreen]'copy file from network to temporary directory[/COLOR]
    UnZipFile [COLOR=#ff0000]"C:\Tempzip\"[/COLOR] & rs("FName"), "C:\Report_Data\Accounts\PNC\Data\Log Parser", True
    [COLOR=red]Kill "C:\Tempzip\" & rs("FName") [/COLOR][COLOR=seagreen]'delete the temporary file[/COLOR]
    rs.MoveNext
 
Loop
[COLOR=red]rmdir "C:\Tempzip" [/COLOR][COLOR=#2e8b57]'delete the temporary directory[/COLOR]
[COLOR=red]rs.close[/COLOR] [COLOR=seagreen]'better practice![/COLOR]
Set rs = Nothing

 

[LadyDi]

That works perfectly. Thank you so much. I ran it on my computer and it ran about 8 minutes faster than the way I had it originally --and I have a good network connection. I'm sure it will save my co-worker even more time.

 

[CJ_London]

Pleased to help - Saved 8 minutes over how many? Will be interested to know how much time your co-worker saves.

 

[LadyDi]

It originally took me 22 minutes to completely run through this process as it was written originally. When I made the modifications you suggested, the process ran in 14 minutes. I just heard back from my co-worker and yesterday this process took her at least 3 hours. Today, after I made the changes to the database, she was able to run the process in 1 hour. She has saved about 2 hours processing time. This is wonderful.

Thank you again for your assistance. It has been invaluable.

 

[CJ_London]

Result! Looks like a 33% saving on your machine is reflected as a 67% saving on your co-workers

There may be more that can be done which you can experiment with, it will use the same commands but in a slightly different order.

For example, copy all the zipped files first, then run the unzip, which would look like this:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Files")
on error resume next 'in case the directory already exists
mkdir "C:\Tempzip" 'create a temporary directory, will generate an error if directory already exists

Do Until rs.EOF
    FileCopy rs("FPath") & rs("FName"), "C:\Tempzip\" & rs("FName") 'copy file from network to temporary directory
    rs.MoveNext
Loop
 
rs.movefirst
Do Until rs.EOF
    UnZipFile "C:\Tempzip\" & rs("FName"), "C:\Report_Data\Accounts\PNC\Data\Log Parser", True
    Kill "C:\Tempzip\" & rs("FName") 'delete the temporary file
    rs.MoveNext
Loop

rmdir "C:\Tempzip" 'delete the temporary directory
rs.close 'better practice!
Set rs = Nothing

The other thing which may also improve performance is DoEvents but not something I have really needed to use in the past so you would need to investigate further as to how best to apply.