June 18, 2013 at 1:39 pm
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.
June 18, 2013 at 1:44 pm
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/
June 18, 2013 at 2:05 pm
I will test it as you specified and get back with you. I will also try adding the "ReturnDate is null" and the SerialNumber to the Order By.
As for the recursion:
http://msdn.microsoft.com/en-us/library/ms175972.aspx
(Notice that it does say 2012 version)
June 18, 2013 at 2:08 pm
realvilla (6/18/2013)
As for the recursion:http://msdn.microsoft.com/en-us/library/ms175972.aspx
(Notice that it does say 2012 version)
Here is the quote from the first paragraph there.
A common table expression can include references to itself. This is referred to as a recursive common table expression.
What I posted is NOT a recursive cte. It does not ever refer to itself (or a self join). A rCTE is similar but a very different animal entirely. 🙂
_______________________________________________________________
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/
June 18, 2013 at 2:08 pm
Oh and by the way, this line has to be what replaced the top clause correct?
where v.RowNum <= r.UnitCount
June 18, 2013 at 2:17 pm
realvilla (6/18/2013)
Oh and by the way, this line has to be what replaced the top clause correct?
where v.RowNum <= r.UnitCount
Yes that is correct. Instead of getting the top we just get the ones we want by leveraging RowNum.
_______________________________________________________________
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/
June 19, 2013 at 9:18 am
OK, I am set up now with rights on the database which is 2008. I run the following code and get 0 records. Notice the "select COUNT(*) from VehiclesToUpdate" line. I noticed that the table (I went ahead and took out the spaces in the file name thinking it would correct what I am about to tell you) has a RED UNDERLINE under the table name whether or not I place a "dbo" in front of it. The fields names have red underlines as well. This is probably something very simple, but I am not as familiar with 2008. I placed underlines under the new stuff I added. Anyway, here is the code:
with Vehicles as
(
select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG order by DeliveryDate, SerialNumber) as RowNum
from VehicleDetail
)
, VehiclesToUpdate as
(
--Now we only want to see the rows we want to update
select v.VIN,
v.SerialNumber,
r.ReturnDate,
v.ProgYear,
v.LeaseCycle,
v.ModelName,
v.RiskNonRiskFlag,
v.Region,
v.MFG,
v.ReturnDate as NewReturnDate --needs to have a unique name so we can update it.
from dbo.ReturnsPros r
join Vehicles v on r.ProgYear = v.ProgYear
and r.LeaseCycle = v.LeaseCycle
and r.ModelName = v.ModelName
and r.RiskNonRiskFlag = v.RiskNonRiskFlag
and r.Region = v.Region
and r.MFG = v.MFG
where v.RowNum <= r.UnitCount and v.ReturnDate is null)
select COUNT(*) from VehiclesToUpdate
--Update VehiclesToUpdate
--set NewReturnDate = ReturnDate
--since NewReturnDate is actually ReturnDate from VehicleDetail
--we know which column we are updating
June 19, 2013 at 9:27 am
realvilla (6/19/2013)
OK, I am set up now with rights on the database which is 2008. I run the following code and get 0 records. Notice the "select COUNT(*) from VehiclesToUpdate" line. I noticed that the table (I went ahead and took out the spaces in the file name thinking it would correct what I am about to tell you) has a RED UNDERLINE under the table name whether or not I place a "dbo" in front of it. The fields names have red underlines as well. This is probably something very simple, but I am not as familiar with 2008. I placed underlines under the new stuff I added. Anyway, here is the code:
The red underlines are most likely just because intellisense is not up to date. When you rename, create or drop objects intellisense is not updated. Just hit ctrl + shift + r and it will update.
As far as no rows being returned, it is impossible for me to determine because I don't have the same data you do. Change your join to a left join. If that now returns rows but the other side is NULL then there is something in your join not right. About the best I can do remotely is get you close. You will have to do the last little bit of tweaking and debugging from where you have access.
_______________________________________________________________
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/
June 19, 2013 at 9:43 am
I so appreciate your help and you have already gone way above and beyond the call of duty. I'll try the left joins and guy more experienced in SQL should be here after lunch. I have seen the "promised land" so I am not giving up.
To go from 3 hours to a few seconds was so amazing that the co-worker who will be using this still may not be totally convinced. It will be difficult to test each piece of your code but I still think we're close.
At this point I will just notify you of the progress since your work is done. Like you said, you got me close and the rest is up to us. Thanks again.
June 19, 2013 at 9:48 am
realvilla (6/19/2013)
I so appreciate your help and you have already gone way above and beyond the call of duty. I'll try the left joins and guy more experienced in SQL should be here after lunch. I have seen the "promised land" so I am not giving up.To go from 3 hours to a few seconds was so amazing that the co-worker who will be using this still may not be totally convinced. It will be difficult to test each piece of your code but I still think we're close.
At this point I will just notify you of the progress since your work is done. Like you said, you got me close and the rest is up to us. Thanks again.
You are quite welcome. It is amazing what a difference it makes when you don't loop and just deal your data as a set. I hope you figure it out and please post back and let me know what happens. It is nice to hear when things like this work out. 😀
_______________________________________________________________
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/
June 20, 2013 at 9:16 am
I am SO close. I have a question for you. Before I ask it, I will show you the SQL Code and it is now.
with Vehicles as
(
select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG order by DeliveryDate, SerialNumber) as RowNum
from dbo.VehicleDetail
)
, VehiclesToUpdate as
(
--Now we only want to see the rows we want to update
select v.VIN,
v.SerialNumber,
r.ReturnDate,
v.ProgYear,
v.LeaseCycle,
v.ModelName,
v.RiskNonRiskFlag,
v.Region,
v.MFG,
v.ReturnDate as NewReturnDate --needs to have a unique name so we can update it.
from dbo.ReturnsPros r
join Vehicles v on r.ProgYear = v.ProgYear
and r.LeaseCycle = v.LeaseCycle
and r.ModelName = v.ModelName
and r.RiskNonRiskFlag = v.RiskNonRiskFlag
and r.Region = v.Region
and r.MFG = v.MFG
where v.RowNum <= r.UnitCount And v.ReturnDate Is Null
)
Update VehiclesToUpdate
Set NewReturnDate = ReturnDate
--since NewReturnDate is actually ReturnDate from VehicleDetail
--we know which column we are updating
--did it actually work?
select * from VehicleDetail where ReturnDate is not null
The question is, where is the best place to put the "ReturnDate is null" statement? Currently, this query generates 73,061 records when it should generate 205,165. Would it be better to put the ReturnDate where clause in the first select?
June 20, 2013 at 9:23 am
That would probably help. It is pretty tough for me to say for certainty though.
_______________________________________________________________
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/
June 20, 2013 at 2:58 pm
I'm trying not to bother you any more but you did ask me to give you a progress update. I am stumped. I have working on this the better part of the day and I haven't made much progress. I placed the following code at the end of your code:
SELECT VehicleDetail.SerialNumber, VehicleDetail.ProgYear, VehicleDetail.ModelName, VehicleDetail.Region, VehicleDetail.MFG
FROM VehicleDetail LEFT JOIN VehiclesToUpdate ON (VehicleDetail.SerialNumber = VehiclesToUpdate.SerialNumber) AND (VehicleDetail.Vin = VehiclesToUpdate.Vin)
Where VehicleDetail.SerialNumber is null
This returned no records which means that all of the cars are accounted for between VehiclesToUpdate and VehicleDetail. However, the number of records to update is still far short. I told you that I'm not giving up but I'm not sure what to try next.
June 20, 2013 at 3:02 pm
realvilla (6/20/2013)
I'm trying not to bother you any more but you did ask me to give you a progress update. I am stumped. I have working on this the better part of the day and I haven't made much progress. I placed the following code at the end of your code:SELECT VehicleDetail.SerialNumber, VehicleDetail.ProgYear, VehicleDetail.ModelName, VehicleDetail.Region, VehicleDetail.MFG
FROM VehicleDetail LEFT JOIN VehiclesToUpdate ON (VehicleDetail.SerialNumber = VehiclesToUpdate.SerialNumber) AND (VehicleDetail.Vin = VehiclesToUpdate.Vin)
Where VehicleDetail.SerialNumber is null
This returned no records which means that all of the cars are accounted for between VehiclesToUpdate and VehicleDetail. However, the number of records to update is still far short. I told you that I'm not giving up but I'm not sure what to try next.
No problem.
The above query returning now rows means there are no rows in VehicleDetail that don't have a serial number.
Does this also return 0?
SELECT count(*)
FROM VehicleDetail
Where VehicleDetail.SerialNumber is null
How many vehicles do you think should be updated? How many rows are in VehiclesToUpdate?
_______________________________________________________________
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/
June 20, 2013 at 10:41 pm
I'm well versed in Access but know very little T-SQL. What does "ROW_NUMBER() over(partition by" do?
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply