Support for users of Firebird/InterBaseI'm talking about FB 2.0 rc5, which states that it has an official PT_BR collation inside.
Thanks
Jorge

----- Original Message -----
From: firebird-support@yahoogroups.com
To: firebird-support@yahoogroups.com
Sent: Saturday, October 14, 2006 8:00 AM
Subject: [firebird-support] Digest Number 4284


Support for users of Firebird/InterBase
Messages In This Digest (10 Messages)
1a. Optimize SP From: sasidhardoc
1b. Re: Optimize SP From: Adam
2. Re: timestamp trigger? From: Adam
3a. How to install Firebird with a different Collation From: luiscolmenter
3b. Re: How to install Firebird with a different Collation From: Alexandre Benson Smith
4a. something very strange going on here From: martinknappe
4b. Re: something very strange going on here From: Adam
4c. Re: something very strange going on here From: Milan Babuskov
4d. Re: something very strange going on here From: martinknappe
5. Re: how do i speed this up From: martinknappe
View All Topics | Create New Topic Messages
1a. Optimize SP
Posted by: "sasidhardoc" madhusasidhar@... sasidhardoc
Fri Oct 13, 2006 9:44 pm (PST)
The SP below, slows down to several minutes when the tables become
larger. How would I optimize the query. The query plan is below.

BEGIN
FOR SELECT ENTY_PATIENT.PATIENT_ID PRIMARYKEY,
ENTY_PERSON.PERSON_LASTNAME || ', ' || ENTY_PERSON.PERSON_FIRSTNAME
"SEARCH RESULT"
FROM ENTY_PATIENT INNER JOIN
ENTY_PERSON ON ENTY_PATIENT.ENTITY_ID = ENTY_PERSON.ENTITY_ID
WHERE (ENTY_PERSON.ENTITY_ID
IN(SELECT ENTITY_ID FROM ENTY_PATIENT
WHERE (PATIENT_ID IN (SELECT PATIENT_ID FROM LINK_MEDICALFACILITY_PATIENT
WHERE (MEDICALFACILITY_ID = :MEDICALFACILITY_ID_IN)))))
AND (lower(ENTY_PERSON.PERSON_LASTNAME) LIKE lower(:SEARCHSTRING_IN)
|| '%')
INTO
:PRIMARYKEY,
:"SEARCH RESULT"
DO
SUSPEND;
END


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
1b. Re: Optimize SP
Posted by: "Adam" s3057043@... s3057043
Fri Oct 13, 2006 10:36 pm (PST)
--- In firebird-support@yahoogroups.com, "sasidhardoc"
<madhusasidhar@...> wrote:
>
> The SP below, slows down to several minutes when the tables become
> larger. How would I optimize the query. The query plan is below.
>
> BEGIN
> FOR SELECT ENTY_PATIENT.PATIENT_ID PRIMARYKEY,
> ENTY_PERSON.PERSON_LASTNAME || ', ' || ENTY_PERSON.PERSON_FIRSTNAME
> "SEARCH RESULT"
> FROM ENTY_PATIENT INNER JOIN
> ENTY_PERSON ON ENTY_PATIENT.ENTITY_ID = ENTY_PERSON.ENTITY_ID
> WHERE (ENTY_PERSON.ENTITY_ID
> IN(SELECT ENTITY_ID FROM ENTY_PATIENT
> WHERE (PATIENT_ID IN (SELECT PATIENT_ID FROM
LINK_MEDICALFACILITY_PATIENT
> WHERE (MEDICALFACILITY_ID = :MEDICALFACILITY_ID_IN)))))
> AND (lower(ENTY_PERSON.PERSON_LASTNAME) LIKE lower(:SEARCHSTRING_IN)
> || '%')
> INTO
> :PRIMARYKEY,
> :"SEARCH RESULT"
> DO
> SUSPEND;
> END
>

There is no difference optimising a stored procedure query from a
normal query. Get the query to behave how you like before wrapping it
in a stored procedure.

This one is difficult because the SQL itself seems to be
overcomplicated. You have used effectively a re-entrant join using
nested subselects, yet you don't need to unless I am reading your
original query wrong.

Firstly, I will rewrite your query as I understand it without the
nested subselects.

SELECT ENTY_PATIENT.PATIENT_ID, ENTY_PERSON.PERSON_LASTNAME || ', ' ||
ENTY_PERSON.PERSON_FIRSTNAME
FROM LINK_MEDICALFACILITY_PATIENT LMP
JOIN ENTY_PATIENT EPA1 ON (LMP.PATIENT_ID = EPA1.PATIENT_ID)
JOIN ENTY_PERSON EPE ON (EPA1.ENTITY_ID = EPE.ENTITY_ID)
WHERE LMP.MEDICALFACILITY_ID = :MEDICALFACILITY_ID_IN
AND UPPER(EPE.PERSON_LASTNAME) STARTING WITH UPPER(:SEARCHSTRING_IN)

I have switched around part of your where clause because:

UPPER is included in the engine of 1.5+ (maybe even earlier), whereas
lower is available only in a UDF library.
Starting with is easier to read than "Like lower(:searchstring_in) || '%'"

Now a couple of observations.

If you have a foreign key constraint on
LINK_MEDICALFACILITY_PATIENT.MEDICALFACILITY_ID, than the underlying
index will be used. If your database contains very few MEDICALFACILITY
records, this index may be worthless or even counter productive.

You are also doing a case insensitive search on
ENTY_PERSON.PERSON_LASTNAME, but you achieve this using an expression,
which means that no index can be used. (Note that in FB2+, you will be
able to add an index on such an expression). One possible solution is
to create a field in ENTY_PERSON called PERSON_LASTNAME_UPPER. Define
a before insert or update trigger that contains the line.

NEW.PERSON_LASTNAME_UPPER = UPPER(NEW.PERSON_LASTNAME);

You can create an index on this field and can use it in your case
insensitive searches.

SELECT ENTY_PATIENT.PATIENT_ID, ENTY_PERSON.PERSON_LASTNAME || ', ' ||
ENTY_PERSON.PERSON_FIRSTNAME
FROM LINK_MEDICALFACILITY_PATIENT LMP
JOIN ENTY_PATIENT EPA1 ON (LMP.PATIENT_ID = EPA1.PATIENT_ID)
JOIN ENTY_PERSON EPE ON (EPA1.ENTITY_ID = EPE.ENTITY_ID)
WHERE LMP.MEDICALFACILITY_ID = :MEDICALFACILITY_ID_IN
AND EPE.PERSON_LASTNAME_UPPER STARTING WITH UPPER(:SEARCHSTRING_IN)

I make the following assumptions.

MEDICALFACILITY -> relatively few records.
ENTY_PATIENT -> lots of records
ENTY_PERSON -> roughly same as enty_patient
LINK_MEDICALFACILITY_PATIENT -> only a few records per patient

If my assumptions are correct and if such a query were used an such an
indexed field were available, I would expect subsecond responses from
even a large database.

If the statistics are bad, it might try to use the index on
MEDICALFACILITY_ID which if my assumptions are right would be a bad
index to use. To avoid it, simply add 0 to the ID before comparisons
which will stop the optimiser from using the index.

SELECT ENTY_PATIENT.PATIENT_ID, ENTY_PERSON.PERSON_LASTNAME || ', ' ||
ENTY_PERSON.PERSON_FIRSTNAME
FROM LINK_MEDICALFACILITY_PATIENT LMP
JOIN ENTY_PATIENT EPA1 ON (LMP.PATIENT_ID = EPA1.PATIENT_ID)
JOIN ENTY_PERSON EPE ON (EPA1.ENTITY_ID = EPE.ENTITY_ID)
WHERE LMP.MEDICALFACILITY_ID+0 = :MEDICALFACILITY_ID_IN
AND EPE.PERSON_LASTNAME_UPPER STARTING WITH UPPER(:SEARCHSTRING_IN)

Adam


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
2. Re: timestamp trigger?
Posted by: "Adam" s3057043@... s3057043
Fri Oct 13, 2006 9:57 pm (PST)
--- In firebird-support@yahoogroups.com, "Fernando Salaices"
<fsalaices@...> wrote:
>
> a generator? Can a generator be created that returns the current date?

No, a generator is a sequence in SQL terms. It will give you a number
that has never been and never will be given to anyone else (*1)

Ann was most likely assuming that you want to flag these records with
a changed date so that you can query all records that have been
changed since last time you ran the query.

The problem with using timestamps for such a purpose is their
precision (current_timestamp only returns to the second) and the fact
they are not unique.

Using generators to provide an update number allows this to be handled
better.

Adam

(*1) providing no-one ever resets or decrements it.


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (12)
3a. How to install Firebird with a different Collation
Posted by: "luiscolmenter" generic.systems@... luiscolmenter
Fri Oct 13, 2006 10:52 pm (PST)
Hi.
I need to install FB but the PT_BR charset does not appears at create
table time. How I can state that the database uses this collation in
all the tables? I can not use a modificator as create table charset
ISO8859_1 collation PT_BR, because I'm using a black-boxed table
create and upgrade package, so I need that the database uses this
collation at once on every table that is created inside it.
Thanks
Jorge


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3b. Re: How to install Firebird with a different Collation
Posted by: "Alexandre Benson Smith" iblist@... thoriblist
Sat Oct 14, 2006 1:23 am (PST)
luiscolmenter wrote:
> Hi.
> I need to install FB but the PT_BR charset does not appears at create
> table time. How I can state that the database uses this collation in
> all the tables? I can not use a modificator as create table charset
> ISO8859_1 collation PT_BR, because I'm using a black-boxed table
> create and upgrade package, so I need that the database uses this
> collation at once on every table that is created inside it.
> Thanks
> Jorge
>

Jorge,

Are you talking about the PT_BR collation of FB 2 or the "un-official"
build of 1.X, 1.5.X series ?

If you are talking about 1.X and 1.5.X I invite you to join cflp_suporte
group since there we could talk freely about it, I think it's off-topic
to talk about unofficial builds here.

you could search the cflp_suporte group at yahoo or visit
www.comunidade-firebird.org

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
4a. something very strange going on here
Posted by: "martinknappe" martin@... martinknappe
Sat Oct 14, 2006 12:39 am (PST)
hi
i had decided not to bother you any more with my "how do i speed this
up" question but now something very strange ocurred and i really need
to ask if someone has an explanation for it:

my procedure is and was correct; when i execute it in the ibexpert
debugger, I get correct results..but when i execute it outside the
debugger(sql editor), i get incorrect results

the procedure is:

CREATE PROCEDURE TEST (
asterm_in varchar(240),
id_in bigint)
returns (
id_out bigint,
pos integer)
as
declare variable id_temp bigint;
declare variable asterm_temp varchar(240) character set unicode_fss;
begin
pos = 0;
id_temp = :id_in;
asterm_temp = :asterm_in;
while (1 = 1) do
begin
select min(asterm) from dicentries where ((asterm = :asterm_temp
and id >= :id_temp)) or (asterm > :asterm_temp) into :asterm_temp;
if (asterm_temp is null) then
exit;
select min(id) from dicentries where asterm = :asterm_temp into
:id_out;
suspend;
pos = pos + 1;
id_temp = :id_out + 1;
if (:pos = 20) then
exit;
end
end

in debugger (asterm_in = 'A', id_in = 0):

id_out pos

142 0
107 1
150 2
147 3
164 4
95 5
41 6
93 7
18 8
23 9
187 10
14 11
188 12
189 13
15 14
12 15
27 16
31 17
32 18
36 19

