Kunal Rai's blog

A Herd of programmers

2011-09-16T11:42:00.000-07:00

Cubicle after cubicle, arranged in long, dreary rows.
A soul farm. The corporate drudgery of unrealistic
schedules, bad management, and disastrous software.
No natural light and awful coffee.

Welcome to the software factory.

Set based approach vs procedural approach in sql

2010-04-11T23:21:00.000-07:00

Perhaps we all have heard that “Set based approaches” are better than the “Procedural approaches” while writing data access routines in SQL. In this article, we will try to investigate the two mentioned approaches to understand why "Set based approaches" are better.

What is a “procedural approach”?
Procedural approach is actually the “programmatic approach” that we are used to work with. In this approach, we tell the system “what to do” and “how to do” it. We write the data operational and manipulation logics using looping, conditional and processing statements. The runtime does whatever we want it to do, however we want it to do.

In TSQL, any User Defined Function (UDF), or Cursor that executes on a result set row by row is a procedural approach.

What is a “Set based approach”?

Set based approach is actually an approach which lets you specify “what to do”, but, does not let you specify “how to do”. That is, you just specify your requirement for set of data that has to be obtained from a “set of data” (Be it a simple table/view, or, joins of tables/views), filtered by optional condition(s). Sometimes, the specification of the “sets” you like to retrieve data from may be obtained by using complex joins/SubQuery/conditional case statements, but, at the end, there is a set of data from which the resultant set has to be obtained. You never have to specify “how” the data retrieval operation has to be implemented internally. You never have to specify how to implement the “joining operation” internally either. Also, you don’t have to specify how to apply filter condition against the rows. The database engine determines the best possible algorithms or processing logics to do these.

Why “Set based approach” is better than the “Procedural approach”?

The internal execution engine of databases are designed and optimized for taking “set based instructions” as input (SQLs) and executing these instructions in the best possible way (that varies based on lots of criteria) to produce output. For example, three types of joins are there in SQL server database. These are,

--Merge join : Most optimized joining algorithm. Takes place when both tables are joined on indexed columns which are sorted.
--Nested loop join : Moderate cost joining algorithm. Takes place when one of the table in the join clause contains small number of records comparing to the number of records in the other participating table.
--Hash join : Most costly joining algorithm. Takes place when large, unsorted, non-indexed columns are used in the join condition.

Now, whenever we specify any join predicate in any SQL, based upon the participating column nature and set (and, nature) of values in the participating columns, the SQL server determines and use the best possible joining algorithm and implement the logic to perform the actual joining operation in memory. Note that, we don’t have to specify the type of joining algorithm in the SQL. SQL server does that, and, does its best to provide the result as fast as possible.

Another important fact is, whatever SQL written in “set based approach” is issued in the database, the query optimizer generates an execution plan first, and then, the execution engine executes the plan to retrieve data from the physical storage and to process output in an efficient manner. That is, there is a single execution plan tree for each single SQL statement be it simple or complex. Executing that single execution plan tree is generally a faster operation.

But, when we specify our own way of processing a result set (That is obtained by an SQL) using another SQL in a row-by-row manner, the database engine has to execute an execution plan for each row. Imagine a row-by-row operation that is getting executed for a result set containing 1 million rows. In this case, the initial data retrieval operation would require one execution plan to be executed, and, later, 1 million times another execution plan has to be executed for processing each row. That’s what happens when a User Defined Function (UDF) is executed for each row in a result set. An additional overhead of using UDF is the amount of stack I/O that takes place for invoking the UDF.

Also, if you use a Cursor to process a result set row-by-row, while executing, the Cursor locks the rows in the corresponding table and unlocks rows when processing done. This involves lots of resource usage on the server and in case of large result sets, this severely slows down performance.

Experiment shows that, using a UDF for row-by-row operation involving up to 1000 rows may provide a performance that is within acceptable range. But, as the number of rows are increased, using UDF would result in dramatically slow performance. The same goes for cursors.

That is why, “set based SQLs” always should outperform the “procedural SQLs”, specially, if the result set to process becomes large.

OK, now, how to get rid of “Procedural SQLs”?

Simple tricks:

--Use inline sub queries to replace User Defined Functions.
--Instead of a cursor, use a table variable to host result set and navigate table variable’s rows using a loop to process rows.

SQL Server Best practice

2008-10-13T22:36:00.000-07:00

http://technet.microsoft.com/hi-in/sqlserver/bb331794(en-us).aspxGo

Temporary tables in SQL Server vs. table variables

2008-10-09T21:24:00.000-07:00

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables. Each of the four table options has its own purpose and use, and each has its benefits and issues:

Normal tables are exactly that, physical tables defined in your database.

Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.

Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.

Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Creating indexes on SQL Server tables

Because both local and global temporary tables are physical tables within the tempdb database, indexes can be created on these tables to increase performance as needed. As with any index creation, this process can take time on larger tables. Because temp tables are physical tables, you can also create a primary key on them via the CREATE TABLE command or via the ALTER TABLE command. You can use the ALTER TABLE command to add any defaults, new columns, or constraints that you need to within your code.

Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table
More on SQL Server tables:
FAQ: Creating and altering SQL Server tables

