Re: Join Query Performance Problme

[irek kordirko]

Hello,

could you explain shortly what are expected results of these queries ? What is a bussiness requirement ?
Actually these queries join other three tables only to stocksymbol table, and as a result there is a cross join generated of rows from three remaining tables. The cross join could explode expotentially, depending on number of rows retrieved from indyvidual tables. If you get X rows from the first table, Y from the second and Z from third, then number of rows in the resultset will be X*Y*Z,
for example:
2 * 2 * 2 = 8
5 * 5 * 5 = 125
10 * 10 * 10 = 1000
50 * 50 * 50 = 125000

If the cross join is really requided, then consider whether retrieving all data from these 4 tables is really required (select * ), or maybe selecting only a few columns would be sufficient (select col1,col2, .. colN) - in the latter case database doesn't need to retrieve, store in the buffer and combine whole rows from all 4 tables, but only a small subset of columns, and that should improve performance.