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 Wednesday, June 12, 2013 1:58 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
Here is the "inner" part of a query in Access.

UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate = #4/29/2013#, [Vehicle Detail].ReturnSource = 'ReturnProcess'
WHERE ((([Vehicle Detail].SerialNumber) In (SELECT TOP 15 [SerialNumber] FROM [Vehicle Detail] WHERE ((([Vehicle Detail].ProgYear)='12') AND (([Vehicle Detail].LeaseCycle)='1') And (([Vehicle Detail].ModelName)='Focus') AND (([Vehicle Detail].RiskNonRiskFlag)='R' AND (([Vehicle Detail].region)='SouthEast') And ReturnDate Is Null))
ORDER BY [Vehicle Detail].DeliveryDate,[Vehicle Detail].SerialNumber)));

All of the values after the "equals" signs are populated using variables. The outer part is a looping VBA program that takes grouping variables (example: ModelName and ProgranYear) to reference from the query above. Even the value for the "top" clause is a variable.

This VBA process takes 3 hours in Access. I have the process in SQL using dynamic SQL and a while loop to fill variables and it shaves an hour off. My question is this: Could this same type of functionality be written using the "merge" statement with an update? My goal is to run the query in an hour or less. Would merge save me time?
Post #1462811
Posted Wednesday, June 12, 2013 2:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 13,067, Visits: 11,904
Hard to give anything resembling specifics with not much detail. I think it would be far better if you can get rid of the looping and just do an update. If you can post some ddl and sample data along with the logic of what the update is supposed to do I bet we can make this run WAY faster than you ever though possible. I am talking a few seconds instead of a couple hours.

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

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 #1462821
Posted Monday, June 17, 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
Thanks for your encouragement! Sorry, I have been on vacation. I will include the two tables and some code now.

Vehicle Detail:
Vin SerialNumber ProgYear LeaseCycle ModelName RiskNonriskFlag Region ReturnDate
ZL152983 1FADP3K22ZL152983 13 1 FOCUS R SOUTHEAST
ZL185223 1FADP3K20ZL185223 13 1 FOCUS R SOUTHEAST
ZL196536 1FADP3F27ZL196536 13 1 FOCUS R SOUTHEAST
ZL265273 1FADP3F27ZL265273 13 1 FOCUS R SOUTHEAST
ZL196580 1FADP3F2XZL196580 13 1 FOCUS R SOUTHEAST
ZL196590 1FADP3F22ZL196590 13 1 FOCUS R SOUTHEAST
ZL171198 1FADP3F29ZL171198 13 1 FOCUS R SOUTHEAST
ZL171258 1FADP3F21ZL171258 13 1 FOCUS R SOUTHEAST
ZL171268 1FADP3K27ZL171268 13 1 FOCUS R SOUTHEAST
ZL211529 1FADP3F2XZL211529 13 1 FOCUS R SOUTHEAST
ZL211532 1FADP3F2XZL211532 13 1 FOCUS R SOUTHEAST
ZL211617 1FADP3F27ZL211617 13 1 FOCUS R SOUTHEAST
Returns Pros:
Region ProgYear RiskNonRiskFlag MFG LeaseCycle ModelName ReturnDate UnitCount
Southeast 13 R FRD 1 FOCUS 1/4/2014 12

I agree with you about the looping, but have not been able to figure out a way to do it without looping. The problem is that the UnitCount column from the "Returns Pros" table becomes the top value in the query - hence the need for a "dynamic" (concatenated) SQL query.

The following looping code is what shaved an hour:

DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(MFG) FROM [Returns Pros])
CREATE TABLE #tmpReturns(ID INT IDENTITY (1,1) PRIMARY KEY, MFG VARCHAR(20), ModelName VARCHAR(20), ProgYear VARCHAR(12),ReturnDate DATETIME,RiskNonRiskFlag VARCHAR(12), LeaseCycle VARCHAR(12), Region VARCHAR(50), UnitCount INT)
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON #tmpReturns(ID);
INSERT INTO #tmpReturns SELECT MFG, ModelName, ProgYear, ReturnDate, RiskNonRiskFlag, LeaseCycle, Region, UnitCount FROM [Returns Pros]

-- Declare an iterator

DECLARE @I INT

-- Initialize the iterator

SET @I = 1
SET NOCOUNT ON

-- Loop through the rows of a table @myTable

WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @mfg VARCHAR(50), @ModelName VARCHAR(50), @ProgYear VARCHAR(50)
DECLARE @ReturnDate DateTime, @RiskFlag VarChar(25), @LeaseCycle VarChar(2)
DECLARE @Region VARCHAR(50), @UnitCount VarChar(20), @SQL VarChar(450)
-- Get the data from table and set to variables
SELECT @mfg=mfg, @ModelName=ModelName, @ProgYear=ProgYear, @ReturnDate=ReturnDate, @RiskFlag=RiskNonRiskFlag,
@LeaseCycle=LeaseCycle, @Region=Region, @UnitCount=UnitCount
FROM #tmpReturns WHERE ID=@I
-- Display the looped data
Set @SQL ='UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate=''' + Cast(@ReturnDate as VarChar(20)) +
''' WHERE SerialNumber in (Select Top ' + @UnitCount + ' SerialNumber From [Vehicle Detail] WHERE MFG='''
+ @MFG + ''' And ModelName=''' + @ModelName + ''' And ProgYear=''' + @ProgYear + ''' And RiskNonRiskFlag='''
+ @RiskFlag + ''' And LeaseCycle=''' + @LeaseCycle + ''' And Region=''' + @Region + ''' And ReturnDate Is Null '
+ 'ORDER BY [Vehicle Detail].DeliveryDate, [Vehicle Detail].[SerialNumber])'


Exec (@sql)

-- Increment the iterator

SET @I = @I + 1

End
Post #1464293
Posted Monday, June 17, 2013 12:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 13,067, Visits: 11,904
The code is much as I expected. It is better if you can post the ddl for your tables and the data as inserts. That way we are not left guessing on datatypes and it makes it a lot easier to work with. It would also help if you could post at least a few rows from the table that you are currently looping. I am 99.9% certain we can do this with no looping 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 #1464304
Posted Monday, June 17, 2013 12: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
Sorry, I totally missed the best practices the first time around. I will work on that this afternoon. It's exciting to think of accomplishing my goal without loops. That should be MUCH faster. I take it that "merge" is not the best way to go. I'll test my table creations and inserts before posting. Thanks.
Post #1464327
Posted Monday, June 17, 2013 12:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 13,067, Visits: 11,904
realvilla (6/17/2013)
Sorry, I totally missed the best practices the first time around. I will work on that this afternoon. It's exciting to think of accomplishing my goal without loops. That should be MUCH faster. I take it that "merge" is not the best way to go. I'll test my table creations and inserts before posting. Thanks.


No problem. It is hard to know what to post and how to make it easy until you have done it a couple times.

I don't think MERGE is what you want here. From BOL:


MERGE - Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.


You are always doing an update and are not concerned about the results of a join to make a decision.


_______________________________________________________________

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 #1464329
Posted Monday, June 17, 2013 3:12 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
Here you go. Now you can test your query (I hope) The VINs are not real for obvioius reasons.

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

--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT (Returns Pros) MMDDYYYY ???


--===== Insert the test data into the test table
INSERT INTO [Vehicle Detail]
(VIN, SerialNumber, ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG, ReturnDate)
Select 'ZL152983', '1FADP3K22ZL152983', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL185223', '1FADP3K20ZL185223', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL196536', '1FADP3F27ZL196536', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL265273', '1FADP3F27ZL265273', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL196580', '1FADP3F2XZL196580', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL196590', '1FADP3F22ZL196590', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL171198', '1FADP3F29ZL171198', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL171258', '1FADP3F21ZL171258', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL171268', '1FADP3K27ZL171268', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL211529', '1FADP3F2XZL211529', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL211532', '1FADP3F2XZL211532', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'ZL211617', '1FADP3F27ZL211617', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL
Select 'C5216918', '1ZVBP8DM2C5216918', '11', '2', 'MUSTANG CONV', 'R', 'ATLANTIC', FRD', NULL UNION ALL
Select 'C5216923', '1ZVBP8DM2C5216923', '11', '2', 'MUSTANG CONV', 'R', 'ATLANTIC', FRD', NULL UNION ALL
Select 'D1220647', '2G1WC6E30D1220647', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', NULL UNION ALL
Select 'D1221984', '2G1WC6E31D1221984', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', NULL UNION ALL
Select 'D1259402', '2G1WC6E30D1259402', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', 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, NULL UNION ALL
Select 'Central' '13', 'N', 'GMC', '1', 'IMPALA', '6/3/2013', 3, NULL UNION ALL
Select 'Southeast', '13', 'R', 'FRD', '1', 'FOCUS', '1/4/2014', 12
Post #1464397
Posted Tuesday, June 18, 2013 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 13,067, Visits: 11,904
The ddl is pretty rough but I can make it work. You should always specify a size for varchar columns. Also, it seems like you could be using ints for some of these columns (ProgYear, LeaseCycle) and I think UnitCount is a count of items so it should probably be an int instead of a float.

Regardless I think this is close enough. What I don't quite understand is what the business logic here? What are we trying to accomplish?


_______________________________________________________________

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 #1464664
Posted Tuesday, June 18, 2013 8:32 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 sorry about the "roughness" but chalk it off to my first experience on this site. I was going to test the inserts but ran out of time here at work and wanted to get it in. The nulls for the [Returns Pros] table shouldn't have been there.

Int would be fine for UnitCount since the numbers will never exceed 1,000. As for the business logic, every month we are sent "deletes" (returns) that have to be updated in the [Vehicle Detail] (VD) table. There is a specified number of vehicle returns for each grouping (example: FOCUS, Program Year 12, Region Central and so forth.) The return date is the critical component. There are a set number of return dates that must be filled in for each grouping using the top clause.

If you will notice, there are the same number of records in the VD table as the specified return units (according to group) in Returns Pros. That isn't always the case in the real world, but there will never be more than the records for that group.

I'm afraid the Program Year and the Lease Cycle will have to be text. I didn't set that up, somebody else did, but for the joins to work, (I assume that's what you will use) they must be text in both tables. I know you are probably going for speed, but hopefully it won't make that much difference. You could probably accomplish this without many variables using joins.

I just noticed that we need to add DeliveryDate to the Vehicle Detail table for sorting. All of the Varchars can be 50 characters for simplicity sake except for ProgYear and LeaseCycle which can both be 2.
Post #1464693
Posted Tuesday, June 18, 2013 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 13,067, Visits: 11,904
OK there are a few thing that we still missing but I pieced together. First thing is in your original logic you updated the returns in order of delivery date but this was missing in your ddl. No biggie.

Also, since dealing with table names with spaces is such a pain I removed them on my side.

alter table VehicleDetail
add DeliveryDate datetime

--now we need to have some sort of delivery date. I just used some existing data to generate some randomish dates.
update VehicleDetail
set DeliveryDate = dateadd(dd, datediff(dd, 0, DATEADD(day, -1 * cast(right(VIN,2) as int), GETDATE())), 0)
from VehicleDetail

OK so now we have a table that should be pretty close to your actual table. I did make a few adjustments to the returns to make sure this works when the return amount is less than the amount delivered.

update ReturnsPros set UnitCount = 1 where Region = 'Atlantic'
update ReturnsPros set UnitCount = 2 where Region = 'Central'
update ReturnsPros set UnitCount = 9 where Region = 'Southeast'

Alright so now we have a setup that is somewhat realistic...but we still need to solve the actual issue. Instead of a cursor or a while loop I am using the ROW_NUMBER() windowed function to add row numbers. This let us know which rows to update based on the values in the return table. The remaining comments are in the code.

--first we need to get the RowNumber
with Vehicles as
(
select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG order by DeliveryDate) 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 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
)

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

That should produce the results as I understand them from your posts.


_______________________________________________________________

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 #1464733
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse