|Subject||Re: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010|
unit_id is primary key and this table(m_unit ) has hardly 20 records
On Tuesday, February 24, 2015 3:43 PM, "Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support]" <firstname.lastname@example.org> wrote:
> 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;
Does this select use any index at all? How many records are there in M_UNIT (and similarly for other tables referred in a similar manner in this stored procedure or its sub-procedures)? Generally, using functions on fields normally means they don't use indexes (computed indexes being the exception). Unless such tables are small, calling this procedure 100000 times could well be the reason for your program taking over 10 hours.
I'll try to look a bit closer at this procedure later today, though I'm not certain I will get the opportunity.