MDBitz Development Resources

When developing a new system or software you will often find yourself unsure on the proper naming schema to utilize, or you may find out you need to comply with a company’s standard nomenclature. When this happens you may want to take note of the sp_rename stored procedure available in Sybase. This procedure allows you to rename a table, column, or index object be forewarned though that renaming a table can break stored procedures or queries that utilize the table and they will need to be updated appropriately.

Renaming a Database Table with sp_rename

To rename a table all you need to do is execute the procedure passing the name of the table to be modified and the new name for the table.

EXEC SP_RENAME MY_TABLE_OLD, MY_TABLE_NEW

Renaming a Table Column with sp_rename

To rename a column you need to specify the table and column in the first parameter and then in the 2nd parameter only put the new name of the column.

EXEC SP_RENAME MY_TABLE_NEW.old_id, new_id

Renaming a Table Index with sp_rename

To rename an index you need to specify the table and index in the first parameter and then in the 2nd parameter only put the new name of the index.

EXEC SP_RENAME MY_TABLE_NEW.id_idx, new_id_idx

It is important to note that if you have a column and index with the same name for a table you can specify a third parameter saying that you want to rename the index or column

EXEC SP_RENAME MY_TABLE_NEW.client_id, new_client_id, "index"

Resources

• Sybase Online : sp_rename function reference

As a certain project at work evolves I find that more and more of my time is spent on optimizing the T-SQL queries that are utilized. Recently I have come across the issue that an index was not getting utilized in a sql query when I was using the UPPER or LOWER function to do an ignore case comparison. As it turns out that using a function on a column causes a table scan and will not use the index ever.

To resolve this issue we fell back on regular expressions as T-SQL does not support the ILIKE function. What this means is that we can use regular expression sets for each character in our String. To illustrate lets look at the following basic SQL string.

SELECT * FROM USERS WHERE UPPER(LAST_NAME) LIKE "DENTON%"

If our table had an index on the LAST_NAME column then it would be ignored. To use our index we would have to rewrite the query to be:

SELECT * FROM USERS WHERE LAST_NAME LIKE "[dD][eE][nN][tT][oO][nN]%"

This may seem odd, but running this second query will utilize the column index. Another item to note is that you can only use the regular expressions in LIKE clauses and not = clauses.

Resources

• Sybase Manual • Matching character strings: like

The NOEXEC option of SQL is used to specify that the SQL statements are not to be executed. Utilizing this command with the SHOWPLAN option enables us to quickly see the query plans for a SQL statement without having the query actually execute. This gives us a quick output of the steps the DB Server uses to execute the queries and if table scans or indexes are used.

Proper Usage

SET SHOWPLAN ON
SET NOEXEC ON
go
 
SELECT ID, F_NAME, L_NAME 
FROM USERS u WHERE u.GENDER = "F"
go

Those familiar with SQL will have most likely used the UNION command when they want to combine select statements into 1 result set. Most often individuals will use UNION without understanding that additional work is done behind the scenes to remove duplicate entries. That is why it is important to understand your database and to know if there is the possiblity of duplicate entries. If there is no chance for duplicate entries or if you want duplicates in your results then you would want to use the UNION ALL command that simply joins the 2 result sets without using any worktables enhancing its performance.

To illustrate lets assume we have an activity log table that stores users activities within an application. In addition we have a log history table that actions get moved to at the end of the day so that the inserting of daily records does not get bogged down by the table having thousands/millions of rows.

Now lets assume we have a monitoring application that allows priveledged individuals to do searches on a user for a time period to get a list of activities they did within the application. To do this most individuals would do a UNION on the LOG and LOG_HISTORY tables.

SELECT USER_ID, USER_ACTION_TS, USER_ACTION_NM, USERACTION_SUB_NM
FROM USER_ACTIVITY_LOG
WHERE USER_ID = @userId
AND USER_ACTION_TS BETWEEN @startTS AND @endTS
 
UNION
 
SELECT USER_ID, USER_ACTION_TS, USER_ACTION_NM, USERACTION_SUB_NM
FROM USER_ACTIVITY_LOG_HIST
WHERE USER_ID = @userId
AND USER_ACTION_TS BETWEEN @startTS AND @endTS

However in this case it is not possible for there to be duplicate records returned from the two tables. So instead to enhance the performance of the query we could use the UNION ALL statement.

SELECT USER_ID, USER_ACTION_TS, USER_ACTION_NM, USERACTION_SUB_NM
FROM USER_ACTIVITY_LOG
WHERE USER_ID = @userId
AND USER_ACTION_TS BETWEEN @startTS AND @endTS
 
UNION ALL
 
SELECT USER_ID, USER_ACTION_TS, USER_ACTION_NM, USERACTION_SUB_NM
FROM USER_ACTIVITY_LOG_HIST
WHERE USER_ID = @userId
AND USER_ACTION_TS BETWEEN @startTS AND @endTS

When writing SQL queries we often measure the performance of the query or procedure by the amount of time it takes to complete. Often I found myself saying that if it completes in a couple seconds then it is good. Lately however I have found myself more concerned with the IO count then with the execution time. The IO count is the amount of reads and writes performed by the execution of a query. This value is what can really tell you if your SQL query is optimized.

To view the IO performance of the query one can use the SET STATISTICS command of SYBASE. To enable the display of the logical reads and writes you would turn on the statistics by the command:

SET STATISTICS IO ON

And would turn it off by running the command:

SET STATISTICS IO OFF

Now that you can obtain the io performance of the query you can make use of the SHOWPLAN command to fine tune your query’s performance. The SHOWPLAN command is enabled similar to the SET STATISTICS command by toggling on and off by the ON/OFF keywords:

SET SHOWPLAN ON

By setting the SHOWPLAN command to ON you will be outputted with the steps that are performed by the query or procedure. The output will display all the selects, inserts, and creations that occur when running the sql statement. Using this input one is able to fine tune the query by the reorganization of statements, addition of indexes to remove or reduce Table Scans, as well as further optimization techniques.