469,287 Members | 2,442 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,287 developers. It's quick & easy.

Inconsistent behaviour while importing tables

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 :-))
Sep 27 '07 #1
5 1103
QVeen72
1,445 Expert 1GB
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
Sep 27 '07 #2
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.Cells(1, 1).CurrentRegion.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 UploadNormalTables()
'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 strTablesNotUploaded 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 blnColumnNamesInSeq As Boolean

intIsError = 0
strTablesNotUploaded = ""

For intCounter1 = 0 To mIntNormalTablesCount - 1
mIntErrorInLine = 0
On Error GoTo ErrorInXLS1
strTableName = ""
strTableName = mStrNormalTableList(intCounter1)
mStrQuery = ""
intNumOfRows = 0

'-------------------Logic to open the xls file selected by the user--------------
strFilePath = mStrNormalTablePath(intCounter1)
Set mObjExel = CreateObject("Excel.Application")
Set mObjWorkBook = mObjExel.Workbooks.Open _
(FileName:=strFilePath, ReadOnly:=True)
If intIsError <> 0 Then
intIsError = 0
GoTo GetNextWorkBook1
End If

Set mObjWorkSheet = mObjWorkBook.Worksheets(mObjWorkBook.ActiveSheet.N ame)
GoTo ProcessFurther1

ErrorInXLS1:
mIntNumOfTblNotUploaded = mIntNumOfTblNotUploaded + 1
mStrErrorMessage = mIntNumOfTblNotUploaded & ". " & _
mStrErrorMessage & mStrNormalTableList(intCounter1) & _
gStrErrMsg129 & vbCrLf & "Error in opening Excel File"
intIsError = 1
Resume Next

ProcessFurther1:
On Error GoTo ErrorInNormalTblProcessing
'Starting the Transaction to upload the table
gConn.BeginTrans

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

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

'function to fetch the data type for all the column from the DB n store
'it in rs4 recordset
Call FetchAllColumnDataType(strTableName)

blnColumnNamesInSeq = False
'Function to check if the column names of XLS and SQL table are in
'the same sequence or not
blnColumnNamesInSeq = CheckColumnNamesInXLS
If blnColumnNamesInSeq = False Then
strErrorMsg = strTableName & gStrErrMsg130
GoTo ErrorInNormalTblProcessing
End If

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

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

mIntTablesUploaded = mIntTablesUploaded + 1

'Commit Transaction in case of no error in processing
GoTo CommitTransaction1

ErrorInNormalTblProcessing:
'Rollback uploading of data in case of error
gConn.RollbackTrans
mIntNumOfTblNotUploaded = mIntNumOfTblNotUploaded + 1
'Store the error message in the string
If mStrErrorMessage <> "" Then
mStrErrorMessage = mStrErrorMessage & vbCrLf & vbCrLf & _
mIntNumOfTblNotUploaded & ". "
Else
mStrErrorMessage = mIntNumOfTblNotUploaded & ". "
End If

mStrErrorMessage = mStrErrorMessage & strTableName & _
gStrErrMsg129 & vbCrLf

If strErrorMsg = "" Then
If mIntErrorInLine <> 0 Then
mStrErrorMessage = mStrErrorMessage & " Error in row : " & _
mIntErrorInLine & " " & Err.Description
Else
mStrErrorMessage = mStrErrorMessage & Err.Description
End If
Else
mStrErrorMessage = mStrErrorMessage & strErrorMsg
End If
If Err.Number = 0 Then
GoTo GetNextWorkBook1
Else
Resume GetNextWorkBook1
End If

CommitTransaction1:
'If no error while uploading, then commit transaction
gConn.CommitTrans
GetNextWorkBook1:
'------------------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.ActiveWorkbook.Close
mObjExel.Quit
Next intCounter1
End Sub
Sep 27 '07 #3
QVeen72
1,445 Expert 1GB
Hi,

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

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

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

Regards
Veena
Sep 27 '07 #4
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(ByVal 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 FindDataTypeGlobal(strTempString, intCounter1, rowIndex)
Next intCounter1
strTempString = strTempString & ")"
'---------Logic to call the stored procedure to execute the insert query---------

cmd5.ActiveConnection = gConn
cmd5.CommandType = adCmdStoredProc
cmd5.CommandText = "Lydo_spc_InsertIntoTable"


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.CreateParameter _
("tblName6", adVarChar, adParamInput, 50, strTableName)
cmd5.Parameters.Append cmd5.CreateParameter _
("strQuery6", adVarChar, adParamInput, 500, strTempString)
Set rs2 = cmd5.Execute

Set cmd5.ActiveConnection = Nothing
Set cmd5 = Nothing

Exit Sub
End Sub

You r right. Performance is very bad. It takes 50 mins for 50 thousand records.
Sep 27 '07 #5
QVeen72
1,445 Expert 1GB
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,Checking 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
Sep 27 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Simon Wittber | last post: by
5 posts views Thread by dixie | last post: by
3 posts views Thread by codeman | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.