I came across a wierd thought about how to de-normalize data, or how to quickly group rows of data into a single delimited string.  I have come up with two scenarios:

First, if you just want to quickly concatenate everything, you can do the following:

DECLARE @Names VARCHAR ( 8000 )
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING ( 2 , @Names , 7998 )

Above, you end up with a variable with everything in it.

 

In the real world, the above example doesn't help most of us.  Using SQL Server 2005, you can use XML PATH to get some cool results.  Imagine you had a table called ORDERS that looks like the following:

OrderID                        ProductID
--------------              -----------------
1                                55
1                                66
1                                77
2                                88
2                                99

Now, imagine you want a result like this:

OrderID                        ProductID
--------------              -----------------
1                                55,66,77
2                                88,99

There is no quick and easy way to do this in SQL Server, until now:

Select  
    Main . OrderID
    ,  Left ( Main . ProductID , Len ( Main . ProductID )- 1 ) As ' ProductID'
From
    (
       Select distinct 
         order2 . OrderID
          ,   (
                Select  
                   order1 . ProductID + ',' AS [ text ()]
                From  
                  dbo . Orders  order1
                Where  
                  order1 . OrderID = order2 . OrderID
               ORDER BY 
                  order1 . OrderID
                For XML PATH ( '' )
            ) [ ProductID ]
      From dbo . Orders order2
    ) [ Main ]

I spent quite a bit of time researching and testing various solutions in trying to preserve focus on the dynamically created control causing a postback in .Net, but nothing seemed to work, most likely due to the very dynamic nature of the page I was creating.

After thinking about what I was really trying to accomplish, I came to the realization that I was not so much concerned about maintaining focus as I was concerned about maintaining the scrolling position of the page.  After about 30 seconds of searching, I discovered a handy one-liner that solved all my problems.

Adding MaintainScrollPositionOnPostBack = True to Page_Load takes care of all of this.  So, no matter how long your page is, adding this one line of code to your page will preserve scroll location on post backs.

I frequently find myself storing a business object in Session, for ease of use across various pages.  One issue that frequently occurs is that the session will timeout (if the user walks away from their computer for a while, etc...), and when work resumes on the page, the object no longer exists. 

To remedy this, I like to store the ID (primary key, etc...) of the object, as a string, in a COOKIE, and in the Page_PreLoad I check the state of the session object, and if it is non-existent, but I do have a cookie value, it is easy to quickly re-populate the session object using the ID.

This is a quick and light way to help fight the war against maintaining state in a state-less environment.  There are more advanced ways of maintaining state, like using SQL Server to maintain state, and it is up to the developer to choose the most effective method of state management for each project.

I was reminded today of something I solved a long time ago, something that TREMENDOUSLY impacts the performance of stored procedures in SQL Server.  This issue relates to 2000 & 2005; I have not tested this in 2008.

Basically, if your stored procedure is using date parameters AT ALL, the performance of the stored procedure will be HORRIBLE.  The whole purpose for using stored procedures is that they are much faster than using what is called a dynamic query, or a plain-text query.  The reason stored procedures are so much faster is that the database caches what is called an execution plan for the stored procedures, enabling the database to get at your data much faster.

For some reason, the use of date parameters prevents the caching of the execution plan, and even makes your stored procedure LESS efficient than a plain-text or dynamic query, because of the overhead of SQL trying to cache the execution plan.

So, the following stored procedure would NEVER get cached:

CREATE PROCEDURE dbo.storedProc1
   @dateParam1 datetime
   , @dateParam2 datetime
   , @otherParam nvarchar(50)
AS
   SELECT
      *
   FROM
      dbo.myTable (NOLOCK)
   WHERE
      dbo.myTable.dateField BETWEEN @dateParam1 AND @dateParam2
      AND dbo.myTable.otherField = @otherParam

HOWEVER, you can fake out SQL Server into caching the execution plan with the use of declared variables within your query.  Using the above example, the stored proc will now need to look like the following, which WILL allow for caching of the execution plan:

CREATE PROCEDURE dbo.storedProc1
   @dateParam1 datetime
   , @dateParam2 datetime
   , @otherParam nvarchar(50)
AS
   DECLARE @dateParam1_new datetime, @dateParam2_new datetime
   SET @dateParam1_new = @dateParam1
   SET @dateParam2_new = @dateParam2

   SELECT
      *
   FROM
      dbo.myTable (NOLOCK)
   WHERE
      dbo.myTable.dateField BETWEEN @dateParam1_new AND @dateParam2_new
      AND dbo.myTable.otherField = @otherParam

As you can see, I have only declared new variables and assigned them to the parameters of the proc.  This is all it takes to trick SQL Server into caching the execution plan.  Also, please notice that I only needed to do this on the DATE PARAMETERS, as they are the culprits.

I have made this a standard in all of my stored procedures that use date parameters, and the results are phenomenal.  For example, I had a complex report that was returning tens of thousands of rows, and it was taking more than 4 minutes to process the stored procedure, and by simply changing the above, the results are now under 45 seconds.

