Solved

Faster File Search?

Posted on 2011-09-04
16
337 Views
Last Modified: 2012-05-12
Hi Experts,

I'm using the code below to check for the existence of a file before processing it. The code is running very slowly over the network - is there a faster way to do this? Thanks.
csvfname = Form_frm_params.CSV_Enq_Fldr & "provbook_" & Form_frm_add_quotations.Lead_Name & "_" & Form_frm_add_quotations.Booking_ID & ".fmencoded"
'MsgBox "csvfname=" & csvfname
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(csvfname) Then
    Call process_provbook_form(csvfname)
Else
    MsgBox "There isn't a Prov Booking file for this Client"
End If
Set fs = Nothing

Open in new window

0
Comment
Question by:redpoppy
  • 5
  • 5
  • 4
  • +1
16 Comments
 
LVL 75
ID: 36481699
I've used that code several times w/o any performance issue.  You might try Early Binding instead by setting a Reference to the Microsoft Scripting Runtime (see image) ... then try this code:

    csvfname = Form_frm_params.CSV_Enq_Fldr & "provbook_" & Form_frm_add_quotations.Lead_Name & "_" & Form_frm_add_quotations.Booking_ID & ".fmencoded"
    'MsgBox "csvfname=" & csvfname
   
     With New FileSystemObject

        If .FileExists(csvfname) Then
            Call process_provbook_form(csvfname)
        Else
            MsgBox "There isn't a Prov Booking file for this Client"
        End If
    End With

Capture1.gif
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36481725
You might want to sprinkle in some displays of Time to get an idea of exactly what statement(s) are causing the delay.

~bp
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36481915
It shouldn't be a delay at all, unless you've got network problems.

It might actually be the process_provbook_form procedure that takes a long time.

Above that call, if you put
MsgBox "Prov Booking found for this client.  Processing..."

then that should come up immediately.

Rob.
0
 

Author Comment

by:redpoppy
ID: 36483545
Hi All,

Thanks for your comments.

I changed the code as below to put in MX's suggestions and to monitor time, as per "biilbrew" and I also chose a record where I know the file does not exist i.e: it should just go to the 'MsgBox "There isn't a Prov Booking file for this Client"' statement, thereby making sure the problem wasn't in the "Call process_provbook_form(csvfname)" routine.

I tested this on my standalone PC so that network issues weren't involved and I found that the instruction it's sticking on is "If .FileExists(csvfname) Then" - it takes approx 10 seconds before displaying the message box. On the multi-user network, this is much longer - any thoughts?


MsgBox "1:time=" & Time
csvfname = Form_frm_params.CSV_Enq_Fldr & "provbook_" & Form_frm_add_quotations.Lead_Name & "_" & Form_frm_add_quotations.Booking_ID & ".fmencoded"
MsgBox "2:time=" & Time 'MsgBox "csvfname=" & csvfname
'Set fs = CreateObject("Scripting.FileSystemObject")
With New FileSystemObject
MsgBox "3:time=" & Time
    If .FileExists(csvfname) Then
MsgBox "4:time=" & Time
        Call process_provbook_form(csvfname)
    Else
MsgBox "5:time=" & Time
        MsgBox "There isn't a Prov Booking file for this Client"
    End If
End With
MsgBox "6:time=" & Time

Open in new window

0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36483577
Just curios, if you do this command from a DOC command prompt in the same configuration, does it delay as well before returning? (replace filename with the exact same value that csvname had above)

if exist "filename" (@echo YES) else (@echo NO)

~bp
0
 

Author Comment

by:redpoppy
ID: 36483709
Hi billprew - I tried your DOS prompt suggestion and it was an instant return!
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 36483723
If you're using Access VBA, can you use Dir?
http://office.microsoft.com/en-us/access-help/dir-function-HA001228824.aspx

Rob.
0
 

Author Closing Comment

by:redpoppy
ID: 36484202
Rob, Thanks for that - for whatever reason there was no delay when I used the following code:

If Dir(csvname) <> "" Then
    Call process_provbook_form(csvfname)
Else
    MsgBox "There isn't a Prov Booking file for this Client"
End If
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 65

Expert Comment

by:RobSampson
ID: 36486123
Great.  Odd.  It may be something to do with Access having to use the Microsoft Scripting Runtime.

Just out of curiosity, if you take that code out into a stand-alone VBS file, and try it, does it return immediately?

Rob.
Set fs = CreateObject("Scripting.FileSystemObject")
csvfname = "F:\YourFolder\YourFile.csv"
If fs.FileExists(csvfname) Then
    MsgBox "File found"
Else
    MsgBox "File not found"
End If

Open in new window

0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36486143
Also curious, are you using a mapped drive to access the network resource, or just a network path like (\\server\share\folder\file.txt)

~bp
0
 

Author Comment

by:redpoppy
ID: 36487563
Rob - not sure what you mean by "stand-alone VBS file" - can you give me a bit more info on how to set that up and test it?

billprew - on the live version it uses a network path, on my testing it's just the directory - although it does have to drill quite a long way down e.g: "C:\Users\Steve\Documents\1nmtest\csvenq\provbook__0.fmencoded" - might that be a factor?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36487864
A stand alone VBS file is created by copying the code I posted into Notepad, then doing a "Save As" and giving it a name like CheckForFile.vbs, making sure it has the VBS extension. Then simply double click the file to run it.

Rob.
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36488187
Quite an interesting question, still odd the gap in performance.  Also read this page, which sort of implies the opposite, although it doesn't specifically relate to network drives.  I think I'll try a little test here just to see how the performance of the two options compares from a VBA project...

http://msdn.microsoft.com/en-us/library/t4a2w9xx%28v=vs.80%29.aspx

~bp
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36489018
Well, for what it's worth, I used the following small VBA subroutine for a test here, and for either method the elapsed time was always .01 secs or less.  Not sure why you are seeing such an issue at your end.

Sub Test()
    csvfname = "\\server\share\d1\d2\d3\d4\d5\d6\d7\file.txt"
    Set fs = CreateObject("Scripting.FileSystemObject")
    t1 = Timer
    If fs.FileExists(csvfname) Then
        t2 = Timer
        MsgBox "Exists - " & Format(t2 - t1, "Fixed")
    Else
        t2 = Timer
        MsgBox "Missing - " & Format(t2 - t1, "Fixed")
    End If
    Set fs = Nothing
    
    t1 = Timer
    If Dir(csvfname) <> "" Then
        t2 = Timer
        MsgBox "Exists - " & Format(t2 - t1, "Fixed")
    Else
        t2 = Timer
        MsgBox "Missing - " & Format(t2 - t1, "Fixed")
    End If
End Sub

Open in new window

~bp
0
 

Author Comment

by:redpoppy
ID: 36494623
Rob: I tried the standalone VBS file and the result was immediate! So no idea what's going on but the problem has been solved.

Thanks again for your help and the billprew for suggestions.
0
 
LVL 75
ID: 36496843
FWIW ... I just tested both ways in a project I'm working on now (using FSO) ... and the results are pretty much identical ... super Fast.  And this in over our 1Gb fiber optic WAN, with a 60 mile round trip between local workstations and our shared drive - at a location 30 miles away.

mx
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now