April 5, 2016 at 3:30 pm
Hi,
I'll start with the quick mock-up of the dataset I'm working with and an accompanying query that queries the data:
CREATE TABLE #Equipment
(
EquipmentPk INT,
EquipmentNumber VARCHAR(20),
EquipmentRate MONEY
)
CREATE TABLE #Contract
(
ContractPk INT,
ContractNumber VARCHAR(20),
UseContractRate BIT,
ContractRate MONEY
)
CREATE TABLE #ContractEquipment
(
ContractEquipmentPk INT,
ContractFk INT,
EquipmentFk INT
)
CREATE TABLE #MeterGroup
(
MeterGroupPk INT
)
CREATE TABLE #MeterGroupEquipment
(
MeterGroupEquipmentPK INT,
MeterGroupFK INT,
EquipmentFK INT
)
INSERT INTO #Equipment (EquipmentPk, EquipmentNumber, EquipmentRate) VALUES (100, 'Equipment 1', 5.5)
INSERT INTO #Equipment (EquipmentPk, EquipmentNumber, EquipmentRate) VALUES (101, 'Equipment 2', 13.35)
INSERT INTO #Equipment (EquipmentPk, EquipmentNumber, EquipmentRate) VALUES (102, 'Equipment 3', 2.05)
INSERT INTO #Equipment (EquipmentPk, EquipmentNumber, EquipmentRate) VALUES (103, 'Equipment 4', 1.15)
INSERT INTO #Equipment (EquipmentPk, EquipmentNumber, EquipmentRate) VALUES (104, 'Equipment 5', 11.10)
INSERT INTO #Equipment (EquipmentPk, EquipmentNumber, EquipmentRate) VALUES (105, 'Equipment 6', 18.40)
INSERT INTO #Contract (ContractPk, ContractNumber, UseContractrate, ContractRate) VALUES (25, 'Cont. ABC', 0, 11.95)
INSERT INTO #Contract (ContractPk, ContractNumber, UseContractrate, ContractRate) VALUES (26, 'Cont. DEF', 1, 8.81)
INSERT INTO #Contract (ContractPk, ContractNumber, UseContractrate, ContractRate) VALUES (27, 'Cont. GHI', 0, NULL)
INSERT INTO #ContractEquipment (ContractEquipmentPk, ContractFk, EquipmentFk) VALUES (1, 25, 100)
INSERT INTO #ContractEquipment (ContractEquipmentPk, ContractFk, EquipmentFk) VALUES (2, 26, 101)
INSERT INTO #ContractEquipment (ContractEquipmentPk, ContractFk, EquipmentFk) VALUES (3, 27, 102)
INSERT INTO #ContractEquipment (ContractEquipmentPk, ContractFk, EquipmentFk) VALUES (4, 27, 103)
INSERT INTO #ContractEquipment (ContractEquipmentPk, ContractFk, EquipmentFk) VALUES (5, 26, 104)
INSERT INTO #ContractEquipment (ContractEquipmentPk, ContractFk, EquipmentFk) VALUES (6, 25, 105)
INSERT INTO #MeterGroup (MeterGroupPk) VALUES (950)
INSERT INTO #MeterGroup (MeterGroupPk) VALUES (951)
INSERT INTO #MeterGroup (MeterGroupPk) VALUES (952)
INSERT INTO #MeterGroup (MeterGroupPk) VALUES (953)
INSERT INTO #MeterGroup (MeterGroupPk) VALUES (954)
INSERT INTO #MeterGroup (MeterGroupPk) VALUES (955)
INSERT INTO #MeterGroupEquipment (MeterGroupEquipmentPK, MeterGroupFK, EquipmentFK) VALUES (88, 950, 100)
INSERT INTO #MeterGroupEquipment (MeterGroupEquipmentPK, MeterGroupFK, EquipmentFK) VALUES (89, 951, 101)
INSERT INTO #MeterGroupEquipment (MeterGroupEquipmentPK, MeterGroupFK, EquipmentFK) VALUES (90, 952, 102)
INSERT INTO #MeterGroupEquipment (MeterGroupEquipmentPK, MeterGroupFK, EquipmentFK) VALUES (91, 953, 103)
INSERT INTO #MeterGroupEquipment (MeterGroupEquipmentPK, MeterGroupFK, EquipmentFK) VALUES (92, 953, 104)
INSERT INTO #MeterGroupEquipment (MeterGroupEquipmentPK, MeterGroupFK, EquipmentFK) VALUES (95, 950, 105)
SELECT
EquipmentNumber,
ContractPk,
UseContractRate,
ContractRate,
mg.MeterGroupPk,
MergeRecords = ''
FROM
#Equipment e
JOIN
#ContractEquipment ce ON ce.EquipmentFk = e.EquipmentPk
JOIN
#Contract c ON c.ContractPk = ce.ContractFk
JOIN
#MeterGroupEquipment mge ON mge.EquipmentFk = e.EquipmentPk
JOIN
#MeterGroup mg ON mg.MeterGroupPk = mge.MeterGroupFK
DROP TABLE #Contract
DROP TABLE #Equipment
DROP TABLE #ContractEquipment
DROP TABLE #MeterGroup
DROP TABLE #MeterGroupEquipment
The task I'm struggling with is to have an incrementing numerical value in the MergeRecords column. The number should be the same for multiple records that either (1) are associated with the same ContractPk and have a UseContractRate = 1, or (2) the UseContractRate = 0, but the equipment are associated with the same MeterGroupPk.
In the results of my SELECT query above, I would expect the following MergeRecords values:
SELECT EquipmentNumber = 'Equipment 1', ContractPk = 25, UseContractRate = 0, ContractRate = 11.95, MeterGroupPk = 950, MergeRecords = 1
UNION
SELECT EquipmentNumber = 'Equipment 2', ContractPk = 26, UseContractRate = 1, ContractRate = 8.81, MeterGroupPk = 951, MergeRecords = 2
UNION
SELECT EquipmentNumber = 'Equipment 3', ContractPk = 27, UseContractRate = 0, ContractRate = NULL, MeterGroupPk = 952, MergeRecords = 0
UNION
SELECT EquipmentNumber = 'Equipment 4', ContractPk = 27, UseContractRate = 0, ContractRate = NULL, MeterGroupPk = 953, MergeRecords = 0
UNION
SELECT EquipmentNumber = 'Equipment 5', ContractPk = 26, UseContractRate = 1, ContractRate = 8.81, MeterGroupPk = 953, MergeRecords = 2
UNION
SELECT EquipmentNumber = 'Equipment 6', ContractPk = 25, UseContractRate = 0, ContractRate = 11.95, MeterGroupPk = 950, MergeRecords = 1
I've tried using the DENSE_RANK() function in the SELECT. This gives me the results I want, but against my actual dataset (which is quite a bit larger than the sample above), it is extremely slow. This query runs from a view, and I have no other option than to use a view for the task at hand.
What would be the best solution from a performance standpoint?
Any help is greatly appreciated!
April 5, 2016 at 3:58 pm
Would help if we also had your code to look at and see what you are doing.
April 5, 2016 at 4:03 pm
Nice job providing sample DDL and data!
Having said that, it doesn't look like the sample data matches your expected results. In your expected results there is an 'Equipment 6' that does not exist in the sample data. 'Equipment 5' is also associated with 3 different MeterGroup values in the sample data.
If we could get the sample data matching the expected results, along with the query you tried as Lynn requested, we should be able to help a bit more.
Cheers!
April 5, 2016 at 4:05 pm
Further on Lynn's reply, any Partition-Order-Covering (POC) indices on those tables?
😎
April 6, 2016 at 7:13 am
Thanks for the responses!
Jacob, you're right, I missed something when pasting my sample data. I've corrected it in the original post.
Lynn, I may end up doing exactly that. I was hesitant because there's a lot of code, and for business security reasons, I'll have to change the names of all of the tables and fields in the query, but it may turn out that there are bigger performance issues that are the real culprit.
Eirikur, could you please demonstrate what you're referring to? My code is simply using DENSE_RANK() OVER(ORDER BY Field1, Field2) in the SELECT.
April 6, 2016 at 10:17 am
tarr94 (4/6/2016)
Thanks for the responses!Jacob, you're right, I missed something when pasting my sample data. I've corrected it in the original post.
Lynn, I may end up doing exactly that. I was hesitant because there's a lot of code, and for business security reasons, I'll have to change the names of all of the tables and fields in the query, but it may turn out that there are bigger performance issues that are the real culprit.
Eirikur, could you please demonstrate what you're referring to? My code is simply using DENSE_RANK() OVER(ORDER BY Field1, Field2) in the SELECT.
So you're not partitioning no "P", the order is by Field1, Field2... Your POC index would look like this:
CREATE INDEX [index name] ON [yourtable](Field1, Field2) INCLUDE([other columns returned by your query])
Note that ROW_NUMBER(), RANK() and DENSE_RANK() are nasty fast provided the correct index is in place.
-- Itzik Ben-Gan 2001
April 6, 2016 at 11:04 am
Thanks Aaron, I'll have to give that a try.
Incidentally, it appears our users who made this request have changed the requirement so that I will not need to group by an incrementing number. I'll go ahead and declare Aaron's response to be the correct answer.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply