Slow Code

[Ernst Guckel]

Any ideas why this runs slow?? Is there a faster way??

'Process Archives for old or missing data
For i = 0 To DateDiff("d", dStartDate, Now())
strSalesDate = Format(dStartDate + i, STRING_IMPORTDATE)
strPath = strStoreNumber & "_" & strSalesDate

If Not IsNull(DLookup("[Sales]", TABLE_SALES, "[SalesDate] = " &
Format(dStartDate + i, STRING_DATE))) Then
GoTo Looping
End If

strFileSource = STR_ARCHIVEPATH & "\" & strPath & "\" & STR_SOURCESALES

strCaption = "Importing - " & Format(dStartDate + i, "dd-mmm-yy")

With Forms("frmImport")
.Controls("lblStatus").Caption = strCaption
.Repaint
End With

For t = 1 To 10
Next

FileCopy strFileSource, strFileDest

Thanks,
Ernst.


DoCmd.SetWarnings (False)
DoCmd.TransferText acImportDelim, STR_IMPORTSALES, TABLE_SALES,
strFileDest
DoCmd.SetWarnings (True)

Looping:
Next

 

[Albert D. Kallal]

Any ideas why this runs slow?? Is there a faster way??

An access application can likely execute 30, to 80 million instructions in
one second with ease.

I suspect the code speed can't be changed much here, and it is the speed of
the operating system for the file copy, and also that of the text file
inport.

You don't mention how large those tables of text files your importing, but
if they are significant in size you might be able to change a few things in
the importing process to speed it up a bit. You could for example ensure
that there are no indexes in the table that you're using for the importing
process, and in that case and its axis can run considerably faster.

For t = 1 To 10
Next

Any idea why you have the above code? It is not doing anything of any use,
and as mentioned since MS access can generate and run about 30 to 18 million
instructions and one second, the above little loop will execute in 10/80
million =

0.000000125

That's not even a millionth of a second delay....

'Process Archives for old or missing data
For i = 0 To DateDiff("d", dStartDate, Now())

In the above line of code, I suspect the user should be using the date
function, and not now() function.

The now function has the time portion to the date function, and in this case
it will not cause a problem, but as a general coding practice you should
avoid using now if you don't need it. However the above two issues are not
significant in terms of program execution and performance.

DoCmd.TransferText acImportDelim, STR_IMPORTSALES, TABLE_SALES,
strFileDest

it is possible that you're importing hundreds and hundreds of small text
files, and what I would suggest you do in this case is considered
concatenation of all the zillion little text files together into one big
text file, and then try importing that. You've not mentioned the number of
files your are importing, and if the number of files is not that large, then
this suggestion likely will not help a lot. On the other hand if you're
talking thousands of small text files, then you might be better off to
preprocessor the text files into several larger text files, and then import
those into access in one shot.

Regardless, I don't see anything in your code that can be changed
significantly to speed up the access processing of this. You're stuck
waiting for the filecopy (windows os), and the text file transfer (again
waits for windows os). So, both of these procesces rely on the windows
operating system, and are outside of MS access.

There is the issue on how many text files you have to import, it's not clear
if it's the number of files in your importing that's the problem, or the
actual volume of data inside of each file. It's also possible that things
are slowing down because of the file copy is going into a very very crowded
directory, or is a newtwork file copy that is slow

I would consider putting at a few debug.prints in your code to see which
area slowing down the most, and you might be able to come up some ideas
here.

 

[Ernst Guckel]

Ok.. here's a bit more info:

'Process Archives for old or missing data
For i = 0 To DateDiff("d", dStartDate, Now())
strSalesDate = Format(dStartDate + i, STRING_IMPORTDATE)
strPath = strStoreNumber & "_" & strSalesDate

String_Importdate stores the first importable date within the sales data
folder on the PC. Once imported the process for getting data flies until it
gets to yesterday's data folder.

If Not IsNull(DLookup("[Sales]", TABLE_SALES, "[SalesDate] = " &
Format(dStartDate + i, STRING_DATE))) Then
GoTo Looping
End If

strFileSource = STR_ARCHIVEPATH & "\" & strPath & "\" & STR_SOURCESALES

strCaption = "Importing - " & Format(dStartDate + i, "dd-mmm-yy")

With Forms("frmImport")
..Controls("lblStatus").Caption = strCaption
..Repaint
End With

For t = 1 To 10
Next

