--- In firebird-support@yahoogroups.com, "william_yuwei" <william.wei.yu@...> wrote:

>
> HI, All
>
> I had a 1.29G database with the following stored procedure:
>
> create or alter procedure SCHEDULE_GETCLIENTLIST (
> LOCATIONS varchar(8192),
> USERID integer,
> INCLUDEGROUPS varchar(1),
> INCLUDEDISCHARGED varchar(1))
> returns (
> ID integer,
> DISPLAY_NAME varchar(128),
> BIRTH_DATE date)
> as
> declare variable SQLTEXT varchar(10240);
> declare variable PROVIDERID integer;
> declare variable VIEWTYPE varchar(1);
> declare variable VIEW_SELF_CLIENTS varchar(1);
> begin
> if (locations = '') then locations = null;
> providerid = null;
>
> if (userid > 0) then
> begin
> select VIEW_TYPE, PROVIDER_ID, VIEW_SELF_CLIENTS from INTOUCH_USERS
> where ID = :userid
> into :viewtype, :providerid, :view_self_clients;
>
> if (viewtype = 'A' or view_self_clients = 'N') then
> providerid = null;
> end
>
> sqltext =
> 'select distinct a.ID, iif(a.IS_MIDDLE_PREFERRED = ''N'' or a.MIDDLE_NAME is null, a.DISPLAY_NAME,
> a.DISPLAY_NAME || '' "'' || a.MIDDLE_NAME || ''"''), c.BIRTH_DATE from CONTACT a
> inner join CLIENT c on c.ID = a.ID + 0 ';
>
> if (includegroups = 'Y') then
> sqltext = sqltext ||
> 'where a.CONTACT_TYPE in (''PT'', ''GP'') and a.DELETED = ''N''
> and exists (select 1 from INCIDENT where CLIENT_ID = a.ID
> and DELETED = ''N''';
> else
> sqltext = sqltext ||
> 'where a.CONTACT_TYPE = ''PT'' and a.DELETED = ''N''
> and exists (select 1 from INCIDENT where CLIENT_ID = a.ID
> and DELETED = ''N''';
>
> if (includedischarged = 'N') then
> sqltext = sqltext || ' and DISCHARGE_DATE is null';
>
> if (locations is not null or locations <> '') then
> sqltext = sqltext || ' and SERVICELOCATION_ID in (' || locations || ')';
>
> if (providerid > 0) then
> sqltext = sqltext || ' and PRIMARY_PROVIDERID = ' || providerid;
>
> sqltext = sqltext || ') order by upper(a.DISPLAY_NAME)';
>
> for execute statement sqltext into :ID, :DISPLAY_NAME, :BIRTH_DATE
> do
> begin
> suspend;
> end
> end;
>
> By running the following query:
>
> select * from schedule_getclientlist('19', -1, 'Y', 'N');
>
>
> ** It took 844ms (avg fetch time: 22.81) under 2.1.3 superserver
>
> Indexed reads:
>
> INCIDENT: 82142, CLIENT: 8277, CONTACT: 63209
>
> A BIG SURPRISE:
>
> ** It took 1m 6s 687ms (avg fetch time: 1802.35MS) under 2.1.4 superserver
>
> Indexed reads:
>
> INCIDENT: 41805, CLIENT: 8277, CONTACT: 63209
>
>
> There are some report stored procedures that usually took minutes under 2.1.3, but now it took hours under 2.1.4
>
> P.S. Database were backup/restore, I did recompile the selectivity of all indexed, all stored procedures and all triggers under each version.
>
> Regards,
>
> William
>

What is plan produced in 2.1.3 and 2.1.4
if you can not get detailed plan when do
select * from schedule_getclientlist('19', -1, 'Y', 'N');

then try change this proc to execute block and get detailed plan
and we will see where is different

Karol Bieniaszewski