CPU Utilization for Excel -> Fortran DLL

[lrpartin]

I wrote an application with a Fortran DLL that does numerical calculations for an Excel spreadsheet. I am concerned over its execution speed. I get fast execution on the development computer but it runs much slower when I move to another computer. The CPU utilitization is <10% for the second computer whereas it reaches 100% often for the development computer. What would slow down the second computer?

What files do I need to transfer to the second computer? I copied the following files:
model.dll (my new fortran DLL)
dforrt.dll (fortran run-time library)
model.xls (spreadsheet file)

The spreadsheet runs fast on a Fortran development computer on Win98 and another Fortran development computer on Win2000. The second computer that runs slow is Win2000 without Compaq Visual Fortran.

Thanks,
Lee

[Steven_L_Intel1]

If it runs at all, you've got all the files you need.

My guess is that the second computer has insufficient RAM and that you're thrashing the pagefile.

Steve

[lrpartin]

Steve,

The computers have the following RAM:
Win 98 Fortran development computer = 96meg
Win 2000 Fortran development computer = 384meg
Second Win 2000 computer without CVF = 256meg

I suppose it could be ram dependent. The fortran dll is 217,088 bytes. It seems so small to cause a ram problem. What could I check about this issue?

I am using Excel 2000 SR-1 on all of the test computers.

Here is another weird fact about it. The Excel spreadsheet/DLL runs fast on the Win 2000 CVF computer sometimes. It runs at high CPU utilitization if I start Excel and load the spreadsheet. If I load another spreadsheet, then it slows down greatly with low CPU utilization. The same thing happens if you click on the open button of Excel, move to a new folder and then cancel the open window. You don't have to open a new file to cause the slow down.

I experienced weird issues in past versions of Excel and DVF. One past solution was to re-enter the VBA function call within the Excel spreadsheet. That solution doesn't work now.

Any recommendations?

Thanks,
Lee

[Steven_L_Intel1]

I'd run the system monitor and look at memory usage and swapfile activity... From your description, that's the only thing that comes to mind.

Steve

[lrpartin]

Steve,

I opened the windows task manager in Win 2000 and ran the Excel program / Fortran DLL. Here are the results:

CPU Usage
Excel.exe 0 - 8%
System 0 - 2%
System Idle Process 90+%
taskmgr.exe 0 - 2%

Totals
Handles 5425 - 5429
Threads 301 (constant)
Process 31 (constant)

Commit Charge (K)
Total 127750 - 127760
Limit 435592 (constant)
Peak 129124 (constant)

Physical Memory (K)
Total 260332 (constant)
Available 116550 - 116650
System Cache 157110 - 157125

Kernel Memory (K)
Total 42048 - 42048
Paged 31540 - 31570
Nonpaged 10504 (constant)

The system is waiting idle most of the time instead of doing the fortran numerical calculations. How do I find out the reason for it?

Does the /base load option have a role in fixing this problem?

Thanks,
Lee

[Steven_L_Intel1]

Lee,

I doubt the /base has any relevance.

I'm now out of ideas. Anyone else?

Steve

[Jugoslav_Dujic]

My experience with Win2000 compared with Win98 is only positive,
both regarding speed and stability, so I'm a bit surprised if such
operation is slower than in Win98. I can only gave you few ideas
what to try:

a) insert time-measurements in critical places (e.g. between
subroutine calls) in fortran code and write results into a file.
This may give you an idea where is the narrow point. The simplest
way is to use SECNDS intrinsic:

 
fTime=SECNDS(0.) 
CALL Foo 
fTime=SECNDS(fTime) 
WRITE(11,"('Foo takes ',f10.2)") fTime 
CALL Foo2 
fTime=SECNDS(fTime) 
WRITE(11,"('Foo2 takes ',f10.2)") fTime 
... 

b) you can debug your dll from within CVF (on your win2k
development machine). Just type the path to excel.exe
in "Project/Settings/Debug/Executable for debug session"
box, do "Debug/Go" and do whatever is necessary in Excel
to invoke DLL.

My experience with Win2K vs. 98 is that a care should be
taken in initializing ALLOCATABLE and AUTOMATIC variables;
98 almost always initializes those to zero; NT/2K always fill
them with garbage. Perhaps there's something related with
it in your code? IMHO, if something works well in Win2000,
it will in 99% cases work well on Win9x (the 1% comes from
my forgiveness to UnhookWindowsHookEx or DeleteObject
in the code, causing Win98 to crash -- but that has nothing
to do with typical "number crunching"); the contrary does
not hold.

HTH

Jugoslav

[Intel_C_Intel]

The DLL runs fine on a Win2k dev PC and a Win98 dev PC, but not on a non-dev Win2k PC, right? It could be that the installation of Excel on that PC is the problem. Some thoughts,

- Is the problem reproducible on another Win2k non-dev PC or is it isolated to this one PC?

- Test calling the DLL on the problem PC but from a test VB app instead of Excel.

- You can create a 'junk' dll that has a routine that 'just spins its wheels' and see if that has problems too.

hth,
John

[lrpartin]

John, Thanks for the ideas on tracking it down.

The problem does not occur on the Win98 dev computer. The Win2k dev computer works in a special case which is:
1) start excel
2) open the spreadsheet and it runs at high CPU utilization
but there are various ways to cause low CPU utilization / long run times such as:
1) open a second spreadsheet
2) just use the open button, move to another folder and cancel the open
3) start with a spreadsheet that was saved from another computer - to make it run fast you must open it, run the DLL, save it, close excel, open excel, open spreadsheet.

What about converting the DLL to COM automation? Maybe that would fix the issue.

