6
\$\begingroup\$

The goal of this query is to get a list of payments where all the corresponding payment lines and transactions have the status CLOSED. These are one to many relationships. The database is Informix.

I wonder if there is a more efficient way to write this? In its current state it has a lot of repetition which, maybe because my Java background, seems quite ugly to me. Eventually this query will need to be generated through Hibernate, so I'm mostly concerned with its performance.

SELECT p.* FROM payment p
JOIN transaction t
ON t.payment_bk = p.bk
JOIN payment_line pl
on  p.bk = pl.payment_bk
WHERE p.valid AND p.valid_to is null
AND p.date>{ts '2015-05-21 00:00:00'} 
AND p.date<{ts '2015-05-22 00:00:00'} 
AND t.valid and t.valid_to is null
AND t.transaction_status = 'CLOSED'
AND NOT EXISTS (SELECT 1 FROM transaction t WHERE t.payment_bk = p.bk 
AND t.transaction_status <> 'CLOSED'
AND t.valid and t.valid_to is null)
AND pl.valid and pl.valid_to is null
AND pl.payment_line_status = 'CLOSED'
AND NOT EXISTS (SELECT 1 FROM payment_line WHERE pl.payment_bk = p.bk 
AND pl.payment_line_status <> 'CLOSED'
AND pl.valid and pl.valid_to is null)

valid is a boolean and the dates/timestamps will sometimes be just a day apart, sometimes a couple of months.

\$\endgroup\$
  • \$\begingroup\$ I wanted to tag this as Informix too, but apparently that tag doesn't exist yet on this site. \$\endgroup\$ – LisaMM May 21 '15 at 8:34
  • 1
    \$\begingroup\$ Now the tag exists. Feel invited to give the tag-wiki an edit to explain what the tag is about ;) \$\endgroup\$ – Vogel612 May 21 '15 at 8:58
4
\$\begingroup\$

White space

Since you come from a Java background, you will understand the importance of using white space to make the code easier to read. Also it is best to stay consistent with capitalization of keywords. I would go for something like this:

SELECT p.* 
FROM payment p
  JOIN transaction t
    ON t.payment_bk = p.bk
  JOIN payment_line pl
    ON  p.bk = pl.payment_bk
WHERE p.valid IS NULL
  AND p.valid_to IS NULL
  AND p.date>{ts '2015-05-21 00:00:00'} 
  AND p.date<{ts '2015-05-22 00:00:00'} 
  AND t.valid IS NULL
  AND t.valid_to IS NULL
  AND t.transaction_status = 'CLOSED'
  AND NOT EXISTS (
    SELECT 1 
    FROM transaction t 
    WHERE t.payment_bk = p.bk 
      AND t.transaction_status <> 'CLOSED'
      AND t.valid and t.valid_to IS NULL
  )
  AND pl.valid IS NULL
  AND pl.valid_to IS NULL
  AND pl.payment_line_status = 'CLOSED'
  AND NOT EXISTS (
    SELECT 1 
    FROM payment_line 
    WHERE pl.payment_bk = p.bk 
      AND pl.payment_line_status <> 'CLOSED'
      AND pl.valid IS NULL
      AND pl.valid_to IS NULL
  );

So, that already reads a lot better!


Table aliases are handy, but in your case I think your use of single-letter aliases makes the query harder to read. For instance, instead of payment p JOIN transaction t I would suggest something like payment AS pay INNER JOIN transaction AS tran. And payment_line pl could be payment_line AS line or something like that.


Normally I would recommend using Common Table Expressions instead of sub-queries, but since this will eventually be done by Hibernate it would not matter too much right now.


For clarity's sake, I would avoid to chain WHERE conditions like this:

WHERE p.valid AND p.valid_to IS NULL

I missed those at first, so that gives you an idea why.

\$\endgroup\$
  • \$\begingroup\$ I think he's looking for anything that is not on that particular date. Your date query doesn't do the same thing. \$\endgroup\$ – RubberDuck May 21 '15 at 15:41
  • 1
    \$\begingroup\$ Note that Informix does not support CTEs (WITH clauses in front of the main SELECT). \$\endgroup\$ – Jonathan Leffler May 21 '15 at 18:19
  • \$\begingroup\$ Note that the date/time range is a day apart; you'd have to work a bit harder to use the != (<>) condition: AND EXTEND(p.date, YEAR TO DAY) != DATETIME(2015-05-21) YEAR TO DAY would be one native notation; another might be AND DATE(p.date) != MDY(5, 21, 2015). \$\endgroup\$ – Jonathan Leffler May 21 '15 at 18:22
  • \$\begingroup\$ @JonathanLeffler I corrected that, I had not noticed it was a day apart \$\endgroup\$ – Phrancis May 21 '15 at 19:39
  • \$\begingroup\$ valid is a boolean and the dates will sometimes be just a day apart, sometimes a couple of months... Do you think I can do anything to improve the performance of the query? Maybe by changing the order of the WHERE clauses? \$\endgroup\$ – LisaMM May 22 '15 at 7:55

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.