Performance
The following query takes longer than 8 minutes running on a 486 machine
running INFORMIX 4 Standard Engine.
The query involves an outer join and involves 4 tables:
"books", "chapters", "titles" and "misc"
The tables themselves do not contain a large number of records or fields.
books (32 records, 6 fields)
chapters (640 records, 12 fields)
titles (640 records, 3 fields)
misc (255 records, 8 fields)
Tables "books" and "chapters" share the field "book_num"
Tables "chapters" and "misc" share the field "charm"
Tables "chapters" and "titles" share the field "name"
Here's the query:
SELECT chapter.book_num,
chapter.etc1
chapter.charm,
chapter.etc2,
chapter.etc3,
chapter.etc4,
chapter.etc5,
chapter.etc6,
chapter.etc7,
chapter.etc8,
chapter.etc9,
chapter.name
titles.etcA
titles.etcB,
misc.charm,
misc.etcY,
books.etcZ
FROM chapters, titles, books OUTER misc
WHERE chapter.name MATCHES titles.name
AND chapters.charm = misc.charm
AND chapters.booknum = books.book_num
INTO TEMP buf_tbl
Any suggestions on how to improve the performance would be
greatly appreciated.
(Please reply to my e-mail address; I'll post all replies)
---
Peter Levine | peterl@minerva x 24945
PBX Software Engineering | levinep@pnna (408) 492-4945
Siemens ROLM Communications Incorporated | peterl@minerva.robadome.com