Keep in mind that the increased performance of the stored procedure is for the 2nd time through and thereon out, as the caching of the execution plan does not happen until the 1st time the stored procedure is ran.  Unfortunately, the execution plan is NOT cached when you CREATE or ALTER the procedure, but only after it is ran to return data.  If I am changing a heavy hitter, I typically run the procedure with parameters that will execute quickly, to force the caching so users are not impacted.

I hope this helps!  I am certain that many people face this problem, and don't know why.  I was helping a guy today who had indexed the hell out of a table, date columns included, and did not understand why the query would still run forever.  It won't matter what you try and do to the table or whatever.  STORED PROCEDURES WILL NOT CACHE THE EXECUTION PLAN WITH DATE PARAMETERS, UNLESS YOU FAKE IT OUT.

ENJOY!

Where would life be without this sweet nectar of the gods?  Ahh, joyous refreshment, you fulfill me.  Thank you for your existence.  Even though you look like urine, I still love you.

It may seem like common sense, but many times we forget the best way to manage tasks and solve problems.  Here is how I manage my long list of things to do (for work).  Each week I write down a list of everything I need to do.  This list is not necessarily everything I need to do for the week.  This list is everything I need to do, period.  Once I have written/typed out my list, I prioritize them.

Here comes the tricky, yet simple part that we often forget: work on the items in list of priority.  I find that I am often working on items in the 3rd spot, while items #1 & #2 are un-done.  Why, you ask?  Maybe #3 is easier.  Is this a good reason?  NO.

There is a reason we prioritize things!  So, once items have been prioritized, we need to stick to the list.  If possible, we should stick with a problem/task until it is completed, BEFORE starting a new task.  Many people waste a crazy amount of time juggling multiple tasks.  In fact, I would venture to say that employee #1, who works on one task at a time, is much more efficient than employee #2, who jumps around from task to task.  Why?  Think about re-learning.  How much time is spent re-learning a task/problem that has not been thought about for a week or more?  When I fall into this pit of re-learning, I often find that I spend about as much time re-learning the task/problem, as I originally spent trying to work on the task or solve the problem.

Is this list of tasks static?  By no means, no.  We can always re-prioritize.  But, I also do not believe that we should re-prioritize daily.  Many times we succumb to outside pressures and/or influences, false deadlines, etc....  Re-prioritization should only come as a result of an emergency.

So, I went on-site for an all-night install with one of my clients, to help them upgrade their servers.  My specific responsibility/role was to move web applications & databases to the new server.  The network 'engineer', was supposedly supposed to research and verify that the other core software they wanted to run on these new servers was Windows Server 2008 compatible.

At about 5 in the morning, we realized that this 'engineer' had NOT done due diligence, and made many false assumptions regarding software compatibility.  I mean, Windows wouldn't release a new OS without first verifying that ALL software in the known universe is compatible, right?

Operator Error Code: 1D10T

Many of us have faced the dilemna of auto-sizing an IFrame based on external web content.  Auto-sizing works just fine, if both the website and content are on the same domain.  But, if you are trying to auto-size content using cross-domain scripting, it is nearly impossible.

I too faced this problem, but using an example I found here: http://scvdotnet.org/index.php?url=archives/8-Autofitting-iFrame.html, I was able to make this work.  Please note, the example only works if you have access to both the website where the IFrame is hosted, and the website where the content is hosted.  I.e. this script will not work if you are trying to auto-size an IFrame for content you do not host or have access to.

I have been plagued for a couple of years trying to integrate accounting type applications with QuickBooks.  It is easier said than done.  The hopes that systems are developed with integration in mind is only valid in school.  In the real world, applications, even applications as large as QuickBooks, are rarely built with easy to use API's.

Luckily, I came across /n software's IBiz Integrator for QuickBooks.  This tool makes my life super easy, as I am able to quickly and efficiently interface with QuickBooks.  Best of all, their support is fast.  On more than one occasion, I have faced an in-surmountable integration obstacle, and /n software has been super-fast on the response, usually the same day.

I am an ASP.Net developer.  I am very biased in this regard, so don't get me started.  Anyway, I was faced with a challenge today, as a new customer is adamant that if I want to work with them, my work must be hosted on their servers.  After going the rounds, I realized how engrained this philosophy is in their company culture, so I decided I better either learn PHP, find an alternative, or forego working with this new customer.

And then I came across Mono.  No, this has nothing to do with monkeys or some weird teenage disease.  Mono is a great (and free!) framework that allows ASP.Net (and more) to run in the Linux environment.  You can learn more about Mono, here: http://www.mono-project.com.

I love to use GUID's in my applications.  Today I was wondering just how unique a GUID is, and if I am safe to use them in my application.  Turns out that the use of GUID's does NOT guarantee a unique string every time, but the likelihood of a GUID not being unique is 1:340,282,000,000,000,000,000,000,000,000,000,000,000.  Yes, that is 340,282 with 33 zeros after it.

So, I would guess it is safe to use GUID's in the future.  ;-)