Random outpourings of a software developer
@set _server=MYSERVER
@set _db=MYDATABASE
@set dbcmd=sqlcmd -S %_server% -d %_db% -E -k -W -Q
@REM select ‘%dbcmd% “sp_helptext ”[‘ + SS.Name + ‘].[‘ + SO.name + ‘]”” > doc’ + SS.Name + ‘.’ + SO.name + ‘.sql’
@REM from sysobjects SO
@REM join sys.schemas SS
@REM on SO.uid = SS.schema_id
@REM where SO.type = ‘P’
Rapid-fire clear down scripts for Sql server 2005
select ‘drop table ‘ + SS.Name + ‘.’ + SO.name
from sysobjects SO
join sys.schemas SS ON SO.uid = SS.[schema_id]
where type = ‘U’
order by SO.name
select ‘drop function ‘ + SS.Name + ‘.’ + SO.name
from sysobjects SO
join sys.schemas SS ON SO.uid = SS.[schema_id]
where type = ‘FN’
order by SO.name
select ‘drop procedure ‘ + SS.Name + ‘.’ + SO.name
from sysobjects SO
join sys.schemas SS ON SO.uid = SS.[schema_id]
where type = ‘P’ and SS.Name != ‘dbo’
order by SO.name
select ‘drop view ‘ + SS.Name + ‘.’ + SO.name
from sysobjects SO
join sys.schemas SS ON SO.uid = SS.[schema_id]
where type = ‘V’
order by SO.name
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:
update MYTABLE
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.
This is a very useful DBA blog.
Developer Rants 