SQL Joins Order of Operations

Question

I'm new to SQL but happen to be dealing with a couple of rather larger table (on the order of a billion rows each).

I want to do an inner join like the following:

SELECT *
FROM XTABLE X
   INNER JOIN YTABLE Y
      ON Y.date=X.date 
      AND Y.symbol=X.symbol
WHERE X.date > 2011
   AND X.symbol in ('a','b','c')
   AND Y.symbol in ('a','b','c')
   AND Y.date > 2011

My question is: What will the order of operations be for a query like this? Will SQL make a new result set for rows with symbols{a,b,c} and dates{>2011} for each table X and Y and then do the inner join?

Answer 1

Short version: It depends. Generally spoken Sybase SQL Server is smart enough to do things the fastest way, though.

Long version:

Sybase's query processor is, at it's core, very similar to the one used in MS SQL Server.

It will create worktables (internal temporary tables; not visible to the user) if the result set is sufficiently large to overflow available memory (similar to a table spill in SQL Server). Otherwise, it'll do a pair of index scans (you do have indexes on date and symbol in both tables, right?), then a join and output, all in memory.

Caveats:

• If the indexes (indices?) aren't selective enough, it will default to a table scan instead. I'm not sure what "enough" is, but I think it's around 30% (i.e. if statistics tell the optimiser it'll get more than 30% of the rows in the table, then just scanning straight through the table will cost the optimiser less than index scanning+lookups).
• Worktables also can come into play if you have an ORDER BY statement or if the optimiser decides to use a merge join and the join criterion isn't the primary key (since a merge join requires sorted inputs).
• Also, the cost estimation at the bottom of any showplan for any non-trivial plan is meaningless, it's a dimensionless value (i.e. just a number) meant for comparisons to other plans for that query only. i.e. You can't compare two queries on two different data sets (even on the same server) and say one's more expensive than the other, because the cost estimation isn't comparable like that. There's also some cases I've seen where the cost estimation for the same query has been wildly off (e.g. merge join with a cost of 1000 takes several hours to complete, SET MERGE_JOIN OFF and it defaults to NL joins with a cost of 10000, but runs in minutes). Optimising Sybase's query processor is a whole different book (actually there's four books, I just linked the advanced QP one).
• There are some tiny corner cases where the query processor will do things in an obviously suboptimal way, but in 99% of cases you won't run into those.

That said, based on what I've seen the query plan would look something like this: (this is vastly simplified, the actual output from sp_showplan is rather more detailed)

|ROOT:EMIT (aka select)
|  
|  | NL JOIN (nested loop join)
|  |
|  |  | RESTRICT Table X
|  |  | 
|  |  |  | SCAN Table X (using index, forward scan)
|  | 
|  |  | RESTRICT Table Y
|  |  | 
|  |  |  | SCAN Table Y (using index, forward scan)

The reason it shows the RESTRICT after the SCAN is because without the SCAN there's no data to RESTRICT - I'm reasonably sure that you can effectively consider them as one operation in this case (i.e. the restriction conditions will be part of each index scan, rather than the index scan copying a whole lump of data and restrict throwing most of it out).

Answer 2

Your query looks fine !! It should execute and result the answer !!

You can also check the SQL Query Cost Estimation, If you feel execution results takes more time , then i would consider on this part ..

ON Y.date=X.date AND Y.symbol=X.symbol