ctrl+R to refresh SQL Server enterprise manager query results

A small tip that many people don't know..
You can run or refresh SQL Server 2000 enterprise manager query results without clicking the (run) toolbar button using the (ctrl+R) shortcut.

Misleading performance comparisons: C# vs. Java

In a blog post: The Ultimate Java Versus C# Benchmark the author tried to prove that Java outperforms C# in a real life benchmark.
The benchmark actually measures the performance of regular expressions in both languages (say frameworks).
He concludes the post saying that C# failed even to give the results, and consumed too much memory till an OutOfMemoryException was thrown.
I tried both code samples (C# using .net 2.0, Java using Java SE runtime version 1.6).
The Java version worked as mentioned in the blog post (took about 4031 milliseconds on my PC), the C# version took too long time so I had to terminate it.
I noticed that the whole benchmark is about comparing these lines of code:
C#:
Regex regexpr = new Regex(matchthis, RegexOptions.Compiled);
Boolean b = regexpr.IsMatch(_doc);
Java:
Pattern regexpr = Pattern.compile(matchthis); Matcher matcher = egexpr.matcher(_doc);
boolean b = matcher.find();

When I changed the C# code to:
Regex regexpr = new Regex(matchthis);
Boolean b = regexpr.IsMatch(_doc);

Execution took about 14672 milliseconds. Which is somehow acceptable, even if a lot longer that Java execution time. Note that this version does not compile the regular expression.

When I removed the regular expression comparison from both codes the result was:
C#: 891 milliseconds
Java: 1750 milliseconds

See the difference!!

Conclusion:

1. It's unfair to compare two versions of code written in a way that is optimized for one language but not for another. In .net, compiling regular expressions is good when you are going to reuse them.
2. Taking a single point of comparison (regular expression in this case) is not a valid measurement when you compare two huge frameworks like .net and Java.

I'm not trying to defend C#, and I'm not claim that .net outperforms Java generally. I just suggest that performance comparisons should not be performed by biased persons. The test should not be optimized for one language without the other.

Virtual methods: Java vs C++,C#

When I first learned about OOP, I thought that one of the challenging decisions in the design was to determine virtual functions (I started OOP using c++). However, this became not a big issue as I got more mature in OOD.

Java differs. In Java, all methods are virtual, unless explicitly declared as final.

I was thinking, which approach is better?
My initial thoughts are that Java's approach is better and easier. Maybe it's not good to apply in C++ because virtual tables are used and calling virtual functions cost more than non-virtual. (not sure about the case in Java).

I don't have a complete image yet. Thoughts and comments are welcome.

Returning random records.. MS SQL Server vs. MySQL

Sometimes it's useful to return a number of random records from a database. For example a "tip of the day" functionality or displaying random products in an e-commerce site.
In MS SQL Server, this can be done using a technique that depends on the newid() function that returns a GUID . This technique is well described in this SQLTeam article.
I tried to use the same technique in MySQL using a query like:

select name from country order by uuid() limit 10;

Thinking that the uuid() function will do the same job as newid() in MS SQL Server.
for my astonishment, this could not do the job at all, the same 10 records are always returned.
After changing the query to :

select name,uuid() from country limit 10;

the results where:

+------------+--------------------------------------+
| name       | uuid()                               |
+------------+--------------------------------------+
| Anguilla   | 85f55cb0-2620-102a-9f3b-3e89f326e405 |
| Argentina  | 85f55cf4-2620-102a-9f3b-3e89f326e405 |
| Australia  | 85f55d0f-2620-102a-9f3b-3e89f326e405 |
| Austria    | 85f55d2a-2620-102a-9f3b-3e89f326e405 |
| Belgium    | 85f55d64-2620-102a-9f3b-3e89f326e405 |
| Brazil     | 85f55d73-2620-102a-9f3b-3e89f326e405 |
| Canada     | 85f55d80-2620-102a-9f3b-3e89f326e405 |
| Chile      | 85f55d8d-2620-102a-9f3b-3e89f326e405 |
| China      | 85f55d9b-2620-102a-9f3b-3e89f326e405 |
| Costa Rica | 85f55da8-2620-102a-9f3b-3e89f326e405 |
+------------+--------------------------------------+

Looking at the results above, the uuid() function seems to return sequential unique identifier. which is not the intended behavior by any means (in my case of course).

So..I tried another technique that would fail in SQL Server(As I may explain why later)

select name from country order by rand() limit 10;

and this worked great!! Each time I run this query, different 10 country names are returned.

Why does using rand() to get random records in SQL Server fail? And how to overcome this?

Ok, maybe I'll talk about this later in another post. I want to sleep.

Editing SQL Server 2000's DTS packages in SQL Server 2005

In MS SQL Server 2005, DTS was replaced with SSIS.
Fortunately, Microsoft appreciates the cost and effort spent by companies and DBAs to create DTS packages. So SSIS has (Execute DTS 2000 Package) task.
This is great, But what if you want to make a small edit to the DTS package after migrating to the new version of SQL Server?
You have 2 options:

1. Import the DTS package to SSIS, it's relatively easy. And although I had minor problems when trying this, the process was successful.
   
2. If you don't want to migrate to SSIS, Microsoft provided a good download in its SQL Server 2005 feature pack called Microsoft SQL Server 2000 DTS Designer Components. which can be used to edit and save DTS packages using the same interface used in SQL Server 2000 and save it. A really good one from Microsoft!!.
   

A Singleton pattern variation

Singleton pattern is one of the famous creational design patterns in software.
The intent of this design pattern according to GoF is to:

ensure a class has only one instance, and provide a global point of access to it.

This is a simple implementation of this pattern in C#:
public class SingletonClass
{
private static SingletonClass _instance=null;
protected SingletonClass()
{
//
// TODO: Add constructor logic here
//
}

public static SingletonClass Instance()
{
if (_instance == null)
{
_instance = new SingletonClass();
}
return _instance;
}
}
This implementation depends on a static instance that is reused by all components within the same Application Domain. Sometimes, especially in web applications (which are multi threaded by nature), this can lead to problems, or it simply can be an undesirable behavior. For example, you may want to share this instance between all classes in the same request only.

To show that above technique will not give the required behavior, I a DateTime field to the class and set it in the constructor, then read it in a ASP.NET page code and display it.
public class SingletonClass
{
private static SingletonClass _instance=null;

private DateTime _creationTime;
protected SingletonClass()
{
_creationTime = DateTime.Now;
}

public DateTime GetCreationTime()
{
return _creationTime;
}

public static SingletonClass Instance()
{
if (_instance == null)
{
_instance = new SingletonClass();
}
return _instance;
}
}
And print this value in the Page_Load Event handler:
protected void Page_Load(object sender, EventArgs e)
{
SingletonClass c = SingletonClass.Instance();
Response.Write(c.GetCreationTime());
}
Try this page in different browser instances, notice that the response is always the same.

To make an instance shared at the request level, we have some candidates where to store it.
One is using thread local storage, which is an idea that I don't recommend after some research.
Another is to store the instance in the Http Context Items collection. This code is based on this idea:
public class SingletonClass
{
private DateTime _creationTime;
protected SingletonClass()
{
_creationTime = DateTime.Now;
}

public DateTime GetCreationTime()
{
return _creationTime;
}

public static SingletonClass Instance()
{
SingletonClass _instance=null;
if(!HttpContext.Current.Items.Contains(typeof(SingletonClass)))
{
_instance=new SingletonClass();
HttpContext.Current.Items.Add(typeof(SingletonClass),_instance);
}
else
{
_instance = (SingletonClass) HttpContext.Current.Items[typeof(SingletonClass)];
}
return _instance;

}
}
This code shows the technique in action:
protected void Page_Load(object sender, EventArgs e)
{
SingletonClass a = SingletonClass.Instance();
System.Threading.Thread.Sleep(1000);
SingletonClass b = SingletonClass.Instance();

Response.Write(a.GetCreationTime().Ticks);

Response.Write(" , ");
Response.Write(b.GetCreationTime().Ticks);
}

Note that the same instance is used in the life time of the request, and when you open the page in a new browser instance or refresh the same page,you'll see that a new instance was created for the new request.

Dynamic declaration of cursors in MS SQL Server

Sometimes it's required to declare and use cursors to apply some logic on the results of queries that return the same columns, but using different criteria.
For example, you format a query based on user's selection in a search screen. Then you need to apply some logic that requires cursors on the result. This logic can be paging logic (as it was the case the first time I use this technique) or whatever logic defined by your application.

This dynamically generated code can look like:

declare @sql varchar(1000)
Set @sql='Select ID,Name,JoinDate Fom Employee Where ID>10

Now, the question is, how to declare a cursor for such a dynamic query and use this cursor in static code within a stored procedure for example to apply the required logic.

This technique cames to work great:

Create Proc CursorLogic(@SQL varchar (1000))
as
declare @id int
declare @name varchar(1000)
declare @joindate datetime

Set @sql='declare cur cursor for '+ @sql
exec (@SQL)
open cur

fetch next from cur into @id,@name,@joindate
while @@FETCH_STATUS=0
Begin

print @name
-- Cursor logic goes here

fetch next from cur into @id,@name,@joindate
End
close cur
deallocate cur
Go

This creates a stored procedure that takes the @sql variable which is a SQL query.
It inserts the declare cursor statement then uses this cursor inside the stored procedure logic, just as if it was declared regularly within the stored procedure code.

This stored procedure can be called as:

declare @sql varchar(1000)
Set @sql='Select ID,Name,JoinDate Fom Employee Where ID>10
Exec CursorLogic @SQL

Set @sql='Select ID,Name,JoinDate Fom Employee Where Name like 'M%'
Exec CursorLogic @SQL