MDBitz Development Resources

Transferring data between tables is a common task of database maintenance. It is used when updating database schemas or when you need to migrate data to a log or history table. There are 2 common methods for performing the transfer the first is done by use of a WHERE NOT EXISTS sub query, while the less common known method is by setting up and utilizing a Trigger, which removes rows from the old table as soon as they get inserted into the new table.

WHERE NOT EXISTS Method

The most common method of transferring data between tables in a database that i have seen is the combination of ROWCOUNT and a WHERE NOT EXISTS clause. How this method works is that you set your ROWCOUNT to the batch size then you insert into your table to be transferred to from a select of your old table where the current record does not exist in your transfer to table. A sample code snippet showing this method is:

DECLARE @ErrorId INTEGER,
    @RowCtrInserted INTEGER
 
SET ROWCOUNT 10000
 
SELECT @RowCtrInserted = 1, @ErrorId = @@ERROR
 
WHILE (@ErrorId = 0 AND @RowCtrInserted > 0)
BEGIN			
    INSERT INTO LOG_HIST	
        (
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
        )
    SELECT 
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
    FROM LOG LOG
    WHERE NOT EXISTS (
        SELECT * 
        FROM LOG_HIST hist
        WHERE LOG.ID = hist.ID 
     )
 
    SELECT @RowCtrInserted = @@ROWCOUNT, @ErrorId = @@ERROR
END
 
IF @ErrorId=0 
BEGIN
    TRUNCATE TABLE LOG
END

As can be seen in the code this method loops while a record exists in the log table that is not present in the log_hist table. This requires an additional query every time the batch size is met, and can be a performance problem on large database tables.

Temporary Trigger Method

The second method that can be used to transfer data between two tables is the use of a temporary insert trigger on the table being transferred to. How it works is that the trigger is defined to delete the newly inserted record from the old table. What this means is that for every iteration of the insert loop as determined by the batch size a sub query to determine if a record already exists in the new table is not necessary as records are deleted as soon as they are transferred. Below is a sample sql script setting up the trigger and performing the transfer, in addition it contains error checking and print statements.

CREATE TRIGGER LOG_HIST_TR ON LOG_HIST FOR INSERT
AS
DELETE LOG FROM LOG, inserted
  WHERE 1=1
    AND LOG.ID = inserted.ID
go
 
PRINT "Transferring data from LOG to LOG_HIST"
go
 
DECLARE @err INT, @n INT
SELECT @n=0
PRINT 'Will use batch size of 10000 rows'
WHILE exists (SELECT * FROM LOG)
BEGIN
    INSERT LOG_HIST (
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
    )
    SELECT TOP 10000
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
    FROM LOG
 
    SELECT @err=@@ERROR, @n=@n+@@ROWCOUNT
 
    IF @err=0
    BEGIN
        DUMP TRAN USERACTIVITY WITH truncate_only
        PRINT '%1! rows transferred', @n
    END
    ELSE
    BEGIN
        RAISERROR 20001 'Failed to transfer data from table LOG to table LOG_HIST. Please recover data from these two tables'
        BREAK
    END
END
 
go
PRINT "Checking data transfer completion"
go
IF not exists (SELECT * FROM LOG)
BEGIN
    DROP TRIGGER LOG_HIST_TR
END
ELSE
BEGIN
    RAISERROR 20001 'Failed to transfer data from LOG into LOG_HIST'
END

In the above example after each batch iteration we output the total number of records transferred, as well as cleaning up the database log so that we don’t max out the allocated space. Once an error is thrown or no more records exists we drop our temporary trigger. You will notice that no truncating of the table we transferred data from is needed as the trigger was responsible for removing them. The additional benefit to using this method is that if a transfer is interrupted no time or data is loss and the transfer can pick up right were it left off without performing an additional WHERE NOT EXISTS sub query.

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

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.

After inserting, modifying, or deleting a large amount of records in a table that contains indexes performance of queries can be increased by use of the update statistics query. The update statistics query creates or replaces statistics on a column and stores them in the system tables systabstats and sysstatistics. Sybase utilizes these statistics when determining how to process a query.

Examples:

Updating all indexes of a Table

UPDATE STATISTICS T_USERS -- T_USERS = table name

Updating a single index of a Table

UPDATE STATISTICS T_USERS user_pk_idx -- user_pk_idx = index name

Resources:

• Sybase T-SQL : Online Manual