Re: [Firebird-net-provider] Peformance running a procedure

[Mercea P. <pau...@al...>]

By the way, you should have an index on SuchName field!

 

Regards,

Paul

 

From: fir...@li... [mailto:fir...@li...] On Behalf Of Ulrich Niemand
Sent: Monday, March 24, 2008 4:37 PM
To: fir...@li...
Subject: [Firebird-net-provider] Peformance running a procedure

 


Hi forum!
My problem is not related to the provider (thanks for your great work at this topic, it runs perfect for me), but perhaps you can help me.

If I run this procedure it take 1,7 seconds !!! to finish. 
The code runs again a table witch have 400.000 rows and a index on field Suchname.

   CREATE PROCEDURE KI_SUCHEPERSONEN_HELP2(
      SUCHNAME1 VARCHAR(200) CHARACTER SET WIN1252,
      BEREICH VARCHAR(10) CHARACTER SET WIN1252
   ) AS
  DECLARE VARIABLE tmpSQL varchar(300);
  BEGIN
     insert into HilfeListe(ID,Bereich)
     select IDPerson, :Bereich from personen
     where SuchName like :SuchName1;
  END


Because the bad performance of this approch I change the procedure to this:

   CREATE PROCEDURE KI_SUCHEPERSONEN_HELP(
      SUCHNAME1 VARCHAR(200) CHARACTER SET WIN1252,
      BEREICH VARCHAR(10) CHARACTER SET WIN1252
   ) AS
   DECLARE VARIABLE tmpSQL varchar(300);
   BEGIN
      tmpSQL =          'insert into HilfeListe(ID,Bereich)';
      tmpSQL = tmpSQL || ' select IDPerson, ''' || Bereich || ''' from personen ';
      tmpSQL = tmpSQL || ' where SuchName like ''' || SuchName1 || '''';
      EXECUTE STATEMENT tmpSQL;
   END

This works fine, and takes (totaly unexpected?) only 10 MILLI-Seconds (10ms) !!!.

In order to avoiding problems of SQL-Injection or "wrong" contens 
of the variable SuchName1 (including -- or ' and so on) I like to switch back to 
the first "version" of giving the searchvalue to the query.

Do you have any idea how to speed up the query of the first approch?
What is wrong with this? 
What is the reason for the bad performance of this code?

Thanks in advance for any help or a hint to a forum with could help me.
Happy easter.
Niels

 

  _____  

 <http://uk.rd.yahoo.com/evt=51524/*http:/de.mobile.yahoo.com/interstitial?refer=e00127%0a> Lesen Sie Ihre E-Mails auf dem Handy..