with bells on.... -----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]
