Thursday, August 26, 2010

Entering the Dark Side

If any of you follow me on SQLServerCentral.com, then you know I recently started a new job.  Well, after two weeks of in-processing training, I have finally started getting involved in actual work.  One of the things I am getting to learn is Agile techniques, in this case SCRUM.  Interesting process, and perhaps I’ll talk more about it in the future as I become more acquainted with the process.

More importantly, I am getting exposed to the “O” word, yes, Oracle, and will be working with both Oracle 8 and Oracle 10.  Wait, you are saying, but you are a SQL Server JOATMON (Jack of all Trades, Master of None)!  What are you doing working with Oracle?  I’m asking myself the same question at the moment.  Today I actually had the opportunity to see some of the code!  ANSI-89 style joins, cursors, and who knows what else.

I had thought I would be able to easily take my SQL Server development skills over to Oracle, and with some work learning Oracle syntax, be working fairly quickly.  I guess I’ll scale that back a bit, and take things a little bit slower.  I’ll need to see if I can get access to a sand box area and start writing exploratory code to learn how things work and try to get up to speed on the language.

There is a light at the end of the tunnel, however, as there is a process that needs work to improve it.  Currently, part of that process uses Access to bridge an Oracle 8 database and an Oracle 10 database.  There are quite a few manual processes involved, and maybe, just maybe, I can put something together using SQL Server (and maybe SSIS) to help bridge this gap and simplify a very manual process.  I may even find a way to use it in another project that is also in progress now as well, but I’ll have to watch things carefully before I go off without knowing as much as possible.

In the mean time, I am going to try and stay active on SSC, as well as attending SQLSat52 in Denver next month.  If you don’t see me as much as you used to, part of it is I’m not checking things as much during the day.  As I get used to my schedule (and remember, soccer season is here), I’ll try to hit the forums more in the evenings.  I WANT to keep my SQL Server skills sharp and usable should other opportunities arise!

 

In the mean time, pray for me as I journey on the Dark Side.

Thursday, April 1, 2010

Getting Scared

This is not a technical post, it’s personal, but I need to express my fears.  It isn’t about our economy, or that I may lose my job.  I’m confident our economy will rebound (eventually) despite the best efforts of Congress and the President.  My job is fairly secure, at least for another year.

 

No, today I found out that my daughter has received her orders regarding her first duty station, Fort Gordon, GA.  There is a very good possibility that see will be deploying overseas within weeks of her arriving there, most likely Afghanistan.

As veteran, I understand what this means, but as a parent, I have never been so scared in my life.  In many ways, I wish I could in her place, but I know that can not happen.  I pray her training serves her and her unit well if this is what truly transpires.  I Pray that God watches over her and her battle buddies daily and that she will come home safely.

I am sure that this is same feeling that every parent, spouse, girl or boy friend, and children go through every time a unit is deployed.  It’s so hard when mind understands but the heart doesn’t, or is it actually the other way around?

 

To all who serve, My God watch over you and your families.  May you all return home safe to your families.

God Speed and Safe Journey.

Saturday, August 22, 2009

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.

Thursday, June 18, 2009

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.

Thursday, June 4, 2009

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.

Wednesday, May 6, 2009

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.

Friday, May 1, 2009

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.