I am not getting it.  Earlier this spring I discussed in these forums the utilization of UDFs in filling a BO.  The conclusion was to not use them since they have a great deal of overhead involved.  So, instead, I went with some raw SQL to achieve the same results.  I was able to get one of my more complex queries down to about 3 seconds.  That was wonderful.  Now, I am simply taking the same script that runs in the SQL Query Analyzer at 3 seconds and placing it into a stored procedure with three parameters.  These parameters are declared at the beginning of the script in the query analyzer, too.  I run the stored procedure and it takes many minutes to run (last run: 10 min 04 sec).  I really don't get this.  It is the exact same script.  Is there really that much of a difference in quality between raw SQL script and the same SQL script placed into a stored procedure?  What am I doing wrong?  I have attached the script that I am running in the Query Analyzer and the stored procedure.  I know that these are not pretty...I am still working through the details of this query.  Any help, tips, criticism is welcome!

Thanks,
Bill

All of Peter's comments were excellent...I thought I would toss in a few more things as well:

1. You can tell a query which index to use by using the WITH and INDEX commands.  Sometimes SQL needs a little help...we ran into this the other day.  It would look like this:
   
   
   SELECT * FROM Customers WITH(INDEX(IX_MyIndex))
   
   
2. The framework is not going to change anything in regards to the execution speed and performance....so if you get it down to 1 second in SQL Server management Studio executing the sproc...this will not change on the framework side unless you have some type of connection issue or something else in the mix.
   
3. DateTime columns are aweful about slowing down queries when in the WHERE...one way to get around this is to store dates as a BigInt data type and then store then DateTimes using Ticks.  We then create a Custom property on the BO that wraps this as a DateTime so that while using the BOs inside of your app you interact with a DateTime...but it is stored as Ticks on the SQL Server side...and this will drastically improve performance....by like a ton when you are testing with <, >, or betweens.