MDBitz Development Resources

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