URGENT! How to make it to run faster

[Guest]

Hi

I have a report that I have to run it monthly in my machine.
My code in VB.NET and I access AS400 to get data, anaysie it and send into
pre formated Excel sheet. The data consist of 9000 rows.

I use data table and with for loop I send the data row by row in
pre-formated Excel sheet.

My machine is:
P4 3.0GHz with Hyperthreading
1 GB of memory

I also use thread technology on higest value to run the report.
If I run the report and not doing anything else with my PC the report will
created aproximetly in 4 hours.

Last weekend I put my application to another machine wich I recognize that
it has less configuration then my PC. P4 2.8Ghz + 996MB Ram with no
hyperthreading technology and report created in 8 hours time.

I know I did good programming but I still wonder why this reports takes long
time run. It looks like it writes each row in 1 sec to excel sheet. By the
way Excel sheet is not open as visualy but I opened it programaticaly to
write.

Here is my code:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Imports KR105U
Imports CLS_MAIN
Imports Microsoft.Office.Core
Imports Excel.XlHAlign
Imports Excel.XlLineStyle
Imports Excel.XlBordersIndex
Imports Excel.XlPattern
Imports Excel.XlDirection
Imports Excel.XlWindowView
Imports Excel.XlPageBreak
Imports System.IO
Imports System.Globalization
Imports System.Threading

Public Class clsWINEXCEL

'PRIVATE
Private UretimDate As String
Private row As DataRow
Private tmpTABLE As DataTable
Private myXRow As DataRow


'FRIEND
Friend WithEvents SaveFileDialog1 As System.Windows.Forms.SaveFileDialog

Public Sub New()
mWriteExcel()
End Sub


#Region " WRITE EXCEL w/DIVISION 1000 "

Private Sub mWriteExcel()

'Save the current Regional Settings
Dim thisThread As System.Threading.Thread =
System.Threading.Thread.CurrentThread
Dim originalCulture As System.Globalization.CultureInfo =
thisThread.CurrentUICulture

'Create the New EXCEL Process
Dim oXL As Object = CreateObject("Excel.Application") 'Excel.Application
Dim oWB As Object 'Excel.Workbook
Dim oST As Object 'Excel.Sheet
Dim oRng As Excel.Range 'Excel.Range

Try
thisThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
oWB = oXL.Workbooks.Open(FileName:=clsConst.m_ExcelFile,
UpdateLinks:=False, ReadOnly:=False)
Dim oSheet As Excel.Worksheet


With oWB.ActiveSheet
.Cells(3, 4).value = "DÖNEM: " & clsConst.m_DONEM_TARIHI
.Cells(4, 4).value = "ÃœRETIM: " & UretimDate

oSheet = oWB.ActiveSheet

Dim myRow As Integer = 13
Dim mySIRANO As Integer = 1
Dim mySIRAWORD As String = ""

For Each row In tmpTABLE.Rows
'ID NO
.Cells(myRow, 1).value = row(0)


'RG CODE
.Cells(myRow, 2).value = row(1)


'RG NAME
.Cells(myRow, 3).value = row(2)


'FK NAME
.Cells(myRow, 4).value = row(3)


'KSID NO
.Cells(myRow, 5).value = row(4)


'TYPE CODE
.Cells(myRow, 6).value = row(5)


myRow = myRow 1
Next



In the For loop it takes 1 second to write each row.

I ask my question before and it seems there is noone to help me.

I am also wondering to buy new PC with dual CPU. But is this new PC with
DUAL CPU make it to run my report much fatser or not.

I thank you in advance for reading my post.

Rgds,
Niyazi

 

[Patrice]

COM interop is costly. I would minimize the number of COM interop calls.

You could try to load all the values in an array and affect this array to a
range. It will copy all the values in a single interop call instead of
making one interop call per cell...

Though it doesn't seem applicable in this case, along the same lines, it's
quicker to run a presentation macro in Excel (possibly created from a .NET
application using few interop calls) rather than to run the same code from a
..NET application with a COM interop call each time you hit Excel...

As a side note, marking a message as URGENT is considered as a bad practice
as you have no way to know other problems are not as urgent as yours...

 

[Code Rodent]

Just a small thought, have you considered creating a CSV file that could be
imported into Excel?

 

[Guest]

I agree with Code Rodent, office applications are by definition slow... I
know this first hand as I have written a template automation system in 2005
for Word....

