I don't think that is strictly speaking the fault of the left join. With proper indexing, the left outer join might be fine.
Paolo > -----Original Message----- > From: Allan Cliff [mailto:[EMAIL PROTECTED]] > Sent: 19 September 2002 15:08 > To: [EMAIL PROTECTED] > Subject: Re: [ cf-dev ] SQL question - brain a blank! > > > As a reference the SQL times are: > > Sub-select: 4875ms > Left Joins: 23547ms > > that was for the 35000 entries I was playing with. > > Allan > > > ----- Original Message ----- > From: Spike > To: [EMAIL PROTECTED] > Sent: Thursday, September 19, 2002 4:04 PM > Subject: RE: [ cf-dev ] SQL question - brain a blank! > > > I was at a client earlier this week looking into exactly > this problem > > They had a stored procedure that was killing their DB > server and they > couldn't figure out what the problem was... > > With 1 user hitting the DB, the stored proc was taking > about 2-3 seconds > to run, when more than 6 users hit the DB, it took up to > 300 seconds to > run. > > After a couple of days of SQL tracing and rewriting of the > stored proc > it turned out that the main culprit was a query containing > 2 left outer > joins. Replacing one of them with a sub-select reduced the > time for the > whole stored proc by about 400ms. > > The tables in question had many thousands of records, so that was > doubtless making the difference very pronounced, but there > is clearly a > difference nonetheless. > > The database server was also a pretty beefy beast with 4 > 2GHz processors > and over 2GB of RAM. > > Spike > > > -----Original Message----- > > From: Stephen Moretti [mailto:[EMAIL PROTECTED]] > > Sent: 19 September 2002 15:35 > > To: [EMAIL PROTECTED] > > Subject: Re: [ cf-dev ] SQL question - brain a blank! > > > > > > > run the query analyser to see which is quicker.... > > > > > > > Ok - I'll take your word for it, but really the JOINs should > > be much more effiecient than sub-queries and not the other > > way around, _as long as_ you have set up your database and > > your indexes correctly... > > > > A rumour I've just been told.... Apparently, Oracle is > > supposed to do sub-queries better than joins.... > > > > > > > > -- > > ** Archive: > http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: > > [EMAIL PROTECTED] For human help, e-mail: > > [EMAIL PROTECTED] > > > > > > > > -- > ** Archive: > http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
