Welcome Home !!

I realize I have been relatively quite for a while, but summer is actually a fairly busy time for us at work.  Finishing things up for the school year and getting ready for the next year.  However, today I am going to talk about something non-technical.

Friday night my youngest daughter and I went out to the Special Events Center on Fort Carson.  Four hundred soldiers of 4th Infantry Division 2nd Brigade Combat Team came home from Iraq.  We were there meet and bring home with us a soldier my oldest daughter had met before going into the Army herself.  Now we hadn't met this soldier yet, but my daughter had, and had been talking to him online and on the phone when he had the opportunity to call from Iraq.

As I stood inside the Special Event Center, I saw family and friends of returning soldiers.  Some were dressed to the nines, others in shirts and jeans.  Some kids wore t-shirts with the name of their mom or dad who was returning.  There was much excitement and anticipation, and you could literally feel it in the air.  To help pass the time for the young kids there was even a jumper that the kids could play in while waiting.  Music was playing, some people (particularly young kids in elementary and middle school, but some older) dancing.  Everyone had signs to welcome home their loved ones.  Some of the songs were patriotic country songs that I'll admit brought tears to my eyes as I thought about the sacrifices made by both the soldiers and their families.

Then they finally arrived.  They marched in while Toby Keith's "Courtesy of the Red, White and Blue" played.  The cheering that accompanied both was loud and raucous.  All became quite once they were all in the Special Events Center and they then played our National Anthem, "The Star Spangled Banner," which end with much more applause.  Following a prayer and a speech by the Commanding General of Fort Carson, the soldiers were dismissed.  That is when the real reunions started.  Wives and husbands and children hugging each other, some soldiers looking for friends or family until they too were found.  My daughter and I stood there with a small sign with just a soldiers name.  He found us and we left the Center to our car and he gathered his things.

As we left, we passed a soldier who looked as if he had been crying, don't know why.  Perhaps no one was there to welcome him home, or maybe he was just happy to be home.  I don't know.

What I do know is this, we need to do more for this men and women in the military and their families.  We say thanks to them when we see them in the stores or restaurants, or airports.  Some people have even picked up their tab for dinners.  We need to more if we can.  What more can I do right now, I don't know but I will try to find something more to honor them.  It may even be nothing more at the moment than pray for them and their safety.  But more I will try to do, and not just because my daughter now too serves her country.

I don't want those who live in other countries to think I am only thinking of our military at this time, I'm not.  The we I am talking about is all of us and the Soldiers, Sailors, Marines, and Airmen I am truly talking about are those that serve in the military of any country, regardless of ideology or politics.

The next time you see one, even if you don't say Thank You to them, remember that they have made a commitment to serve and protect your country, and honor them.

To all the Soldiers, Sailors, Marines, and Airmen; Thank you for your selflessness and sacrifice by both you and your families.  Some have given the ultimate sacrifice and they too must be honored.  Freedom isn't free and their must be those who stand ready when the call is made.  Thank you.

Learning a New Development Platform

This past week at work we have been having some in-depth training.  The interesting thing is it hasn't been SQL Server, although the Microsoft product involved uses it extensively, it has been Windows SharePoint Services and Microsoft Office SharePoint Server.

The reason I thought I'd talk about it briefly is what I have learned about it during the last 4 days.  My previous employer used it, and based on one f my co-workers who also worked there with me, its use there exploded after I left.  Everyone in the company used it extensively.  We started using Windows SharePoint Services about three years ago a little bit.  I implemented a small team site when we first started our Data Warehouse project, but it didn't go anywhere beyond that for a quite awhile.  We developed a small POC site last school year hat went over pretty good at the school where it was used, but it wasn't really a ready for prime-time site.

We have put together a site just recently that went into production for our Professional Development department.  We'll see how this site goes.  The interesting thing here is that the developer that put it together wishes we had had this class first.  Found some things he would have done quite differently had he known more about the product.

I have learned quite a bit myself.  WSS and MOSS are both extremely powerful development platforms.  WSS for department level applications, and MOSS for Enterprise applications.  If you get the opportunity to work with either product, take the time to really learn it in depth, I think you'll be quite surprised.  For me, there are a couple of minor issues, the most glaring is the lack of scheduled backups for Farms, Site Collections, and Sites.  You either have to use Task Scheduler (I haven't figured out yet how to get a job in SQL Server Agent to run the STSADM tool to run a backup) or purchase a third party tool to accomplish the task.

I'm actually looking forward to learning a lot more about WSS and MOSS over the next few months.

My Latest Articles

A few weeks ago I mentioned my blog that I would have two articles published on sswug.org.  Well, they have been published and are available to members of sswug.org.

The first article, published on May 26th, is titled Deleting Large Number of Records.

The second article, published on June 1st, is titled The Dynamic Tally or Numbers Table.

I am a somewhat disappointed that only paid members can read these articles at this time, as I am sure they would have been read by many more people by now than have read them on sswug.org.

Once I am able to republish these two articles, I will submit them to Steve Jones on SSC where I am certain they will get a much wider reading then they have so far to date.

Although disappointed by the low number of views, I do plan on writing additional articles for sswug.org, as it is one way to get free monthly memberships.  But I am also going to try and write more articles so that I can also have some published on SSC as well.  Hopefully as I get more proficient and prolific, I'll have multiple articles be published on multiple sites at the same time, and be able to republish the more restricted ones later.

Comparing Hardcoded functions, In-Line TVF's, and Scalar Functions

While perusing some of the threads on SQLServerCentral.com one of the questions that hit me was regarding the use of scalar functions in queries.  In this particular case, in the column list of a SELECT statement.  Did it make sense to encapsulate a routine to strip of the time portion of a datetime value or should you put the actual function call(s) in the SELECT list.

I decided to run some tests so I created to user-defined functions that would strip off the time portion of a datetime value, one was created as a scalar function and the second as an in-line table valued function.  In my SandBox database I have a table, dbo.LAPTest, that has 17,996,362 records.  One of the columns in this table is a datetime column with random datetime values.

Here is the code for the two functions I wrote for the test:

CREATE FUNCTION [dbo].[ufnDayOnly](@DateValue DateTime)
RETURNS table
AS
RETURN (select dateadd(dd, datediff(dd,0, @DateValue),0) as DateOnly );
GO

CREATE FUNCTION [dbo].[fn_DayOnly] (@DateValue DateTime)
RETURNS DateTime
AS
BEGIN
    RETURN dateadd(dd, datediff(dd,0, @DateValue),0) 
END
GO

I then wrote three separate T-SQL scripts, shown below, that will run 8 time each selecting 1 to 10,000,000 rows.  Each script strips the time portion of datetime values using three methods; hardcoded date functions, the table-valued function, and the scalar function.  Each time through the loop we will clear the caches.

Set NoCount On;
Declare @cpu_ int,
        @lreads_ int,
        @eMsec_ int;
declare @AcctID int,
        @Amount money,
        @TDate datetime,
        @ADate datetime;
declare @LoopCnt int;
set @LoopCnt = 0;

while @LoopCnt < 8
begin
    dbcc freeproccache with no_infomsgs;
    dbcc freesystemcache('ALL') with no_infomsgs;

    Select
        @cpu_ = cpu_time 
        , @lreads_ = logical_reads 
        , @eMsec_ = total_elapsed_time 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;

    select top (power(10, @LoopCnt))
        @AcctID = AccountID,
        @Amount = Amount,
        @TDate  = TDate,
        @ADate  = dateadd(dd,datediff(dd,0,TDate),0)
    from
        dbo.LAPTest;
     
    Select
        power(10, @LoopCnt) as RowsProcessed,
        cpu_time-@cpu_ as CpuMs 
        , logical_reads- @lreads_ as LogRds 
        , total_elapsed_time - @eMsec_ as Elapsed 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;
    set @LoopCnt = @LoopCnt + 1;
end

 

Set NoCount On;
Declare @cpu_ int,
        @lreads_ int,
        @eMsec_ int;
declare @AcctID int,
        @Amount money,
        @TDate datetime,
        @ADate datetime;
declare @LoopCnt int;
set @LoopCnt = 0;

while @LoopCnt < 8
begin
    dbcc freeproccache with no_infomsgs;
    dbcc freesystemcache('ALL') with no_infomsgs;

    Select
        @cpu_ = cpu_time 
        , @lreads_ = logical_reads 
        , @eMsec_ = total_elapsed_time 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;

    select top (power(10, @LoopCnt))
        @AcctID  = AccountID,
        @Amount = Amount,
        @TDate   = TDate,
        @ADate   = DateOnly
    from
        dbo.LAPTest
        cross apply dbo.ufnDayOnly(TDate);
     
    Select
        power(10, @LoopCnt) as RecordsProcessed,
        cpu_time-@cpu_ as CpuMs 
        , logical_reads- @lreads_ as LogRds 
        , total_elapsed_time - @eMsec_ as Elapsed 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;
    set @LoopCnt = @LoopCnt + 1;
end

 

Set NoCount On;
Declare @cpu_ int,
        @lreads_ int,
        @eMsec_ int;
declare @AcctID int,
        @Amount money,
        @TDate datetime,
        @ADate datetime;
declare @LoopCnt int;
set @LoopCnt = 0;

while @LoopCnt < 8
begin
    dbcc freeproccache with no_infomsgs;
    dbcc freesystemcache('ALL') with no_infomsgs;

    Select
        @cpu_ = cpu_time 
        , @lreads_ = logical_reads 
        , @eMsec_ = total_elapsed_time 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;

    select top (power(10,@LoopCnt))
        @AcctID = AccountID,
        @Amount = Amount,
        @TDate  = TDate,
        @ADate   = dbo.fn_DayOnly(TDate)
    from
        dbo.LAPTest;
     
    Select
        power(10,@LoopCnt) as RecordsProcessed,
        cpu_time-@cpu_ as CpuMs 
        , logical_reads- @lreads_ as LogRds 
        , total_elapsed_time - @eMsec_ as Elapsed 
    From
        sys.dm_exec_requests 
    Where
        session_id = @@spid;
    set @LoopCnt = @LoopCnt + 1;
end

The results of running these scripts are as follows:

	Hardcoded Functions			In-Line TVF/Cross Apply			Scalar Function
RowsProcessed	CpuMs	LogRds	Elapsed	CpuMs	LogRds	Elapsed	CpuMs	LogRds	Elapsed
1	0	26	1	0	52	1	0	40	2
10	0	4	0	0	4	1	0	18	1
100	0	4	1	0	4	0	0	18	2
1000	0	14	1	0	14	2	16	28	36
10000	16	58	12	16	58	13	94	72	102
100000	125	523	143	125	523	115	703	537	699
1000000	703	5130	730	703	5130	715	7687	5144	7958
10000000	6984	51171	7072	7063	51171	7157	67016	51185	67414

As you can see there isn't much difference between the hardcoded functions and the In-Line TVF/Cross Apply, however, as the number of rows increase there is a definite disadvantage to using the scalar function.

Does this mean you should convert all your scalar functions to In-Line TVF's?  Not necessarily, as you may need those functions else where, such as a WHERE clause where it may only be called once.

Does this mean you should always use the CROSS APPLY with In-Line TVF's? Again, not necessarily as it may not be performant either in all cases.

What can I suggest?  Test, test, and test again is the best advice.  The testing I ran above is limited and not necessarily what you may do on a normal, day to day basis.  But it does give you some food for thought.

 

See you on the forums.  I look forward to seeing what you all have to say.

Published, Again!

I have written two new articles and they are going to be published, not on SSC but on SSWUG.  I thought I'd help out Chris Shaw a bit and write a few articles for him as well.  Plus it broadens my horizons some as well.

The first article, based on numerous posts here on SSC is titled Deleting Numerous Rows from a Table, and will be published on May 25th.  It shows how to delete records in batches and control the size of the transaction log files, if the database is using the bulk logged or full recovery model.  This article originally started as my first blog entry, but when I finished it, it seemed more like an article.

The second article, to be published on June 1st, is titled The Dynamic Tally or Numbers Table.  I wrote this one after reading an article in SQL Server Magazine about creating a tally table.  I think you will find the methodology behind my dynamic tally table much more performant than the one in the SQL Server Magazine article.

