2
\$\begingroup\$

Is there any way to simplify this query? It's being run against a Progress Database via an ODBC connection.

The CustomerSalesperson table has a CustomerID, a SalespersonID, and a CreateDate. Each CustomerID could have many records, each with a different SalespersonID. I'd like to get a list of the SalespersonIDs with the most recent CreateDate for each CustomerID. This is what I'm doing currently:

SELECT DISTINCT cs.CustomerID, cs.SalespersonID
FROM PUB.CustomerSalesperson cs
INNER JOIN
  (SELECT CustomerID, MAX(CreateDate) AS MaxDate
  FROM PUB.CustomerSalesperson
  GROUP BY CustomerID) csmax
ON cs.CustomerID = csmax.CustomerID
WHERE csmax.MaxDate = cs.CreateDate

It take a little longer to return than I would expect. When I remove the DISTINCT, it goes a lot faster, but it returns many duplicates as a result of other fields in the table not which aren't relevant to the query.

\$\endgroup\$
2
\$\begingroup\$

This is a messy query, I personally don't like nesting a select statement in my joins, I would rather use a Common Table Expression like this

WITH csmax AS (
    SELECT CustomerID, MAX(CreateDate) AS MaxDate
        FROM PUB.CustomerSalesperson
        GROUP BY CustomerID
)
SELECT DISTINCT cs.CustomerID, cs.SalespersonID
    FROM PUB.CustomerSalesperson cs
    INNER JOIN csmax ON cs.CustomerID = csmax.CustomerID
    WHERE csmax.MaxDate = cs.CreateDate

This should help the DB run the query more efficiently as well. using the with statement should allow the SQL Engine to create a table and then compare the tables the way they were meant to be compared. The CTE actually prepares the table before the outer query is run, so we aren't doing this on the fly.


Sounds like you can't run this through the ODBC connection, I looked at the documentation that you linked me to and found a CREATE VIEW command that would probably have similar efficiency to using a CTE.

CREATE VIEW csmax (CustomerID, CreateDate) AS 
    SELECT CustomerID, MAX(CreateDate) AS MaxDate
        FROM PUB.CustomerSalesperson
        GROUP BY CustomerID ;

SELECT DISTINCT cs.CustomerID, cs.SalespersonID
    FROM PUB.CustomerSalesperson cs
    INNER JOIN csmax ON cs.CustomerID = csmax.CustomerID
    WHERE csmax.MaxDate = cs.CreateDate

DROP VIEW csmax

This code is more readable than the code that you wrote, it is easier to follow.

First thing you want to do is get a list of all the customers and the most recent date they did whatever.

Then, you want to know which Sales Person is associated with the "whatever" that customer did at their most recent visit.

\$\endgroup\$
9
  • \$\begingroup\$ Thanks for this, but I don't think my ODBC connection supports CTEs. Here are the docs for OpenEdge 10.2B: documentation.progress.com/output/OpenEdge102b/pdfs/dmsrf/… I get a syntax error when trying to run this. :( \$\endgroup\$ May 17 '16 at 13:35
  • \$\begingroup\$ you could try creating a view instead, that might work better for you \$\endgroup\$
    – Malachi
    May 17 '16 at 13:52
  • \$\begingroup\$ Thanks. This looks like it may work, though I currently don't have the required permissions to create views. \$\endgroup\$ May 17 '16 at 14:14
  • \$\begingroup\$ @WhiteHotLoveTiger , wouldn't it be MaxDate instead of CreateDate in the column declaration? \$\endgroup\$
    – Malachi
    May 17 '16 at 16:56
  • \$\begingroup\$ A Derived Table is exactly the same as a Common Table Expression, it's just a syntax variation. Of course a (weak) optimizer might not treat them (always) the same, but DTs are much older than CTEs, so if there's a difference I would expect DTs to perform better. \$\endgroup\$
    – dnoeth
    May 18 '16 at 19:29
0
\$\begingroup\$

You might try standard rewrites, but I don't have any expertise in Progress (didn't know it's still in use :):

Q1:
SELECT DISTINCT cs.CustomerID, cs.SalespersonID
FROM PUB.CustomerSalesperson cs
WHERE CreateDate =
 ( SELECT MAX(CreateDate)
   FROM PUB.CustomerSalesperson cs2
   WHERE cs.CustomerID = cs2.CustomerID
 )

Q2:
SELECT DISTINCT cs.CustomerID, cs.SalespersonID
FROM PUB.CustomerSalesperson cs
WHERE NOT EXISTS
 ( SELECT *
   FROM PUB.CustomerSalesperson cs2
   WHERE cs.CustomerID = cs2.CustomerID
     AND cs2.CreateDate > c2.CreateDate 
 )

A decent optimizer should create the same plan for your query and Q1, Q2 might be different.

\$\endgroup\$
2
  • \$\begingroup\$ if the optimizer will create the same query plan as the OP then it wouldn't help very much, because the query that OP is using is "slow" and the second query you post would probably be slower, wouldn't it? \$\endgroup\$
    – Malachi
    May 18 '16 at 19:52
  • \$\begingroup\$ @Malachi: Yep, but it's a very simple query, so this is the only chance. Progress doesn't support newer SQL features like CTE or ROW_NUMBER, maybe the optimizer is not decent :) \$\endgroup\$
    – dnoeth
    May 18 '16 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.