I am telling my team that queries like this are incorrectly formatted. Am I wrong?

Question

question

technette asked Jan 20, '21

I am telling my team that queries like this are incorrectly formatted. Am I wrong?

Hi! We are troubleshooting issues that are causing a high spike in lock requests on our database and I'm seeing queries with 'ands' where I believe there there should be inner joins. The ands I believe cause extra scans. We have developers putting ands before the where clause.

SELECT DISTINCT

F.UserRecordNumber

, F.PartNumber

, COL.PART_ID

, COL.LAST_SHIPPED_DATE

, F.SalesOrder

, COL.LINE_NO

, F.SalesOrderLine

FROM dbo.Findings F

LEFT JOIN ONTIC.dbo.CUST_ORDER_LINE COL ON

F.SalesOrder = COL.CUST_ORDER_ID

AND F.SalesOrderLine = COL.LINE_NO

WHERE F.UserRecordNumber = '57695'

AND COL.LAST_SHIPPED_DATE IS NOT NULL AND COL.LAST_SHIPPED_DATE <> ''

sql-queries poor execution query format

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette commented · Jan 20, 2021 at 04:42 PM

Thank you Anthony, it is the joins that cause the issue, Many of the queries also have and's instead of joins. When we are connecting tables that don't have primary and foreign keys related, the execution creates high cost index scans. We will review the execution plans to improve the queries. I hope we will be able to pinpoint the queries that are killing performance.

0 ·

anthony.green technette commented · Jan 21, 2021 at 06:35 AM

When you say you have queries which are “and” do you mean the follow this format?

FROM table1, table2, table3

WHERE table1.col1 = ‘abc’ AND table2.col1 = ‘def’ AND table3.col1 = ‘ghi’

if so that is really old syntax and SHOULD NOT be used, you should find those queries and change those to the new ANSII standard joins.

0 ·

Answer 1 · 2021-01-20T07:55:58Z

anthony.green answered Jan 20, '21

Doesn't seem to be anything wrong in that to the eye, it's syntax correct.

Now if that's not the correct join method then you may have some merit on changing it.

SELECT
  DISTINCT F.UserRecordNumber,
  F.PartNumber,
  COL.PART_ID,
  COL.LAST_SHIPPED_DATE,
  F.SalesOrder,
  COL.LINE_NO,
  F.SalesOrderLine
FROM
  dbo.Findings F
LEFT JOIN ONTIC.dbo.CUST_ORDER_LINE COL 
  ON 
  F.SalesOrder = COL.CUST_ORDER_ID
  AND F.SalesOrderLine = COL.LINE_NO
WHERE
  F.UserRecordNumber = '57695'
  AND COL.LAST_SHIPPED_DATE IS NOT NULL
  AND COL.LAST_SHIPPED_DATE <> ''

What is it that you believe the syntax should be?

Poor execution is more than just poor code, it's poor database design also, so if you are having poor execution yes start with looking at the query but you also need to start by looking at the query execution plan to see where it is going wrong

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question