in sql-editor (same parameters):

id_out pos

142 0
107 1
150 2
147 3
164 4
<null> 5
41 6
93 7
18 8
23 9
187 10
14 11
188 12
<null> 13
15 14
12 15
27 16
31 17
32 18
<null> 19

Isn't that strange?

Martin


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
4b. Re: something very strange going on here
Posted by: "Adam" s3057043@... s3057043
Sat Oct 14, 2006 1:41 am (PST)
> Isn't that strange?

There is no debugging hooks for Firebird stored procedures. IBExpert
is simply an emulator. Like all emulators, sometimes they have bugs
the original doesn't and sometimes the original has bugs that the
emulator doesn't.

I have not taken a look at your procedure, but there may well be
behaviour differences between the two.

Adam


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
4c. Re: something very strange going on here
Posted by: "Milan Babuskov" milanb@... mbabuskov
Sat Oct 14, 2006 2:28 am (PST)
martinknappe wrote:
> select min(asterm) from dicentries where ((asterm = :asterm_temp
> and id >= :id_temp)) or (asterm > :asterm_temp) into :asterm_temp;

Try using a separate variable here. IIRC, I've already seen some
problems when the same variable is used in both WHERE and INTO clauses.

--
Milan Babuskov
http://swoes.blogspot.com/
http://www.flamerobin.org


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
4d. Re: something very strange going on here
Posted by: "martinknappe" martin@... martinknappe
Sat Oct 14, 2006 3:22 am (PST)
> Try using a separate variable here. IIRC, I've already seen some
> problems when the same variable is used in both WHERE and INTO clauses.

Ok, after I got the other procedure fixed, I'm now having a similar
problem with this procedure (when executed in the debugger,
everything's fine; outside the debugger the procedure seems to somehow
strangely enter an infinite loop (but of course I don't know where):

CREATE PROCEDURE GET_20_DICENTRIES_BY_ASTERM (
"TERM" varchar(240))
returns (
pos integer,
id bigint)
as
declare variable asterm_try varchar(240) character set unicode_fss;
declare variable asterm_temp varchar(240) character set unicode_fss;
begin
pos = 10;
asterm_temp = :term;
while (1 = 1) do
begin
select max(asterm) from dicentries where asterm < :asterm_temp
into :asterm_try;
if (:asterm_try is null) then
break;
for select id from dicentries where asterm = :asterm_try order
by id descending into :id do
begin
suspend;
pos = :pos - 1;
if (:pos = 0) then
break;
end
if (:pos = 0) then
break;
asterm_temp = :asterm_try;
end

pos = 11;
for select id from dicentries where asterm = :term order by id
ascending into :id do
begin
suspend;
pos = :pos + 1;
if (:pos = 20) then
break;
end

asterm_temp = :term;
while (1 = 1) do
begin
select min(asterm) from dicentries where asterm > :asterm_temp
into :asterm_try;
if (:asterm_try is null) then
break;
for select id from dicentries where asterm = :asterm_try order
by id ascending into :id do
begin
suspend;
pos = :pos + 1;
if (:pos = 20) then
break;
end
if (:pos = 20) then
break;
asterm_temp = :asterm_try;
end
end

firebird version is 1.5


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
5. Re: how do i speed this up
Posted by: "martinknappe" martin@... martinknappe
Sat Oct 14, 2006 1:43 am (PST)
Hi Sven,
in case you're still interested, your procedure works now; I've
changed it a bit:

CREATE PROCEDURE GET_NEXT_20 (
asterm_in varchar(240),
id_in bigint)
returns (
id_out bigint,
pos integer)
as
declare variable id_temp bigint;
declare variable asterm_temp varchar(240) character set unicode_fss;
begin
pos = 0;
id_temp = :id_in;
asterm_temp = :asterm_in;
while (1 = 1) do
begin
select min(asterm) from dicentries where ((asterm = :asterm_temp
and id >= :id_temp)) or (asterm > :asterm_temp) into :asterm_temp;
if (asterm_temp is null) then
exit;
for select id from dicentries where asterm = :asterm_temp and id
>= :id_temp order by id ascending into :id_out do
begin
suspend;
pos = pos + 1;
if (:pos = 20) then
exit;
end
id_temp = :id_out + 1;
end
end

Also, see my other post from an hour ago for something very strange
with firebird 1.5:
http://tech.groups.yahoo.com/group/firebird-support/message/80244

Regards,

Martin

--- In firebird-support@yahoogroups.com, "martinknappe" <martin@...>
wrote:
>
> --- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
> <svein.erling.tysvaer@> wrote:
> >
> > Hi again!
> >
> > Your procedure is similar to what I suggested, but probably a bit
> > slower. At first glance, I do not see how it could return NULL, but I
> > haven't looked closely since this may be gone when you clean up your
> > procedure and if not, then it could possibly be easily
circumvented by
> > adding WHERE IDOUT IS NOT NULL to your calling query.
>
> well, that would prevent entries w/o an idout value from appearing in
> the result set, but it's not the solution to the problem; it's just a
> cover-up which effectively hides data away from the user when he is
> really requesting it..as a matter of fact, for a simple test, i
> changed my procedure in such a way that it also returned the proper
> value for asterm of the result set entries..what i did then was copy
> asterm of those entries that had null in their id field and queried
> the following:
>
> select id from dicentries where asterm = copiedValue
>
> and it did give me an id so this shows that the id really exists..i
> don't know what's going on here..im nearly so far as to claim this is
> a bug..
>
> > You seem to prefix variables with a colon everywhere.
>
> afaik that's absolutely necessary
>
> > Moreover, SET TERM is part of the
> > stored procedure language, so I would try to avoid having TERM as
> > the name of a parameter.
>
>
> ok, i tried with a different identifier; didn't help either
>
> > You combine SELECT FIRST with counting manually,
> > and that is just wasting time and resources.
>
> i know, i tried "select min(id)..." before but then tried with "first"
> just to see if that was the problem..it really kept me busy for a good
> while yesterday :-(
>
> > And finally, is ASTERM at
> > all indexed
>
> yes it is; in the first post of this thread theres a link to the sql
> script for creating the database where you can look everything up
>
> i've nearly given up..what ive done now is some kind of a
> workaround...whereas before the select procedure my client user would
> invoke did something like:
>
> select first 20 id from dicentries
> where (asterm = :asterm and id >= :id) or (asterm > :asterm)
> order by asterm ascending, id ascending
>
> i now changed the procedure so that it be more selective:
>
> select first 20 id from dicentries
> where ((asterm = :asterm and id >= :id) or (asterm > :asterm)) and
> (asterm <= :upperbound)
> order by asterm ascending, id ascending
>
> so now it's in the user's hand to make response time shorter by simply
> "cutting off" the end of the table, which he wouldn't have seen in his
> result set anyway...not the nices of solutions but better than keep
> the user waiting for 7 seconds on every page scroll...
>
> thanx anyway,
>
> martin
>


Back to top Reply to sender | Reply to group | Reply via web post
Messages in this topic (25) Recent Activity
a.. 20 New Members
Visit Your Group
SPONSORED LINKS
a.. Computer telephony
b.. Portable computer
c.. Technical support software
d.. Technical support
e.. Computer telephony integration
Drive Sales
Connect with

customers who are

searching for you.

Ads on Yahoo!
Learn more now.

Reach customers

searching for you.

Yahoo! Groups
Start a group

in 3 easy steps.

Connect with others.

Need to Reply?
Click one of the "Reply" links to respond to a specific message in the Daily Digest.
Create New Topic | Visit Your Group on the Web
Messages
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe


[Non-text portions of this message have been removed]