This has oftern left me being the most experienced SQL ‘expert’ on the team.
I am lucky at my current position to have a real Sybase expert around.
I am learning more about tuning stored procedures from him than I have in years of experimentation.
In the past I had avoided using temporary tables in sprocs as they only seemed to cause problems.
Now I am seeing temp tables in almost every procedure.
What would have been a join with a complex OR clause can be better implemented as a pair of insert statements plus a delete. This can allow the use of an index for all of the select.
Another key point is to always be explicit about creating temporary tables. Never use insert into #table to create the table. This locks the temp db and can impact other users or reduce concurrency in the database.
The following construct looked wierd as part of a join:
col_b = Isnull(col_a, NULL)
Why would you replace a NULL value with a NULL?
Apparently in Sybase this converts the NULL into a comparable type so that NULL equals NULL.
However if this is used on both sides of the join:
IsNull(col_b, NULL) = IsNull(col_a, NULL)
Then this is a means of not allowing the column to be used in an index – which can ensure that the correct index is chosen!
Another construct that I had not seen before was using an update statement to populate a local variable:
set REP_VERSION = @NewVersion,
STATUS_IND = ‘A’,
@OldVersion = VERSION
where MY_REF = @Ref
and STATUS_IND = ‘R’
This can lead to some highly efficient code – there is only one trip to the filesystem where normally there would have been two.
Use of these techniques alone has reduced the runtime of one set of sprocs that are run daily from 30 mins to 90s.
Give these are run concurrently with some oher time critical sprocs this is a big win.