When a Community Rallies!

I have been a bit lax these past few days, but I have been having a bit of a writers block not knowing what to write about.  Well, I realized this evening there is something worth writing about, and it is happening right now on SQLServerCentral.com.  An amazing thing is happening on this thread Performance issue with tally solution.

The SSC community has come together in a effort to develop a variety of methods to parse strings.  People have put together routines that use cursors, while loops, tally tables, double barreled tally tables, double barreled double carbed tally tables, CLR procedures, and (if I remember correctly, which I might not) even a XML version.  Florian Reischl started this particular thread and has been spearheading the testing of the procedures.  Many others have joined in the project that has definitely shown what can happen when a community like that on SSC gets together.

This has been one of the most interesting threads I have had the opportunity to be just a small participant in as it has grown.  Many of us are eagerly awaiting the testing of the procedures, looking to see which one is "the best".  Early testing, however, has shown that the choice of procedures is really dependant on many factors.  I'm not going to spoil anything here, so I'm not even going to say anything more on that particular subject.

We are encouraging Flo to fully document everything that is occurring and to submit an article (or two or three) when he finally completes the testing.  I really think it will be a significant article and one that many people will reference.

If you are interested to see what is happening click on the hyperlink above and start reading!

Career Development and Training

I know I discussed this in an early post, but it seems to have reared its ugly head again.  This time during the discussion of Barry Young's first article in his series on removing cursors from your code.

It seems interesting that there are individuals and companies out there the subscribe to the theory of developing to the lowest common denominator on their team.  If this results in the writing of code that is inefficient, not scalable, but works in the test and development environment and meets the customers requirements, that's good enough.  The excuse being that they have deadlines to meet, and customers to satisfy; they don't have time to train their developers in better, more efficient development techniques in a database environment like MS SQL Server.  So, if you know and understand cursors, go ahead and use them even though they are not the best tool in the toolbox.

What is scary about this, is I could be in that category.  When I first started working with SQL Server 6.5 back in 1997, I didn't have the benefit of working with anyone who had experience with SQL Server.  Everything I learned, I learned from reading books or just experimenting.  There was no SQLServerCentral.com at the time, in fact not much on the Internet at all back then really.  I'm sure code I had written back then, except maybe the trivial code, is something now I would never write with the knowledge and experience I have now.  Even some of the things I learned from books back then I will try to avoid today, like triangular joins for one.

I have learned new ways to look at things in SQL, and I am now willing to challenge the code I currently develop.  Yes, it may work and provide me the answers I require, but is it really the best code I can develop?  Is there something I am missing that could improve the performance or scalability of the code?  I have even started search sites like SSC for similar problems to see if I could find a better solution that I could use.

I have also found myself sharing techniques I have learned from others on SSC with my coworkers, wether it has been simple things like determining the first and last days of the month, calculating work days, or parsing strings using a Tally table.  These techniques have allowed us to implement new processes in our environment that might otherwise not have been considered viable projects.

As professionals, we must take personal responsibility learning the tools we are working with and how best to use them for the benefit of both ourselves, our employers, and our customers.  If the company we work for is willing to pay for that training, great, make the best of those training dollars.  If, however, they can't, or won't, then we have to step up to the plate and do what is necessary to help ourselves and our employer.  The better we are able to use the tools we have, the more valuable we are to the people we work for, and in today's economy, that is very important.

But beyond that, we also need to be willing to share our knowledge with others we work with as well.  Some may say that in today's economy that may not be the best thing to do.  I have to disagree with that as well.  Your willingness to share knowledge shows that you are a team player, working for the best interest of not only yourself, but the company as well.  This is, in fact, an area I need to improve myself.  As a lead for our database team, I should be doing more to improve the knowledge of all my team members.  Perhaps by developing some simple brown-bag lunch seminars or half day classes.

So, I guess the best way to end this, is with a question.  What are your thoughts regarding this subject?  You can leave your comments here, or drop a short comment and link back to your own blog and post an answer there,  You can be sure that I'll come and read it.