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