1) The (default) 4 table limit on where clauses:

If there are more than 4 tables in a join criterion then the optimizer does not calculate all possible query plans as there will be too many. What it does is find all plans for first 4 tables. Then find all plans for next 4 tables (alone) etc until all tables are covered. It then selects the most optimal plan. This can lead to apparently strange behaviour e.g.

Select table1.* from table1, ..2, ..3, ..4, ..5

where table1.x = table2.x

and table1.x = table3.x

and table1.x = table4.x

and table1.x = table5.x

The query plans that include the table 1 to 4 where clauses will be calculated together. However the final where clause with table5 will be calculated in isolation. In this situation it would be important to ensure that the most important tables (i.e. the ones with the most data) are present in the table 1-4 group. Likewise, grouping related tables is advisable.

2) Set the table limit in development to solve hard optimisation problems:

You can change the above setting to be other numbers but this may result in it taking a long time to calculate the query plans (on compilation). However, a good trick, if you have a performance problem, is to up the number of tables considered in a development environment and run the problematic stored proc. This will take some time but it should allow you to view the optimal plan from all possibilities.

3) Indexes are used in two different ways:

Indexes can be used in two ways with one being more efficient than the other. The less efficient version is identified by in the query plan by something along the lines of "Non Clustered Leaf-Level Scan". Each page costs the same IO (approx) regardless of its size.

4) Speed Things Up with Big Non-Clustered Indexes:

Adding extra key values to a non clustered index will speed it up. Remember that for every lookup in a non clustered index there is an additional I/O required to get the actual data page and read it (this is not needed in a clustered index where the index is the data page).  Thus by adding extra data to a non-clustered index, that data is being demoralised into the index. Thus there may be no need for the extra I/O if all the data required is already in the index. Obviously there is an extra overhead of this for both insert speed (to update the index) and physical memory usage.

5) Data Page Locking (Because I always forget):

Data page locking is a combination of page level locking (in that it still locks at a data page level) and row level locking (as it uses all the fancy techniques Sybase developed to support row level locking). The result is that the speed of page level locking is retained (almost) but the indexes are not locked in the same way. In usual operation the entire B tree traversal from the root to the rows would be locked. Instead Sybase only locks the leaf pages allowing the indexes to perform as if they were under page level locking. The best of both worlds J

I asked an expert from Sybase about this when he came to visit us at Barcap. Interesting he said that whilst in theory there is a difference between row and page locking in terms of performance in reality the differences are rarely observed!!

 5) Clustered Unique Indexes with page level locking - Warning

A potential problem can occur when a clusted index is used if you have a large number of inserts. The problem arises as all inserts will need to be added to the last data page. This creates contention as all inserts are requesting the same page lock. The solution is to use the (slightly slower) non-clustered index type.