|Subject||RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010|
|Author||Svein Erling Tysvær|
> Select first(1) u.UNIT_ID from m_unit uDoes 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.
> 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;
I'll try to look a bit closer at this procedure later today, though I'm not certain I will get the opportunity.