redpoppy
asked on
Faster File Search?
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.
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
You might want to sprinkle in some displays of Time to get an idea of exactly what statement(s) are causing the delay.
~bp
~bp
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.
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.
ASKER
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(csvf name)" 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?
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(csvf
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
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
if exist "filename" (@echo YES) else (@echo NO)
~bp
ASKER
Hi billprew - I tried your DOS prompt suggestion and it was an instant return!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(csvf name)
Else
MsgBox "There isn't a Prov Booking file for this Client"
End If
If Dir(csvname) <> "" Then
Call process_provbook_form(csvf
Else
MsgBox "There isn't a Prov Booking file for this Client"
End If
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.
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
Also curious, are you using a mapped drive to access the network resource, or just a network path like (\\server\share\folder\fil e.txt)
~bp
~bp
ASKER
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\cs venq\provb ook__0.fme ncoded" - might that be a factor?
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\
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.
Rob.
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
http://msdn.microsoft.com/en-us/library/t4a2w9xx%28v=vs.80%29.aspx
~bp
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
~bp
ASKER
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.
Thanks again for your help and the billprew for suggestions.
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
mx
csvfname = Form_frm_params.CSV_Enq_Fl
'MsgBox "csvfname=" & csvfname
With New FileSystemObject
If .FileExists(csvfname) Then
Call process_provbook_form(csvf
Else
MsgBox "There isn't a Prov Booking file for this Client"
End If
End With
Capture1.gif