Sybase : How to count number of rows in a table – demystified

Today I’ve encountered a great forum post explaining how to count the number of rows in a table “the right way” by Derek Asirvadem.

So, it’s not:

SELECT COUNT(*) FROM mytable

it is not even:

SELECT COUNT(1) FROM mytable

can be slightly better by hinting the usage of an index:

SELECT COUNT(*) FROM mytable (INDEX column_1_index)

the right way is to do:

SELECT ROW_COUNT(DB_ID(), OBJECT_ID("mytable") [, partition_id ])

because it uses sybstats, no I/O.

I knew about it, but the documentation discouraged me from using this approach. Take a look:

sp_spaceuseddisplays estimates of the number of data pages, space used by a specified table or by all tables in the current database, and the number of rows in the tables. sp_spaceused computes the rowtotal value using the rowcnt built-in function. This function uses a value for the average number of rows per data page based on a value in the allocation pages for the object. This method is very fast, but the results are estimates, and update and insert activity change actual values. The update statistics command, dbcc checktable, and dbcc checkdb update the rows-per-page estimate, so rowtotal is most accurate after one of these commands executes. Always use select count(*) if you need exact row counts.

What I’ve found out reading the forum post, is, that if you really, really, need an exact value in current moment, you should perform these just before ROW_COUNT():

EXEC sp_flushstats [Table]
-- or
UPDATE TABLE STATISTICS [Table]

Thanks Derek!

How to trace user activity to a file in SAP Sybase ASE

Today, I’ve been checking if there are any videos on YouTube on Sybase ASE’s dbcc traceon usage and I’ve found this interesting video by Lester Leonares about a nice functionality to log user activity to a file:


(This video is a part of the SAP D&T Academy, definitely worth checking out.)

Googling further on the “set tracefile”, I’ve found a White Paper by Sybase entitled “Application Tracing in Adaptive Server Enterprise (ASE) 15” (opens pdf file in new window).

Below are some notes from the video and the white paper.

To enable tracing:

set tracefile "<file-path>" [for <spid>]

To disable tracing:

set tracefile off [for <spid>]

The “set” commands in ASE whose output can be traced under application tracing context are:

set show_sqltext <on/off>
set showplan <on/off>
set statistics io <on/off>
set statistics time <on/off>
set statistics plancost <on/off>
-- and all ‘set option’ subcommands

Obviously, as you could imagine, there are some documents in Sybase Infocenter about Finding Slow Running Queries where you can read about tracefile and see some examples. This link is an excerpt from a Sybase book on Performance and Tuning Series: Query Processing and Abstract Plans (opens a 3.9MB pdf file in new window).

Sybase APT Workbench – Text GUI RAD

I’m just curious if any of you had/have pleasure to work with Sybase’s text GUI rapid application development environment called APT Workbench.

Below are some screenshots to illustrate what I’m writing about.

First, the login window:
Sybase APT Workbench - Login screen

Creating new form:
Sybase APT Workbench - New Form

Forms menu:
Sybase APT Workbench - Forms Menu

Form attributes:
Sybase APT Workbench - Form Attributes

Fields menu:
Sybase APT Workbench - Fields Menu

Field attributes:
Sybase APT Workbench - Field Attributes

Field special attributes:
Sybase APT Workbench - Field Special Attributes

Does any of the keywords ring the bell?:

CALLFORM
CANCELFORM
POSITIONFORM
USEFORM

SUBMIT LOCAL
SQLBEGIN
   SELECT ... FROM ... WHERE ...
SQLEND

After Googling for a while I’ve found some archive of a group: sybase.public.apt-dwb-sqr and also here on Sybase Community Forums.

Since APT isn’t, for quite a long time, any more supported by Sybase, some applications needed to be converted/rewritten to other platforms. I’ve seen some code automatically converted from APT to PowerBuilder by AnyLEX (site unfortunately under construction).

If you happen to be stuck with some of your legacy code written in the APT, contact me, I can try to help.

Sybase : How to remove leading zeros from a column?

This is how I do it:

UPDATE <tablename>
SET <column> = SUBSTRING(<column>, PATINDEX('%[^0]%', <column>), CHAR_LENGTH(<column>))
WHERE <column> LIKE '0%'

Obviously, the most interesting part for me is the:

'%[^0]%'

construct that finds the position of the first non-ZERO character in the <column> to start substring from.

For detailed info about used functions, please visit Sybase’s documentation:

patindex

char_length