This simple join query brings maxdb 7.5.0.14 to its knees where this same
*exact* query using the same data in PostgreSQL only takes 6 seconds to
fetch and 24 seconds to display. MaxDB (SQL Studio) however locks up and
never finishes. Below is some EXPLAIN output for queries I used to see what
was needed to improve performance. I have also included the defined schema
and indexes for each table.
SELECT C.*,P.* FROM COMPANY C INNER JOIN PERSON P ON P.COMPANY_ID =
C.COMPANY_ID ORDER BY C.COM_NAME ASC
P TABLE SCAN 988
C COMPANY_ID JOIN VIA KEY COLUMN 787
RESULT IS COPIED, COSTVALUE IS 42303
EXPLAIN SELECT C.COMPANY_ID,P.PERSON_ID FROM "COMPANY" C INNER JOIN "PERSON"
P ON C."COMPANY_ID" = P."COMPANY_ID" ORDER BY C."COM_NAME" ASC
P TABLE SCAN 988
C COMPANY_ID JOIN VIA KEY COLUMN 787
RESULT IS COPIED,COSTVALUE IS 2408
EXPLAIN SELECT C.*,P.PERSON_ID FROM "COMPANY" C INNER JOIN "PERSON" P ON
C."COMPANY_ID" = P."COMPANY_ID" ORDER BY C."COM_NAME" ASC
P TABLE SCAN 988
C COMPANY_ID JOIN VIA KEY COLUMN 787
TABLE HASHED
RESULT IS COPIED,COSTVALUE IS 33500
***** THE FOLLOWING IS INTERESTING *****
EXPLAIN SELECT C.COMPANY_ID,C.COM_NAME,P.PERSON_ID,P.FNAME,P.LNAME FROM
"COMPANY" C INNER JOIN "PERSON" P ON C."COMPANY_ID" = P."COMPANY_ID" ORDER
BY C."COM_NAME" ASC
P TABLE SCAN 988
C COMPANY_ID JOIN VIA KEY COLUMN 787
RESULT IS COPIED,COSTVALUE IS 2973
EXPLAIN SELECT
C.COMPANY_ID,C.COM_NAME,C.NOTE,P.PERSON_ID,P.FNAME,P.LNAME,P.NOTE FROM
"COMPANY" C INNER JOIN "PERSON" P ON C."COMPANY_ID" = P."COMPANY_ID" ORDER
BY C."COM_NAME" ASC
P TABLE SCAN 988
C COMPANY_ID JOIN VIA KEY COLUMN 787
RESULT IS COPIED,COSTVALUE IS 20511
By including the note fields the performance is reduced dramatically. These
are not large fields by any stretch (5k bytes & 255 bytes)
Systems used in testing:
MaxDB 7.5.0.14 server -
AMD Dual 2.0 GHz, 2GB RAM, 4 IDE DRIVES.
All data segments (data,log,sys) are on seperate disks.
OS = SuSE 8.1
PostgreSQL machine (workstation)
AMD 1.6GHz, 512MB RAM, 2 IDE DRIVES
(16406 records total)
CREATE TABLE "COMPANY"
(
"COMPANY_ID" Fixed (10,0),
"COM_NAME" Varchar (40) ASCII,
"ADDRESS1" Varchar (100) ASCII,
"ADDRESS2" Varchar (100) ASCII,
"CITY" Varchar (75) ASCII,
"STATE" Varchar (20) ASCII,
"COUNTRY" Varchar (20) ASCII,
"ZIP" Varchar (10) ASCII,
"WEB" Varchar (255) ASCII,
"PHONE" Varchar (20) ASCII,
"FAX" Varchar (20) ASCII,
"ACCT_OWNER" Varchar (20) ASCII,
"ACCT_NUM" Varchar (20) ASCII,
"ANNUAL_REVENUE" Fixed (18,0),
"NUM_OF_EMPL" Fixed (18,0),
"SIC_CODE" Varchar (20) ASCII,
"NOTE" Varchar (5000) ASCII,
"CREATE_DATE" Timestamp,
"CREATE_USER" Varchar (20) ASCII,
"MODIFY_DATE" Timestamp,
"MODIFY_USER" Varchar (20) ASCII,
"SYNC_DATE" Timestamp,
"SYNC_USER" Varchar (20) ASCII,
"ACTIVE" Varchar (1) ASCII,
"FAX_LIST" Varchar (1) ASCII,
"TERRITORY_CODE" Varchar (3) ASCII,
PRIMARY KEY ("COMPANY_ID")
)
[COMPANY TABLE INDEXES]
CREATE INDEX "IDX_COMPANY_COM_NAME" ON "COMPANY" ("COM_NAME" ASC )
CREATE INDEX "IDX_COMPANY_ADDRESS" ON "COMPANY" ("ZIP" ASC,"STATE"
ASC,"CITY" ASC )
(32532 records total)
CREATE TABLE "PERSON"
(
"PERSON_ID" Fixed (10,0),
"COMPANY_ID" Fixed (10,0),
"DIVISION_ID" Fixed (10,0),
"FNAME" Varchar (15) ASCII,
"LNAME" Varchar (20) ASCII,
"TITLE" Varchar (30) ASCII,
"ADDRESS1" Varchar (100) ASCII,
"ADDRESS2" Varchar (100) ASCII,
"CITY" Varchar (75) ASCII,
"STATE" Varchar (20) ASCII,
"COUNTRY" Varchar (20) ASCII,
"ZIP" Varchar (10) ASCII,
"PHONE" Varchar (20) ASCII,
"EXT" Varchar (10) ASCII,
"CELLPHONE" Varchar (20) ASCII,
"PAGER" Varchar (20) ASCII,
"FAX" Varchar (20) ASCII,
"EMAIL" Varchar (80) ASCII,
"USERNAME" Varchar (20) ASCII,
"PASSWORD" Varchar (20) ASCII,
"NOTE" Varchar (255) ASCII,
"CREATE_DATE" Timestamp,
"CREATE_USER" Varchar (20) ASCII,
"MODIFY_DATE" Timestamp,
"MODIFY_USER" Varchar (20) ASCII,
"SYNC_DATE" Timestamp,
"SYNC_USER" Varchar (20) ASCII,
"ACTIVE" Varchar (1) ASCII,
"FAX_LIST" Varchar (1) ASCII,
PRIMARY KEY ("PERSON_ID"),
FOREIGN KEY "FK_PERSON_COMPANY" ("COMPANY_ID") REFERENCES "COMPANY"
("COMPANY_ID") ON DELETE RESTRICT,
FOREIGN KEY "FK_PERSON_DIVISION" ("DIVISION_ID") REFERENCES "DIVISION"
("DIVISION_ID") ON DELETE RESTRICT
)
[PERSON TABLE INDEXES]
CREATE INDEX "IDX_PERSON_FK" ON "PERSON" ("COMPANY_ID" ASC,"DIVISION_ID"
ASC )
CREATE INDEX "IDX_PERSON_ADDRESS" ON "PERSON" ("ZIP" ASC,"STATE"
ASC,"CITY" ASC )
CREATE INDEX "IDX_PERSON_NAME" ON "PERSON" ("LNAME" ASC,"FNAME" ASC )
CREATE INDEX "IDX_PERSON_NAME2" ON "PERSON" ("FNAME" ASC, "LNAME" ASC )
CREATE INDEX "IDX_PERSON_FNAME" ON "PERSON" ("FNAME" ASC )
CREATE INDEX "IDX_PERSON_LNAME" ON "PERSON" ("LNAME" ASC )
