One more question on this table structure and join. In the query below, the main table filter is the "FA3=10", the "FA4=1" only subsets them. In other words... Table TestA has over 4,000,000 records - but "FA3=10" returns the 500 I need for this one user. Then "FA4=1" just cuts the 500 down to 400. Anyone following this?
Second, the join - I am only really needing to get the second table (TestB) fields (FB1, FBPayload1) for those 400 records from TestA. I am uncertain as the capability of SAPDB stored procedures. Is it possible that I could just write a stored procedure that cuts the disk I/O? One that just brings the 500 in (exclude "FA4=1" from WHERE), filters it manually in the stored procedure (loop through in stored procedure code and remove the FA4=1 manually) - then do a UNION to get the join? I know this defeats the simplicity of SQL joins - but this particular query is performance critical. Other than to create combined index fields or denormalize TableA + TableB - I'm looking for other tricks to speed things up. There seem to be very few who understand the capability and syntax of SAPDB Stored Procedures. Anyone willing to help me out by making this a sample :) Thanks. Stephen Gutknecht -----Original Message----- From: Stephen Gutknecht (SAPDB) [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 4:37 PM To: [EMAIL PROTECTED] Subject: MaxDB 7.5 - performance - Indexes, Joins and optimizer Hey, More learning about inner workings of the database engine and performance tuning. I've seen some behavior I can't understand when studying EXPLAIN output. Attached is an ASCII text file SQL1.txt has the CREATE TABLE, INSERT, CREATE INDEXES required for this sample. After the two tables are populated, I issue the following using SQL Studio: EXPLAIN SELECT FA1, FA2, FA3, FAPayload1, FAPayload2, FAPayload3, FB1, FBPayload1 FROM TestA, TestB WHERE FA3=10 AND FA4=1 AND TestA.FA2=TestB.FB1 My Questions: 1. Explain output shows 154 PAGECOUNT for the Join. Are these 8KB MaxDB storage pages? Does that mean 1232 kilobytes of Disk I/O? 2. Is there a better (performing) way to write such a join? Union? Subselect? 3. The SQL1.txt setup creates 6 indexes. For the EXPLAIN output, it shows that TESTAINDEX5 is being used. Would it not make more sense for the MaxDB engine to use TESTAINDEX6 - isn't that query an exact match with the WHERE clause? Isn't this causing more I/O than needed using a INDEX that has a unused field (FAPayload3 is on TESTAINDEX5 - but is not used in the WHERE). In our production application. TableA has about 4 million records and TableB has about 3.5 million records. The indexes and keys illustrated are the same type for our most frequent query. We know we can denormalize the two tables - but we also want to understand why there is so much disk I/O on this query - and why we do not understand the selection of INDEX. Thank you. Stephen Gutknecht Full time traveler... Currently in Lake Havasu City, Arizona USA -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
