>For iLoop as Integer=0 to grid.Rows.Count-1 '--------Grid Has 100,000 Rows
>'---------Sometimes Hang Here Also---------
>rdr = Get_Reader("USP_CHECK_AND_GET_ITEM_EXIST('" &
>Mid(Trim(dicFields_Name.Item("SP_ITEM_CODES")), 1, 20) & "','" &
>Mid(Trim(dicFields_Name.Item("ITEM_PART_CODES")), 1, 20) & "','" &
>Mid(Trim(dicFields_Name.Item("ITEM_NAMES")), 1, 50) & "','" &
>Mid(Trim(dicFields_Name.Item("CUSTOM_PART_CODE")), 1, 20) & "','" &
>Mid(Trim(dicFields_Name.Item("CUSTOM_PART_NAME")), 1, 50) & "','" &
>Mid(Trim(dicFields_Name.Item("SALE_UNIT")), 1, 10) & "','" &
>Mid(Trim(dicFields_Name.Item("SALE_SUB_UNIT")), 1, 10) & "','" &
>Mid(Trim(dicFields_Name.Item("ITEM_LOCAL_TAX")), 1, 20) & "','" &
>Mid(Trim(dicFields_Name.Item("ITEM_GROUP")), 1, 50) & "','" &
>Mid(Trim(dicFields_Name.Item("ITEM_CATEGORY_ID")), 1, 50) & "')", fbTrans, ,
>myImportConnection)
...
>If Is_Update=True Then
> If
> Update_Item_Master(nItem_IDS,Item_Name,Rate,bla,bla,bla,myImportConnection)=True
> Then
...
>Public Function
>Update_Item_Master(Var1,Var2,Var3,Bla,Bla,Bla,myImportConnection)
>Try
> 'Insert Statement
> PrePare_Column_Parameter_And_Values
>
> Using fCommand As FbCommand = myImportConnection.CreateCommand
> fCommand.Cancel()
> fCommand.CommandText = "Insert Into M_ITEM(" &
>strColumns.ToString.TrimEnd(",") & ") Values(" &
>strValues.ToString.TrimEnd(",") & ")"
> fCommand.Connection = myImportConnection
> fCommand.CommandType = CommandType.Text
> fCommand.Transaction = fbTrans
> myResult = fCommand.BeginExecuteNonQuery(Nothing, Nothing)
>
> 'While Not myResult.IsCompleted
> 'Me.Text = lblInvoice_Caption.Text & "[Processing Row No."
>& Row_No.ToString() & " - Adding In Item Master]"
> 'End While
> 'iError = fCommand.EndExecuteNonQuery(myResult)
>
> iError = fCommand.ExecuteNonQuery() '-------Hang In THis
> Line--------
> fCommand.Parameters.Clear()
> End Using
> Return True
>Catch ex as Exception
> return False
>Finally
>End Try
>End Function
Thanks, though not quite the type of code I'd expected (I'd expected more SQL
and less VB). I notice a couple of things:
a) You try to insert when Is_Update is true (not false)
b) You create a completely new statement for each row
Now, I don't know whether or not it is deliberate to INSERT inside the
Update_Item_Master function or not. What I do know, is that creating a new
statement 100000 times is considerably slower (though 10 hours would mean only
three record per second and that would surprise me) than writing one statement
with parameters, prepare it once and execute it 100000 times with different
parameter values.
I do not know VB and cannot give much other hints from this code, I guess the
problem could either be on the Firebird side (you could show us more
Firebird-related code, e.g. the source of SP_ITEM_CODES if that is a Firebird
stored procedure, or one of the troublesome INSERT queries that you end up
creating (i.e. strColumns and strValues expanded) or on the VB side (this list
cannot help with VB issues).
Set
