Issue Details (XML | Word | Printable)

Key: CORE-4675
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Conditions like WHERE <field> = <cursor>.<field> don't use existing index

Created: 28/Jan/15 07:52 PM   Updated: 29/May/15 04:43 PM
Component/s: Engine
Affects Version/s: 3.0 Beta 1
Fix Version/s: 3.0 Beta 2

QA Status: Done successfully
Test Details:
Test uses MON$ tables to gather statistics snapshot before and after each of query.
Database that is restored (''mon-stat-gathering-N_M.fbk') contains procedures for gathering statistics and view for displaying results in convenient form. In particular, this view has columns that show DIFFERENCE of natural and indexed reads, and thus we can estimate performance.


 Description  « Hide
DDL:
====
recreate table ttt(id int primary key using index pk_ttt_id, x int, y int);
commit;
insert into ttt select row_number()over(), rand()*10, rand()*100 from rdb$types, rdb$types rows 10000;
commit;
create index ttt_x on ttt(x);
commit;

Test-1 (fast):
==========

set term ^;
execute block as
  declare a_x int;
  declare v_id int;
  declare v_x int;
  declare v_y int;
  declare c_upd cursor for (select id, x, y from ttt where x = :a_x);
begin
  a_x = 5;
  open c_upd;
  while (1=1) do begin
    fetch c_upd into v_id, v_x, v_y;
    if (row_count = 0) then leave;
    update ttt v set y = c_upd.x, x = c_upd.y
    where v.id = :v_id; ------------------------------ ::: key is specified by VARIABLE which has value from FETCH statement
  end
  close c_upd;
end
^ set term ;^
rollback;


Trace-1:
======

Select Expression
    -> Filter
        -> Table "TTT" as "C_UPD TTT" Access By ID
            -> Bitmap
                -> Index "TTT_X" Range Scan (full match)
Select Expression
    -> Filter
        -> Table "TTT" as "V" Access By ID
            -> Bitmap
                -> Index "PK_TTT_ID" Unique Scan
0 records fetched
     26 ms, 165 read(s), 15 write(s), 18741 fetch(es), 3034 mark(s)

Table Natural Index Update Insert
************************************************************************
RDB$INDICES 14
RDB$RELATION_CONSTRAINTS 200
TTT 2016 1008


Test-2 (VERY slow):
===============

set term ^;
execute block as
  declare a_x int;
  declare v_id int;
  declare v_x int;
  declare v_y int;
  declare c_upd cursor for (select id, x, y from ttt where x = :a_x);
begin
  a_x = 5;
  open c_upd;
  while (1=1) do begin
    fetch c_upd; -- into v_id, v_x, v_y;
    if (row_count = 0) then leave;
    update ttt v set y = c_upd.x, x = c_upd.y
    where v.id = c_upd.id; --------------------------------- ::: key is specified by CURSOR field using "cursor name + dot + field" syntax
  end
  close c_upd;
end
^ set term ;^
rollback;


Trace-2:
=======

Select Expression
    -> Filter
        -> Table "TTT" as "C_UPD TTT" Access By ID
            -> Bitmap
                -> Index "TTT_X" Range Scan (full match)
Select Expression
    -> Filter
        -> Table "TTT" as "V" Full Scan
0 records fetched
   8669 ms, 20317223 fetch(es), 3015 mark(s)

Table Natural Index Update
**************************************************************
TTT 10080000 1008 1008


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no subversion log entries for this issue yet.