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

Thanks for the info...it always good to hear how other developers solve their isssues.  In our case, we have some extremely complex queries that take place between 8+ tables and get extremely nested while calculating something called "pending."  This basically determine how much a patient owes and an insurance owes....but it has to take into account all of their tran history, insurance plans (primary, secondary, tertiary, etc.), deductibles, write-offs, bad debt, and about 50 other things (not kidding on the 50 ).  We tried using dates, indexes, and even tried the a nmber of conversion routines...and once we turned this into ticks with and index versus dates with an index (that was the only change) the query went from 4 1/2 minutes for a single patient with 6000 trans (don't ask me why they have 6000 trans for one patient...we just crunch the numbers ) to 30 ms...so we started doing a little digging and learned that between and ORs are bad words with SQL Server and dates when dealing with any type of complex query.  This proved true again just the other day...I had a query running in 4 seconds (way too slow) once we started testing on a large database...changed the dates to ticks...1 ms...crazy.

One other thing on this too, we have to be able to have extremely fast queries run on MS Express with siingle core processors as we have a lot of users with 2 GB plus databases that will till use MS SQL Express on existing equipment iin the field...we call this zero impact.  It may not be but we have to get as close to that as possible for existing users.  Then are the much larger sites they will have a more complex server setup and a full version of SQL Server...so we have to work in a lot of different environments...so absolute optimization is the only way we can do this.