|
I have a legacy app that reads a lot of config settings from several Excel files.
The app reads the settings from Excel using OLE. I think it's slow.
Some ideas about the best way to speed up the reading ?
Excel is used because our customer asked for it (every sheet is a grid of values and it's an easy way for them to change the values)
|
|
|
|
|
aoe wrote: The app reads the settings from Excel using OLE. I think it's slow
Are the cells by any chance read one by one?
In that case it can be very slow because each call is made out-of-process since Excel runs as such server.
I've encoutered this once when writing a huge amount of data into an Excel sheet and it took about 40 minutes to complete the operation. Then I realized that it could all be done in one single call by using a multidimensional SafeArray. The only thing is that the SafeArray can only hold one datatype at a time, i.e. you cannot read headings for the columns in the same call as numeric data.
After I made the change the operation completed in less than 10 seconds.
"It's supposed to be hard, otherwise anybody could do it!" - selfquote "High speed never compensates for wrong direction!" - unknown
|
|
|
|
|
I read the cells in a big chunk, using an Excel range.
Maybe I have reached the limit for speeding up that operation ?
|
|
|
|
|
How much data are we talking about, what datatypes, how long time does it really take and how long time would you expect it to take?
"It's supposed to be hard, otherwise anybody could do it!" - selfquote "High speed never compensates for wrong direction!" - unknown
|
|
|
|
|
I read some 30 Excel files. Amount of data in each file varies from 50 to 600 cells.
Total time is in the range of 4 seconds (P4 2.4 GHz, 1GB RAM, XP SP2, Excel 2003)
From the previous reply, I suppose a big amount of the time comes from the out-of-process switch.
It's not a big problem in my machine, but some users have older PC's and I've been trying to improve the startup time. Excel reading was one of the targets and I've improved that, caching some values. I have to check the library used to access Excel, because I think it's doing some redundant work.
Do you have some criteria to decide when to stop improving that way (because of the limits of using Excel that way) and go to another method ?? Maybe text files or a database would do the work, but currently our customer is very happy with Excel (he can change settings very easily), and I don't want to build a front end interface for another method.
|
|
|
|
|
aoe wrote: I read some 30 Excel files. Amount of data in each file varies from 50 to 600 cells.
The amount of data is ridiculously small.
In my case it was a couple of hundred thousand cells.
What takes time in your case is probably dealing with so many different Excel files.
For each file you have to make a large number of calls to Excel before you can actually read the data. Reading the data in this case is neglectable in the aspect of required CPU time.
aoe wrote: currently our customer is very happy with Excel....and I don't want to build a front end interface for another method.
I can appreciate that. Using Excel for such purpose can be justified in many ways so in my opinion you shouldn't change that approach unless being forced to for some reason.
aoe wrote: Do you have some criteria to decide when to stop improving that way.....and go to another method?
Well, a kind of.
I usually think that when things get unreasonably complicated I suspect that I'm doing something wrong and try to find another way to do it. This is mainly at design level.
If I was in your situation I would first measure what operations that consume the most CPU time by measuring it to prevent me from solving the wrong problem. I would compare the amount of time required for opening the files to the time required for reading the data. I would expect to find that opening the files is the culprit by far, but I would like to make sure.
If I would find that opening the files is the performance bottleneck, I would try opening the files directly instead of using Excel for that. This can get somewhat complicated and you would have to know the Excel file format.
You can find the Excel file format here[^], but there's another thing that you have to care about: given the complexity of this solution you have to get approval from your customer before starting.
You have to ask your customer if he/she think it's worth 2-4 weeks of implementation to save those 4 seconds and still using Excel. The amount of time it will take is dependant on your skill and how generic you want the solution.
"It's supposed to be hard, otherwise anybody could do it!" - selfquote "High speed never compensates for wrong direction!" - unknown
|
|
|
|
|
Roger Stoltz wrote: I can appreciate that. Using Excel for such purpose can be justified in many ways so in my opinion you shouldn't change that approach unless being forced to for some reason.
Yes. I think i'm not going to change that. I even considered using Excel as a front end for my customer but saving data in another format (plain text, database), but I think is too much work (and adding complexity to the process -> more errors possible).
Roger Stoltz wrote: If I would find that opening the files is the performance bottleneck, I would try opening the files directly instead of using Excel for that.
I don't want to go so far. I suppose that the bottleneck is in the opening of files AND in some redundant work in my library. I'm going to review the library to improve that. It's probably a good compromise "improvement / work".
Thank you very much for your help.
|
|
|
|
|
Hi,
why does the view, which will be destroyed get the focus during destruction?
Anyone an idea?
Thanks
|
|
|
|
|
Hi,
I have to create a application which parses the xml data,for that i have seperate parser,
Function to accept Xml file from the user
Storing the file content in a buffer
passing the buffer pointer in the parser...
How can i implement this tasks,Please help out with your suggestions.
Thanks in advance.
Siddharth
|
|
|
|
|
siddharthsan wrote: How can i implement this tasks,
Start with one (task), get it working, and then start on another. Don't try to do it all at once.
"Approved Workmen Are Not Ashamed" - 2 Timothy 2:15
"Judge not by the eye but by the heart." - Native American Proverb
|
|
|
|
|
hi
calling a lot GlobalAlloc function, does it create problem for exe?
Zo.Naderi-Iran
|
|
|
|
|
if you don't free the memory then, yes
|
|
|
|
|
the allocated memory is using in endless loop, then i don't free it.
do i allocate mamory (to call GlobalAlloc) in begin of loop then free it end of loop?
Zo.Naderi-Iran
|
|
|
|
|
dude, do you think the memory is infinite ?
all you'll get is an error saying that you cannot allocate much memory...
moreover, if you don't free the memory you allocate, you're going into leaks, obviously.
|
|
|
|
|
what do i do????
i create a thread, and this thread has an endless loop. in this loop i call api functions , they needs memory.
do i allocate mamory (to call GlobalAlloc) in begin of loop then free it end of loop?
Zo.Naderi-Iran
|
|
|
|
|
all depends what you really need to do, but you seem to have a serious design problem.
can you post a relevant piece of code ?
|
|
|
|
|
my code is secret... thank you
maybe problem for my exe has other cause.
anyhow if you can, you help me
thank you
-- modified at 7:24 Tuesday 27th March, 2007
Zo.Naderi-Iran
|
|
|
|
|
i don't ask you a part of your secret code... i want relevant information so that i can understand the problem, and help you.
a relevant piece of code is not a entiere copy/paste of your cpp file. i want you to show us a small function which can reproduce the problem.
and if you can't, then at least provide full comments in your question, because i doubt anybody can help you with what you gave here
|
|
|
|
|
For example: i call EnumProcesses function in endless loop (in thread),
first parameter of this function is a Pointer to an array that receives the list of process identifiers.
i call GlobalAlloc function to allocate memory with 1024 bytes or more for size (befor endless loop), then i send pointer to this memory (return value of GlobalAlloc) to EnumProcesses function :
__asm<br />
{<br />
mov ecx, 4096
push ecx<br />
push GMEM_FIXED<br />
call GlobalAlloc
mov dword ptr[ebp + 0x48], eax
<br />
EndlessLoop:<br />
<br />
lea ecx,dword ptr[ebp + 0x49]
push ecx<br />
mov ecx, 4096<br />
push ecx<br />
mov edx,[ebp + 0x48]<br />
push edx<br />
call EnumProcesses
<br />
<br />
<br />
<br />
jmp EndlessLoop<br />
<br />
}<br />
i add a code section to an exe. this section creates thread white endless loop. all of this codes should be assembly language
Zo.Naderi-Iran
|
|
|
|
|
sorry, i talk C++, not asm.
so, as i ***still*** don't know what you want to achieve with this, I can't help you much
|
|
|
|
|
anyhow, thank you
Zo.Naderi-Iran
|
|
|
|
|
|
That's really strange... everywhere you write to me, you answer with an agressive speaking, and i get a low vote.
do you have any problem understanding what CP is for ?
if you roam here not to help anyone but to annoying people who try to help other, i really don't understand what you're here for
|
|
|
|
|
DavidCrow answered your question but looking at your code, you're not allocating memory in the
loop so what are you worried about?
"If you can dodge a wrench, you can dodge a ball."
|
|
|
|
|
zoncpp25 wrote: do i allocate mamory (to call GlobalAlloc) in begin of loop then free it end of loop?
Yes, assuming you know how much you need before the loop starts. If not, you have no choice but to do it in the loop itself.
An alternative is to figure out a worst-case scenario (e.g., 2000 * sizeof(DWORD) ) and allocate for that. While it will likely be too much most of the time, that's a whole lot cheaper (from the Memory Manager) than a ton of smaller requests.
"Approved Workmen Are Not Ashamed" - 2 Timothy 2:15
"Judge not by the eye but by the heart." - Native American Proverb
|
|
|
|