Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2015
    Posts
    17

    Unanswered: Complex Query with View (Joins in definition of View)

    Dear All,

    We have a complex query which is similar to what is given below (the Query and definition of the View). The Query takes around 2 second to execute. We have around 20 Million records in Table 01 and around 0.5 million records in Table 11 (Table01 has a join on Table11 in the View). We need to bring down this time. Can you please help answer the below Questions?
    1. Would the Where conditions in the Query would be applied after the complete View is created?
    2. Would there be any difference in the performance of the Query if rather than using the View, we have the Query with UNION and have the required conditions applied to the Table01 on left of the Union and to the Table02 on the right of the Union. Basically by avoiding usage of View
    3. Any suggestions on writing the Query and defining the View in a better way.

    Thanks in Advance!

    Query
    Select ViewColumnA, sum (ViewColumnE), max(ColumnF) from ViewTable01Table02 where ViewColumnB = value from application , ViewColumnD = value from application

    Definition of View - ViewTable01Table02
    Select '1' As ViewColumnA, ' ' as ViewColumnB, Table01.ColumnC as ViewColumnC, Table01.ColumnD as ViewColumnD, ((Table01.ColumnE - Table01.ColumnH)* (CASE WHEN Table11.ColumnX = '1' THEN 1 WHEN Table11.ColumnX = '2' THEN -1 ELSE 0 END)) as ViewColumnE, CASE WHEN Table11.ColumnY = 1 THEN COALESCE(Tabl01.ColumnF, ' ') ELSE ' ' END AS ColumnF
    from (Select * from Table01 where ColumnG in ('1','2') ) Table01
    JOIN on Table 11
    where Table01.ColumnG = Table11.ColumnZ
    Union All
    Select '2' As ViewColumnA, Table02.ColumnB as ViewColumnB, ' ' as ViewColumnC, Table02.ColumnD as ViewColumnD, ((Table02.ColumnE - Table02.ColumnH)* (CASE WHEN Table12.ColumnX = '1' THEN 1 WHEN Table11.ColumnX = '2' THEN -1 ELSE 0 END)) as ViewColumnE, CASE WHEN Table12.ColumnY = 1 THEN COALESCE(Tabl02.ColumnF, ' ') ELSE ' ' END AS ColumnF
    from (Select * from Table02 where ColumnG in ('1','2') ) Table02
    JOIN on Table 12
    where Table02.ColumnG = Table12.ColumnZ

    Thanks & Regards,
    Dhiraj

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    based on the limited info you have given us here, the only thing I can say that would help your performance is to ensure there is an index on ColumnD in both table01 and table02.

    when using CASE in a math equation with ELSE 0, I prefer to use ELSE NULL, as the 0 is added and the NULL is ignored, helps if most times you end up with a 0.

    Not sure why you use syntax like:
    from (Select * from Table02 where ColumnG in ('1','2') ) Table02
    JOIN on Table 12 where Table02.ColumnG = Table12.ColumnZ
    I would prefer it be written as to ensure no materialization when not needed:
    Code:
    from Table02
    INNER JOIN Table 12  ON Table02.ColumnG = Table12.ColumnZ
    where ColumnG in ('1','2')
    Dave

  3. #3
    Join Date
    Sep 2015
    Posts
    17

    Complex Query with View (Joins in definition of View)

    Thanks Dave for the quick reply and the suggestion! Had below points based on the response provided

    1. Why should the index be only on Column D? Shouldn't the index be on both Column B and Column D as it is in the where clause of the Query? If yes, Column B wouldn't be in Table01 so is it okay to have only on Column B for Table 01?
    2. The point about Null value to be as Default, I hope it wouldn't impact the calculations etc
    3. Would putting a Null Value in ViewColumnB (rather than moving ' ') while creating View from Table01 would help. I ask because in the Query a check is made for the ViewColumnB to be a specific value which would never be SPACE.
    4. Will get the syntax changed as suggested for the ColumnG check
    5. Can you please mention where can I get more details on how the Queries on Views are processed?

    Thanks & Regards,
    Dhiraj

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    1. do you provide any input values for column in table02??? Doesn't look like it, so no need for it in the index. You might want column as you are providing inputs, but do the values of 1 and 2 really filter much data? If not, then why bother.
    2. how would nothing affect a calculation?
    3. about the same
    4. good
    5. IBM Knowledge Center, search on explain. Partake in local User group meetings. Classes. etc...
    Dave

Tags for this Thread

Posting Permissions

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