When true IS false the hope IS lost

Logic, mathematical logic is one of the most pure concepts that serves as the basis for the programmers value system, core beliefs or even in the extreme cases the religion. One of my university professors used to draw a simple logical chart that is universally true in programming and the life:

Input	Result	Philosophy
True	True	Possible
True	False	Possible
False	True	Devil exists
False	False	Possible

In other words both true and False results can be inferred from a true statement, and only false results can be inferred from the initially false statement. In the case if false assumption results into a true statement, the logic is non-existent, and your core beliefs are fractured, might as well be the end of the word, or for the devil to exist.


Having a special fondness for the programming language Perl, which I use for most of my coding (as a job requirement) I grew fond of it over the years, through it's wonderful unpredictability and sometimes weirdness, there is a powerful open source community backing it Cpan.org, and the general easiness of using the scripting language versus general purpose languages, I was caught by a surprise running into a pure existence of devil the other day. A code snapshot, to better prove the point:

use strict;

use warnings;

my $var1 = '';

my $var2 = '';

$var1 = (1 == 1)

        ? "true"

        : "false";

(1 == 1)

        ? $var2 = "true"

        : $var2 = "false";

print "var1 $var1\n";

print "var2 $var2\n";

Can you guess the value of $var2?
FALSE!!!
The value is false, this is the proof that Perl is evil, and human race should avoid it. This is the prove that the condition when logic stops working and devil exists, consuming your reality and beliefs.

Servers in the fog

Server in the fog...

Cloud computing is the direction most successful software firms are heading today, it is the future of technology, when instead of buying servers and hardware, one day enterprise clients will have the tools to utilize all their tools through an Internet browser. Browser being the window to the enterprise cloud offered by so many companies today.. Amazon, Microsoft offer hosting your files, your documents remotely on their clouds. Companies giving access to their programs and your personal directories, like mail archives, server backups in the remote clouds on the other side of the country.. The tendency is that the hard disk sizes on the computers sold in few years will not increase, and that the external hard drives sales will decrease in the years to come, letting the way to the cloud. Like these days Ipad2 is advertised as fashion accessory in glossy magazines, one would foresee Amazon file hosting as the next "Hot thing of the season" in Vogue September  2014 issue.

Cloud is the new buzz word, the sexy term everybody is using... Recently I worked on a project of transferring files from backups from one cloud, through multiple filters breaking the file into into chucks for simultaneous streaming through the second cloud to finally transferred to a third cloud. As crazy as it sounds it is no much different than working on multiple physical servers in one room, but instead of local IP addresses using IP gateways to the Great Cloud. With all its adverseness and modernity, cloud has some disadvantages. The downside is when poetically speaking the clouds disappear in the fog, in more technical terms, the trouble comes whenever your Internet connection is drifting. Because of the weather, or overload, or your Internet provider failing to supply decent service, the Internet goes down.. and so does all your information... That did sound a bit too pessimistic, your information is still on the remote cloud, behind the lakes and the mountains, just the fog and the distance do not allow you to see it. 

More people will start using better Internet connection packages both in the US and worldwide, and mobile coverage with data plans will improve allowing to tether and use your wifi connections with the phone. Since the beginning days of the operating systems such as Windows, there have been ill-wishers, hackers, and with the rise of the Internet the interest in the information theft has only increased. More minds are committed into finding efficient ways to find your social security number, credit card number and bank account number being transferred over the network, by click of the wrong button, and submission to wrong URL. This will rise with the cloud obsession.

With one of the most reliable companies in the world Sony PlayStation network being hacked and unrecoverable for over next two months, this is the time to deeply analyze which clouds you are going to trust. There is no guarantee, whatsoever that this information, your privacy on Google or Facebook will not be once in the hands of those ill-wishers, but before submitting all your information to some anonymous cloud, before shopping for the best price it is the time to carefully consider how much do you trust the website you are about to share some personal details with, and how much do you believe in the security of that cloud, that it will not soon be hacked and your life stolen.

Not to sound paranoid with the technological advancements, we all are target of attacks, and research (note Google is not always synonymous for the world research) is the key of finding the trustworthiness of your connection medium, of your cloud and of your fog.

Dynamic search in SQL

There may be a number of reasons to implement dynamic SQL, some are good, some bad, and others...well, how else would we learn?

In this post I am going to focus on one of the better uses for dynamic SQL-building dynamic search based on conditions.

Let's take an example scenario where we need to search a table based on user user input. For the sake of this demonstration let's assume that application logic implies that empty fields should be excluded from search conditions. This leaves a question on how to implement supporting functionality on the server side. While this may not be a problem for databases that use row modeling where data is represented across multiple rows, most of today's relational databases use good ol' columnar representation. 

One of the ways to implement dynamic search query in MS SQL Server 2005/2008 is using COALESCE function.

Select Firstname,LastName, Phone,CustID from myTable where

Firstname=COALESCE(@name,Firstname)

and

Phone=COALESCE(@phone, Phone)

This functionality can easily be replaced with ISNULL checks if you are running an earlier version of SQL Server.

First nuance, which  is rarely addressed, is the issue of NULLs in tuples. Without introducing additional NULL checks for column contents you are running into risk of having a portion of your query turn into NULL=NULL comparison, yielding empty result sets.

Another drawback to keep in mind is that similar queries are great for small tables and smaller set of searchable variables. COALESCE usage may cause full table scans thus significantly affecting performance.  As a faster option for searching large tables we can use dynamic queries. So the statement will now look like this:

Declare @QueryString as nvarchar(1000)

set @QueryString='Select Name,Address,Phone from Customer where '

if @CustName is not null

                set @QueryString+='Name = '+@CustName +' and '

if @CustAdderss is not null

                set @QueryString+='Address='+@CustAdderss+'and '

set @QueryString+='1=1'

EXECUTE sp_executesql @QueryString

While the second option performs faster and generally utilizes less resources, there are a few caveats to keep in mind. As Erland Sommarskog warns in his much referenced "The Curse and Blessings of Dynamic SQL", there are consequences to using dynamic SQL, a few of which are:

  * You cannot access local variables within the block of the dynamic SQL. We can still pass parameters in and out though.

  * Temp tables created in dynamic SQL will not be accessible to the rest of the SP (as a matter of fact, they will be dropped as soon as the dynamic SQL exectues).

  * USE statements within the dynamic SQL do not affect the calling stored procedure.

I have mentioned above that dynamic search query performs better than coalesce search. Now it is time to put the money where the cursor is and deliver the test results. For this performance test I have created three tables, with 78,000 , 430,000 and 2,200,000 records.  I ran each query several times, each time introducing more search variables to see whether the number of search variables may (or may not) affect performance.  

Trace on the smallest table yields the following results:

	COALESCE Search					Dynamic Query
78,000 rows
Search Variables	CPU	Duration	Reads		Search Variables	CPU	Duration	Reads
1	47	65	1598		1	0	49	1598
2	31	69	1598		2	31	34	1598
4	63	62	1598		4	16	42	1598
6	32	70	1680		6	31	42	1680
avg	43.25	66.5	1618.5		avg	19.5	41.75	1618.5

As we can see, the dynamic query is slightly faster than COALESCE search.

For the larger table trace results yield the following:

	COALESCE Search					Dynamic Query
430,000 rows
Search Variables	CPU	Duration	Reads		Search Variables	CPU	Duration	Reads
1	297	231	8834		1	125	102	8870
2	295	217	8834		2	126	128	8876
4	249	221	8834		4	172	118	8878
6	281	181	8834		6	171	126	8834
avg	280.5	212.5	8834		avg	148.5	118.5	8864.5

It is clear from the second table that the performance gap increases.

And the largest table yields:

	COALESCE Search					Dynamic Query
2,200,000 rows
Search Variables	CPU	Duration	Reads		Search Variables	CPU	Duration	Reads
1	1450	1120	44568		1	719	483	44502
2	1435	1117	44568		2	671	434	44518
4	1451	1068	44568		4	811	604	44520
6	1295	1049	44568		6	857	497	44568
avg	1407.75	1088.5	44568		avg	764.5	504.5	44527

Now we can see that the dynamic query performs nearly twice as fast as COALESCE search, with CPU resource utilization of nearly half than that of COALESCE search.  Based on trace results it is also clear that there is no significant performance change associated with the number of search variables.