Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009

    Unanswered: Performance tuning of query

    Hi All,

    I have a requirement of creating a view which is having joins on 9 tables. This view is used by Cognos team to fetch data for reporting.

    For reporting purpose, they need mostly counts based on some where clauses.For one of those where clauses the query on view is taking too long. This where clause contains a derived column. Even if I create index on the base column, the query dosen't speeds up.

    The query which they are firing on view is:

    select count(*) from test_view where col2='Approved';

    This col2 is derived as follows:

    Coalesce((CASE col1 When 'Y' Then 'Approved' When 'N' Then 'Rejected' Else NULL End),'Pending') col2

    Please guide as to how can i increase the query efficiency.

    Thanks in adv

  2. #2
    Join Date
    May 2009
    Provided Answers: 1
    shalini11, First, this has nothing to do with your performance issue but I would remove the COALESCE and replace NULL with 'Pending':

    CASE col1 WHEN 'Y' THEN 'Approved' WHEN 'N' THEN 'Rejected' ELSE 'Pending' END AS col2

    There is just extra work by having the CASE set a NULL and then using a COALESCE to replace the NULL with a value when the CASE could set the value in the first place.

    Second, as you found out, creating an Index on the base column but using a derived (or converted) value from that column does not use the index (because the derived value is not indexed).

    I don't know if this will help or not but you can return both the base value (col1) and the derived value (col2) in the View and then change the query on the View to use the base value (col1) in the WHERE clause and the derived value (col2) in the SELECT clause. This should allow the Index to be use by the WHERE clause but still display the user friendly text string for the report. (But if all that is being done is COUNT(*) you don't really need the derived/converted value although it may be needed by other queries.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts