| Subject | Re: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010 |
|---|---|
| Author | SUKHEN DASS |
| Post date | 2015-02-24T09:44:32Z |
table & stored procedure structure
CREATE TABLE M_ITEM
(
ITEM_IDS Integer NOT NULL,
SP_ITEM_CODES Varchar(20), ----Its a column not stored procedure,in front end we check if any columns starts with SP_ then code to be generated using sp
ITEM_NAMES Varchar(50),
RATE Decimal(10,2),bla,bla),
CONSTRAINT PK_M_ITEM_IDS PRIMARY KEY (ITEM_IDS),
CONSTRAINT UNQ_M_ITEM_SP_ITEM_CODES UNIQUE (SP_ITEM_CODES)
)
SET TERM ^ ;
ALTER PROCEDURE USP_CHECK_AND_GET_ITEM_EXIST (
ITEM_CODES Varchar(20),
ITEM_PART_CODES Varchar(20),
ITEM_NAME Varchar(50),
CUSTOM_PART_CODE Varchar(20),
CUSTOM_PART_NAME Varchar(50),
MAIN_UNIT Varchar(10),
SUB_UNIT Varchar(10),
TAX Varchar(20),
ITEM_GROUP Varchar(50),
ITEM_CATEGORY Varchar(50) )
RETURNS (
ITEM_IDS Integer,
ITEM_CODE Varchar(20),
ITEM_PART_NAME Varchar(50),
ITEM_PART_CODE Varchar(20),
ITEM_SALE_MAIN_UNIT_ID Integer,
ITEM_SALE_SUB_UNIT_ID Integer,
ITEM_SALE_RATE_UNIT_ID Integer,
ITEM_PURCHASE_MAIN_UNIT_ID Integer,
ITEM_PURCHASE_SUB_UNIT_ID Integer,
ITEM_PURCHASE_RATE_UNIT_ID Integer,
OPENING_UNIT_ID Integer,
STOCK_UNIT_ID Integer,
NMAIN_UNIT_ID Integer,
NSUB_UNIT_ID Integer,
NTAX_ID Integer,
NGROUP_ID Integer,
NMAIN_UNIT_TO_SUB_UNIT_FACTOR Decimal(10,3),
SIS_LINK_EXIST Char(1),
NITEM_CATEGORY_ID Integer,
NSALE_RATE Decimal(10,2),
NPUR_RATE Decimal(10,2),
NSALES_DISC_PER Decimal(10,2),
NPUR_DISC_PER Decimal(10,2) )
AS
Declare variable Main_Unit_Id integer=0;
Declare variable Sub_Unit_Id integer=0;
Declare variable Tax_Id integer=0;
Declare variable Group_Id integer=0;
Declare variable Item_Category_Id integer=0;
Declare MAIN_UNIT_TO_SUB_UNIT_FACTOR DECIMAL(10,3)=0;
Declare variable Is_Link_Exist Char(1)='N';
BEGIN
Select first(1) u.UNIT_ID from m_unit u where (upper(trim(u.UNIT_NAME))=upper(trim(:MAIN_UNIT)) or upper(trim(u.UNIT_SYMBOL))=upper(trim(:MAIN_UNIT))) and coalesce(CONVERSION_UNIT_ID,0)=0 Into :Main_Unit_Id;
Select first(1) u.UNIT_ID from m_unit u where (upper(trim(u.UNIT_NAME))=upper(trim(:SUB_UNIT)) or upper(trim(u.UNIT_SYMBOL))=upper(trim(:SUB_UNIT))) and coalesce(CONVERSION_UNIT_ID,0)=0 Into :Sub_Unit_Id;
Select first(1) s.STATUTORY_IDS from m_statutory s where upper(trim(s.STATUTORY_NAMES))=upper(trim(:TAX)) Into :Tax_Id;
if (trim(:item_group)='') Then
Begin
item_group='General';
End
Select first(1) g.GROUP_ID from m_item_group g where upper(trim(g.GROUP_NAME))=upper(trim(:item_group)) Into :Group_Id;
if (:Main_Unit_Id<>0 And :Sub_Unit_Id<>0) then
Begin
SELECT first(1) p.IS_EXIST FROM USP_CHECK_EXIST_UNITS(:Main_Unit_Id, :Sub_Unit_Id) p Into :Is_Link_Exist;
SELECT first(1) p.MAIN_UNIT_FACTOR FROM USP_GET_UNIT_FACTOR(:Main_Unit_Id, :Sub_Unit_Id, :Sub_Unit_Id) p Into :MAIN_UNIT_TO_SUB_UNIT_FACTOR;
End
if (trim(:item_category)<>'') Then
Begin
Select first(1) m.ID from FINNECAL_MISC_MASTER m where upper(trim(m.DATA))=upper(trim(:item_category)) And m.NAME='Item Category' Into :Item_Category_Id;
End
For
Select first(1) ITEM_IDS,SP_ITEM_CODES,ITEM_NAMES,ITEM_PART_CODES,SALE_UNIT_IDS,SUB_UNIT_ID_SALE,SALE_RATE_PER_UNIT_ID,
PUR_UNIT_IDS,SUB_UNIT_ID_PUR,PUR_RATE_PER_UNIT_ID,OPENING_UNIT_IDS,STOCK_UNIT_IDS,Main_Unit_Id,Sub_Unit_Id,Tax_Id,Group_Id,
MAIN_UNIT_TO_SUB_UNIT_FACTOR,Is_Link_Exist,Item_Category_Id,ITEM_SALE_RATES,ITEM_PURCHASE_RATES,ITEM_SALES_DISCOUNTS,ITEM_PURCHASE_DISCOUNTS
from (
Select 1 as OrderBy,a.ITEM_IDS,a.SP_ITEM_CODES,a.ITEM_NAMES,a.ITEM_PART_CODES,a.SALE_UNIT_IDS,a.SUB_UNIT_ID_SALE,a.SALE_RATE_PER_UNIT_ID,
a.PUR_UNIT_IDS,a.SUB_UNIT_ID_PUR,a.PUR_RATE_PER_UNIT_ID,a.OPENING_UNIT_IDS,a.STOCK_UNIT_IDS,
:Main_Unit_Id Main_Unit_Id,:Sub_Unit_Id Sub_Unit_Id,:Tax_Id Tax_Id,:Group_Id Group_Id,:MAIN_UNIT_TO_SUB_UNIT_FACTOR MAIN_UNIT_TO_SUB_UNIT_FACTOR,
:Is_Link_Exist Is_Link_Exist,:Item_Category_Id Item_Category_Id,a.ITEM_SALE_RATES,a.ITEM_PURCHASE_RATES,a.ITEM_SALES_DISCOUNTS,a.ITEM_PURCHASE_DISCOUNTS
from M_ITEM a
where ((upper(trim(a.SP_ITEM_CODES))=upper(trim(:ITEM_CODES)) And trim(:ITEM_CODES)<>'')) or
(upper(trim(a.ITEM_PART_CODES))=upper(trim(:ITEM_PART_CODES)) And trim(:ITEM_PART_CODES)<>'')
or (upper(trim(a.ITEM_NAMES))=upper(trim(:ITEM_NAME)) And trim(:ITEM_NAME)<>'')
or (upper(trim(a.ITEM_CUSTOM_CODE))=upper(trim(:CUSTOM_PART_CODE)) And trim(:CUSTOM_PART_CODE)<>'')
or (upper(trim(a.ITEM_CUSTOM_NAME))=upper(trim(:CUSTOM_PART_NAME)) And trim(:CUSTOM_PART_NAME)<>'')
union
Select 2 as OrderBy,0,'','','',0,0,0,0,0,0,0,0,
:Main_Unit_Id,:Sub_Unit_Id,:Tax_Id,:Group_Id,:MAIN_UNIT_TO_SUB_UNIT_FACTOR,:Is_Link_Exist,:Item_Category_Id,0,0,0,0 from FINNECAL_LAST_UPDATE a --RDB$DATABASE a
where :Main_Unit_Id>0 or :Sub_Unit_Id>0 or :Tax_Id >0 or (:Group_Id>0 or :item_group='General')
or :MAIN_UNIT_TO_SUB_UNIT_FACTOR>0 or :Is_Link_Exist<>'' or :Item_Category_Id>0) as T Order by OrderBy
Into :ITEM_IDS,:Item_Code,:Item_Part_Name,:Item_Part_Code,:Item_Sale_Main_Unit_Id,
:Item_Sale_Sub_Unit_Id,:Item_Sale_Rate_Unit_Id,:Item_Purchase_Main_Unit_Id,:Item_Purchase_Sub_Unit_Id,:Item_Purchase_Rate_Unit_Id,
:Opening_Unit_Id,:Stock_Unit_Id,:nMain_Unit_Id,:nSub_Unit_Id,:nTax_Id,:nGroup_Id,:nMAIN_UNIT_TO_SUB_UNIT_FACTOR,:sIs_Link_Exist,:nItem_Category_Id,
:nSale_Rate,:nPur_Rate,:nSales_Disc_Per,:nPur_Disc_Per
Do
Begin
SUSPEND;
End
END^
SET TERM ; ^
On Tuesday, February 24, 2015 3:04 PM, "Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:
>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