I use this because the .Controls("lblStatus").Caption = strCaption didn't
repaint right. Sometimes it hung up and sometimes it just stopped updating
the user... A little look seem to let Access catch up with itself.

FileCopy strFileSource, strFileDest

DoCmd.SetWarnings (False)
DoCmd.TransferText acImportDelim, STR_IMPORTSALES, TABLE_SALES,
strFileDest
DoCmd.SetWarnings (True)

And the last question you had I believe was how much data was in each text
file...
There are 96 records of about 10 fields each. Each import is a days worth
of data. I cannot string them together because I get them a day at a time..
With the exception of the first run which gets all previous days data.

Oh... I have to copy the file becuase Access does not seem to want to import
a CSV formatted file without the .CSV extention. The filename originally is
Works020.cdf. I could skip a step if I could import the original file rather
than the copy....

Ernst.

 

[Albert D. Kallal]

Ok.. here's a bit more info:

'Process Archives for old or missing data
For i = 0 To DateDiff("d", dStartDate, Now())
strSalesDate = Format(dStartDate + i, STRING_IMPORTDATE)
strPath = strStoreNumber & "_" & strSalesDate

Some good extra info, thanks. You don't mention how many files on average
your processing in this loop. As I said before the speed of your code is not
going significantly affect this import system. as I said before, if it's
several hundred files that you're doing, then perhaps we eliminate the copy
command, and simply use a rename command (this is called the "name" command
in access).

strFileSource = STR_ARCHIVEPATH & "\" & strPath & "\" & STR_SOURCESALES

strCaption = "Importing - " & Format(dStartDate + i, "dd-mmm-yy")

With Forms("frmImport")
.Controls("lblStatus").Caption = strCaption
.Repaint
End With

For t = 1 To 10
Next

I use this because the .Controls("lblStatus").Caption = strCaption didn't
repaint right. Sometimes it hung up and sometimes it just stopped
updating
the user... A little look seem to let Access catch up with itself.

Ok...the repaint should have worked. if you want, you can remove the
Repaint, and put in a

doEvents

command in place of the loop + the repaint

(however, once again these issues have no significance in terms of the
performance issues were trying to deal with here)

And the last question you had I believe was how much data was in each text
file...
There are 96 records of about 10 fields each. Each import is a days worth
of data. I cannot string them together because I get them a day at a
time..

Well, we could write some code to string them together to make one fast
import, and that could significantly alter performance. So I well understand
you get these files one at a time, but I'm suggesting the *possible* idea of
doing some preprocessing to increase the speed of the import by a
significant amount. If we're to take 100 files, and convert them into one
file, then we would only do one transfertext file command, and that would
increased the speed of our import by 100 times (less the time of the code to
run and convert the text files into one text file).

Oh... I have to copy the file becuase Access does not seem to want to
import
a CSV formatted file without the .CSV extention. The filename originally
is
Works020.cdf. I could skip a step if I could import the original file
rather
than the copy....

As I said, you could put a little timer code to see if it's the transfertext
or in fact the file copy that slowing down the overall speed here. As I
said, the speed of executing each command is around the 80 million per
second mark, therefore the only way we can speed up this code is to limit
the number of times we execute the file copy (waits for windows os), and the
transfertext command (which again waits for windows).

here's what I mean by trying to find out is that the file copy or transfer
text that slow:

dim fTime as double ' file copy time
dim iTime as double 'import trans time
dim t as double ' timer


t = timer

FileCopy strFileSource, strFileDest

t = timer - t
fTime = fTime + t

Do The same idea for the import text command you're using.

and, at the very end of your code simply go

debug.print "file copy time = " & fTime
debug.print "input time = " & iTime

That way you'll get an idea if it's the actual file copy time, or the
transfer text time that's running slow. If it's the file copy code, then we
modify our code to rename the files in place of copying them. If it's the
transfer text command, that we explore some ideas like disabling the
indexing, or perhaps concatenation several text files into one.

You could also start (set) anohter variable with the timer value at the very
beginning of the code, and then take the difference at the VERY end of the
code. That way we can subtract that value and find out how much time is
taking to execute your actual code in your application (as compared to the
file copy, and transfer text command). As I stated before you'll find it
VERY VERY small amount (my bets are in the 30 million instructions per
second range by the way). in other words if we limit the time taken by the
file copy command, and also that of the transfer text command, you'll find
the rest of the codes execution speed is completely insignificant in this
situation.