table & stored procedure structureCREATE 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)
)ASDeclare 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; EndEND^SET TERM ; ^
On Tuesday, February 24, 2015 3:04 PM, "Svein Erling Tysvær
[email protected] [firebird-support]"
<[email protected]> 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
#yiv1093483007 #yiv1093483007 -- #yiv1093483007ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1093483007
#yiv1093483007ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1093483007
#yiv1093483007ygrp-mkp #yiv1093483007hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv1093483007 #yiv1093483007ygrp-mkp #yiv1093483007ads
{margin-bottom:10px;}#yiv1093483007 #yiv1093483007ygrp-mkp .yiv1093483007ad
{padding:0 0;}#yiv1093483007 #yiv1093483007ygrp-mkp .yiv1093483007ad p
{margin:0;}#yiv1093483007 #yiv1093483007ygrp-mkp .yiv1093483007ad a
{color:#0000ff;text-decoration:none;}#yiv1093483007 #yiv1093483007ygrp-sponsor
#yiv1093483007ygrp-lc {font-family:Arial;}#yiv1093483007
#yiv1093483007ygrp-sponsor #yiv1093483007ygrp-lc #yiv1093483007hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1093483007
#yiv1093483007ygrp-sponsor #yiv1093483007ygrp-lc .yiv1093483007ad
{margin-bottom:10px;padding:0 0;}#yiv1093483007 #yiv1093483007actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1093483007
#yiv1093483007activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1093483007
#yiv1093483007activity span {font-weight:700;}#yiv1093483007
#yiv1093483007activity span:first-child
{text-transform:uppercase;}#yiv1093483007 #yiv1093483007activity span a
{color:#5085b6;text-decoration:none;}#yiv1093483007 #yiv1093483007activity span
span {color:#ff7900;}#yiv1093483007 #yiv1093483007activity span
.yiv1093483007underline {text-decoration:underline;}#yiv1093483007
.yiv1093483007attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv1093483007 .yiv1093483007attach div a
{text-decoration:none;}#yiv1093483007 .yiv1093483007attach img
{border:none;padding-right:5px;}#yiv1093483007 .yiv1093483007attach label
{display:block;margin-bottom:5px;}#yiv1093483007 .yiv1093483007attach label a
{text-decoration:none;}#yiv1093483007 blockquote {margin:0 0 0
4px;}#yiv1093483007 .yiv1093483007bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1093483007
.yiv1093483007bold a {text-decoration:none;}#yiv1093483007 dd.yiv1093483007last
p a {font-family:Verdana;font-weight:700;}#yiv1093483007 dd.yiv1093483007last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1093483007
dd.yiv1093483007last p span.yiv1093483007yshortcuts
{margin-right:0;}#yiv1093483007 div.yiv1093483007attach-table div div a
{text-decoration:none;}#yiv1093483007 div.yiv1093483007attach-table
{width:400px;}#yiv1093483007 div.yiv1093483007file-title a, #yiv1093483007
div.yiv1093483007file-title a:active, #yiv1093483007
div.yiv1093483007file-title a:hover, #yiv1093483007 div.yiv1093483007file-title
a:visited {text-decoration:none;}#yiv1093483007 div.yiv1093483007photo-title a,
#yiv1093483007 div.yiv1093483007photo-title a:active, #yiv1093483007
div.yiv1093483007photo-title a:hover, #yiv1093483007
div.yiv1093483007photo-title a:visited {text-decoration:none;}#yiv1093483007
div#yiv1093483007ygrp-mlmsg #yiv1093483007ygrp-msg p a
span.yiv1093483007yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1093483007
.yiv1093483007green {color:#628c2a;}#yiv1093483007 .yiv1093483007MsoNormal
{margin:0 0 0 0;}#yiv1093483007 o {font-size:0;}#yiv1093483007
#yiv1093483007photos div {float:left;width:72px;}#yiv1093483007
#yiv1093483007photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv1093483007
#yiv1093483007photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1093483007
#yiv1093483007reco-category {font-size:77%;}#yiv1093483007
#yiv1093483007reco-desc {font-size:77%;}#yiv1093483007 .yiv1093483007replbq
{margin:4px;}#yiv1093483007 #yiv1093483007ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv1093483007 #yiv1093483007ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1093483007
#yiv1093483007ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1093483007
#yiv1093483007ygrp-mlmsg select, #yiv1093483007 input, #yiv1093483007 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv1093483007
#yiv1093483007ygrp-mlmsg pre, #yiv1093483007 code {font:115%
monospace;}#yiv1093483007 #yiv1093483007ygrp-mlmsg *
{line-height:1.22em;}#yiv1093483007 #yiv1093483007ygrp-mlmsg #yiv1093483007logo
{padding-bottom:10px;}#yiv1093483007 #yiv1093483007ygrp-msg p a
{font-family:Verdana;}#yiv1093483007 #yiv1093483007ygrp-msg
p#yiv1093483007attach-count span {color:#1E66AE;font-weight:700;}#yiv1093483007
#yiv1093483007ygrp-reco #yiv1093483007reco-head
{color:#ff7900;font-weight:700;}#yiv1093483007 #yiv1093483007ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv1093483007 #yiv1093483007ygrp-sponsor
#yiv1093483007ov li a {font-size:130%;text-decoration:none;}#yiv1093483007
#yiv1093483007ygrp-sponsor #yiv1093483007ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv1093483007
#yiv1093483007ygrp-sponsor #yiv1093483007ov ul {margin:0;padding:0 0 0
8px;}#yiv1093483007 #yiv1093483007ygrp-text
{font-family:Georgia;}#yiv1093483007 #yiv1093483007ygrp-text p {margin:0 0 1em
0;}#yiv1093483007 #yiv1093483007ygrp-text tt {font-size:120%;}#yiv1093483007
#yiv1093483007ygrp-vital ul li:last-child {border-right:none
!important;}#yiv1093483007