Try not to write to excel directly, create a CSV and import it by code, into
excel when finished, it will run a lot faster!

Remove the imports for excel, when you don't need them...

good luck

 

[Guest]

Hi all,

To Patrice:
I am realy sorry that I use the URGENT keyword. I learn it now, and I will
not do it again. Thank you for kind understanding.

To Code and Michel:
Thank you very much for reading my post and giving me idea that I never
thought of.
Unfortunatly I don't know how to create a CSV and import it by code, into
excel when finished. Is it possible to show it to me by smal example.

To Michel:
What do you mean by "Remove the imports for excel, when you don't need
them.."?

I thank you all of you for your kind help.

Rgds,
Niyazi

 

[Adrian Gallero]

Hi,

Hi

I have a report that I have to run it monthly in my machine.
My code in VB.NET and I access AS400 to get data, anaysie it and send
into pre formated Excel sheet. The data consist of 9000 rows.

Here is my code:
.Cells(myRow, 1).value = row(0)

You can speed up this a lot by not writing one value at a time, but
writing arrays of values instead. There is a big difference there.

But if you really want to make this fast, you might want to try a third
party solution for writing xls files. My company develops one, at
www.tmssoftware.com/go/?flexcelnet
you might want to take a look, but a 9000 rows report should not take
more than a couple of seconds.

I am also wondering to buy new PC with dual CPU. But is this new PC
with DUAL CPU make it to run my report much fatser or not.

It will be much cheaper if you but a third party solution, and the
report will run much faster too With a faster CPU you might win
some minutes but I doubt you will get down to minutes.

Hope this helps
Best regards,
Adrian.

 

[Guest]

Hi Adrian,

Thank you very much your kind help. First I don't effort to but Third Party
product, but I check the link and it is very good product. Fortunetly help of
some MS KB team member made my report to run in 4 minutes in my PC and 14
minutes in the second PC that I desribe in above.

I use the XSD adapter as well as the I construct my code so I copied on to
the clipborad and pasted whithout loosing any formating from the data.

First I createa string call mDataHolder. In my for loop instead of writing
into excel row by row I add them to the string.

BEGIN TO LOOP
mDataHolder = mDataHolder & row(1) & vbTab
....
....
....
....
mDataHolder = mDataHolder & row(1) & vbCr
NEXT


The string delimited by Tab characters for column and rows are delimited by
carriage returns.

Then use the code below to copy into clipboard:
System.Windows.Forms.Clipboard.SetDataObject(mDataHolder)

After initial interop call I use similar to below code:
oBook.Worksheets(1).Range("A1").Select()
oBook.Worksheets(1)..PasteSpecial(Excel.XlPasteType.xlPasteAll)

Then everythings worked in 4 minute. The report analysis the data nearly 3
min 20 second. And it takes about 30 second to writes into Excel.

Thank you very much and I have to give my special thank to MS Support team.

Thank you MS.

Rgds,
GC

 

[Adrian Gallero]

Glad you could solve it!
I personally would not go with com operation anyway, but if it works
for you then it is good. Just 2 small comments:

1)

Then use the code below to copy into clipboard:
System.Windows.Forms.Clipboard.SetDataObject(mDataHolder)

I would make sure you clear the clipboard after pasting it into Excel.
If not, if your user by any change presses ctrl-v, it will paste the
whole 9000 rows in its application, and he probably will ont like this.
Probably you can even save the original clipboard contents, and restore
them after pasting in excel.

2)
As said on the previous post, if you really need to go with ole
automation, you can go much faster by setting the Range.Value to an
array, instead of a single value. I imagine it will work as fast as
copying from the clipboard, and you will not need to change your user's
clipboard. I am not sure on the syntax on .net (since luckily it has
been a lot of years since I had to do my last interop), but you can
probably search the interner for it, there should be plenty of
information about this.

Best regards,
Adrian.

 

[Guest]

Hi Adrian,
Thank you for comment. After I post my comment I realize that I should clear
the clipboard before inserting.

Simply for a few second I used API and lock the mouse and keyboard,
clear the clipboard and pasted into pre-formataed Excel sheet.

My problem was tha data hat I receive (in real-time) from AS400 DB2. The
database constructed nearly 10 years ago and had many user error. After I
received the data (in 49 sec) I analyz it (4 min) and re-formated as what
they want me to show and copy it into Excel sheet in 3 min.

Example the ID 1 have to be shown in 00001 format. The data I get from AS400
for custmer number nearly 5 characters but report says I have to show it in 4
characters and help nof the ms community I solved the all the problem. Expext
the insertaion into Excel. Now I solve it that via copying. If data was
already ready for Excel via AS400 then it will be perfect to use third party
solution as you describe above. But unfortunetly I have to create 23 reports
each month into Excel and as well as I make small program that converts Excel
sheet into notpad before we send it to destination database.

Once the 23 reports were okay it will goto management than they decide what
I should have to say. It means sometimes they play with Excel data because of
the inconsistent data that I work with. Once the 23 Excel sheet is ready for
send I recivied them by mail and convert then into notpad.

Part of the world that I am living doesn't have the great IT things anyway.
So I am stuck the garbiage data that was design 25 years ago and updated only
10 years ago.

My job real Job is Software Engineer, but currently I am working as
Programmer and IT Instructor. Things too worst in here comparing when I lived
in Japan nearly 6 years. People doesn't admit wheere the problem is.

So it wasn't too hard to work with but I have to keep change it allways
things I do, until I satisfy them. Till 9000 rows report everythings was
perfect. Because in Japan we used to AS400 to do the job and it does it in
few second. But the data that I work for has many user interface error. So if
the data missing I have to find one way or another. But I was realy upset
that it took 4 houers to complete 1 report.

Then actualy idea came from your third party soulution. All I did is cahne
the for loop to send the data into string and paste it into Excel. I worked
real hard solve this problem last 4 days and suddenly Friday morrning at 2.30
am came the idea that one way or another before the day finishes I have to
make it much fatsrer than what I did before. But I did a lot search and
reading and get many help from ms support kb team.

I realy find your third party product very useful. But I guess it is very
useful to normal programmer. I like the challange so so I combine your xsd
idea with copy-paste option and I did it. Friday I finshed my job at 11am.
And rest I enjoy to read the article in your third party product.

I created most of the class as in your third party product but it was very
time consuming. If I had some money I realy consider to buy the product for
my own use.


Again that me thank you and others for all your kind help.

Rgds,
GC

 

[Adrian Gallero]

Hi,

Thanks for the detailed comments, I always like to know what other
people think and does, and the reasons why.

Part of the world that I am living doesn't have the great IT things
anyway. So I am stuck the garbiage data that was design 25 years ago
and updated only 10 years ago.

Believe me, it is the same here. I have been trying to get a dual core
machine for months, and everybody looks at me like I came from another
planet. What's wrong with a pentium 4?? It has hyperthreading! they
tell me.

Then actualy idea came from your third party soulution. All I did is
cahne the for loop to send the data into string and paste it into
Excel. I worked real hard solve this problem last 4 days and suddenly
Friday morrning at 2.30 am came the idea that one way or another
before the day finishes I have to make it much fatsrer than what I
did before. But I did a lot search and reading and get many help from
ms support kb team.

Just a quick note. Make sure you use a StirngBuilder and not a real
string, it will be much faster too.

I realy find your third party product very useful. But I guess it is
very useful to normal programmer. I like the challange so so I
combine your xsd idea with copy-paste option and I did it. Friday I
finshed my job at 11am. And rest I enjoy to read the article in your
third party product.

I created most of the class as in your third party product but it was
very time consuming. If I had some money I realy consider to buy the
product for my own use.

Yes, understanding a new product is time consuming, but I think it does
pay the price many times. I am not sure if it is clear to you from the
demos, but there are actually 2 ways you can create an xls file with
our product:
1) Use it just like ole automation:
To create a simple file you just need code like:

XlsFile xls = new XlsFile(true);
xls.Newfile();
for (int row = 0 ; row < NumberOfRows; row++)
for (int col = 0; col < NumberOfCols; col++)
{
xls.SetCellValue(row +1, col +1, Data[row][Col]);
}

xls.Save("myfile");

2) you can create a template, fill the data in a dataset and run a
report. While this is more time consuming initially than 1), it lets
you change the format very easily once created, and even your customers
can do it. (without touching any code). For pretty output, reports is
normally the way to go, but if you want a quick dump you can do it too.

And about the money, I understand that not everybody has the money to
pay, and this is why we price it on the inexpensive range. We prefer to
sell a lot of products at 100 bucks to a lot of people, than a little
at 2000 bucks.

