Many indexed reads in a compound index from stored procedure
------------------------------------------------------------
Key: CORE-4877
URL: http://tracker.firebirdsql.org/browse/CORE-4877
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.4
Reporter: Roman Lusnikov
CREATE TABLE DATALOG (
DLID INTEGER NOT NULL,
DLTABLE VARCHAR(30),
DLRECID COMMENT /* COMMENT = VARCHAR(300) */,
DLTEXT LONGCOMMENT /* LONGCOMMENT = VARCHAR(1000) */,
DLTIME TIMESTAMP DEFAULT current_timestamp,
DLOPID SMALLINT,
DLTYPE CHAR(1)
);
ALTER TABLE DATALOG ADD CONSTRAINT PK_DATALOG PRIMARY KEY (DLID);
CREATE INDEX DATALOG_IDX1 ON DATALOG (DLTABLE, DLRECID);
CREATE INDEX DATALOG_IDX2 ON DATALOG (DLTIME);
When I select records from stored procedure:
create or alter procedure GETORDERLOG2 (
ORDID integer)
returns (
DLTIME timestamp,
DLTABLE varchar(30),
DLRECID integer,
DLTYPE char(1),
DLOPERATION char(100),
DLTEXT varchar(1000),
OPCAPTION varchar(100))
as
begin
DLTABLE = 'TORDER';
for
select L.DLTIME, L.DLRECID, L.DLTYPE,
case L.DLTYPE
when 'I' then 'Добавление'
when 'U' then 'Обновление'
when 'D' then 'Удаление'
when 'P' then 'Добавление свойства'
when 'R' then 'Удаление свойства'
when 'G' then 'Телефония'
end as DLOPERATION,
L.DLTEXT, O.OPCAPTION
from DATALOG L left join OPERATOR O on (L.DLOPID = O.OPID)
where L.DLTABLE = :DLTABLE and L.DLRECID = :ORDID
-- and L.DLTIME between dateadd(hour, -10, :ORDTIME) and dateadd(hour, 10,
:ORDTERMTIME) -- Оптимизация (ФБ не хочет нормально использовать составной
индекс DLTABLE, DLRECID ???)
-- order by L.DLTIME desc
into :DLTIME,
:DLRECID,
:DLTYPE,
:DLOPERATION,
:DLTEXT,
:OPCAPTION
do
begin
suspend;
end
end
I have too many indexed reads (apr. 190000 in my case)
But in case selecting by the same query - all right (1 indexed read)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Don't Limit Your Business. Reach for the Cloud.
GigeNET's Cloud Solutions provide you with the tools and support that
you need to offload your IT needs and focus on growing your business.
Configured For All Businesses. Start Your Cloud Today.
https://www.gigenetcloud.com/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
