Inconsistent behaviour while importing tables

[Abhishek Bhatt]

I need to import data programatically from spreadsheet file into database table. There are 30 such tables. User can upload multiple files at a time. For a table(which does not have any referential integrity relation) the alogorithm is as follow:
For each file, do the following operations :
a) Read the excel file
b) Begin Transaction
c) Delete all records from the tables
d) INSERT all of the record from excel into the table
e) If some error, then rollback the transaction for that table and repeat the above steps for the next table
f) If no error, then commit transaction and repeat the above steps for the next table
g) Show message specifying number of successfully uploaded table and names of unsuccessful files

All files are getting uploaded except one(Let's say ABC). This ABC file has 50 thousands records. All other tables have less than 5 thousand record. So I guess the problem is the huge data in the ABC table. The bigger problem is that the behaviour is inconsistent everytime I try to upload. It takes 50 mins to complete the upload of ABC and after that sometime it uploads only 12 thousand records, sometime 45 thousand and sometime 30 thousand. This is making me more worried. If there is some problem in logic of algorithm or of there is some technical limitation then alteast it should give the same output everytime I try to upload.

Any pointer why a program can give different output for different run?

In a Lighter Vein, if this problem is not solved I would submit it as a random number generator. I understand that there is no perfect random number generator :-))

[QVeen72]

Hi,

Do u run this Upload Program in a Multi-User Envi.
Say, 2 people are trying to upload same file at same time..?
If yes, then u will have to do some kind of Table-Locking..
And to what Backend DB u r exporting?
How did u open the Excel? Excel Object or using ADO?
What creiteria u use to check if it is End of File /Range in Excel..?
Can u paste the code here?

Regards
Veena

[Abhishek Bhatt]

Hi,

Do u run this Upload Program in a Multi-User Envi.
Say, 2 people are trying to upload same file at same time..?
If yes, then u will have to do some kind of Table-Locking..
And to what Backend DB u r exporting?
How did u open the Excel? Excel Object or using ADO?
What creiteria u use to check if it is End of File /Range in Excel..?
Can u paste the code here?

Regards
Veena

It's a single user env.
Backend DB is SQL server.
I am opening Excel obejct.
I am using mObjWorkSheet.C ells(1, 1).CurrentRegio n.ROWS.count
to find out the number of rows and then inserting each record one by one.

The actual code is very big so I am pasting the main method here
Private Sub UploadNormalTab les()
'Objects to store the count of non-RI tables
Dim intTableCount As Integer
'Object to store the table name
Dim strTableName As String
'Object to store the filepath of the table
Dim strFilePath As String
'Object to store the number of rows present in the SQL table
Dim intNumOfRows As Long

'objects used as counters
Dim intCounter1 As Long
Dim intCounter2 As Long
'Object to store the error message
Dim strErrorMsg As String
'Flag to check whether the table is empty
Dim blnTableEmpty As Boolean
'Object to store the Table names not uploaded successfully
Dim strTablesNotUpl oaded As String
'Flag to check for error condition if any
Dim intIsError As Integer
'Flag to check if the column names of XLS and the database are in the
'same sequence
Dim blnColumnNamesI nSeq As Boolean

intIsError = 0
strTablesNotUpl oaded = ""

For intCounter1 = 0 To mIntNormalTable sCount - 1
mIntErrorInLine = 0
On Error GoTo ErrorInXLS1
strTableName = ""
strTableName = mStrNormalTable List(intCounter 1)
mStrQuery = ""
intNumOfRows = 0

'-------------------Logic to open the xls file selected by the user--------------
strFilePath = mStrNormalTable Path(intCounter 1)
Set mObjExel = CreateObject("E xcel.Applicatio n")
Set mObjWorkBook = mObjExel.Workbo oks.Open _
(FileName:=strF ilePath, ReadOnly:=True)
If intIsError <> 0 Then
intIsError = 0
GoTo GetNextWorkBook 1
End If

Set mObjWorkSheet = mObjWorkBook.Wo rksheets(mObjWo rkBook.ActiveSh eet.Name)
GoTo ProcessFurther1

ErrorInXLS1:
mIntNumOfTblNot Uploaded = mIntNumOfTblNot Uploaded + 1
mStrErrorMessag e = mIntNumOfTblNot Uploaded & ". " & _
mStrErrorMessag e & mStrNormalTable List(intCounter 1) & _
gStrErrMsg129 & vbCrLf & "Error in opening Excel File"
intIsError = 1
Resume Next

ProcessFurther1 :
On Error GoTo ErrorInNormalTb lProcessing
'Starting the Transaction to upload the table
gConn.BeginTran s

'To fetch the number of rows and columns in the Excel Sheet
mIntNumOfRows = mObjWorkSheet.C ells(1, 1).CurrentRegio n.ROWS.count
mIntNumOfCols = mObjWorkSheet.C ells(1, 1).CurrentRegio n.Columns.count

'If there is no data in the table
If mIntNumOfRows < 1 Then
strErrorMsg = strTableName & gStrErrMsg132
GoTo ErrorInNormalTb lProcessing
End If

'function to fetch the data type for all the column from the DB n store
'it in rs4 recordset
Call FetchAllColumnD ataType(strTabl eName)

blnColumnNamesI nSeq = False
'Function to check if the column names of XLS and SQL table are in
'the same sequence or not
blnColumnNamesI nSeq = CheckColumnName sInXLS
If blnColumnNamesI nSeq = False Then
strErrorMsg = strTableName & gStrErrMsg130
GoTo ErrorInNormalTb lProcessing
End If

'function to delete all the rows from the table
Call TruncateTable(s trTableName)

'function to insert the rows into SQL database
For intCounter2 = 0 To mIntNumOfRows - 2
Call InsertRowInDB(i ntCounter2 + 2, strTableName)
Next intCounter2

mIntTablesUploa ded = mIntTablesUploa ded + 1

'Commit Transaction in case of no error in processing
GoTo CommitTransacti on1

ErrorInNormalTb lProcessing:
'Rollback uploading of data in case of error
gConn.RollbackT rans
mIntNumOfTblNot Uploaded = mIntNumOfTblNot Uploaded + 1
'Store the error message in the string
If mStrErrorMessag e <> "" Then
mStrErrorMessag e = mStrErrorMessag e & vbCrLf & vbCrLf & _
mIntNumOfTblNot Uploaded & ". "
Else
mStrErrorMessag e = mIntNumOfTblNot Uploaded & ". "
End If

mStrErrorMessag e = mStrErrorMessag e & strTableName & _
gStrErrMsg129 & vbCrLf

If strErrorMsg = "" Then
If mIntErrorInLine <> 0 Then
mStrErrorMessag e = mStrErrorMessag e & " Error in row : " & _
mIntErrorInLine & " " & Err.Description
Else
mStrErrorMessag e = mStrErrorMessag e & Err.Description
End If
Else
mStrErrorMessag e = mStrErrorMessag e & strErrorMsg
End If
If Err.Number = 0 Then
GoTo GetNextWorkBook 1
Else
Resume GetNextWorkBook 1
End If

CommitTransacti on1:
'If no error while uploading, then commit transaction
gConn.CommitTra ns
GetNextWorkBook 1:
'------------------Closing all the recordsets---------------------------------
If rs.State = 1 Then
rs.Close
End If
If rs2.State = 1 Then
rs2.Close
End If
If rs3.State = 1 Then
rs3.Close
End If
If rs4.State = 1 Then
rs4.Close
End If
If rs5.State = 1 Then
rs5.Close
End If
'------------------- Closing to the Excel Sheet----------------------------------
mObjExel.Active Workbook.Close
mObjExel.Quit
Next intCounter1
End Sub

[QVeen72]

Hi,

Why u r calling another Procedure to Insert Rows.here....

For intCounter2 = 0 To mIntNumOfRows - 2
Call InsertRowInDB(i ntCounter2 + 2, strTableName)
Next intCounter2

U can open recset locally and add rows there itself..
If u r opening a Recordset everytime in procedure "InsertRowI nDB" to add a row, Performance will be hugely affected.. try to do this part locally...

Regards
Veena

[Abhishek Bhatt]

How would be it differnt whether I call another method or I do it locally? Actually we have to use strored procedure for all kind of database interaction.
Please find below the code for insert


Private Sub InsertRowInDB(B yVal rowIndex As Long, ByVal strTableName As String)
Dim strTempString As String
Dim intCounter1 As Long
Dim cmd5 As New ADODB.Command

'string used to prepare the insert query
strTempString = "("
For intCounter1 = 1 To mIntNumOfCols
'function to find the data type of the column and prepare
'the query string accordingly
Call FindDataTypeGlo bal(strTempStri ng, intCounter1, rowIndex)
Next intCounter1
strTempString = strTempString & ")"
'---------Logic to call the stored procedure to execute the insert query---------

cmd5.ActiveConn ection = gConn
cmd5.CommandTyp e = adCmdStoredProc
cmd5.CommandTex t = "Lydo_spc_Inser tIntoTable"


cmd5.Parameters .Refresh
'To delete parameters, if any
For intCounter1 = 0 To cmd5.Parameters .count - 1
cmd5.Parameters .Delete (0)
Next intCounter1

mIntErrorInLine = rowIndex - 1
cmd5.Parameters .Append cmd5.CreatePara meter _
("tblName6", adVarChar, adParamInput, 50, strTableName)
cmd5.Parameters .Append cmd5.CreatePara meter _
("strQuery6" , adVarChar, adParamInput, 500, strTempString)
Set rs2 = cmd5.Execute

Set cmd5.ActiveConn ection = Nothing
Set cmd5 = Nothing

Exit Sub
End Sub

You r right. Performance is very bad. It takes 50 mins for 50 thousand records.

[QVeen72]

Hi,

In ur procedure, First u find the DataType, then Declare a
New Command and Then Do the Parameters.. and Then Call Stored Procedure..
Means, for 50000 Rows, U loop thru all these declaration,Che cking Deleting and then Destroying the object.. It will naturally take long time..
U also have to check , whether Error Handling in Stored Procedure is done properly or not.. may be, that's the reason for Inconsistent Behaviour..

Why not Simply Declare RecordSet and use AddNew ..?
I'am sure, it will not take more than 10 mins for 50000 recs..

But cant help if u have to follow company's procedure..

REgards
Veena