The type of query you use (outer, inner, equi) depends on what data you are
trying to get. There are a few syntactical differences between DBMS's and
even more than one way to describe a kind of join per DBMS (i.e. oracle
uses the (+) notation, SQL Server uses JOIN ON, or LEFT JOIN ON, or *=).
In Oracle, the order in which you list tables can affect the execution plan,
and affect the indexes used.
There are all sorts of books on query tuning. It is an in depth process,
specific to the software you are using - well, ok, some things are sort of
generalized. It is usually done at the end of a development cycle, after
quality assurance (i.e. Get it working right, then speed it up).
As was suggested earlier, the tuning gains will likely be from how you index
your tables, and how you configure memory in your DBMS. With large data
sets, it could even matter how you arrange your disks.
Hope this helps.
--b
--------------------------------------------------
Bryan Batchelder Home:727.547.1322
Web Application Developer Work:813.935.7100 x427
Cell:727.460.6665
--------------------------------------------------
Company:http://www.connectwise.com
Personal:http://pontiphex.stetsonsucks.com
PGP Key:http://pontiphex.stetsonsucks.com/pgp.txt
--------------------------------------------------
At the macro level, it may appear that I am doing
nothing. But at the cellular level I am really
quite busy.
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, January 09, 2001 2:28 PM
> To: CF-Talk
> Subject: Re: Faster Join
>
>
> It wouldn't seam to me that one cannot substitute a
> particular join type for
> another. An inner and outer join return two different recordsets.
>
> You might take a look at the underlying table indices to ensure best
> performance. My experience showed that certain composite
> indices produced the
> best results ... all based on what the original query is. If
> your using
> MS-SQL check the generated execution plan to see which if any
> indices are
> being applied ... and watch out for table scans.
>
> Another option, at least in 6.5, if you have ample server
> memory is to pin
> the index in memory.
>
> Try different index combinations and try a benchmark perform
> as best as you
> can.
>
> Bill Reichard
> Willow Gold
> [EMAIL PROTECTED]
> http://www.willowgold.com
>
>
> In a message dated 1/9/01 2:07:25 PM Eastern Standard Time,
> [EMAIL PROTECTED] writes:
>
>
> > What would be faster an inner join, an outer join, or a where
> > something = something join. I am working with huge queries
> and a little bit
> > of time on a small query could translate to a huge gain on my joins.
> >
> > Robert Everland III
> >
>
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
