performance issue, import xls with vb

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

performance issue, import xls with vb

Author

Topic

inside-man
Starting Member

2 Posts

Posted - 2009-03-16 : 23:11:16

performance issue, import xls with vb

Hello, the problem I have is easy to describe.
I want to import data of an xls sheet (150 lines) with a small vb prog, the relevant code comes below. The import is going very well and takes 4 seconds, But I have to calculate a lot of the data after importing, there are some table triggers, and a lot of cursors, This all is working well, but takes approx. 35 seconds.
In my small vbprogramm I tried to apply a bargraph , this is not working.
There is a short line which shows success.
Now the problem, the VB Frontend shows only success if all the database triggers and calculations are done, this maybe in many cases useful, but here it seems that the frontend freezes. The only job of this prog is to import this 150 lines and start a trigger. But the user has to wait 35 seconds to get a return message.
A bargraph would be nice, or a changed approval....
I am not familar with VB, but there are only a few lines of code , the real problems are in SQL Server. The time to calculate is not the problem, but the behavior of the frontend....
REGARDS Randolf Balasus
below the relevant VB code:(hope this helps...)
(only one insert, few metadata, one update to fire up the trigger....)
(it seems the frontend has to wait until the last calculation in SQL Server is done....)

Public Sub importToServer(ByVal ExcelPath As String, ByVal ServerName As String, ByVal DBName As String, ByVal UserName As String, ByVal Password As String, ByVal InsertedTableName As String, ByVal txtxlsTableName As String, ByVal txtDRNO As String, ByVal txtkrz As String, ByVal txtANL As String, ByVal txtANLNR As String)
Try
Dim ExceCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelPath & "; Extended Properties=Excel 8.0"
Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ExceCon)
excelConnection.Open()
'Dim OleStr As String = "SELECT * INTO [ODBC; Driver={SQL Server};" & Server=Hoss;Database=Excel_Test;Uid=sa;Pwd=sa2008; ].[myTable] FROM [Sheet1$];"
Dim OleStr As String = "INSERT INTO [ODBC; Driver={SQL Server};Server=" & ServerName & ";Database=" & DBName & ";Uid=" & UserName & ";Pwd=" & Password & "; ].[" & InsertedTableName & "] SELECT * FROM [" & txtxlsTableName & "$];"
'Dim OleStr As String = "SELECT * INTO [ODBC; Driver={SQL Server};Server=" & ServerName & ";Database=" & DBName & ";Uid=" & UserName & ";Pwd=" & Password & "; ].[" & InsertedTableName & "] FROM [" & txtxlsTableName & "$];"
'Dim Ole2Str As String = "UPDATE [ODBC; Driver={SQL Server};Server=" & ServerName & ";Database=" & DBName & ";Uid=" & UserName & ";Pwd=" & Password & "; ].[" & InsertedTableName & "] SET F3 ='outdated' where F3 like 'Friedrich';"
Dim Ole3Str As String = "UPDATE [ODBC; Driver={SQL Server};Server=" & ServerName & ";Database=" & DBName & ";Uid=" & UserName & ";Pwd=" & Password & "; ].[tb_meta_le] SET drno='" & txtkrz & "' where id=2;"
Dim Ole4Str As String = "UPDATE [ODBC; Driver={SQL Server};Server=" & ServerName & ";Database=" & DBName & ";Uid=" & UserName & ";Pwd=" & Password & "; ].[tb_meta_le] SET drno='" & txtANL & "' where id=3;"
Dim Ole5Str As String = "UPDATE [ODBC; Driver={SQL Server};Server=" & ServerName & ";Database=" & DBName & ";Uid=" & UserName & ";Pwd=" & Password & "; ].[tb_meta_le] SET drno='" & txtANLNR & "' where id=4;"
Dim Ole6Str As String = "UPDATE [ODBC; Driver={SQL Server};Server=" & ServerName & ";Database=" & DBName & ";Uid=" & UserName & ";Pwd=" & Password & "; ].[tb_drno_le] SET drno='" & txtDRNO & "' where id=1;"

Dim excelCommand As New System.Data.OleDb.OleDbCommand(OleStr, excelConnection)
'Dim excel2Command As New System.Data.OleDb.OleDbCommand(Ole2Str, excelConnection)
Dim excel3Command As New System.Data.OleDb.OleDbCommand(Ole3Str, excelConnection)
Dim excel4Command As New System.Data.OleDb.OleDbCommand(Ole4Str, excelConnection)
Dim excel5Command As New System.Data.OleDb.OleDbCommand(Ole5Str, excelConnection)
Dim excel6Command As New System.Data.OleDb.OleDbCommand(Ole6Str, excelConnection)
excelCommand.ExecuteNonQuery()
'excel2Command.ExecuteNonQuery()
excel3Command.ExecuteNonQuery()
excel4Command.ExecuteNonQuery()
excel5Command.ExecuteNonQuery()
excel6Command.ExecuteNonQuery()
excelConnection.Close()
Catch ex As Exception
Throw New Exception("Error: " & ex.Message)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-03-17 : 03:38:12

What you can do is place a progress bar control along with a label control on top. Then to avoid freezing of the screen, and update progress status, add below code:

excelCommand.ExecuteNonQuery()
ProgressBar1.Value=20
Label1.Caption = "20% done"
doevents
'excel2Command.ExecuteNonQuery()
excel3Command.ExecuteNonQuery()
ProgressBar1.Value=40
Label1.Caption = "40% done"
doevents
excel4Command.ExecuteNonQuery()
ProgressBar1.Value=60
Label1.Caption = "60% done"
doevents
excel5Command.ExecuteNonQuery()
ProgressBar1.Value=80
Label1.Caption = "80% done"
doevents
excel6Command.ExecuteNonQuery()
ProgressBar1.Value=80
Label1.Caption = "100% done"
doevents

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

inside-man
Starting Member

2 Posts

Posted - 2009-03-19 : 04:27:09

Thanks for your answer, the approach seems to be o.k., I will test this code fast a spossible.
Randolf Balasus

O.K. I try to test it, but I have to declare the Progressbar, and the doevents?
Canyou give a text snippet for the declaration?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-03-20 : 09:25:42

Progress bar is a control which will be available if you add Microsoft windows common controls library as a reference. DoEvents is a statement, not a variable, so no need for declaration.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-20 : 11:52:23

35 seconds seems quite long for the SQL Server side of things for only 150 rows. You may be able to speed this up quite a bit by replacing the cursors and possibly the triggers with a set based approach. You might want to post the manipulation / calculations you are doing to see if this can be speeded up.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources