Hi,
As part of an evaluation of a small utility that I wrote that converts some
data held in a large number of Excel spreadsheets into SQL Server, I decided
to convert the utility to VB .NET and run both versions both in the IDE and
as standalone compiled exe and see what the difference in time to complete
was.
The utility has to read 3842 single page Excel Sheets that contain a block
of data 12 rows deep and 55 columns across, this is then transferred into an
SQL Server table with the same number of columns.
I thought VB .NET might be slower, but I was surprised by how much.
I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and I
don't want to get into trouble with MS (particuarly as I make my living on
the back of Microsoft's products). What I would like to release is just a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.
If anyone from MS is looking in I would be pleased to know if you would be
unhappy about me releasing my findings??
Siv
25  1909 
* "Siv" <gs@remove.sivill.com> scripsit: I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks.
Re-read the EULA. I remember that benchmarks were forbidden for the
beta version, but this is maybe not the case for the release versions.
--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Herfried,
I had a look on Google and found the following link to the EULA for the .NET
framework 1.1 Redistributable, which seems to contain the line that I
remember seeing pop up somewhere:
a.. You may not disclose the results of any benchmark test of the .NET
Framework component of the OS Components to any third party without
Microsoft's prior written approval.
I tried hunting through the .NET IDE help system looking for "Licence"
"EULA" etc but couldn't find anything.
I suppose the question is, does releasing the timings I have done with my
applications constitute a "benchmark", I don't think they do, as I am sure
someone will point out that my program should be re-written from scratch
using VB.NET rather than by converting a VB6 app.
My reason for posting it would be to see if anyone had any ideas on how the
application could be improved to get better speed under .NET (as you know
from my previous posts I am converting from VB6 to VB.NET and am looking to
_learn_ rather than take a swipe at Microsoft and I expect to get some
useful information from this group about squeezing best performance from
..NET).
Siv
"Herfried K. Wagner [MVP]" <hi***************@gmx.at> wrote in message
news:O8**************@TK2MSFTNGP15.phx.gbl... * "Siv" <gs@remove.sivill.com> scripsit: I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks.
Re-read the EULA. I remember that benchmarks were forbidden for the
beta version, but this is maybe not the case for the release versions.
--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
wrote: Hi,
As part of an evaluation of a small utility that I wrote that converts some
data held in a large number of Excel spreadsheets into SQL Server, I decided
to convert the utility to VB .NET and run both versions both in the IDE and
as standalone compiled exe and see what the difference in time to complete
was.
The utility has to read 3842 single page Excel Sheets that contain a block
of data 12 rows deep and 55 columns across, this is then transferred into an
SQL Server table with the same number of columns.
I thought VB .NET might be slower, but I was surprised by how much.
Make sure you have Option Explicit On and Option Strict set. Watch
out for string concatenation (use System.Text.StringBuilder instead).
Watch out for legacy VB methods, classes, etc., and use .NET versions
instead.
I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and I
don't want to get into trouble with MS (particuarly as I make my living on
the back of Microsoft's products). What I would like to release is just a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.
If anyone from MS is looking in I would be pleased to know if you would be
unhappy about me releasing my findings??
Siv
I ANAL, but you should be fine; you're trying to figure out what's
wrong with your code. It's to prevent articles like "C# vs. Java",
etc.
Austin
The EULA states:
" 3.4 Benchmark Testing. The Software may contain the Microsoft
..NET Framework. You may not disclose the results of any benchmark test of
the .NET Framework component of the Software to any third party without
Microsoft's prior written approval."
However, the EULA also states:
" 1.1 General License Grant. Microsoft grants to you as an
individual, a personal, nonexclusive license to use the Software, and to
make and use copies of the Software for the purposes of designing,
developing, testing, and demonstrating your software product(s), provided
that you are the only individual using the Software. "
The EULA specifically provides for "demonstrating your software product(s)",
so that's quite a conflict.
Note that just because a license/contract states 'this or that" does not
mean that "this or that" is legal, enforceable, or would be upheld by any
rational court of competent jurisdiction.
-- http://www.standards.com/; See Howard Kaikow's web site.
Thanks for the advice, I do have Option Explicit on, but not Option Strict.
The conversion routine picked up a few "I can't figure ot the default property" errors, but I can't see a way round those as I am using OLE Automation into Excel to open the sheets. The code is as follows:
================================================== =================================================
Dim FName, SheetName As String
Dim DataArray(12, 60) As Object
Dim mth, cl As Short
Dim xlc, xlr, n As Short
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim strSQL, strCnn, MSg As String
Dim XLApp As Object
Dim LastM As Short
On Error GoTo ConvertRoutine_Err
ConvertRoutine = False 'Start off assuming fail and correct this if we do succeed.
'Work out the lastM var from LastMonth
LastM = Val(LastMonth)
FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)
PrintLine(1, "TACSY 7 Conversion Log - " & Now)
PrintLine(1, "===========================================")
PrintLine(1, " ")
'Look for the files in the source directory by
FName = Dir(tp & "*.xls") 'tp is the var holding the path to the spreadsheets being processed.
If FName = "" Then
'No xls files in this directory
MSg = "WARNING: No Excel files found in:" & NL & NL
MSg = MSg & tp & NL & NL
MSg = MSg & "Click OK to exit the Conversion Routine, "
MSg = MSg & "so that you can reselect the source folder."
MsgBox(MSg, MsgBoxStyle.Information, H)
PrintLine(1, "No Files found in selected folder ( " & tp & " ) to convert!")
PrintLine(1, "Aborting Conversion Run at " & Now)
FileClose(1)
Exit Function
Else
'Found some XL files.
'So Get Excel fired up
XLApp = CreateObject("Excel.Application")
XLApp.screenupdating = False
'Get the Tacsy7Data tble open ready to add records
Cn = New ADODB.Connection
Cn.ConnectionString = "Driver={SQL Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"
Cn.Open()
rs = New ADODB.Recordset
rs.Open("SELECT * FROM Tacsy7Data", Cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)
Do
XLApp.workbooks.Open(tp & FName)
lblReadout.Text = "Processing " & FName & "."
PrintLine(1, "Started converting " & tp & FName & " at " & Now)
System.Windows.Forms.Application.DoEvents() 'Let system in for a tick + allow readout refresh.
xlr = 64 'Row start, 1 less than desired so that the mth can be added to it to get the correct row.
DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points score
If XLApp.cells(84, 2).Value = "EmpNo" Then
DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)
Else
DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo
End If
For mth = 1 To 12
For cl = 1 To 55
'Read the existing data into the array
DataArray(mth, cl) = XLApp.cells(xlr + mth, cl).Value
Next cl
Next mth
'Array now has all the data from the old sheet - close the sheet
XLApp.workbooks(FName).Close(savechanges:=False) 'close but do not save
For mth = 1 To 12
'Read the data back from the array - Up to col 36 they are the same, then
'the new column appears at the 37th column and then the data is the same to the end.
Select Case mth
Case 1 To 5
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0, 1)) 'Employee Number
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(0, 0). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value = DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now, "YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 1). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value = DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 2). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value = DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 55). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNumShade").Value = DataArray(mth, 55)
rs.Update()
Case 6 To 12 'beyond m6 data is screwed from column 36, data is in 1 to right of where it should be ie 37.
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0, 1)) 'Employee Number
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(0, 0). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value = DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now, "YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 1). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value = DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 2). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value = DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 55). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNum").Value = DataArray(mth, 55)
rs.Fields("BusMixPCInvNumShade").Value = 0 'Just have to hardcode a value of no shade as there is no data on sheets.
rs.Update()
End Select
Next mth
AfterError:
'Clear the array ready for next loop (if any).
For mth = 1 To 12
For cl = 1 To 55
'Clear the array
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, cl). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
DataArray(mth, cl) = 0
Next cl
Next mth
'do a dir for the next file name or exit if FName=""
'UPGRADE_WARNING: Dir has a new behavior. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'
FName = Dir()
Loop Until FName = "" 'Move on to next sheet.
'Close recordset
rs.Close()
'Write closing log entry
PrintLine(1, " and finished file writes at " & Now) 'Finishes a line started near top of loop
PrintLine(1, "================================================= ====================")
FileClose(1) 'close log file!
'UPGRADE_WARNING: Couldn't resolve default property of object XLApp.screenupdating. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.screenupdating = True 'Turn on screen updating
'UPGRADE_WARNING: Couldn't resolve default property of object XLApp.quit. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.quit() 'Quit excel
End If
ConvertRoutine = True
ConvertRoutine_End:
Exit Function
ConvertRoutine_Err:
Select Case Err.Number
Case 13 'Type Mismatch
Resume Next
Case 94 'Invalid Use of Null
Resume Next
Case 3021 'No record found
Resume ConvertRoutine_End
Case 3022 'Duplicate record
Resume ConvertRoutine_End
Case -2147217887 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty due to sheet being empty?? Found one so have put in this patch to bypass it.
Resume AfterError
Resume 'debugging
Case Else
Me.Cursor = System.Windows.Forms.Cursors.Default
Call ProgErrorHandler("ConvertRoutine in frmConvertT7ToData", False)
Resume ConvertRoutine_End
Resume 'Debugging
End Select
================================================== ===
I have retained the "On error Goto" because using Try, Catch, Finally I can't find out how to trap a specific error like the -2147217887 Multiple Step OLE error the help on this is "pants".
Siv
"Austin Ehlers" <li*******************@ku.edu> wrote in message news:f0********************************@4ax.com... On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
wrote:
Hi,
As part of an evaluation of a small utility that I wrote that converts some
data held in a large number of Excel spreadsheets into SQL Server, I decided
to convert the utility to VB .NET and run both versions both in the IDE and
as standalone compiled exe and see what the difference in time to complete
was.
The utility has to read 3842 single page Excel Sheets that contain a block
of data 12 rows deep and 55 columns across, this is then transferred into an
SQL Server table with the same number of columns.
I thought VB .NET might be slower, but I was surprised by how much.
Make sure you have Option Explicit On and Option Strict set. Watch
out for string concatenation (use System.Text.StringBuilder instead).
Watch out for legacy VB methods, classes, etc., and use .NET versions
instead.
I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and I
don't want to get into trouble with MS (particuarly as I make my living on
the back of Microsoft's products). What I would like to release is just a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.
If anyone from MS is looking in I would be pleased to know if you would be
unhappy about me releasing my findings??
Siv
I ANAL, but you should be fine; you're trying to figure out what's
wrong with your code. It's to prevent articles like "C# vs. Java",
etc.
Austin
Howard,
Thanks for your insight. I just want to understand why my code compiled
under VB.NET runs way slower than under VB6 and if there are any ways to
improve how my apps run under VB.NET. I am increasingly finding that once I
have peeled off the outer layers of confusion and find ways to do stuff with
VB.NET I really like working with it, it's just frustrating that as a guy
who has been dveloping applications in VB since Version 1.0 I feel like a
complete beginner again going from VB6 to VB8 and on top of that the new
version runs at about 70% the speed of the old version???
I am a small "one man band" developing apps for customers and couldn't
afford to spend any time in litigation with the likes of Microsoft!
Siv
"Howard Kaikow" <ka****@standards.com> wrote in message
news:eP**************@TK2MSFTNGP12.phx.gbl... The EULA states:
" 3.4 Benchmark Testing. The Software may contain the Microsoft
.NET Framework. You may not disclose the results of any benchmark test of
the .NET Framework component of the Software to any third party without
Microsoft's prior written approval."
However, the EULA also states:
" 1.1 General License Grant. Microsoft grants to you as an
individual, a personal, nonexclusive license to use the Software, and to
make and use copies of the Software for the purposes of designing,
developing, testing, and demonstrating your software product(s), provided
that you are the only individual using the Software. "
The EULA specifically provides for "demonstrating your software
product(s)",
so that's quite a conflict.
Note that just because a license/contract states 'this or that" does not
mean that "this or that" is legal, enforceable, or would be upheld by any
rational court of competent jurisdiction.
--
http://www.standards.com/; See Howard Kaikow's web site.
Hello Siv:
You wrote in conference microsoft.public.dotnet.languages.vb on Sun, 12 Sep
2004 18:45:48 +0100:
S> I would like to release the findings to this community for comment, but
S> I remembered something in the EULA for .NET that says you can't release
S> benchmarks.
....of the .net framework, which is not the case. You are releasing the
benchmark of your applications. A benchmark of the whole framework would be
quite a project.
Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073 www.vadimrapp.com
I am sure you are right:
Details as follows:
VB6 Versus VB.NET
Actual Elapsed
Time
Time Taken
To Process
Difference Between
VB6 and VB.NET
% VB6
VB6 In VB6 IDE - Start:
10/09/2004 21:48:27
VB6 In VB6 IDE - End:
10/09/2004 23:39:09
01:50:42
00:49:54
68.92902
VB6 Compiled EXE - Start:
12/09/2004 15:32:32
VB6 Compiled EXE - End:
12/09/2004 17:22:53
01:50:21
00:42:48
72.05354
VB.NET IDE - Start:
11/09/2004 15:47:22
VB.NET IDE - End:
11/09/2004 18:27:58
02:40:36
VB.NET Compiled Release Binary - Start:
12/09/2004 02:18:11
VB.NET Compiled Release Binary - End:
12/09/2004 04:51:20
02:33:09
Running the app in the IDE 69% of VB6, as a compiled EXE 72%.
This seems to me to be a large difference. Why?
Please see previous posts to see the code. Clearly code just run through the VB converter runs at around 70% of the speed of VB6, clearly the converter is only the starting point, you need to rewrite the code from scratch to get best performance from .NET.
Siv
"Vadim Rapp" <vr@myrealbox.nospam.com> wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl... Hello Siv:
You wrote in conference microsoft.public.dotnet.languages.vb on Sun, 12 Sep
2004 18:45:48 +0100:
S> I would like to release the findings to this community for comment, but
S> I remembered something in the EULA for .NET that says you can't release
S> benchmarks.
...of the .net framework, which is not the case. You are releasing the
benchmark of your applications. A benchmark of the whole framework would be
quite a project.
Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com
The code will run a lot faster if you use With ... End With to reduce the
number of object references.
-- http://www.standards.com/; See Howard Kaikow's web site.
"Siv" <gs@remove.sivill.com> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
Thanks for the advice, I do have Option Explicit on, but not Option Strict.
The conversion routine picked up a few "I can't figure ot the default
property" errors, but I can't see a way round those as I am using OLE
Automation into Excel to open the sheets. The code is as follows:
================================================== ==========================
=======================
Dim FName, SheetName As String
Dim DataArray(12, 60) As Object
Dim mth, cl As Short
Dim xlc, xlr, n As Short
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim strSQL, strCnn, MSg As String
Dim XLApp As Object
Dim LastM As Short
On Error GoTo ConvertRoutine_Err
ConvertRoutine = False 'Start off assuming fail and correct this if
we do succeed.
'Work out the lastM var from LastMonth
LastM = Val(LastMonth)
FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)
PrintLine(1, "TACSY 7 Conversion Log - " & Now)
PrintLine(1, "===========================================")
PrintLine(1, " ")
'Look for the files in the source directory by
FName = Dir(tp & "*.xls") 'tp is the var holding the path to the
spreadsheets being processed.
If FName = "" Then
'No xls files in this directory
MSg = "WARNING: No Excel files found in:" & NL & NL
MSg = MSg & tp & NL & NL
MSg = MSg & "Click OK to exit the Conversion Routine, "
MSg = MSg & "so that you can reselect the source folder."
MsgBox(MSg, MsgBoxStyle.Information, H)
PrintLine(1, "No Files found in selected folder ( " & tp & " )
to convert!")
PrintLine(1, "Aborting Conversion Run at " & Now)
FileClose(1)
Exit Function
Else
'Found some XL files.
'So Get Excel fired up
XLApp = CreateObject("Excel.Application")
XLApp.screenupdating = False
'Get the Tacsy7Data tble open ready to add records
Cn = New ADODB.Connection
Cn.ConnectionString = "Driver={SQL
Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"
Cn.Open()
rs = New ADODB.Recordset
rs.Open("SELECT * FROM Tacsy7Data", Cn,
ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic,
ADODB.CommandTypeEnum.adCmdText)
Do
XLApp.workbooks.Open(tp & FName)
lblReadout.Text = "Processing " & FName & "."
PrintLine(1, "Started converting " & tp & FName & " at " &
Now)
System.Windows.Forms.Application.DoEvents() 'Let system in
for a tick + allow readout refresh.
xlr = 64 'Row start, 1 less than desired so that the mth can
be added to it to get the correct row.
DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points
score
If XLApp.cells(84, 2).Value = "EmpNo" Then
DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)
Else
DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo
End If
For mth = 1 To 12
For cl = 1 To 55
'Read the existing data into the array
DataArray(mth, cl) = XLApp.cells(xlr + mth,
cl).Value
Next cl
Next mth
'Array now has all the data from the old sheet - close the
sheet
XLApp.workbooks(FName).Close(savechanges:=False) 'close but
do not save
For mth = 1 To 12
'Read the data back from the array - Up to col 36 they
are the same, then
'the new column appears at the 37th column and then the
data is the same to the end.
Select Case mth
Case 1 To 5
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNumShade").Value =
DataArray(mth, 55)
rs.Update()
Case 6 To 12 'beyond m6 data is screwed from column
36, data is in 1 to right of where it should be ie 37.
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNum").Value =
DataArray(mth, 55)
rs.Fields("BusMixPCInvNumShade").Value = 0 'Just
have to hardcode a value of no shade as there is no data on sheets.
rs.Update()
End Select
Next mth
AfterError:
'Clear the array ready for next loop (if any).
For mth = 1 To 12
For cl = 1 To 55
'Clear the array
'UPGRADE_WARNING: Couldn't resolve default property
of object DataArray(mth, cl). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
DataArray(mth, cl) = 0
Next cl
Next mth
'do a dir for the next file name or exit if FName=""
'UPGRADE_WARNING: Dir has a new behavior. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'
FName = Dir()
Loop Until FName = "" 'Move on to next sheet.
'Close recordset
rs.Close()
'Write closing log entry
PrintLine(1, " and finished file writes at " & Now) 'Finishes a
line started near top of loop
PrintLine(1,
"================================================= ====================")
FileClose(1) 'close log file!
'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.screenupdating. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.screenupdating = True 'Turn on screen updating
'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.quit. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.quit() 'Quit excel
End If
ConvertRoutine = True
ConvertRoutine_End:
Exit Function
ConvertRoutine_Err:
Select Case Err.Number
Case 13 'Type Mismatch
Resume Next
Case 94 'Invalid Use of Null
Resume Next
Case 3021 'No record found
Resume ConvertRoutine_End
Case 3022 'Duplicate record
Resume ConvertRoutine_End
Case -2147217887 'Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.
rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty
due to sheet being empty?? Found one so have put in this patch to bypass it.
Resume AfterError
Resume 'debugging
Case Else
Me.Cursor = System.Windows.Forms.Cursors.Default
Call ProgErrorHandler("ConvertRoutine in
frmConvertT7ToData", False)
Resume ConvertRoutine_End
Resume 'Debugging
End Select
================================================== ===
I have retained the "On error Goto" because using Try, Catch, Finally I
can't find out how to trap a specific error like the -2147217887 Multiple
Step OLE error the help on this is "pants".
Siv
"Austin Ehlers" <li*******************@ku.edu> wrote in message
news:f0********************************@4ax.com... On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
wrote:
Hi,
As part of an evaluation of a small utility that I wrote that converts
somedata held in a large number of Excel spreadsheets into SQL Server, I
decidedto convert the utility to VB .NET and run both versions both in the IDE
andas standalone compiled exe and see what the difference in time to complete
was.
The utility has to read 3842 single page Excel Sheets that contain a block
of data 12 rows deep and 55 columns across, this is then transferred into
anSQL Server table with the same number of columns.
I thought VB .NET might be slower, but I was surprised by how much.
Make sure you have Option Explicit On and Option Strict set. Watch
out for string concatenation (use System.Text.StringBuilder instead).
Watch out for legacy VB methods, classes, etc., and use .NET versions
instead.
I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and I
don't want to get into trouble with MS (particuarly as I make my living on
the back of Microsoft's products). What I would like to release is just a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.
If anyone from MS is looking in I would be pleased to know if you would be
unhappy about me releasing my findings??
Siv
I ANAL, but you should be fine; you're trying to figure out what's
wrong with your code. It's to prevent articles like "C# vs. Java",
etc.
Austin
Are you sure, or do you mean the code will be more concise??
Siv
"Howard Kaikow" <ka****@standards.com> wrote in message
news:eb**************@TK2MSFTNGP12.phx.gbl... The code will run a lot faster if you use With ... End With to reduce the
number of object references.
--
http://www.standards.com/; See Howard Kaikow's web site.
"Siv" <gs@remove.sivill.com> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
Thanks for the advice, I do have Option Explicit on, but not Option
Strict.
The conversion routine picked up a few "I can't figure ot the default
property" errors, but I can't see a way round those as I am using OLE
Automation into Excel to open the sheets. The code is as follows:
================================================== ==========================
=======================
Dim FName, SheetName As String
Dim DataArray(12, 60) As Object
Dim mth, cl As Short
Dim xlc, xlr, n As Short
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim strSQL, strCnn, MSg As String
Dim XLApp As Object
Dim LastM As Short
On Error GoTo ConvertRoutine_Err
ConvertRoutine = False 'Start off assuming fail and correct this if
we do succeed.
'Work out the lastM var from LastMonth
LastM = Val(LastMonth)
FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)
PrintLine(1, "TACSY 7 Conversion Log - " & Now)
PrintLine(1, "===========================================")
PrintLine(1, " ")
'Look for the files in the source directory by
FName = Dir(tp & "*.xls") 'tp is the var holding the path to the
spreadsheets being processed.
If FName = "" Then
'No xls files in this directory
MSg = "WARNING: No Excel files found in:" & NL & NL
MSg = MSg & tp & NL & NL
MSg = MSg & "Click OK to exit the Conversion Routine, "
MSg = MSg & "so that you can reselect the source folder."
MsgBox(MSg, MsgBoxStyle.Information, H)
PrintLine(1, "No Files found in selected folder ( " & tp & " )
to convert!")
PrintLine(1, "Aborting Conversion Run at " & Now)
FileClose(1)
Exit Function
Else
'Found some XL files.
'So Get Excel fired up
XLApp = CreateObject("Excel.Application")
XLApp.screenupdating = False
'Get the Tacsy7Data tble open ready to add records
Cn = New ADODB.Connection
Cn.ConnectionString = "Driver={SQL
Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"
Cn.Open()
rs = New ADODB.Recordset
rs.Open("SELECT * FROM Tacsy7Data", Cn,
ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic,
ADODB.CommandTypeEnum.adCmdText)
Do
XLApp.workbooks.Open(tp & FName)
lblReadout.Text = "Processing " & FName & "."
PrintLine(1, "Started converting " & tp & FName & " at " &
Now)
System.Windows.Forms.Application.DoEvents() 'Let system in
for a tick + allow readout refresh.
xlr = 64 'Row start, 1 less than desired so that the mth
can
be added to it to get the correct row.
DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points
score
If XLApp.cells(84, 2).Value = "EmpNo" Then
DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)
Else
DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo
End If
For mth = 1 To 12
For cl = 1 To 55
'Read the existing data into the array
DataArray(mth, cl) = XLApp.cells(xlr + mth,
cl).Value
Next cl
Next mth
'Array now has all the data from the old sheet - close the
sheet
XLApp.workbooks(FName).Close(savechanges:=False) 'close but
do not save
For mth = 1 To 12
'Read the data back from the array - Up to col 36 they
are the same, then
'the new column appears at the 37th column and then the
data is the same to the end.
Select Case mth
Case 1 To 5
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNumShade").Value =
DataArray(mth, 55)
rs.Update()
Case 6 To 12 'beyond m6 data is screwed from column
36, data is in 1 to right of where it should be ie 37.
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNum").Value =
DataArray(mth, 55)
rs.Fields("BusMixPCInvNumShade").Value = 0
'Just
have to hardcode a value of no shade as there is no data on sheets.
rs.Update()
End Select
Next mth
AfterError:
'Clear the array ready for next loop (if any).
For mth = 1 To 12
For cl = 1 To 55
'Clear the array
'UPGRADE_WARNING: Couldn't resolve default property
of object DataArray(mth, cl). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
DataArray(mth, cl) = 0
Next cl
Next mth
'do a dir for the next file name or exit if FName=""
'UPGRADE_WARNING: Dir has a new behavior. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'
FName = Dir()
Loop Until FName = "" 'Move on to next sheet.
'Close recordset
rs.Close()
'Write closing log entry
PrintLine(1, " and finished file writes at " & Now) 'Finishes a
line started near top of loop
PrintLine(1,
"================================================= ====================")
FileClose(1) 'close log file!
'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.screenupdating. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.screenupdating = True 'Turn on screen updating
'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.quit. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.quit() 'Quit excel
End If
ConvertRoutine = True
ConvertRoutine_End:
Exit Function
ConvertRoutine_Err:
Select Case Err.Number
Case 13 'Type Mismatch
Resume Next
Case 94 'Invalid Use of Null
Resume Next
Case 3021 'No record found
Resume ConvertRoutine_End
Case 3022 'Duplicate record
Resume ConvertRoutine_End
Case -2147217887 'Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.
rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty
due to sheet being empty?? Found one so have put in this patch to bypass
it.
Resume AfterError
Resume 'debugging
Case Else
Me.Cursor = System.Windows.Forms.Cursors.Default
Call ProgErrorHandler("ConvertRoutine in
frmConvertT7ToData", False)
Resume ConvertRoutine_End
Resume 'Debugging
End Select
================================================== ===
I have retained the "On error Goto" because using Try, Catch, Finally I
can't find out how to trap a specific error like the -2147217887 Multiple
Step OLE error the help on this is "pants".
Siv
"Austin Ehlers" <li*******************@ku.edu> wrote in message
news:f0********************************@4ax.com... On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
wrote:
Hi,
As part of an evaluation of a small utility that I wrote that converts somedata held in a large number of Excel spreadsheets into SQL Server, I decidedto convert the utility to VB .NET and run both versions both in the IDE andas standalone compiled exe and see what the difference in time to
complete
was.
The utility has to read 3842 single page Excel Sheets that contain a
block
of data 12 rows deep and 55 columns across, this is then transferred into anSQL Server table with the same number of columns.
I thought VB .NET might be slower, but I was surprised by how much.
Make sure you have Option Explicit On and Option Strict set. Watch
out for string concatenation (use System.Text.StringBuilder instead).
Watch out for legacy VB methods, classes, etc., and use .NET versions
instead.
I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and
I
don't want to get into trouble with MS (particuarly as I make my living
on
the back of Microsoft's products). What I would like to release is just
a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.
If anyone from MS is looking in I would be pleased to know if you would
be
unhappy about me releasing my findings??
Siv
I ANAL, but you should be fine; you're trying to figure out what's
wrong with your code. It's to prevent articles like "C# vs. Java",
etc.
Austin
Hi Siv,
Try using Integer instead of Short. That might give you a little boost. Ken.
Hi Siv,
One more thing to try. You could change the DataArray to be of type Integer or Long. It seems like everything in there is a number. If I mis-read then you could still use String instead of Object and it may go faster for you. At least you'll have early binding and you can use the CInt(), CLng(), etc. for conversions which may be faster. Can't hurt to test. Good luck! Ken.
one of the reasons may be because you are using classic ADO instead of the
newer optimized for .NET ADO.NET, maybe try to convert it and see how that
effects the performance.
"Siv" <gs@remove.sivill.com> wrote in message
news:u1**************@TK2MSFTNGP12.phx.gbl... Howard,
Thanks for your insight. I just want to understand why my code compiled
under VB.NET runs way slower than under VB6 and if there are any ways to
improve how my apps run under VB.NET. I am increasingly finding that once
I have peeled off the outer layers of confusion and find ways to do stuff
with VB.NET I really like working with it, it's just frustrating that as a
guy who has been dveloping applications in VB since Version 1.0 I feel
like a complete beginner again going from VB6 to VB8 and on top of that
the new version runs at about 70% the speed of the old version???
I am a small "one man band" developing apps for customers and couldn't
afford to spend any time in litigation with the likes of Microsoft!
Siv
"Howard Kaikow" <ka****@standards.com> wrote in message
news:eP**************@TK2MSFTNGP12.phx.gbl... The EULA states:
" 3.4 Benchmark Testing. The Software may contain the Microsoft
.NET Framework. You may not disclose the results of any benchmark test
of
the .NET Framework component of the Software to any third party without
Microsoft's prior written approval."
However, the EULA also states:
" 1.1 General License Grant. Microsoft grants to you as an
individual, a personal, nonexclusive license to use the Software, and to
make and use copies of the Software for the purposes of designing,
developing, testing, and demonstrating your software product(s), provided
that you are the only individual using the Software. "
The EULA specifically provides for "demonstrating your software
product(s)",
so that's quite a conflict.
Note that just because a license/contract states 'this or that" does not
mean that "this or that" is legal, enforceable, or would be upheld by any
rational court of competent jurisdiction.
--
http://www.standards.com/; See Howard Kaikow's web site.
if you'd use ADO.NET instead of ADO you could use TRY CATCH END TRY because
it has a exception handler for the OleDB,ODBC, and SQL providers already in
the framework to handle errors
"Siv" <gs@remove.sivill.com> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
Thanks for the advice, I do have Option Explicit on, but not Option Strict.
The conversion routine picked up a few "I can't figure ot the default
property" errors, but I can't see a way round those as I am using OLE
Automation into Excel to open the sheets. The code is as follows:
================================================== =================================================
Dim FName, SheetName As String
Dim DataArray(12, 60) As Object
Dim mth, cl As Short
Dim xlc, xlr, n As Short
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim strSQL, strCnn, MSg As String
Dim XLApp As Object
Dim LastM As Short
On Error GoTo ConvertRoutine_Err
ConvertRoutine = False 'Start off assuming fail and correct this if
we do succeed.
'Work out the lastM var from LastMonth
LastM = Val(LastMonth)
FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)
PrintLine(1, "TACSY 7 Conversion Log - " & Now)
PrintLine(1, "===========================================")
PrintLine(1, " ")
'Look for the files in the source directory by
FName = Dir(tp & "*.xls") 'tp is the var holding the path to the
spreadsheets being processed.
If FName = "" Then
'No xls files in this directory
MSg = "WARNING: No Excel files found in:" & NL & NL
MSg = MSg & tp & NL & NL
MSg = MSg & "Click OK to exit the Conversion Routine, "
MSg = MSg & "so that you can reselect the source folder."
MsgBox(MSg, MsgBoxStyle.Information, H)
PrintLine(1, "No Files found in selected folder ( " & tp & " )
to convert!")
PrintLine(1, "Aborting Conversion Run at " & Now)
FileClose(1)
Exit Function
Else
'Found some XL files.
'So Get Excel fired up
XLApp = CreateObject("Excel.Application")
XLApp.screenupdating = False
'Get the Tacsy7Data tble open ready to add records
Cn = New ADODB.Connection
Cn.ConnectionString = "Driver={SQL
Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"
Cn.Open()
rs = New ADODB.Recordset
rs.Open("SELECT * FROM Tacsy7Data", Cn,
ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic,
ADODB.CommandTypeEnum.adCmdText)
Do
XLApp.workbooks.Open(tp & FName)
lblReadout.Text = "Processing " & FName & "."
PrintLine(1, "Started converting " & tp & FName & " at " &
Now)
System.Windows.Forms.Application.DoEvents() 'Let system in
for a tick + allow readout refresh.
xlr = 64 'Row start, 1 less than desired so that the mth can
be added to it to get the correct row.
DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points
score
If XLApp.cells(84, 2).Value = "EmpNo" Then
DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)
Else
DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo
End If
For mth = 1 To 12
For cl = 1 To 55
'Read the existing data into the array
DataArray(mth, cl) = XLApp.cells(xlr + mth,
cl).Value
Next cl
Next mth
'Array now has all the data from the old sheet - close the
sheet
XLApp.workbooks(FName).Close(savechanges:=False) 'close but
do not save
For mth = 1 To 12
'Read the data back from the array - Up to col 36 they
are the same, then
'the new column appears at the 37th column and then the
data is the same to the end.
Select Case mth
Case 1 To 5
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNumShade").Value =
DataArray(mth, 55)
rs.Update()
Case 6 To 12 'beyond m6 data is screwed from column
36, data is in 1 to right of where it should be ie 37.
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNum").Value =
DataArray(mth, 55)
rs.Fields("BusMixPCInvNumShade").Value = 0 'Just
have to hardcode a value of no shade as there is no data on sheets.
rs.Update()
End Select
Next mth
AfterError:
'Clear the array ready for next loop (if any).
For mth = 1 To 12
For cl = 1 To 55
'Clear the array
'UPGRADE_WARNING: Couldn't resolve default property
of object DataArray(mth, cl). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
DataArray(mth, cl) = 0
Next cl
Next mth
'do a dir for the next file name or exit if FName=""
'UPGRADE_WARNING: Dir has a new behavior. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'
FName = Dir()
Loop Until FName = "" 'Move on to next sheet.
'Close recordset
rs.Close()
'Write closing log entry
PrintLine(1, " and finished file writes at " & Now) 'Finishes a
line started near top of loop
PrintLine(1,
"================================================= ====================")
FileClose(1) 'close log file!
'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.screenupdating. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.screenupdating = True 'Turn on screen updating
'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.quit. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.quit() 'Quit excel
End If
ConvertRoutine = True
ConvertRoutine_End:
Exit Function
ConvertRoutine_Err:
Select Case Err.Number
Case 13 'Type Mismatch
Resume Next
Case 94 'Invalid Use of Null
Resume Next
Case 3021 'No record found
Resume ConvertRoutine_End
Case 3022 'Duplicate record
Resume ConvertRoutine_End
Case -2147217887 'Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.
rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty
due to sheet being empty?? Found one so have put in this patch to bypass it.
Resume AfterError
Resume 'debugging
Case Else
Me.Cursor = System.Windows.Forms.Cursors.Default
Call ProgErrorHandler("ConvertRoutine in
frmConvertT7ToData", False)
Resume ConvertRoutine_End
Resume 'Debugging
End Select
================================================== ===
I have retained the "On error Goto" because using Try, Catch, Finally I
can't find out how to trap a specific error like the -2147217887 Multiple
Step OLE error the help on this is "pants".
Siv
"Austin Ehlers" <li*******************@ku.edu> wrote in message
news:f0********************************@4ax.com... On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
wrote:
Hi,
As part of an evaluation of a small utility that I wrote that converts
some
data held in a large number of Excel spreadsheets into SQL Server, I
decided
to convert the utility to VB .NET and run both versions both in the IDE
and
as standalone compiled exe and see what the difference in time to complete
was.
The utility has to read 3842 single page Excel Sheets that contain a block
of data 12 rows deep and 55 columns across, this is then transferred into
an
SQL Server table with the same number of columns.
I thought VB .NET might be slower, but I was surprised by how much.
Make sure you have Option Explicit On and Option Strict set. Watch
out for string concatenation (use System.Text.StringBuilder instead).
Watch out for legacy VB methods, classes, etc., and use .NET versions
instead.
I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and I
don't want to get into trouble with MS (particuarly as I make my living on
the back of Microsoft's products). What I would like to release is just a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.
If anyone from MS is looking in I would be pleased to know if you would be
unhappy about me releasing my findings??
Siv
I ANAL, but you should be fine; you're trying to figure out what's
wrong with your code. It's to prevent articles like "C# vs. Java",
etc.
Austin
In addition to other suggestions, here's what I would suggest: Instead of looping and setting each array element to 0 here, you should just erase the entire array and redim it at the begining of each loop (see code below). Doing this is just wasteful - 55*12 = 660 iterations - just for each worksheet. However, that won't account for the speed difference between VB6 and VB.NET. This change should equally (more or less) speed up both versions.
hope that helps..
Imran.
AfterError:
'Clear the array ready for next loop (if any).
For mth = 1 To 12
For cl = 1 To 55
'Clear the array
'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, cl). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
DataArray(mth, cl) = 0
Next cl
Next mth
'Instead of looping above, use this:
Erase DataArray
Siv,
I saw you code, that is not VBNet, that is converted VB6 using the dotNet
namespace, the Microsoft VisualBasic namespace and the VBNet compatible
namespace.
When you want a true comparasing, you should write a program in the VBNet
way, which only contains the dotNet and the Microsoft VisualBasic namespace.
That means at least using ADONET instead of ADODB which is used in the
interop way
You can use ADONET to get the data and probably as well to set the Excel
sheets in a dataset.
Also you can you can use early binding, what will be archieved when you set
the Option Strict to On because with that you cannot do late binding. Late
binding means that the program search on runtime moment for the most right
object to use, while with early binding that is already told.
That does not mean that your program will not run, however not with much
advantages over a real VBNet program (maybe even with disadvantages)
I hope this helps?
Cor
"Siv" > As part of an evaluation of a small utility that I wrote that converts
some data held in a large number of Excel spreadsheets into SQL Server, I
decided to convert the utility to VB .NET and run both versions both in the IDE
and as standalone compiled exe and see what the difference in time to complete
was.
The utility has to read 3842 single page Excel Sheets that contain a block
of data 12 rows deep and 55 columns across, this is then transferred into
an SQL Server table with the same number of columns.
I thought VB .NET might be slower, but I was surprised by how much.
I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and I
don't want to get into trouble with MS (particuarly as I make my living on
the back of Microsoft's products). What I would like to release is just a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.
If anyone from MS is looking in I would be pleased to know if you would be
unhappy about me releasing my findings??
Siv
* "Vadim Rapp" <vr@myrealbox.nospam.com> scripsit: S> I would like to release the findings to this community for comment, but
S> I remembered something in the EULA for .NET that says you can't release
S> benchmarks.
...of the .net framework, which is not the case. You are releasing the
benchmark of your applications. A benchmark of the whole framework
would be quite a project.
Mhm... But the code you benchmark includes implementations provided by
MSFT (or at least the CLR)...
--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
LOL!!!!!
Shrink wrapping gone mad! What a bunch of dominating bastards! I've dissed
the speed of things in here many times, but you have to remember something,
it's not the Framework or OS components that your benchmarking, it's your
software! Who is to say that your code is 100%, and regardless of that;
there are always ways to improve things.
Remember, *your* software benchmarks, not the framework or os.
Nick.
"Siv" <gs@remove.sivill.com> wrote in message
news:OU*************@TK2MSFTNGP10.phx.gbl... Herfried,
I had a look on Google and found the following link to the EULA for the
.NET framework 1.1 Redistributable, which seems to contain the line that I
remember seeing pop up somewhere:
a.. You may not disclose the results of any benchmark test of the .NET
Framework component of the OS Components to any third party without
Microsoft's prior written approval.
I tried hunting through the .NET IDE help system looking for "Licence"
"EULA" etc but couldn't find anything.
I suppose the question is, does releasing the timings I have done with my
applications constitute a "benchmark", I don't think they do, as I am sure
someone will point out that my program should be re-written from scratch
using VB.NET rather than by converting a VB6 app.
My reason for posting it would be to see if anyone had any ideas on how
the application could be improved to get better speed under .NET (as you
know from my previous posts I am converting from VB6 to VB.NET and am
looking to _learn_ rather than take a swipe at Microsoft and I expect to
get some useful information from this group about squeezing best
performance from .NET).
Siv
"Herfried K. Wagner [MVP]" <hi***************@gmx.at> wrote in message
news:O8**************@TK2MSFTNGP15.phx.gbl...* "Siv" <gs@remove.sivill.com> scripsit: I would like to release the findings to this community for comment, but
I
remembered something in the EULA for .NET that says you can't release
benchmarks.
Re-read the EULA. I remember that benchmarks were forbidden for the
beta version, but this is maybe not the case for the release versions.
--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Your right, it makes very little difference to speed, just because you write
the name of an object on 2 different lines does not mean that there are 2
objects in memory, its still the same one being referenced.
Nick.
"Siv" <gs@remove.sivill.com> wrote in message
news:ew****************@TK2MSFTNGP15.phx.gbl... Are you sure, or do you mean the code will be more concise??
Siv
"Howard Kaikow" <ka****@standards.com> wrote in message
news:eb**************@TK2MSFTNGP12.phx.gbl... The code will run a lot faster if you use With ... End With to reduce
the
number of object references.
--
http://www.standards.com/; See Howard Kaikow's web site.
"Siv" <gs@remove.sivill.com> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
Thanks for the advice, I do have Option Explicit on, but not Option
Strict.
The conversion routine picked up a few "I can't figure ot the default
property" errors, but I can't see a way round those as I am using OLE
Automation into Excel to open the sheets. The code is as follows:
================================================== ==========================
=======================
Dim FName, SheetName As String
Dim DataArray(12, 60) As Object
Dim mth, cl As Short
Dim xlc, xlr, n As Short
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim strSQL, strCnn, MSg As String
Dim XLApp As Object
Dim LastM As Short
On Error GoTo ConvertRoutine_Err
ConvertRoutine = False 'Start off assuming fail and correct this
if
we do succeed.
'Work out the lastM var from LastMonth
LastM = Val(LastMonth)
FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)
PrintLine(1, "TACSY 7 Conversion Log - " & Now)
PrintLine(1, "===========================================")
PrintLine(1, " ")
'Look for the files in the source directory by
FName = Dir(tp & "*.xls") 'tp is the var holding the path to the
spreadsheets being processed.
If FName = "" Then
'No xls files in this directory
MSg = "WARNING: No Excel files found in:" & NL & NL
MSg = MSg & tp & NL & NL
MSg = MSg & "Click OK to exit the Conversion Routine, "
MSg = MSg & "so that you can reselect the source folder."
MsgBox(MSg, MsgBoxStyle.Information, H)
PrintLine(1, "No Files found in selected folder ( " & tp & " )
to convert!")
PrintLine(1, "Aborting Conversion Run at " & Now)
FileClose(1)
Exit Function
Else
'Found some XL files.
'So Get Excel fired up
XLApp = CreateObject("Excel.Application")
XLApp.screenupdating = False
'Get the Tacsy7Data tble open ready to add records
Cn = New ADODB.Connection
Cn.ConnectionString = "Driver={SQL
Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"
Cn.Open()
rs = New ADODB.Recordset
rs.Open("SELECT * FROM Tacsy7Data", Cn,
ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic,
ADODB.CommandTypeEnum.adCmdText)
Do
XLApp.workbooks.Open(tp & FName)
lblReadout.Text = "Processing " & FName & "."
PrintLine(1, "Started converting " & tp & FName & " at " &
Now)
System.Windows.Forms.Application.DoEvents() 'Let system in
for a tick + allow readout refresh.
xlr = 64 'Row start, 1 less than desired so that the mth
can
be added to it to get the correct row.
DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points
score
If XLApp.cells(84, 2).Value = "EmpNo" Then
DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)
Else
DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo
End If
For mth = 1 To 12
For cl = 1 To 55
'Read the existing data into the array
DataArray(mth, cl) = XLApp.cells(xlr + mth,
cl).Value
Next cl
Next mth
'Array now has all the data from the old sheet - close
the
sheet
XLApp.workbooks(FName).Close(savechanges:=False) 'close
but
do not save
For mth = 1 To 12
'Read the data back from the array - Up to col 36 they
are the same, then
'the new column appears at the 37th column and then
the
data is the same to the end.
Select Case mth
Case 1 To 5
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0,
1))
'Employee Number
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNumShade").Value =
DataArray(mth, 55)
rs.Update()
Case 6 To 12 'beyond m6 data is screwed from
column
36, data is in 1 to right of where it should be ie 37.
rs.AddNew()
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("EmpNo").Value = Val(DataArray(0,
1))
'Employee Number
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.
If mth > LastM Then
rs.Fields("Year").Value = Year(Now) - 1
Else
rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")
End If
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)
... Lots more fields as per the above ...
'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
rs.Fields("BusMixPCInvNum").Value =
DataArray(mth, 55)
rs.Fields("BusMixPCInvNumShade").Value = 0
'Just
have to hardcode a value of no shade as there is no data on sheets.
rs.Update()
End Select
Next mth
AfterError:
'Clear the array ready for next loop (if any).
For mth = 1 To 12
For cl = 1 To 55
'Clear the array
'UPGRADE_WARNING: Couldn't resolve default
property
of object DataArray(mth, cl). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
DataArray(mth, cl) = 0
Next cl
Next mth
'do a dir for the next file name or exit if FName=""
'UPGRADE_WARNING: Dir has a new behavior. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'
FName = Dir()
Loop Until FName = "" 'Move on to next sheet.
'Close recordset
rs.Close()
'Write closing log entry
PrintLine(1, " and finished file writes at " & Now) 'Finishes
a
line started near top of loop
PrintLine(1,
"================================================= ====================")
FileClose(1) 'close log file!
'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.screenupdating. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.screenupdating = True 'Turn on screen updating
'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.quit. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
XLApp.quit() 'Quit excel
End If
ConvertRoutine = True
ConvertRoutine_End:
Exit Function
ConvertRoutine_Err:
Select Case Err.Number
Case 13 'Type Mismatch
Resume Next
Case 94 'Invalid Use of Null
Resume Next
Case 3021 'No record found
Resume ConvertRoutine_End
Case 3022 'Duplicate record
Resume ConvertRoutine_End
Case -2147217887 'Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.
rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty
due to sheet being empty?? Found one so have put in this patch to bypass
it.
Resume AfterError
Resume 'debugging
Case Else
Me.Cursor = System.Windows.Forms.Cursors.Default
Call ProgErrorHandler("ConvertRoutine in
frmConvertT7ToData", False)
Resume ConvertRoutine_End
Resume 'Debugging
End Select
================================================== ===
I have retained the "On error Goto" because using Try, Catch, Finally I
can't find out how to trap a specific error like the -2147217887 Multiple
Step OLE error the help on this is "pants".
Siv
"Austin Ehlers" <li*******************@ku.edu> wrote in message
news:f0********************************@4ax.com... On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
wrote:
Hi,
As part of an evaluation of a small utility that I wrote that converts
somedata held in a large number of Excel spreadsheets into SQL Server, I
decidedto convert the utility to VB .NET and run both versions both in the IDE
andas standalone compiled exe and see what the difference in time to
complete
was.
The utility has to read 3842 single page Excel Sheets that contain a
block
of data 12 rows deep and 55 columns across, this is then transferred
into
anSQL Server table with the same number of columns.
I thought VB .NET might be slower, but I was surprised by how much.
Make sure you have Option Explicit On and Option Strict set. Watch
out for string concatenation (use System.Text.StringBuilder instead).
Watch out for legacy VB methods, classes, etc., and use .NET versions
instead.
I would like to release the findings to this community for comment, but
I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and
I
don't want to get into trouble with MS (particuarly as I make my living
on
the back of Microsoft's products). What I would like to release is just
a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.
If anyone from MS is looking in I would be pleased to know if you would
be
unhappy about me releasing my findings??
Siv
I ANAL, but you should be fine; you're trying to figure out what's
wrong with your code. It's to prevent articles like "C# vs. Java",
etc.
Austin
> I ANAL, but you should be fine; you're trying to figure out what's wrong with your code. It's to prevent articles like "C# vs. Java",
etc.
LOL, please inform me what you mean by "ANAL" as I think I have the wrong
idea :-\
Nick.
Hello Siv:
You wrote in conference microsoft.public.dotnet.languages.vb on Mon, 13 Sep
2004 01:04:50 +0100:
S> Details as follows:
....
it's very hard to judge, having the code you provided. The code does too
many various things. If you want to narrow down the performance difference,
you have to split your code by many parts, each repetitively performing some
atomic function, then measure each one.
That's why Microsoft is trying to limit publishing the benchmarks. Actually,
you should ask your question not in the newsgroup, but rather send it to
Microsfot media contacts. Most likely, they would try to evaluate what
audience would read your results, and if large, they would probably assign
someone who would eventually help you either to improve the performance, or
at least to get more accurate and representing results.
Vadim
Vadim,
Thanks for your comments. Sorry I didn't respond quickly have been "up to
my eyeballs in it!" and haven't looked in on this thread for a while.
You mention "Microsoft Media Contacts" what or who is that?? and how do I
contact them?
Siv
"Vadim Rapp" <vr@myrealbox.nospam.com> wrote in message
news:es**************@TK2MSFTNGP09.phx.gbl... Hello Siv:
You wrote in conference microsoft.public.dotnet.languages.vb on Mon, 13
Sep 2004 01:04:50 +0100:
S> Details as follows:
...
it's very hard to judge, having the code you provided. The code does too
many various things. If you want to narrow down the performance
difference, you have to split your code by many parts, each repetitively
performing some atomic function, then measure each one.
That's why Microsoft is trying to limit publishing the benchmarks.
Actually, you should ask your question not in the newsgroup, but rather
send it to Microsfot media contacts. Most likely, they would try to
evaluate what audience would read your results, and if large, they would
probably assign someone who would eventually help you either to improve
the performance, or at least to get more accurate and representing
results.
Vadim
Hello Siv:
You wrote in conference microsoft.public.dotnet.languages.vb on Fri, 17 Sep
2004 11:14:22 +0100:
S> You mention "Microsoft Media Contacts" what or who is that?? and how do
S> I contact them?
I have no such experience, but if you click "for journalists" at www.microsoft.com, then PR contacts, then you'll find "rapid response team".
Vadim
Cheers Vladim,
I'll try that.
Siv
"Vadim Rapp" <vr@myrealbox.nospam.com> wrote in message
news:uj**************@TK2MSFTNGP09.phx.gbl... Hello Siv:
You wrote in conference microsoft.public.dotnet.languages.vb on Fri, 17
Sep 2004 11:14:22 +0100:
S> You mention "Microsoft Media Contacts" what or who is that?? and how do
S> I contact them?
I have no such experience, but if you click "for journalists" at
www.microsoft.com, then PR contacts, then you'll find "rapid response
team".
Vadim
CreateObject creates an instance of Excel. The easiest workaround is
available even in VB6 which i think you did not try in VB6 itself. If you
are using ADODB, Create two different connections. One with your SQL server
and the other with the excel file. You can use the Jet OLEDB provider to
conect to EXCEL files. The only change will be that the SQL string will be
like
"select * from [Sheet1]"
of course with square brackets for sheet name which is considered as table.
In this way your whole excel data is in your recordset. Doing your
processing between recordsets which has Cachesize property set with an
optimised value something like 200 can make extraordinary performance
difference.
Now in Dot NET you can still open Excel files as Dataset or Datareader as
required. You have to find methods or providers and how to do this yourself.
The moral of the story is not to use CreateObject or GetObject. To get the
connection string of how to connect to excel files or text files using ADODB
or DotNET in multiple languages, check www.able-consulting.com and find
connection strings page.
I have done this with Excel files with 125 columns and over 80,000 rows with
multiple sheets and processed against AS400 tables [PF] using VB6's ADODB
connections. [ADODB connections are independent in same project for both
Excel and AS400]
Good luck
Raja Venkatesh rv*****@hotmail.com
"Siv" wrote: Hi,
As part of an evaluation of a small utility that I wrote that converts some
data held in a large number of Excel spreadsheets into SQL Server, I decided
to convert the utility to VB .NET and run both versions both in the IDE and
as standalone compiled exe and see what the difference in time to complete
was.
The utility has to read 3842 single page Excel Sheets that contain a block
of data 12 rows deep and 55 columns across, this is then transferred into an
SQL Server table with the same number of columns.
I thought VB .NET might be slower, but I was surprised by how much.
I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and I
don't want to get into trouble with MS (particuarly as I make my living on
the back of Microsoft's products). What I would like to release is just a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.
If anyone from MS is looking in I would be pleased to know if you would be
unhappy about me releasing my findings??
Siv
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Madhusudan Singh |
last post by:
Hi
I am using time.clock() to get the current time of the processor in seconds.
For my application, I need really high resolution but currently seem to be
limited to 0.01 second. Is there a way...
|
by: Dieter Vanderelst |
last post by:
Dear all,
I'm currently comparing Python versus Perl to use in a project that
involved a lot of text processing. I'm trying to determine what the most
efficient language would be for our...
|
by: wo_shi_big_stomach |
last post by:
Newbie to python writing a script to recurse a directory tree and delete
the first line of a file if it contains a given string. I get the same
error on a Mac running OS X 10.4.8 and FreeBSD 6.1.
...
|
by: fawzia |
last post by:
I want to read from file like this an find the winner for 500m, 1000m, and so on
But how can I compare the timings column by column( or extract the timing to compare)
Or do I have to declare...
|
by: smahaboob |
last post by:
Actually iam preparing a windows application. My winform duty is storing finding the start time and stop timings of the applications. when ever i opened the word my winform should catch this one and...
| | |
by: likong |
last post by:
Hi,
I am looking for a tool/product (commerical or shareware) to correlate
classic ASP pages (not ASP.NET) with timings of ADO calls made from
each ASP page. The information I am looking for...
|
by: aj |
last post by:
DB2 8.2 LUW FP14
Is there any real difference between
select blahblahblah... where blah IN (select blah......)
versus
select blahblahblah... where blah = ANY (select blah.....)
versus
select...
|
by: renuks |
last post by:
the command for subtacting 2 different railway timings
e.g:
start time :15:30
end time : 17:30
to get the answer as 1:00 hr
|
by: MRAB |
last post by:
I'm looking at the implementation of regular expressions in Python and
wrote a script to test my changes. This is the script:
import re
import time
base = "abc"
final = "d"
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |
