How to use cross apply instead of cursors in SQL Server

I recently ran into a stored procedure with cursor logic, I wanted to see what would be the performance gain if cursor logic is rewritten with set theory operations.

Here is a simplified description of the stored procedure. There is a table with five columns, one column is an identity and a primary, (let's call it the ID), three columns (x, y, z) are of type integers and the fifth column (a) is a computed column. The computation is quite complex so it cannot be declared as a computed column expression. For each row, the values of the three columns (x, y, z) are passed in as a parameters to a custom function (where the logic is encapsulated) which spits out a calculated value. Finally, for every row the column "a" is updated with the calculated value from the custom function. There are around 7000 rows in this table. 

I put this blogpost on code project as well. Check out the following link
http://www.codeproject.com/Tips/654894/How-to-use-cross-apply-instead-of-cursors-in-SQL-S

Let's create a table called test1 with five columns. For this example lets stick with a simple logic, the fifth column is a sum of cols x,y,z.   

Step 1: Create the test table  
create table test1 
(
    id int not null identity(1,1), 
    x int,
    y int,
    z int,
    a int null
)

Step 2: Insert dummy data    
insert into test1 values (5,5,5, NULL)
go 10
-- (I inserted 9132 rows, took five minutes for the code to execute) 

Step 3: Using cursors to update the column "a"  

declare @x1 int
declare @x2 int
declare @x3 int
declare @x4 int
declare @x5 int
declare c1 cursor local for  
select id, x, y, z, a from test1
open c1
    while (0=0)
        begin
        fetch next from c1 into 
        @x1, @x2,@x3,@x4,@x5
        
        if (@@FETCH_STATUS = -1)
            break
        
        -- your logic
        set @x5 = @x4 + @x2 + @x3
        
    
        update test1
        set test1.a = @x5
        from test1
        where id = @x1
        end
close c1
deallocate c1

-- Exexcution time 01:09

Step 4: Reset column "a" 

update test1 set a = null 

Step 5: Create a table valued function (tvf) shown below

create function dbo.fnsomelogic (@x int ,@y int, @z int)
returns @val table
(
    q int
)
as 
begin 
    declare @q int 
    set @q = @x + @y +@z    
    insert into @val (q) values (@q)
    return
end

-- tvf can be invoked as shown below
-- select * from dbo.fnsomelogic(1,2,3)

Step 6: use cross apply and the tvf  to update column a

update test1
set test1.a = c.q
from test1 b cross apply dbo.fnsomelogic(b.x,b.y,b.z) c
--Execution Time: (9132 row(s) affected) in less than a second.   

If you observe the messages, the cursor which is a row based operator, displays (1 row(s) affected) for every row it updated, unlike the cross apply which displays (9132 row(s) affected). Although the problem is screaming out use cursors, however with little observation a cross apply along with a table valued function can boost the performance significantly. Relational/Set theory concepts are deeply embedded within SQL Server.    

Stored Procs 101

Stored procedures are programmability features of a database engine.

Advantages of stored procs (sp)

1. Reduce server/client network traffic
2. Stronger security 
3. Modularization & Code re-usability
4. Easier maintainance
5. Improved performance 

-- 1.  basic sp

create procedure TestProc1
as 
begin --optional
select top 5 * from sales.customers 
end -- optional 

-- 2. execute a sp

exec TestProc1 -- or execute TestProc1

-- 3. alter a sp 

alter procedure TestProc1
as
begin 
select top 10 * from sales.customers
end

-- 4. drop a sp

drop procedure TestProc1

-- 5. Rename/ "sp_" are system defined stored procs

exec sp_rename 'TestProc1', 'TestProc2'

-- 6. View the definition of sp

-- approach 1

exec sp_helptext 'TestProc1'

-- approach 2

select object_definition (object_id('TestProc'))

-- approach 3

select definition 
from sys.sql_modules
where object_id = (object_id('TestProc'))

-- 7 sp with one parameter

create procedure TestProc
( -- optional 
@custid int 
) -- optional
as
select * from sales.customers where custid = @custid

-- execute sp with parameters 

exec testproc 3

--or 

exec testProc @custid = 3

-- 8  sp with more than one parameter
create procedure TestProc
@custid int,
@contacttitle varchar(30) 
as
select * from sales.customers where custid = @custid

and contacttitle = @contacttitle

-- 9 proc which returns data, use output keyword

create procedure TestProc
@custid int,
@contacttitle varchar(30),
@companyname varchar(40) output
as
select * from sales.customers where custid = @custid

and contacttitle = @contacttitle

declare @c varchar(30)

exec testproc 2, 'Owner', @companyname = @c output

select @c -- print @c

If you don't use output keyword then if the sp will return a table if called from .net code. 

If we use the output keyword then a single value is returned which can be captured as a .net data type

Error codes of sp with output keyword

0 : Successful execution
1 : Required param is not specified
2 : Specified param value is not valid
3 : Error has occurred in getting the value
4 : Null value found for variable 


-- 10 recompiling sp
There are three ways to recompile a sp

exec testproc with recompile

exec sp_recompile 'testproc'

Powershell File Copy

Microsoft Powershell is a nifty scripting tool which is deeply integrated with .NET framework. Recently I had an opportunity to write an automated script for copying files greater than a certain date from one location to another. This is a rudimentary task, and there are a ton of tested approaches such as robocopy etc., but I decided to write my own script in powershell.

Check out the implementation details at 
http://www.codeproject.com/Tips/642297/File-copy-using-Microsoft-Powershell

Check out the code at
https://github.com/tkmallik/Powershell/blob/master/PSFileCopy.ps1 

Comparison of Java & Microsoft Technologies

 

A short comparison between software's and tools related to Microsoft technologies & Java platform.

Microsoft	Java Platform
Visual Studio	Eclipse/Netbeans
C#/VB	Java
CLR	JVM
WCF	JAX-WE. JMS
WPF	Swing
Silverlight	JavaFX
ADO.NET/EF/NHibernate	JDBC, Hibernate
ASP.NET	JSP/Servlet/JSF
IIS	Websphere/Glassfish/JBoss/Tomcat
COM+/MTS	Enterprise Java Beans (EJB)
LINQ	Jaque
System.Xml	JAX Pack (JAXM, JAXR, JAXB, JAXP)

Preconfigured Single Node Hadoop Distributions

Setting up Hadoop can be quite a hassle if you are not familiar with Linux. Below are a short list of vm’s which are preconfigured with single node Hadoop distribution.  

IBM Big Insight

http://www.ibm.com/developerworks/downloads/im/biginsights/

Pivotal

http://gopivotal.com/pivotal-products/pivotal-data-fabric/pivotal-hd#4

Cloudera  Quick start VM

https://downloads.cloudera.com/demo_vm/virtualbox/cloudera-quickstart-vm-4.3.0-virtualbox.tar.gz

MapR – Choose M3

http://www.mapr.com/products/download/download-mapr-on-premise

Hortonworks

http://hortonworks.com/products/hdp/

Wandisco

http://www.wandisco.com/hadoop/download

and for the brave souls who want to setup Hadoop from scratch (its easy actually)

http://www.michael-noll.com/tutorials/running-hadoop-on-ubuntu-linux-single-node-cluster/

SQL SERVER Collation Conflict

Recently, DBA attached a copy of  our a SQL 2000 mdf file to our new SQL 2012 server.   I had a join query on two different databases within the same server, I ran into this error. 

Cannot resolve the collation conflict between "SQL_Latin1_General_CP437_CI_AS" 
and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Turns out to be SQL 2000 database and SQL 2012 had two different collations and the cross database queries should be written as shown below

select <cols>
from <table1> t1 join <table2> t2 on 
t1.col1 = t2.c1 COLLATE Database_Default
where t1.c1 = 'Blah'

The sys.databases would have the collation name of the database 

The people & business behind Hadoop

Every decade has a key technology and a wave of new companies breed up around this technology. For the 90’s it was write once run anywhere Java, for the 2000’s the whole world was caught up in the web. For the 2010 decade, the software section is buzzing with the term Big Data along with a significant increase in consumer electronics such as tablets and phones. The key software framework which is most sought out for understanding Big Data is Hadoop. There are three main next gen companies which want customers to ask big questions and brave into new frontiers. In this blog I want to write about Hadoop and its allies.

Hadoop was created by Doug Cutting (used to work for Yahoo, currently Chief Architect at Cloudera & Director at Apache Software Foundation) and Mike Cafarella (currently a professor of computer science at University of Michigan) in 2005

Hadoop was quickly recognized as the go to solution for distributed computing using commodity hardware. This framework promised painless deployment, easy maintenance and was at the right time for handling the big data explosion (being a bit dramatic).  Ever since its release under the Apache federation foundation this project amassed lot of committers; soon an ecosystem was built around Hadoop. Variety of companies started providing Hadoop consulting services, amongst them the most popular are Cloudera, Hortonworks & MapR 

Cloudera

Three top engineers from Google, Yahoo and Facebook (Christophe Bisciglia, Amr Awadallah and Jeff Hammerbacher) foresaw a need for analyzing, managing and processing big data, so they teamed up with an Oracle employee and formed Cloudera in the early 2008.

Open Source Contribution

The employees of this company are active participants to many open source projects, some popular projects are:

Apache Avro, Apache Bigtop, Apache Crunch, Apache Flume, Apache Hadoop, Apache Hive, Apache Lucene, Apache MRUnit, Apache Oozie, Apache Sqoop, Apache Whirr, Apache ZooKeeper, Cloudera Development Kit, Crepo, Hue, Impala, Kitten, ML, Seism, CDH - 100% 
Open Source Hadoop Distribution

Other services provided by this company include a Cloudera Manager & Navigator- Centralized System Management which provide a rich set of features which include

Manager

Deployment & Configuration, Service Management, Service & Host Monitoring, Diagnostics API, Rolling Updates/Restarts, SNMP Support, LDAP Integration, Configuration History & Rollbacks, Operational Reports, Automated Disaster Recovery, BDR Add-on

Navigator

Data Audit – HDFS, HBase & Hive, Navigator Add-on, Access Management, Technical Support and Indemnity, Core Projects, Apache HBase, RTD Add-on, Cloudera Impala, RTQ Add-on, Cloudera Manager, Cloudera Navigator, Navigator Add-on

Partners & valuation

Over 160 active partners

Current net valuation of $700 million at least that the word on the street, along with an additional $65 million through Series E round of venture capital funding by Accel patners .

Developers Community

Cloudera offers training for only 5 days at a price of around $2500. This training includes Hadoop development, administration and data science. Also they provide certification as well.  

Hortonworks

This company was incubated by Yahoo. The employees of this company are regular committers to various Apache Hadoop eco systems projects. Primarily this company provides consulting services and offers an end to end solution with the Hadoop framework. They are emerging out with various data products with the newer version of Hadoop. 

Open Source Contribution

Yarn, Tez, Stinger, Ambari, HDP platform

Partners & Valuation

Microsoft, Teradata, Talend, Rackaspace are official partners. This company is currently valued at $200 million.

Developers Community

Hortonworks offers a similar training like Cloudera for only 5 days at a price of around $2500. This training includes Hadoop development, administration and data science. Also they provide certification as well. The advantage with the Hortonworks is they are committed with Microsoft. They provide training for Hadoop on Windows platform.

MapR

My personal opinion their business model is not viable, although backed up with EMC and Cisco, their online presence is dominated by Cloudera and Hortonworks. This company provides a commercial Hadoop solution by offering three products M3, M5 and M7. The employees of this firm actively participate with HBase, Pig, Apache Hive, ZooKeeper.

Partners & valuation
Google Compute Engine, EMC Cisco, valued at $30 million

What’s in it for us?

Well Hadoop is free, anyone can download it and use it, and the trick is to know what to use it for and how to manage it. There is a need for distributed computing platform and Hadoop by itself is in its nascent stages. It’s definitely not a threat to existing database platforms however investing time and money with Hadoop would be a safe bet for future.