Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Improve performance of joins with many null values in the key field(s) [CORE4531] #4849

Open
firebird-automations opened this issue Aug 25, 2014 · 2 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Alex Bekhtin (afgm)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5.3 [ 10461 ]

Version: 2.1.6 [ 10460 ]

Version: 3.0 Alpha 1 [ 10331 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.1.5 Update 1 [ 10522 ]

Version: 2.5.2 [ 10450 ]

Version: 2.1.5 [ 10420 ]

summary: ineffective inner join with more null values => Improve performance of joins with many null values in the key field(s)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment