QualityCentral

Public Report

Report From: InterBase/Server/Internal/Optimizer [ Add a report in this area ]

Report #: 47598

Status: Closed

(Pulled) Left outer join performance without an index is terrible This one has been around since 4.0 (at least), but has never been fi

Project: InterBase

Build #: V4.0G

Version: 8.0

Submitted By: QC<->Raid Replicator Account

Report Type: Basic functionality failure

Date Reported: 8/26/1996 12:00:00 AM

Severity: Commonly encountered problem

Last Updated: 7/10/2008 9:10:30 PM

Platform: Not OS or platform specific

Internal Tracking #: 58257

Resolution: Fixed (Resolution Comments)

Resolved in Build: : 8.1.0.257

Duplicate of: None

Voting and Rating

Overall Rating: No Ratings Yet

0.00 out of 5

Total Votes: None

Description

Left outer join performance without an index is terrible

This one has been around since 4.0 (at least), but has never been fixed.  It was postponed for 5.0 and never reopened.  I've reproduced it in 5.5 and 6.0.0.385 and it still exists.

The left outer join takes 3.5 seconds with an index and 1+ hours without an index.

There are only 10000 records in the left hand table so we are not talking a large number of records.

-------------- running script on thriller -----------------------
thriller% isql -z
ISQL Version: SO-I6.0.0.375

thriller% mmi.bat
+ gbak -c mmi_dm.gbk mmi_dm
+ isql mmi_dm -i mmi.sql
Database:  mmi_dm
SET STATS;
ALTER INDEX ZIP_SCF ACTIVE;
SELECT COUNT(*) FROM CUSTOMER C, ZIP_SCF Z
WHERE  (C.SCF = Z.SCF) AND (C.SCF_PLUS = Z.SCF_PLUS);

COUNT
============

9536

Current memory = 2587634
Delta memory = 63488
Max memory = 2916514
Elapsed time= 6.72 sec
Cpu = 0.00 sec
Buffers = 256
Reads = 3268
Writes = 4
Fetches = 70312

SELECT COUNT(*) FROM CUSTOMER C LEFT JOIN ZIP_SCF Z
ON (C.SCF = Z.SCF) AND (C.SCF_PLUS = Z.SCF_PLUS);

COUNT
============

10000

Current memory = 2607090
Delta memory = 19456
Max memory = 2916514
Elapsed time= 3.50 sec
Cpu = 0.00 sec
Buffers = 256
Reads = 3220
Writes = 0
Fetches = 69345

ALTER INDEX ZIP_SCF INACTIVE;
SELECT COUNT(*) FROM CUSTOMER C, ZIP_SCF Z
WHERE  (C.SCF = Z.SCF) AND (C.SCF_PLUS = Z.SCF_PLUS);

COUNT
============

9536

Current memory = 2608098
Delta memory = 6128
Max memory = 3001950
Elapsed time= 1.41 sec
Cpu = 0.00 sec
Buffers = 256
Reads = 477
Writes = 0
Fetches = 34098

SELECT COUNT(*) FROM CUSTOMER C LEFT JOIN ZIP_SCF Z
ON (C.SCF = Z.SCF) AND (C.SCF_PLUS = Z.SCF_PLUS);

COUNT
============

10000

Current memory = 2607074
Delta memory = -1024
Max

  - Brett
  - 14-jun-1999

--
1/14/05

For additional details see bugdiscussion newsgroup titled "whatever happened to bug 8257..." on 1/11/05. Customer should be able to provide a test case. - QAW

Steps to Reproduce:

Deej requested this be listed as a JOIN OPTIMIZER bug.

Problem is that when an OUTER JOIN is done the performance is
very poor.

A left join without an index in the test case will hang.

I've attached the testcase.

To reproduce the issue:

1) restore the database to mmi_db (because script uses this name)
2) run the isql script mmi.sql

Note: there is a batch file (mmi.bat) that will execute the 2 steps for you.

Workarounds

None

Attachment

Comments

None

Quality Central

Developer Tools

Database Tools