Re: Clarification on SQL performance tuning....

See the suggestions below ... Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "Rajasekaran, Rajesh" To: "'informix-list@iiug.org'" <informix-list@iiug.org> <RRajasekaran@fores cc: "'sapmix@iiug.org'" <sapmix@iiug.org> tpharm.com> Subject: Clarification on SQL performance tuning.... Sent by: owner-informix-list @iiug.org 09/15/2003 09:55 AM Hello all, ' I would like to discuss a SQL performance issue here and i am hoping to get some suggestions/tips here. We have SAP R/3 46c running on' IDS 7.31 UD2XG on Solaris 9 . Here is the issue. 'The query should extract some records based on the date filter from "mkpf" table and joins those records to "mseg" table where the material records to be picked.Also, it has to join with "mcha" to pick some more columns for the resultant set. There were some couple of other tables(small in size) which i didnt include here was a part of the original SQL to pick some more information. When i break it up the SQL by introducing tables and join conditions for those tables one by one, i found out that the delay happened only when i introduce the "MCHA" table and its related joins. Hence my SQL & its query plans pasted here involved only those tables and joins. The whole query takes 30-40 mins to get the results. Please find the attached table info for 3 specific tables and the SQL query optimizer plan. Questions -------------- 1) Does the query path chosen here get executed in the same sequence as it shows in the SQL plan ? ''' -- I mean does the optimiser sequence in terms of' applying join and filters as it shows on the query plan like first on "marm",then on "mcha", then on "mseg", then on "makt",then on "afpo",then on "mkpf". -- YES 2) Ideally i feel based on the table data & considering the type of application data stored, size etc. , the query can be better off by choosing the route' "mkpf", "mseg","mcha" order to get the desired records. This can happen if the optimizer chooses hash join i guess since the MSEG and MKPF are bigger tables of size. I have seen before sometimes the "estimation cost shows high figure" and the query results come in quite a good time but not for this case though. -- If that's the case, you can re-order the order of the tables in the FROM clause and force the optimizer to use the order using the ORDERED hint Check the cost, after you run it with the ORDERED hint ... u'll definitely see the cost to be higher than what the optimizer is showing without the ORDERED hint. But, then the execution time of the query and the COST are not always directly related .... (COST is a relative cost and is an approximation) 3) I have the update statistics executed upto date for these tables. at 0% from sapdba tool with default suggested method. Do' you think the optimiser behaving wrongly here ? ''' Our OPTCOMPIND is supposed to be 0 for our SAP R/3 environment. Hence the optimizer prefers nested loop join by default. -- U never know... there are OPTIMIZER bugs .. it would require more research to conclude that its a bug. 4)' Do you think this SQL can be re-framed in any order to get better results ? -- Possibly yes... Here is how I will proceed if I were you ... -- Do a select count(*) with the join conditions for T_04 and T_05 to see what's the number of rows selected, and proceed to find out which would eliminate the most # of rows from the resultant set (by trying different joins). -- Essentially the goal is to make the resultant set to be small so that the next join works faster instead of joining un-necessary rows and then discarding them later on. The optimizer will try and decide that for you (there was a paper on 'Peephole optimization' some time back) but it can do so much. 5)' Also on MKPF table, there is another index with "mandt,budat,mblnr". Ideally the date search should have used this index. But i think bcos of the join condition involved between MKPF & MSEG on the sql, the optimiser always choose the unique index (mandt,mblnr,mjahr) and apply the date filters on that index. Is there any way to change that behaviour ? Iam right now testing the SQL with optimiser hints like forcing a specific index,hash join etc. -- U will have to try Optimizer hints for that ... Any suggestions/comments' are greatly appreciated. Thanks Rajesh Rajasekaran Informix Database Administrator Forest Pharmaceuticals Inc. (314) 493-7073 rrajasekaran@forestpharm.com #### sqexplain.out has been removed from this note on September 15, 2003 by Rajib Sarkar sending to informix-list