Performance issues with DENSE_RANK(). Is there a better alternative?

  • 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!

  • Would help if we also had your code to look at and see what you are doing.

  • 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!

  • Further on Lynn's reply, any Partition-Order-Covering (POC) indices on those tables?

    😎

  • 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.

  • 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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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