MDBitz Development Resources

Recently I came across the issue in java where the split command I was executing was returning an array split on every character and not the | character as I had specified.

    String[] subStrings  = myString.split( "|" );

The reason for this functionality is that the | character represent a character delimeter. To split the string only on the | character we need to escape it. However do to the nature of the split method we need to escape twice so that the final regex string is an escaped |. This means me need the following to split on the Pipe | delimeter.

    String[] subStrings = myString.split( "\\|" );

If you wanted to split on a double pipe then you would use the following:

    String[] substrings = myString.split( "\\|\\|" );

In Dynamic websites we often find that we need to modify page content on the fly based on user actions. A common example of this is allowing the user to add additional input boxes for configurations or attachments. I personally like to contain such input as a new row in a table. To accomplish this I utilize the jQuery library for JavaScript.

For illustration lets assume we have the following table in our page:

<table id="exampleTable1">
<tr>
    <th>City</th><th>State</th>
</tr>
<tr>
    <td>Rochester</td><td>New York</td>
</tr>
<tr>
    <td>Boston</td><td>Massachusetts</td>
</tr>
</table>

In this example the approach to adding more columns is very straightforward as we can utilize jQuery’s child and last selectors with the after modifier to add a new table row.

var newRow = '<tr><td>Albany</td><td>New York</td></tr>';
$('#exampleTable1 tr:last').after(newRow);

These 2 lines of JavaScript create the new row as a string then utilizes jQuery to get the last row of the table and append the new row after it, adding this row to the end of our table.

If a table contains a tfoot element with rows then the above will insert a row in the footer, A modification that can be done is to obtain the tbody element of the table and append the new row.

$('#exampleTable1 tbody').append(newRow);

That is all there is to it simply define your new row as a string then utilize jQuery to identify where you would like that row added to the DOM.

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

Recently at work I have been working with Database Tables that have been denormalized to “enhance” their performance. As part of the denormilization process table columns where converted from int to the varchar data type. This change lead me to wonder if there is a performance gain from using varchar over int.

After doing some research on this subject it turns out that int columns provide both performance and data integrity.

1. varchar takes longer to compare
   Due to the varchar type allowing the storage of character data a varchar type takes longer to compare then an int value that uses only numeric values
2. varchar removes type check
   Perhaps the greatest reason for not changing the type to varchar is that if you are expecting the column’s value to be an integer then the int column will do a type check that the value inserted is an integer. However by making the column a varchar this check is removed and your column is open for invalid character data that could cause failure to any sql scripts that do conversions.

To wrap things up, lets keep it simple if you need to save a value in a table that is an integer then use the int data type.