You are here: Home / 2009 / Archives for November 2009

Expressor – SSIS on Steroids?

11/30/2009 By

I was doing some research on the web today after running into an SSIS issue I was having and I came across a product called Expressor.  It seems to be a pretty powerful integration product so I downloaded a whitepaper on it.  I have included the white paper here Expressor White Paper .  I also found a demo on YouTube as well.

Has anyone had any experience with this product before or are they a current user?  I am wondering if this is something that could fit in with one of my clients but I am not sure I know enough about them just yet to reccommend them.  Let me know your thoughts.

Filed Under: Business Intelligence, DB Tool Review Tagged With:

Reduce your code by 75% and an alternative to cursors

11/29/2009 By

Ok this is more just a little trick with Dynamic sql, but it saves you tons of code and you avoid having to use cursors or single row iterations.  You can use it anywhere you would a cursor.  More set based operations…Horray!!!!

For this example we will do backups 

declare @sql nvarchar(max) -- used for dynamic sql
declare @fileDate varchar(20) -- used for file name

select @fileDate = convert(varchar(20),getdate(),112) -- used for file name

set @sql = 'declare @fileName varchar(256)' + char(13)-- we need a declare statement for filename

select @sql = @sql + char(13)+
'set @fileName = ''c:/backup/'+ name + '_' + @fileDate + '.BAK''' + char(13)+
'backup database '+ [name] + ' to disk = @fileName '+ char(13)
from sys.sysdatabases
where name not in ('master','model','msdb','tempdb') 

exec sp_executesql @sql

Here is a snippit from http://www.mssqltips.com/tip.asp?tip=1070 that illustrates a simple cursor alternative

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:Backup' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name
END  

CLOSE db_cursor
DEALLOCATE db_cursor

When you compare the two and look at the profiler results it is an extreme performance increase as well

dbcc dropcleanbuffers
dbcc freeproccache
go

declare @sql nvarchar(max) -- used for dynamic sql
declare @fileDate varchar(20) -- used for file name

select @fileDate = convert(varchar(20),getdate(),112) -- used for file name

set @sql = 'declare @fileName varchar(256)' + char(13)-- we need a declare statement for filename

select @sql = @sql + char(13)+
'set @fileName = ''c:/backup/'+ name + '_' + @fileDate + '.BAK''' + char(13)+
'backup database '+ [name] + ' to disk = @fileName '+ char(13)
from sys.sysdatabases
where name not in ('master','model','msdb','tempdb') 

print( @sql )
go

dbcc dropcleanbuffers
dbcc freeproccache
go

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:Backup' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
      Print ('BACKUP DATABASE @name TO DISK = ' + @fileName) 

       FETCH NEXT FROM db_cursor INTO @name
END  

CLOSE db_cursor
DEALLOCATE db_cursor

When you run this and profile the results you can see the duration, reads and cpu use are dramatically less.

profiler

Filed Under: Database Administration, Database Development, Tips

SQLTech Consulting becomes a Friend of Red Gate

11/18/2009 By

Red Gate software has recently added SQLTech Consulting as a Friend of Red Gate.   This will enable SQLTech to beta test and review all of the latest and greatest database tools before they are released.  We are extremely excited about this new partnership and it will prove extremely helpful for keeping our knowledge and expertise for our clients in the forefront of technology.  Red Gate will also sponsor some events and provide us with various software and other swag for us to give out to our event attendees.

Filed Under: News

Avoid cursors using dynamic sql trick

11/18/2009 By

As a DBA we should always try to avoid row based processing and cursors as they are often seen in the dba world as a no-no.  Cursors require a round trip every time you go through an iteration and they require a lot more overhead.  Often times developers grab a set of records that they want to iterate through and then apply some logic to perform the actions one by one.  Let’s create a simple example with a table of fruit.  We want to select the top 2 fruit and then update the top 2 fruit to ‘bananas’

Using a cursor this is probably something like what the developer would have written:

create table test(Id int identity(1,1) Primary Key,fruit varchar(25))
Insert into Test(fruit)
values (’apples’), (’peaches’),(’pears’), (’plums’)

declare @fruit varchar(25)
declare @getfruit cursor

set @getfruit = cursor for
select top 2 fruit
from test
open @getfruit
fetch next
from @getfruit into @fruit
while @@fetch_status= 0
begin

Declare @sql nvarchar(max)
Set @sql = ‘Update test Set fruit = ”bananas” from test where fruit=”’ + @fruit +”’;’
exec sp_executesql @sql

fetch next
from @getfruit into @fruit
end close @getfruit
deallocate @getfruit

drop table test

While working with a colleague of mine, recently we found a cool trick to create sets with dynamic SQL and avoid cursors all together.  Normally I do not reccommend using dynamic sql but this has been one of the few exceptions. Using the set based method we can simplify the process by creating the set and then just apply the logic to the set itself:

create table test(Id int identity(1,1) Primary Key,fruit varchar(25))
Insert into Test(fruit)
values (’apples’), (’peaches’),(’pears’), (’plums’)
declare @sql as nvarchar(max)
set @sql= ”
select top 2 @sql = @sql + ‘Update test Set fruit = ”bananas” from test where fruit=”’ + [fruit]+”’;’+ CHAR(13)
from test
exec sp_executesql @SQL
drop table test

Summary:  As you can see it requires a lot less code to implement right off the bat.  It is also a lot easier to understand as well.   If you run the two together as a batch in SSMS with show execution plan on you will see that the set based operations will give you a much better outcome and perform the tasks a lot faster as well.  In this example we got 40% gain!

Filed Under: Tips

Idera adds SQLTech Consulting as consulting partner

11/15/2009 By
 
Filed Under: News

Red Gate’s SQL Compare 8.1 Review

11/15/2009 By

Filed Under: DB Tool Review Tagged With:

Idera’s SQLSafe and Virtual Database Review

11/15/2009 By

Filed Under: DB Tool Review Tagged With: