Your SP I would change in something like this:
CREATE PROCEDURE KI_SUCHEPERSONEN_HELP2(
SUCHNAME1 VARCHAR(200) CHARACTER SET WIN1252,
BEREICH VARCHAR(10) CHARACTER SET WIN1252
) AS
DECLARE VARIABLE v_id as integer;
BEGIN
FOR select IDPerson from personen
where SuchName like :SuchName1
Order by idperson
Into :v_ID
Do
begin
insert into HilfeListe(ID,Bereich) values (:v_id, :bereich);
end
END
Best regards,
Paul
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ulrich Niemand
Sent: Monday, March 24, 2008 4:37 PM
To: firebird-net-provider@lists.sourceforge.net
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..
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
