HPCBench Now Supports Linux Kernel 2.6.X

I just updated the HPCBench utility last week. It now can work in latest Linux distributions with kernel 2.6.x.

You can visit http://hpcbench.sourceforge.net for more information about HPCBench.

Replacing Tokenized String Using Regular Expression In .NET

using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;

class Program
{
    static void Main(string[] args)
    {
        string text = "Contact name:$NAME$, address:$ADDRESS$, phone:$PHONE$, email:$EMAIL$...";
        Dictionary<string, string> tokenDictionaries = new Dictionary<string, string>();
        tokenDictionaries.Add("NAME", "Rob");
        tokenDictionaries.Add("PHONE", "123456789");
        tokenDictionaries.Add("EMAIL", "Rob@abc.com");
        Console.WriteLine(ReplaceToken(text, tokenDictionaries));
        Console.Read();
    }

    public static string ReplaceToken(string text, Dictionary<string, string> tokenDictionaries)
    {
        string pattern = @"\$.*?\$";
        Match mc = Regex.Match(text, pattern);
        if (mc.Success)
        {
            while (mc.Success)
            {
                if (!string.IsNullOrEmpty(mc.Value))
                {
                    string tokenName = mc.Value.Substring(1, mc.Length - 2);
                    string tokenValue = string.Empty;
                    if (!string.IsNullOrEmpty(tokenName) && tokenDictionaries.ContainsKey(tokenName))
                        tokenValue = tokenDictionaries[tokenName];
                    text = text.Substring(0, mc.Index) + tokenValue + text.Substring(mc.Index + mc.Length);
                    mc = Regex.Match(text, pattern);
                }
            }
        }
        return text;
    }
}

Result:

Contact name:Rob, address:, phone:123456789, email:Rob@abc.com...

Avoid Table Scan In SQL Server

How SQL Server handles a query exactly? Its query optimizer takes the query, performs some analysis of related objects, and comes up a execution plan. The worst scenario is a full table scan with large amount of data. The full table scan is not efficient because every row in the table will be searched no matter it's qualified or not; it's also not scalable as data grows.

We can use indexing to avoid the full table scan. With indexed column(s), SQL Server will do the binary search (Index Seek) which is log2(N) complexity. A single record search with full table scan against 1 million records results in 1 million of row process, and the same search using indexed column(s) could have maximum of 20 row process (log2(1000000) < 20). It's a big difference when the row number is huge.

An index should include all columns that are involved in the Where clause. Also we need to write our queries carefully so that SQL Server can understand to use the index for searching. Suppose we have an Employee table with FirstName, LastName, StartDate and Country columns. All columns except Country column have been indexed (non-cluster indexing). Following queries will be index-based and fast:

SELELCT * FROM Employee WHERE FirstName = 'Mike'
SELELCT * FROM Employee WHERE FirstName = 'Mike' AND LastName = 'Bauer'
SELELCT * FROM Employee WHERE FirstName = 'Mike' OR LastName = 'Bauer'
SELELCT * FROM Employee WHERE FirstName = 'Mike' AND Country = 'Canada'
SELELCT * FROM Employee WHERE FirstName LIKE 'M%'
SELELCT * FROM Employee WHERE StartDate BETWEEN '20000101' AND '20001231'

But following queries will be less efficient because of the table scan:

SELELCT * FROM Emoloyee WHERE Country = 'Canada'
SELELCT * FROM Emoloyee WHERE FirstName = "Mike' OR Country = 'Canada'
SELELCT * FROM Employee WHERE FirstName LIKE '%M'
SELELCT * FROM Employee WHERE FirstName <> 'Mike'
SELELCT * FROM Employee WHERE StartDate = DATEPART(yyyy, OrderDate) = 2000

Free Tools For Developers

Unlike Scott Hanseleman's long list of useful tools, following are just a few free utilities I use most during my work:

.NET related:

• Reflector (http://www.aisto.com/roeder/dotnet/): an excellent tool to view the source code from .NET assemblies.

Web related:

• IE Developer Toolbar Beta (http://www.microsoft.com/downloads/details.aspx?FamilyID=e59c3964-672d-4511-bb3e-2d5e1db91038): an IE add-on to view/edit page's DOM objects, JavaScript, CSS, etc.
  
• FireBug(http://www.joehewitt.com/software/firebug/): a FireFox add-on with similar functions of IE developer toolbar. You can debug JavaScript easily with latest version of 0.4.
  
• Fiddler (http://www.fiddlertool.com/): a traffic monitor tool to view all data transferred between your IE browser and a server.
  

File related:

• WinMerge (http://sourceforge.net/projects/winmerge/): a tool to compare and merge the files.
  
• NotePad++ (http://sourceforge.net/projects/notepad-plus/): a notepad that's much powerful than Microsoft's.

SQL Server 2005 Tips

List All Recent Changes

SELECT * FROM sys.objects WHERE create_date >= '2006-04-01' OR modify_date >= '2006-04-01'

List all Stored Procedures

SELECT * FROM sys.procedures WHERE [type] = 'P' AND is_ms_shipped = 0 AND [name] NOT LIKE 'sp[_]%diagram%'
--Or
SELECT * FROM sys.objects WHERE [type]='p' AND is_ms_shipped=0 AND [name] NOT LIKE 'sp[_]%diagram%'
--Note: 'NOT LIKE' is to skip stored procedures created during database installation.

Delete All User Created Stored Procedures

SELECT 'Drop Procedure ' + name FROM sys.procedures WHERE [type] = 'P' AND is_ms_shipped = 0 AND [name] NOT LIKE 'sp[_]%'

List Schemas Owned By A Login

SELECT * FROM sys.schemas WHERE principal_id = user_id('DBUser')
--Note: To delete a login we need to change owner of the schemas owned by that login

Cross Apply

--Select top 5 quantity of production:
CREATE FUNCTION dbo.GetOrderDetail(@OrderID AS int, @MaxRow)

RETURNS TABLE AS
RETURN
SELECT TOP(MaxRow) * FROM OrderDetails WHERE OrderID = @OrderID ORDER BY Quantity DESC
GO

SELECT O.OrderID, O.Date, D.ProductName, D.Quantity
FROM Orders AS O CROSS APPLY GetOrderDetail(O.OrderID, 5) AS D

CTE(Common Table Expressions), ROW_NUMBER And RANK

--Efficient Paging:
DECLARE @PageNumber int
SET @PageNumber = 2;
DECLARE @PageSize int
SET @PageSize = 10;
WITH CTE_ORDER (OrderID, TotalAmount, Ranking, PageNumber) AS
(
SELECT O.OrderID, O.TotalAmount,
RANK() OVER (ORDER BY O.TotalAmount DESC) AS Ranking,
CEILING((ROW_NUMBER() OVER (ORDER BY O.TotalAmount DESC)) * 1.0 / @PageSize) AS PageNumber
FROM
(SELECT OrderID, SUM(Amount) AS TotalAmount FROM OrderDetails GROUP BY OrderID) AS O
)
SELECT * FROM CTE_ORDER WHERE PageNumber = @PageNumber
--Row_NUMBER() is incremental and unique but Rank() can be duplicate

--Feb. 2007 Updated: Concatenate column values using CTE
--http://www.projectdmx.com/tsql/rowconcatenate.aspx
;WITH CTE (CategoryID, JoinName, Name, length )
AS
(
SELECT CategoryID, CAST('' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM Products GROUP BY CategoryId
UNION ALL
SELECT p.CategoryId, CAST( JoinName +
CASE WHEN length = 0 THEN '' ELSE ',' END + p.Name AS VARCHAR(8000)),
CAST(p.Name AS VARCHAR(8000)), length + 1
FROM CTE c INNER JOIN Products p ON c.CategoryID = p.CategoryID
WHERE p.Name > c.Name
)
SELECT CategoryId, JoinName
FROM ( SELECT CategoryId, JoinName,
RANK() OVER ( PARTITION BY CategoryID ORDER BY length DESC) AS Ranking
FROM CTE) AS r
WHERE r.Ranking = 1

Configure Firewall Setting with Netsh

Check machine firewall setting:
Netsh firewall show state
Netsh firewall show config
Netsh firewall show allowedprogram
Netsh firewall show portopening

If firewall blocks access to the SQL Server:

Netsh firewall set portopening tcp 445 SQLNP ENABLE ALL
Netsh firewall set portopening tcp 1433 SQL_PORT_1433 ENABLE ALL
Netsh firewall set portopening udp 1434 SQLBrowser enable ALL

Check Connection Status

Select * --P.spid, P.status, P.program_name, P.cmd
FROM
master.dbo.sysprocesses P with (nolock) JOIN
master.dbo.sysdatabases D with (nolock) ON P.dbid = D.dbid
WHERE D.Name = 'Northwind'


Efficiently Get Total Row Number

SELECT rowcnt FROM sysindexes WHERE OBJECT_NAME(id) = 'NorthWind'
AND indid IN (1,0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1

SQL Server Tips

Ordering varchar Column Numerically

SELECT * FROM ProductSales WHERE CustormerID = '12345'
ORDER BY Description, CASE WHEN QuantityOfItems LIKE '%[^0-9]%' THEN 9E99 ELSE CAST(QuantityOfItems AS INTEGER) END

Only Compare Date Without Time

DECLARE @selectedDate datetime
SET @selectedDate = '04/20/2006'
SELECT * FROM ProductSales WHERE datediff(day, @selectedDate, PurchaseDate) = 0
--Get sales on current day:
SELECT * FROM ProductSales WHERE PurchaseDate >= dateadd(day, datediff(day, 0, getdate()), 0)
--Get sales on last 24 hours:
SELECT * FROM ProductSales WHERE PurchaseDate > DateAdd(d,-1,GetDate())

Insert Data Returned From Stored Procedure Into a Table

INSERT INTO ProductAnalysis EXEC('spGetProductsByTime "2005/1/1", "2005/12/31"')

Select And Insert Into New Table

INSERT INTO OrdersBackup(Customer, OrderDate, ShippingCost)
SELECT Customer, OrderDate, ShippingCost FROM Orders;

Copy Table Definition

SELECT * INTO OrdersBackup FROM Orders WHERE 1 IS NULL
--Following will also copy data:
SELECT * INTO OrdersBackup FROM Orders

Identity Handling

SET IDENTITY_INSERT Industry ON
INSERT Department(DepartmentID, Name, Description) Values(1, 'ABC', 'BCD')
SET IDENTITY_INSERT Industry OFF
GO

Delete FROM Department
DBCC CHECKIDENT('Department', RESEED, 0)
Set IDENTITY_INSERT Department OFF
INSERT Department (Name) Values ('IT') -- DepartmentID = 1

Handling Null Field/Parameter

SELECT * FROM Users WHERE LastNam LIKE IsNull(@LastName,'%')
SELECT * FROM Users WHERE LastNam LIKE COALESCE(@LastName,'%')
SELECT COALESCE(BusinessPhone, CellPhone, HomePhone) AS Phone From Users

Change Column Data Type

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers' AND COLUMN_NAME = 'Notes' AND DATA_TYPE = 'varchar' )
ALTER TABLE Customers ALTER COLUMN Notes TEXT
--or:
IF (SELECT type_name(xtype) FROM syscolumns
WHERE id = object_id('tblname') AND name = 'colname'
ALTER TABLE Customers ALTER COLUMN Notes TEXT

Case When

SELECT Country = CASE
WHEN CountryCode = 1 THEN 'USA
WHEN CountryCode = 2 THEN 'CANADA'
ELSE 'Other' END
FROM Users

SELECT CASE CountryCode
WHEN 1 THEN 'USA'
WHEN 2 THEN 'CANADA'
ELSE 'Other' END AS Country
FROM Users

SELECT OrderID, SUM(Quantity), SUM
(CASE DiscountID
WHEN DiscountID IS NOT NULL THEN Quantity
ELSE 0 END
) AS DiscountQuantity
FROM Sales GROUP BY OrderID

SELECT FirstName, LastName, RegisterDate FROM Users ORDER BY CASE
WHEN CountryCode = 1 THEN 2
WHEN CountryCode = 2 THEN 1
ELSE 3 END

Multi-Value in One Parameter

CREATE PROCEDURE TestParameters
@idList nvarchar(500)
AS
DECLARE @sql nvarchar(520)
SET @sql = 'SELECT * FROM Products WHERE id IN (' + @idList + ')'
EXEC (@sql)
GO

--Note: potential SQL injection issue with above command.

Table Insertion Trigger

CREATE TRIGGER [dbo].[OrderInsertTrigger]
On [dbo].[OrderDetails]
FOR INSERT
AS
BEGIN
DECLARE @OrderID int, @TotalItem int
SELECT @OrderID = OrderID FROM INSERTED
SELECT @TotalItem = TotalItem FROM Orders WHERE OrderID = @OrderID
IF @TotalItem IS NULL
SET @TotalItem = 1
ELSE
SET @TotalItem = @TotalItem + 1
UPDATE Orders SET TotalItem = @TotalItem WHERE OrderID = @OrderID
END

Select 10 Random Rows From A Table

SELECT TOP 10 * FROM Orders ORDER BY newid()

Interact With Shell Commands

--First you need to turn on xp_cmdshell option:
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
--Inserting c:\data.txt data into a temp table:
CREATE TABLE #tmp(line varchar(2000))
INSERT INTO #tmpEXEC xp_cmdshell 'more <>

Delete Data From SQL Server Tables

Deleting huge set of data from a table in SQL Server by DELETE command is costly, because the deletion is logging each row in the transaction log, and it consumes noticeable resources and locks. Use TRUNCATE table command instead if you want to quickly deleting the data without locking the table and writing the log file. It took almost 1 minute to delete 2-million records in a test database with DELETE command, and only 1 second with TRUNCATE command.

Note that it's possible to rollback the data after DELETE command is executed, but not for TRUNCATE command.

Oracle Database Acess in .NET

I wanted to import a Oracle database backup to a test environment and used .NET to talk with it.

First import the database backup:
C:> imp system/orcl@odbdev file= dbbackup.dmp fromuser=orms touser=orms

Configure .NET Data Provider for Oracle:
Data Source: Oracle Database (Oracle Client);
Data Provider: .NET Framework Data Provider for Oracle;
Server Name: DBServer (This is the service name configured in tnsname.ora which is set using oracle client tools)
User Name:[User]
Password: [Password]

Connection String:

<add key="OracleDBConnString" value="user ID=[USER];Password=[PASSWORD];data source=ODBDEV;" />

Both Oracle and Microsoft make their Oracle data providers available for free. Microsoft Oracle data provider is available in .NET 1.1 and .NET 2.0 framework, but it still requires Oracle client software installed; Oracle Data Provider for .NET (ODP.NET) is included with the Oracle database installation. The recommendation is use ODP.NET since it's optimized for Oracle by Oracle, and we did find ODP.NET running faster than Microsoft's in our test.

To use ODP.NET, you need to add reference of Oracle.DataAccess.dll from GAC, import the name space, and the rest is identical to the regular ADO.NET with SQL Server:

using System.Configuration;
using Oracle.DataAccess.Client;

public class DAL
{
    public static DataTable GetAllUsers()
    {
         DataTable dtUser = new DataTable();
         string connString= ConfigurationSettings.AppSettings("OracleDBConnString");
         string sqlText = "SELECT * FROM Users";
         try
         {      
             using (OracleConnection conn = New OracleConnection(connString))
             {
                  OracleAdapter adapter= new OracleDataAdapter(sqlText, conn);
                  adapter.Fill(dtUser);
             }
         }
         catch (Exception ex)
         {
             ErrorLog.Write(ex);
         }

         return dtUser;
    }
}

Concatenate Generic String List To A String

How to convert .NET 2.0 Generic List<string> to a string like "str1, str2,..."? Of course we can loop through each items inside the generic list, and add each to a string builder. But that doesn't look very elegant. The easier way is use string.Join static method and Generic ToArray method:

using System;
using System.Collections.Generic;

class Program
{
    static void Main(string[] args)
    {
        List<string> Names = new List<string>();
        Names.Add("Bob");
        Names.Add("Rob");
        string strNames = string.Join(", ", Names.ToArray());
        Console.WriteLine(strNames);
        Console.Read();
    }
}

The result is:

Bob, Rob

.NET DateTime Format String

You can display a DateTime with custom format using DateTime.ToString() or String.Format() methods in .NET. The custom format string follows a set of naming convention such as y for year, M for month, d for day, h for hour 12, m for minute, s for second, and z for time zone. Let's create a simple ASP.NET page to exam the DateTime format string:

    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string dtString = string.Empty;
            DateTime dt = DateTime.Now;
            StringBuilder sb = new StringBuilder();
            sb.Append("y yy yyy yyyy \t--- (Year) "  + dt.ToString("y yy yyy yyyy") + "<br>");
            sb.Append("M MM MMM MMMM \t--- (Month) "  + dt.ToString("M MM MMM MMMM") + "<br>");
            sb.Append("d dd ddd dddd \t--- (Day) "  + dt.ToString("d dd ddd dddd") + "<br>");
            sb.Append("h hh H HH \t--- (Hour) "  + dt.ToString("h hh H HH") + "<br>");
            sb.Append("m mm \t\t--- (Minute) "  + dt.ToString("m mm") + "<br>");
            sb.Append("z zz zzz \t--- (Zone) "  + dt.ToString("z zz zzz") + "<br>");

            sb.Append("d \t--- " + dt.ToString("d") + "<br>");
            sb.Append("D \t--- " + dt.ToString("D") + "<br>");
            sb.Append("f \t--- " + dt.ToString("f") + "<br>");
            sb.Append("F \t--- " + dt.ToString("F") + "<br>");
            sb.Append("g \t--- " + dt.ToString("g") + "<br>");
            sb.Append("G \t--- " + dt.ToString("G") + "<br>");
            sb.Append("m \t--- " + dt.ToString("m") + "<br>");
            sb.Append("r \t--- " + dt.ToString("r") + "<br>");
            sb.Append("s \t--- " + dt.ToString("s") + "<br>");
            sb.Append("u \t--- " + dt.ToString("u") + "<br>");
            sb.Append("U \t--- " + dt.ToString("U") + "<br>");
            sb.Append("y \t--- " + dt.ToString("y") + "<br>");

            sb.Append("yyyy-MM-dd \t\t--- " + dt.ToString("yyyy-MM-dd") + "<br>");
            sb.Append("yyyy-MM-dd HH:mm \t--- " + dt.ToString("yyyy-MM-dd HH:mm") + "<br>");
            sb.Append("yyyy-MM-dd HH:mm:ss \t--- " + dt.ToString("yyyy-MM-dd HH:mm:ss") + "<br>");
            sb.Append("yyyy-MM-dd HH:mm tt \t--- " + dt.ToString("yyyy-MM-dd HH:mm tt") + "<br>");
            sb.Append("yyyy-MM-dd H:mm \t--- " + dt.ToString("yyyy-MM-dd H:mm") + "<br>");
            sb.Append("yyyy-MM-dd h:mm \t--- " + dt.ToString("yyyy-MM-dd h:mm") + "<br>");
            sb.Append("ddd, dd MMM yyyy HH:mm \t--- " + dt.ToString("ddd, dd MMM yyyy HH:mm") + "<br>");
            sb.Append("dddd, dd MMMM yyyy HH:mm \t--- " + dt.ToString("dddd, dd MMMM yyyy HH:mm") + "<br>");

            lblDateTimeFormat.Text = sb.ToString();
        }
    }

Result:

y yy yyy yyyy  --- (Year) 6 06 2006 2006
M MM MMM MMMM  --- (Month) 1 01 Jan January
d dd ddd dddd  --- (Day) 18 18 Wed Wednesday
h hh H HH      --- (Hour) 9 09 21 21
m mm           --- (Minute) 12 12
z zz zzz       --- (Zone) -5 -05 -05:00
d  --- 1/18/2006
D  --- Wednesday, January 18, 2006
f  --- Wednesday, January 18, 2006 9:12 PM
F  --- Wednesday, January 18, 2006 9:12:36 PM
g  --- 1/18/2006 9:12 PM
G  --- 1/18/2006 9:12:36 PM
m  --- January 18
r  --- Wed, 18 Jan 2006 21:12:36 GMT
s  --- 2006-01-18T21:12:36
u  --- 2006-01-18 21:12:36Z
U  --- Thursday, January 19, 2006 2:12:36 AM
y  --- January, 2006
yyyy-MM-dd           --- 2006-01-18
yyyy-MM-dd HH:mm     --- 2006-01-18 21:12
yyyy-MM-dd HH:mm:ss  --- 2006-01-18 21:12:36
yyyy-MM-dd HH:mm tt  --- 2006-01-18 21:12 PM
yyyy-MM-dd H:mm      --- 2006-01-18 21:12
yyyy-MM-dd h:mm      --- 2006-01-18 9:12
ddd, dd MMM yyyy HH:mm    --- Wed, 18 Jan 2006 21:12
dddd, dd MMMM yyyy HH:mm  --- Wednesday, 18 January 2006 21:12

In reality a DateTime value is often passed through with a formatted string, and is converted back to DateTime at some point. The safest way for parsing a DateTime string is using the TryParseExact method:

string dtString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
DateTime curDate;
DateTime.TryParseExact(dtString, "yyyy-MM-dd HH:mm:ss", DateTimeFormatInfo.InvariantInfo, 
                 DateTimeStyles.None, out curDate);