I had a query on SQLServer Mobile v3.0, joining a large history table to a smaller lookup table. I swapped the INNER JOIN for a LEFT JOIN, and the result set was identical, however the performance was not.
Table A contained about 10,000 records, table B contained 50 and the result set was 50 records long.When I ran the following query:
SELECT *
FROM A
INNER JOIN B ON A.BID = B.BID
WHERE A.CID = @CID
it took 22 seconds to run. WHen I ran:
SELECT *
FROM A
LEFT JOIN B ON A.BID = B.BID
WHERE A.CID = @CID
it took <1 second to run.
It seems that when running the INNER JOIN, it joined all 10,000 records in table A to table B, then filtered the result to only include the items with CID=@CID. When the LEFT JOIN ran, it appears to first filter table A to include records where CID=@CID (50 records) then join those results to table B.
I've never been a fan of INNER JOIN, since I first started using SQL in anger and found that when I was querying history tables, rows would go missing from the result set if items had been delted from live related lookup tables.
Update: I ran the original query with INNER JOIN on a database with about 150,000 and 40 records in tables A and B, respectively, and the performance was the same as the LEFT JOIN. So I presume that the execution plan was different due to the different relative sizes. Still, the LEFT JOIN gave more deterministic results.
Tags: sql, left join, inner join, SQL Server 2005 Compact, SQL Server 2005 Mobile, performance, optimise
