Many of us of might have experienced that it takes ridiculously long time to retrieve the list of stored procedures from database, when you are creating a stored procedure-based datawindow or when you choose the 'Objects->Procedure Syntax' menu option in PowerBuilder's database admin. painter. And this happens even with a moderately taxed server.

Here is what one can do to speed up the response from server in this case. It also serves as a good example of how one can customize Sybase's interface to Sybase/SQL Server databases to advantage.

PowerBuilder uses a no. of stored procedures to communicate with the databases SQL Server and SYBASE System 10/11. In case of Sybase System 10/11 they reside in the Sybsystemprocs database. One can either change one or more of these stored procedures or override them by having a procedure with the same name in the user-database he/she generally works with. The changed version of the stored procedure has to go under his/her own login name or the dbo.

In particular, the Sybase-defined stored procedure 'sp_pbX0proc' brings the list of all stored procedures from the databases residing on the server (where X = 4, 5 depending on PowerBuilder's major version). It actually pulls the list of all stored procs from system databases Master and Sybsystemprocs along with the current user database; and this is what makes it take such a lot of time. Usually, when you are in development you are not much bothered about system stored procs from system databases Master and Sybsystemprocs. Hence you may want to modify the Sybase procedure sp_pb40proc to refer only to the currently connected database, for faster access. Following is the changed version of this procedure which will retrieve the list within no time on most of the installations:

create proc sp_pb40proc @procid int = NULL,
   @procnumber smallint = NULL as
if @procid = null
   select o.id, o.name, o.uid, user_name(o.uid), 1, '1'
   from dbo.sysobjects o
   where o.type = 'P'
else
   select name, type, length, colid from dbo.syscolumns
   where (id = @procid and number = @procnumber)            
return;

Similarly, the procedure 'sp_pbX0table' retrieves the list of tables from a database in the database painter. Many times one doesn't want to see the development versions of actual tables created by various other developers in the development database. If this no. is very large instead of scanning the huge list every time for actual (dbo-owned) or your tables, you can customize this PB-procedure by adding following WHERE criteria in the SELECT statement:

   where(user_name(o.uid) in ("dbo", "") )

It will then retrieve only your or dbo's tables in the database painter.