Thanks,
Lee

[lrpartin]

I tried the spreadsheet/DLL on another Win2k non-dev computer and it has the same problem.

Lee

[lrpartin]

I tried a spreadsheet/DLL from my past work in Win98 and it works fine on the Win2k systems.

Lee

[lrpartin]

It looks like nobody has the answer. The Excel / DLL link is flaky. At least it does the calculations; but slowly.

I'm using the method of writing a VBA function macro within a VBA module to call the Fortran DLL. I'll try the COM approach when I have time.

Lee

[Intel_C_Intel]

Do you think it's just an Excel thing?(likely) Is it worth trying to use the DLL from a straight VB app to see how that works?

How big is the workbook? Does it have alot of macros? Excel can run into some difficulties as you develop solutions with macros/forms. This can be solved by a process called 'code cleaning' : export each module and form to a text file, remove all of the modules and forms, save the workbook, and then re-import the saved modules and forms. I remember an add-in that automated this process. I've seen it work some magic when all else has failed.

good luck,
John

[lrpartin]

Thanks, John, for the tip about the code cleaning.

My current project is very simple in regards to VBA. Here is the full extext of the VBA code:

Declare Sub MULTISOLUTION2 Lib "CrimpDLL.dll" _
(ByRef nfil As Double, ByRef B2 As Double, ByRef q2 As Double, ByRef D02 As Double, ByRef cpi2 As Double, ByRef B1 As Double, ByRef q1 As Double, ByRef D01 As Double, ByRef cpi1 As Double, ByRef R2 As Double, ByRef R1 As Double, ByRef Initial_lbf As Double, ByRef PSI01 As Double, ByRef PSI02 As Double, ByRef results As Double, Error As Long)

Function CalculateModel(nfil As Double, B2 As Double, q2 As Double, D02 As Double, cpi2 As Double, B1 As Double, q1 As Double, D01 As Double, cpi1 As Double, R2 As Double, R1 As Double, Initial_lbf As Double, PSI01 As Double, PSI02 As Double) As Variant

Dim results(1 To 8, 1 To 11) As Double, Error As Long
Call MULTISOLUTION2(nfil, B2, q2, D02, cpi2, B1, q1, D01, cpi1, R2, R1, Initial_lbf, PSI01, PSI02, results(1, 1), Error)
If (results(1, 1) = 0 And Error > 0) Then
CalculateModel = "Error from initial profile calc = " & Error
Else
CalculateModel = results
End If
End Function

There is a single call to this array function within the spreadsheet.

I tested the code cleaner but it did not help.

The code cleaner may have helped in my past work on this project, though. I had a situation where the DLL calls did not function and I fixed it by re-creating the spreadsheet from scratch. Code cleaner is much easier. In the past, I had numerous interface calls to different subroutines in the DLL with lots more coding as VBA functions.

It seems to be an Excel issue. I also use VBA within Visio Technical 2000 so I tried the DLL from a Visio document. The DLL runs very fast within Visio on the non-dev Win 2000 computer and I don't know how to make it run fast on Excel 2000 SR1. As mentioned earlier, I did have a trick to get fast execution in Excel 97 by updating the array formula call within the spreadsheet.

Thanks,
Lee

[Intel_C_Intel]

If you're resorting to grasping at straws, just for kicks, try removing the ByRef keywords for the routine's args in the declare statement and see if it makes a difference (it's the default - note you don't have it on the Error arg).

Sorry, but I just don't have any good ideas.

-John

[lrpartin]

John,

I deleted the ByRef's and I still get the problem.

My previous note had an error. I tested the DLL call using Visio Technical 5 instead of Visio Technical 2000 and it worked well. I have not tested it on Visio Technical 2000.

Thanks,
Lee

[lrpartin]

I re-wrote the application as a COM automation DLL. The class works well in Win 98 / Excel 2000 SR1. It runs fast with 100% CPU utilization. The same problem as before occurs in Win2k/Excel 2000 SR1. The CPU utilization is very slow and the spreadsheet takes very long to calculate. It is slow for all of the Win2k computers that I've tried. My previous notes tell of special conditions for the spreadsheet to run fast on a CVF Win2k computer.

Lee

[Intel_C_Intel]

Hi,

Did you ever try either of the DLLs (plain or COM) with Excel 97 on the different OSs? I'm curious if the problem is more of a XL version issue or an OS version issue.

-John

[lrpartin]

John,

No, I have not tried the original DLL or the new COM DLL with Excel 97. I do not have access to a computer running Excel 97 on Win2k.

The previous notes mention my past problems with DLLs/Excel 97 on Win98. Then, I was able to solve the CPU slow down my re-entering the VBA function call within the spreadsheet.

Lee

[lrpartin]

I re-wrote the application as a VB6 ActiveX dll calling the original CVF dll for the numerical calculations. As before, the new application runs at high CPU utilization for Win98/Excel 2000 SR1 but at low CPU utilization for Win2k/Excel 2000 SR1.

I added a method to the VB class that does a large for-loop in VB to eat up time. When I access that method from Win2k/Excel 2000 SR1, I get 100% CPU utilization. Therefore, my VB dll does not have the slow down problem.

John asked about the possibility of Win2k/Excel 97 working ok. I have not tried it but I did try Win2k/Visio 5 as mentioned in a previous note and it worked. Visio has VBA 5 as does Excel 97. My problem may be centered on CVF 6.5 dlls for VB6 and VBA6 on Win2k.

Is this a problem for Compaq or Microsoft? or just me?

Lee

[Steven_L_Intel1]

I cannot imagine anything in CVF that could be related to this behavior.

Steve