Thread: Left Outer join performance
06-25-09, 12:24 #1Registered User
- Join Date
- Jan 2009
Unanswered: Left Outer join performance
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:
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.
06-25-09, 14:54 #2:-)
Provided Answers: 1
- Join Date
- Jun 2003
- Toronto, Canada
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.