TSQL Query Optimization
A lot of us CF'ers are not the greatest when it comes to SQL queries. That's why some of us have dba geeks backing us up to point out our stupid errors and write efficient queries for us. However, not all of us have that luxury so we have to hack together our own solutions some times. Here's one such solution that I use all the time. I'm no dba so don't make too much fun of me!
The premise of this technique is to optimize queries which may require data from multiple tables via joins. Even with a properly indexed table, doing index seeks/scans of 1 million+ rows for multiple tables can slow down the query. My technique employs the use of temp table variables to pull only the necessary data from each of the tables which are finally joined and returned as a single resultset. I promise you that I've tested this on perfectly indexed tables and the results using my method absolutely outperform pulling the data in a single query with direct joins to the necessary tables.
Here's a sample:
set @var = 'foo' --likely a cf variable #foo#
declare @tblOne
table(pk varchar(25), tblOneColOne varchar(25), tblOneColTwo varchar(25))
insert into @tblOne
(pk, tblOneColOne, tblOneColTwo)
select pk, tblOneColOne, tblOneColOne
from aDatabaseTable
where pk = @var
declare @tblTwo
table(pk varchar(25), tblTwoColOne varchar(25), tblTwoColTwo varchar(25))
insert into @tblTwo
(pk, tblTwoColOne, tblTwoColTwo)
select tblOneColOne, tblTwoColOne
from anotherDatabaseTable
where pk = @var
select a.tblOneColOne, a.tblOneColTwo,
b.tblTwoColOne, b.tblTwoColTwo
from @tblOne as a
inner join @tblTwo as b
on a.pk = b.pk
One minor downside is that you can't 'select into' temp table var's. I've also been 'warned' by those in the know to place this kind of logic into a stored procedure. Not sure if that makes a huge difference or not. Any dba's out there feel free to speak up and let me know any other downfalls, but from a performance standpoint this is the best I can do!
RSS
SELECT a.pk, a.one AS a1, a.two AS a2, b.one AS b1, b.two AS b2
FROM aDatabaseTable AS a
INNER JOIN anotherTable AS b ON (a.pk = b.pk)
WHERE (a.pk = 'foo')
Did you also try this?
SELECT a.pk, a.one AS a1, a.two AS a2, b.one AS b1, b.two AS b2
FROM aDatabaseTable AS a
INNER JOIN anotherTable AS b ON (a.pk = 'foo') AND (b.pk = 'foo') AND (a.pk = b.pk)
It seems a little hinky to me that, as you said in a wonderfully-indexed table, that PK-lookups would be slow. The only thinkg I could think is that the optimizer was falling over and JOIN-ing before filtering. (Hence, moving the filter into the ON clause.)
You could also try the same thing, only more explicitly:
SELECT a.pk, a.a1, a.a2, b.b1. b.b2
FROM ( SELECT pk, one AS a1, two AS a2 FROM tableOne WHERE pk = 'foo' ) AS a
INNER JOIN
( SELECT pk, one AS b1, two AS b2 FROM tableOne WHERE pk = 'foo' ) AS b
ON (a.pk = b.pk)
That has the benefit of reducing it down to one query, but the downside of making it significantly harder to read.
-R
It doesn't make sense to me that a 3 statements should be any faster then 1 statement.
Make sure you do your testing in all different connections as well -- on your 3 query plan, you may be getting a cached resultset (which obviously would make it faster)
Maybe you could post your execution plan(s)?
Very interesting to me.
Also a "select into" is explicitly writing to disk.
dan