But I would not want money to be the reason why you don't use it. So if
you like, just send me a private email at
(e-mail address removed) and I will send you a full registered
version for free.

Best regards,
Adrian.

 

[Guest]

Hi Adrian,

The reason that I cannot afford to pay is that I had some bank credit for my
father hospital expense. After 17 years I return my home country and start
everything from 0. Price is realy cheap but as I said it is imposible for now
for me to buy it.

I red the all article but I didn't try it yet. It looks like it doeas many
job very easily and over come time consuming job for many programmers.

Thank you for affer and I wil send you mail.

Thank you for your kind undersatnding.

Rgds,
Niyazi

Hi,

Thanks for the detailed comments, I always like to know what other
people think and does, and the reasons why.

Part of the world that I am living doesn't have the great IT things
anyway. So I am stuck the garbiage data that was design 25 years ago
and updated only 10 years ago.

Believe me, it is the same here. I have been trying to get a dual core
machine for months, and everybody looks at me like I came from another
planet. What's wrong with a pentium 4?? It has hyperthreading! they
tell me.

Then actualy idea came from your third party soulution. All I did is
cahne the for loop to send the data into string and paste it into
Excel. I worked real hard solve this problem last 4 days and suddenly
Friday morrning at 2.30 am came the idea that one way or another
before the day finishes I have to make it much fatsrer than what I
did before. But I did a lot search and reading and get many help from
ms support kb team.

Just a quick note. Make sure you use a StirngBuilder and not a real
string, it will be much faster too.

I realy find your third party product very useful. But I guess it is
very useful to normal programmer. I like the challange so so I
combine your xsd idea with copy-paste option and I did it. Friday I
finshed my job at 11am. And rest I enjoy to read the article in your
third party product.

I created most of the class as in your third party product but it was
very time consuming. If I had some money I realy consider to buy the
product for my own use.

Yes, understanding a new product is time consuming, but I think it does
pay the price many times. I am not sure if it is clear to you from the
demos, but there are actually 2 ways you can create an xls file with
our product:
1) Use it just like ole automation:
To create a simple file you just need code like:

XlsFile xls = new XlsFile(true);
xls.Newfile();
for (int row = 0 ; row < NumberOfRows; row++)
for (int col = 0; col < NumberOfCols; col++)
{
xls.SetCellValue(row +1, col +1, Data[row][Col]);
}

xls.Save("myfile");

2) you can create a template, fill the data in a dataset and run a
report. While this is more time consuming initially than 1), it lets
you change the format very easily once created, and even your customers
can do it. (without touching any code). For pretty output, reports is
normally the way to go, but if you want a quick dump you can do it too.

And about the money, I understand that not everybody has the money to
pay, and this is why we price it on the inexpensive range. We prefer to
sell a lot of products at 100 bucks to a lot of people, than a little
at 2000 bucks.

But I would not want money to be the reason why you don't use it. So if
you like, just send me a private email at
(e-mail address removed) and I will send you a full registered
version for free.

Best regards,
Adrian.

 

[Guest]

Hi Adrian,

I also ask you about the TDBPlanner. Do you sell any commponents for VS.NET
2003 for Planning and scheduling.

I realy like the TDBPlanner month and horizontal view. But I couldn't see
any demo version for VS.NET 2003.

Is it there or do you plan to make same TDBPlanner for VS.NET 2003?

Thank you.

Rgds,
Niyazi

 

[Adrian Gallero]

Hi Niyazi,

I also ask you about the TDBPlanner. Do you sell any commponents for
VS.NET 2003 for Planning and scheduling.

I realy like the TDBPlanner month and horizontal view. But I couldn't
see any demo version for VS.NET 2003.

Is it there or do you plan to make same TDBPlanner for VS.NET 2003?

I am not the best man to answer this, since I am only FlexCel's
developer. TMS is a big company and they offer a lot of products, and I
am not always up to date.

But about a planner, I know TMS offers an ASP.NET version (Webplanner),
on partnership with componentscience at http://www.componentscience.net/
I know because I wrote the code to export the planner to excel

About winforms, I don't think we are offering anything now, but you
might want to contact TMS support.

Best regards,
Adrian.

Ps: I got your email, I will be sending you a registered version on
monday.

 

[Guest]

Hi Adrian,

Thank you very much for all your kind help.

Rgds,
Niyazi