[#CORE-2200] Extremely slow executing a cross join of 3 tables in Firebird 2.X

Firebird Core

Extremely slow executing a cross join of 3 tables in Firebird 2.X

Created: 19/Nov/08 04:14 AM Updated: 26/Jan/09 11:36 AM

Return to search

Component/s:

Engine

Affects Version/s:

2.0.0, 2.0.1, 2.0.2, 2.0.3, 2.1.0, 2.0.4, 2.1.1

Fix Version/s:

2.1.2, 2.5 Beta 1, 2.0.6

Time Tracking:

Not Specified

File Attachments:	1. CORE.2200.zip (323 kB)
Environment:	« Hide Server: Firebird 2.1.1 on either Linux or Windows platform Client : IBExpert running on Windows OS Show » Server: Firebird 2.1.1 on either Linux or Windows platform Client : IBExpert running on Windows OS

Planning Status:

Unspecified

Description

« Hide

I have 3 tables:

1. Agent (0 row)
2. AR_PM (4,300+ rows)
3. AR_KnockOff (18,000+ rows)

I execute the following statement in Firebird 2.1.1:

SELECT A.Code FROM AGENT A, AR_PM B, AR_KnockOff C

It takes long times to get the first response back to my client software (IB Expert). However, this situation doesn't happen on Firebird 1.5 running the same query and same amount of data.

Case 1:
======
Firebird 2.1.1: 173 seconds
Firebird 1.5.X: 0 seconds

Case 2:
======
If I append one row into AGENT table, I get the response immediately after running the query:

Firebird 2.1.1: 0.093 seconds
Firebird 1.5.X: 0.031 seconds

I suspect there is some problem with Firebird 2.X. I also attach my database backup for both Firebird 1.5 and 2.1 for you to try out. Please pay attention on the time spent to run the query on Case 1.

All

Comments

Work Log

Change History

Version Control

Sort Order:

The cvs commits can not be displayed for repository Firebird at the moment since the log has not yet been parsed. The log will be parsed the next time the VcsService runs. If you have administrators privileges you can hasten the next time the service will run in the service section of the Administration pages.