Board index » delphi » Major problem with Delphi4/Interbase, Help Needed

Major problem with Delphi4/Interbase, Help Needed

Ok people,

I have the following simple table. it holds business property
information.

CREATE TABLE LIST95 (
  BA_REFERENCE_NUMBER CHAR(25),
  PSD_CODE CHAR(6),
  DESCRIPTION CHAR(60),
  UARN CHAR(20) NOT NULL,
  FIRM_NAME VARCHAR(80),
  NUMBER_AND_NAME VARCHAR(80),
  STREET VARCHAR(80),
  TOWN VARCHAR(80),
  POSTAL_DISTRICT VARCHAR(80),
  COUNTY VARCHAR(80),
  POSTCODE CHAR(10),
  EFFECTIVE_DATE CHAR(12),
  PARTIAL_DOMESTIC_SIGNAL CHAR(2),
  ADOPTED_RATEABLE_VALUE DOUBLE PRECISION,
  SETTLEMENT_TYPE CHAR(2),
  BACODE CHAR(4)
)

And the following indicies:

/*  Index LISTINDEX  */
CREATE ASCENDING INDEX LISTINDEX ON LIST95 (STREET)
---

/*  Index LISTINDEX2  */
CREATE ASCENDING INDEX LISTINDEX2 ON LIST95 (POSTCODE)
---

/*  Index LISTINDEX3  */
CREATE ASCENDING INDEX LISTINDEX3 ON LIST95 (STREET, TOWN, POSTCODE,
PSD_CODE)
---

/*  Index LISTINDEX4  */
CREATE ASCENDING INDEX LISTINDEX4 ON LIST95 (NUMBER_AND_NAME, STREET)
---

/*  Index RDB$PRIMARY28  */
CREATE UNIQUE ASCENDING INDEX RDB$PRIMARY28 ON LIST95 (UARN)

I want to be able to input from my delphi application parts of the
postal code/street/ name/town etc and then insert the records which meet
the criteria into a copy of the table (LIST95C) which is empty. Easy
Huh?
I might for example want to insert all properties beginning with a
postal code of 'CH3' and a street called 'Boughton' etc etc.

In isql this is very easy.

INSERT INTO LIST95C SELECT * FROM LIST95 WHERE POSTCODE LIKE 'CH3%' AND
STREET LIKE '%BOUGHTON%';

This works a treat. Considering there is 1.7million records it is
absolutley instantaneous. I guess because of the indicies.

OK my problem is that I want to use a stored procedure to do this for
example as above with two input parameters from delphi. The problem is
it works but takes 5 minutes! as opposed to  0.5 of a second!

The stored procedure (purely for postcode searches only) is

CREATE PROCEDURE POSTCODESEARCH (
  REFCODE VARCHAR(10)
)  AS
begin
insert into list95C select * from list95 where postcode like :REFCODE;
end

The procedure in delpi that calls this is: (btw postcode is the name of
the edit field in delphi)

procedure TForm1.SpeedButton6Click(Sender: TObject);
begin
with storedproc1 do begin
refcode := postcode.text + '%';
parambyname('refcode').asstring := postcode.text + '%';
execproc;
table1.active:=false;
table1.active:=true;
edit3.text:=inttostr(table1.recordcount);
speedbutton7.visible:=true;
speedbutton6.visible:=false;
speedbutton4.visible:=true;
speedbutton1.visible:=true;
end;
end;

Note: it adds the % as a wildcard so someone can type in a partial
postcode.
Ive added a table on the page, a dataset, set the tables index to the
postcode based one, everything, however it is still miles too slow.

You can tell i aint even going to bother searching on more than the
postcode just yet if even one param aint working right. I also tried
storing the result of the delphi input into the postcode field in the
table. it stored 'CH3%' perfectly, no trailing blanks or nothing which
would upset the search.

Any help, very much appreciated

ANDY

 

Re:Major problem with Delphi4/Interbase, Help Needed


Quote
Andy Murphy wrote in message

<38C6B9B6.A4A0E...@rateablevalueagency.co.uk>...

Quote

>OK my problem is that I want to use a stored procedure to do this for
>example as above with two input parameters from delphi. The problem is
>it works but takes 5 minutes! as opposed to  0.5 of a second!

>The stored procedure (purely for postcode searches only) is

>CREATE PROCEDURE POSTCODESEARCH (
>  REFCODE VARCHAR(10)
>)  AS
>begin
>insert into list95C select * from list95 where postcode like :REFCODE;
>end

As Robert explained, as a parameter it cannot use the index because you may
place wild cards in it. However you can use 'starting with':

 insert into list95C select * from list95
 where postcode starting with :REFCODE

That should make the index usable again.

Note that boht 'like' and 'starting with' are case-sensitive.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:Major problem with Delphi4/Interbase, Help Needed


Could be because you use parameter in stored proc, so the engine won't use
indexes
select * from table where <condition>
condition 1: POSTCODE LIKE 'CH3%' - it will prepare to use index
condition 2: POSTCODE LIKE '%CH3%' - it won't prepare to use index
condition 3: POSTCODE LIKE :PARAM - also won't prepare to use index, because
param value could be either 1. or 2.

In this case: INSERT INTO LIST95C SELECT * FROM LIST95 WHERE POSTCODE LIKE
'CH3%' AND STREET LIKE '%BOUGHTON%';
it uses index for PostCode and filters meanwhile for second condition.

Better use dynamically built queries, because with exact values it can
allways choose optimal plan.

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Re:Major problem with Delphi4/Interbase, Help Needed


Yeah, i agree the time difference seems to be because it wasn't using the index.

I only ever need a wildcard at the end of a postcode 'CH3%' but
towns/streets/countys etc need to be '%BOUGHTON%' so it will bring up a record
with the street address of '26, BOUGHTON ROAD' . Like I say it works great
typing the line into isql. Weird.

Ok would it be better to store the params in a small one record table and then
do a where statement using the two tables?? would that invoke the indexes???

Thanks. ANDY

Quote
Robert Cerny wrote:
> Could be because you use parameter in stored proc, so the engine won't use
> indexes
> select * from table where <condition>
> condition 1: POSTCODE LIKE 'CH3%' - it will prepare to use index
> condition 2: POSTCODE LIKE '%CH3%' - it won't prepare to use index
> condition 3: POSTCODE LIKE :PARAM - also won't prepare to use index, because
> param value could be either 1. or 2.

> In this case: INSERT INTO LIST95C SELECT * FROM LIST95 WHERE POSTCODE LIKE
> 'CH3%' AND STREET LIKE '%BOUGHTON%';
> it uses index for PostCode and filters meanwhile for second condition.

> Better use dynamically built queries, because with exact values it can
> allways choose optimal plan.

> --
> ----------------------
> Regards
> Robert Cerny
> Remove both qwe when replying
> email: robert.qwe.ce...@neosys.xrs.qwe.si

> No questions via email, unless explicitly invited.

Re:Major problem with Delphi4/Interbase, Help Needed


Thanks a million Wayne, That works fine.

I'm still quite new to delphi/interbase and the user help files are very
basic so its taking longer than expected for me to pick it up.

Thanks again

ANDY

Other Threads