Issue Details (XML | Word | Printable)

Key: CORE-4531
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Alex Bekhtin
Votes: 0
Watchers: 5
Operations

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

Improve performance of joins with many null values in the key field(s)

Created: 25/Aug/14 06:49 PM   Updated: 09/Mar/16 01:40 PM
Component/s: Engine
Affects Version/s: 2.1.5, 2.5.2, 2.1.5 Update 1, 2.5.2 Update 1, 3.0 Alpha 1, 3.0 Alpha 2, 2.1.6, 2.5.3
Fix Version/s: None


 Description  « Hide
select count(*) from MS2008_TABLES_FIELDS F1
  inner join MS2008_TABLES_FIELDS F2 on F1.defname = F2.defname

COUNT
76071

------ Performance info ------
Prepare time = 0ms
Execute time = 15s 538ms
Avg fetch time = 15 538,00 ms
Current memory = 557 312 224
Max memory = 557 647 744
Memory buffers = 32 768
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 606 267

select count(*) from MS2008_TABLES_FIELDS F1
  inner join MS2008_TABLES_FIELDS F2 on F1.defname = F2.defname
  where f1.defname is NOT NULL

COUNT
76071

------ Performance info ------
Prepare time = 15ms
Execute time = 265ms
Avg fetch time = 265,00 ms
Current memory = 557 315 736
Max memory = 557 647 744
Memory buffers = 32 768
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 257 217

--------------------------------------------------
CREATE TABLE MS2008_TABLES_FIELDS (
 ...
    DEFNAME VARCHAR(128),
 ...
);

CREATE INDEX MS2008_TABLES_FIELDS_IDX4 ON MS2008_TABLES_FIELDS (DEFNAME);

select count(*) from MS2008_TABLES_FIELDS F1
---
COUNT
41682

select count(*) from MS2008_TABLES_FIELDS F1 where F1.defname is not null
---
COUNT
6777


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