realvilla (6/18/2013)
I have some good news and some more good news and then some bad news. First, we found a 2008 database that we can use. Second, the query ran UNBELIEVABLY fast, a few seconds. The bad news is that it only updated about 30,000 records and it should have been over 200,000 records. If you sum the UnitCount column in Returns Pros, you will have the number of rows that should be updated in the VD table. It shouldn't make any difference whether it is a small or large set should it?My first thought was the joins, but then I thought about the fact that it should only update rows that are blank. I don't think that should be a big deal, but there are some return dates in the VD that we don't want to overwrite.
So we're probably just looking at some minor tweaks. I'm thinking about taking your select statements to see how many rows are affected unless you have a better idea.
That recursive table idea is very elegant. They never taught me that in SQL class - probably because it wasn't available then.
Should be easy enough to add an extra condition in the where clause to exclude where the return date is not null. The easiest way to test it out is to comment out the update statement and add in a select * from VehiclesToUpdate. That will show you all the rows that would be updated. I am sure you will have to do some tweaking and fine tuning to get this to work with your real data.
Not sure what you mean about recursion, there isn't any in there at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/