Create private temp tables to execute same procedure

variables can have a primary key defined upon creation using the DECLARE @variable TABLE command. This will then create a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.

Changes to table variables in SQL 2005

There are limitations on how you can use table variables. The limitations began changing with SQL Server 2005. Namely, table variables could not be used as the destination of an INSERT EXEC command such as

insert @variable
exec sp_who

Starting in SQL Server 2005, this limitation was removed and table variables can now be used as the destination for INSERT EXEC commands.

Changes to table variables in SQL Server 2008

Microsoft SQL Server 2008 introduces another important change to table variables. The latest SQL Server edition allows you to create a table variable as an input or output parameter of a stored procedure. An array of information can now be passed to a stored procedure without creating a string or XML block and then having to parse it out after executing the stored procedure. There are some restrictions on this however. The table variable parameter must be declared based on a table data type, and the variable must be read-only. When compared to not having this ability at all, these restrictions are minor inconveniences at best.

In the below sample code, a user-defined table type is created and a stored procedure is then defined using this table type. A local variable is declared also using the table type, data is loaded into the variable and then the array of data is passed from the local variable to the input parameter where the records are used within the stored procedure. Here is an example:

create type tt_example AS TABLE
(spid int)
go
create procedure usp_example
@spids tt_example READONLY
AS
SELECT *
FROM @spids
GO
declare @spids tt_example

insert into @spids
select top 10 spid
from sys.sysprocesses

exec usp_example @spids=@spids

How do the internal workings of SQL Server perform differently between table variables and temporary tables?

The differences between accessing tables and variables cause the internal processes within SQL Server to treat the objects quite differently. Temporary tables are actually physical tables, so the SQL Optimizer and locking engine handle the tables just as they would any other database tables. Because reads to a temporary table are made (including local temporary tables), a read lock is placed on the table.

This locking process takes time and CPU resources. When reading from a table variable – because the table variable is stored partially within memory and cannot be accessed by any other user or process on the system – SQL Server knows locking is not required. In a very busy database, this lack of locking can improve system performance because locks do not have to be taken, escalated and checked for each data access operation.

Limits of temporary tables and table variables

Temporary tables and table variables both have their strengths, but they both have weaknesses too. On a heavy load system that has lots of usage of temporary tables, the disk array servicing the tempdb database will experience a higher than expected load. This happens because all reads and writes to the temporary tables are done within the tempdb database. Table variables will perform poorly with large record sets, especially when doing joins because there can be no indexes other than a primary key. Beware, though, when many users start using table variables -- large amounts of RAM are used because all temporary tables are stored and processed directly in memory. Table variables should hold no more than 2 Megs to 3 Megs of data each (depending on user load and system memory).

Both temporary tables and table variables can be extremely useful tools in developers' and administrators' arsenals; however, care must be taken as to when to use each solution. There is no end-all solution, and you must choose the correct solution for the correct situation.

All information provided about Microsoft SQL Server 2008 (Katmai) is based on beta edition 10.0.1019 of the software and is subject to change without notice.

DeploymentEngineering.com - The Blog: Multiple Instance MSI's and InstallShield 12

2008-10-07T05:13:00.000-07:00

DeploymentEngineering.com - The Blog: Multiple Instance MSI's and InstallShield 12

Installing multiple instance of product using wix

2008-10-07T04:58:00.000-07:00

At my last job, I was responsible for build and install for a single tenant SaaS system that consisted of ASP.NET WebServices, WinUI and WebUI clients. This system lent itself to needing the ability to have different versions installed in parallel with different configuration options. At the time, InstallShield 12 had the ability to generate and embed product code changing multiple instance transforms but the bootstrapper lacked the ability to service these instances. As a result I authored a custom bootstrapper that interacted with the MSI API and simple reflection against the MSI database to connect the pieces into a solution that looked very much like InstallScripts multiple instance support. In fact I wrote about this on my blog: Multiple Instance MSI's and InstallShield 12. This blog article has continued to be very popular among my visitors.

Orca install package

2008-10-05T23:10:00.000-07:00

Stephane Rodriguez posted an interesting comment that I hadn't thought of - it isn't the easiest thing in the world to download Orca. You have to go through the Platform SDK web-based installer tool and also install a bunch of pieces of the core SDK to get orca.msi which lets you install Orca. I have a copy of it that I downloaded and posted at http://astebner.sts.winisp.net/Tools/Orca.zip for easier access. I hope this makes things easier for you as you debug your setup packages.

Parsing XML in SQL2005

2007-12-25T20:19:00.000-08:00

DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
SET @xmlDocument = N'
ABC123
ABC1233232
'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
--INSERT Customers

declare @Test nvarchar(10)
declare @Test2 nvarchar(10)

SELECT @Test=text
FROM OPENXML(@docHandle, N'/Parameters/Parameter[@name=''Test'']/text()')

SELECT @Test2=text
FROM OPENXML(@docHandle, N'/Parameters/Parameter[@name=''Test2'']/text()')

print @Test
print @Test2

-- WITH Customers
-- Use OPENXML to provide rowset consisting of order data.