Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Access Query ported to SQL Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 9:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, Visits: 49
All I can say is "wow"! You did this without variables, dynamic SQL or a top clause. That is amazing to me. I messed up on the RiskNonRisk column which you probably discovered wouldn't match between tables. The delivery dates were no big deal for such a small set. You could have placed almost anything in there that was not ahead of the returns dates in the VD table. As the Russians say, "Thank you a thousand times!"

I went back and changed what I sent and will post it, just to be sure everything is what is should be. But the important thing is the benchmark. I will test your code and let you know how much time it saved. That should be the fun part. This will be on approximately 250,000 records. Anyway, the following is what I should have sent:

--===== Create the test table with
CREATE TABLE [Vehicle Detail]
(
VIN Varchar PRIMARY KEY CLUSTERED,
SerialNumber Varchar 50,
ProgYear Varchar 2,
LeaseCycle Varchar 2,
ModelName Varchar 50,
RiskNonRiskFlag Varchar 2,
Region Varchar 50,
MFG Varchar 50,
ReturnDate Datetime,
)
CREATE TABLE [Returns Pros]
(
Region Varchar 50,
ProgYear Varchar 2,
RiskNonRiskFlag Varchar 2,
MFG Varchar 50,
LeaseCycle Varchar 2,
ModelName Varchar 50,
ReturnDate Datetime,
UnitCount Int
)


--===== Insert the test data into the test table
INSERT INTO [Vehicle Detail]
(VIN, SerialNumber, ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG, DeliveryDate, ReturnDate)
Select 'ZL152983', '1FADP3K22ZL152983', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL185223', '1FADP3K20ZL185223', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL196536', '1FADP3F27ZL196536', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL265273', '1FADP3F27ZL265273', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL196580', '1FADP3F2XZL196580', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL196590', '1FADP3F22ZL196590', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL171198', '1FADP3F29ZL171198', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL171258', '1FADP3F21ZL171258', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL171268', '1FADP3K27ZL171268', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL211529', '1FADP3F2XZL211529', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL211532', '1FADP3F2XZL211532', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL211617', '1FADP3F27ZL211617', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'C5216918', '1ZVBP8DM2C5216918', '11', '2', 'MUSTANG CONV', 'R', 'ATLANTIC', FRD', '12/03/2011', NULL UNION ALL
Select 'C5216923', '1ZVBP8DM2C5216923', '11', '2', 'MUSTANG CONV', 'R', 'ATLANTIC', FRD', '12/03/2011', NULL UNION ALL
Select 'D1220647', '2G1WC6E30D1220647', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', '12/03/2011', NULL UNION ALL
Select 'D1221984', '2G1WC6E31D1221984', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', '12/03/2011', NULL UNION ALL
Select 'D1259402', '2G1WC6E30D1259402', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', '12/03/2011', NULL UNION

INSERT INTO [Returns Pros]
(Region, ProgYear, RiskNonRiskFlag, MFG, LeaseCycle, ModelName, ReturnDate, UnitCount)
Select 'Atlantic', '11', 'R', 'FRD', '2', 'MUSTANG CONV', '6/3/2013', 2, UNION ALL
Select 'Central' '13', 'N', 'GMC', '1', 'IMPALA', '6/3/2013', 3, UNION ALL
Select 'Southeast', '13', 'R', 'FRD', '1', 'FOCUS', '1/4/2014', 12
Post #1464748
Posted Tuesday, June 18, 2013 10:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,095, Visits: 11,929
realvilla (6/18/2013)
All I can say is "wow"! You did this without variables, dynamic SQL or a top clause. That is amazing to me.


Hopefully you can understand what I did. It is a mental shift to think about changing the columns instead of the rows. Once you make that mental shift you are thinking about your data as sets instead of values.


I messed up on the RiskNonRisk column which you probably discovered wouldn't match between tables. The delivery dates were no big deal for such a small set. You could have placed almost anything in there that was not ahead of the returns dates in the VD table. As the Russians say, "Thank you a thousand times!"


Yeah the RiskNonRisk was kind of driving me nuts for a bit until I realized that it was excluded because of the value difference. And you are welcome.


I went back and changed what I sent and will post it, just to be sure everything is what is should be. But the important thing is the benchmark. I will test your code and let you know how much time it saved.


I look forward to hearing the results of your testing.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464762
Posted Tuesday, June 18, 2013 11:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, Visits: 49
I'm on version 2000 which is probably why I generated these errors when I tried to run your code. The good news is that SQL admin people are trying to get me access to another database that is 2008. Actually, I am running Management Studio 2008.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'.
Msg 195, Level 15, State 10, Line 4
'ROW_NUMBER' is not a recognized function name.

The data set I sent still does not have "N" for non-risk in the RiskNonRiskFlag column but I think you have already corrected that. I think we may have to sort on serial number as well as DeliveryDate but we are so close.
Post #1464812
Posted Tuesday, June 18, 2013 11:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, Visits: 49
I meant to say non-risk on the IMPALAs. Sorry.
Post #1464815
Posted Tuesday, June 18, 2013 11:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,095, Visits: 11,929
realvilla (6/18/2013)
I'm on version 2000 which is probably why I generated these errors when I tried to run your code. The good news is that SQL admin people are trying to get me access to another database that is 2008. Actually, I am running Management Studio 2008.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'.
Msg 195, Level 15, State 10, Line 4
'ROW_NUMBER' is not a recognized function name.

The data set I sent still does not have "N" for non-risk in the RiskNonRiskFlag column but I think you have already corrected that. I think we may have to sort on serial number as well as DeliveryDate but we are so close.


Yeah if you are running against 2000 then you can't do this at all. I assumed you would not still be on 2000.

You can add as many additional columns to the order by as you need to get your final results.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464817
Posted Tuesday, June 18, 2013 1:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, Visits: 49
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.
Post #1464854
Posted Tuesday, June 18, 2013 1:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,095, Visits: 11,929
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464859
Posted Tuesday, June 18, 2013 2:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, Visits: 49
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)
Post #1464866
Posted Tuesday, June 18, 2013 2:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,095, Visits: 11,929
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464867
Posted Tuesday, June 18, 2013 2:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, Visits: 49


Oh and by the way, this line has to be what replaced the top clause correct?

where v.RowNum <= r.UnitCount
Post #1464868
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse