Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    8

    Unanswered: Left Outer join performance

    Hello,

    I am having a bit of an issue with a left outer join and the time it takes to complete execution and i am looking for some advice on how to resolve the speed issue.
    I have written a web based application in Coldfusion that uses Oracle for the db.
    The application has a graphing package bolted onto it to generate dynamic graphing.
    I am outputting data to create xml so that the charting engine can read in the values and display the graph.

    To create a graph series there are several queries that run. The first query is the outer query which outputs the series name, Within the output of these queries sits anleft outer join query which returns the results and the week number. This inner query runs for each week number and the outer query runs for each series selected.

    The reasonfor the left outer join is to return week numbers where there is no result fronm the main table.
    The left outer joined table is dynamically created from the date range the user enters into the system, so if the user enters 1 Jan 2009 to the 25th June all of the week numbers between these two dates are populated into the table.

    The output results in some week numbers have null values whilst other having a result in them, which is exactly what should happen.

    I have created indexes onto the week numbers on both tables to see if this would increase the speed and to be honest i havnet seen any improvement.

    The left oout join query looks like this:
    Code:
    SELECT
    	TBL_GRAPH_DATA.TOTALFAILEDQTY,
        TBL_DAYS.DATE_RANGE
    FROM
    	UKCHAPP145.TBL_DAYS
    LEFT OUTER JOIN TBL_GRAPH_DATA
    	ON
    TBL_DAYS.DATE_RANGE = TBL_GRAPH_DATA.FTBR_DATE 
    AND
    	TBL_GRAPH_DATA.GRAPH_UUID = '#cookie.GRAPH_UUID#'	
    ORDER BY
        TBL_DAYS.DATE_RANGE

    This param '#cookie.GRAPH_UUID#' is just a coldfusion variable.

    I am looking for any advice on how i can improve the speed.

    Many thanks

    Matt

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You may want to begin with looking at the query plan. For example, in sqlplus you can "set autotrace on" and then execute your query.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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