question

technette avatar image
technette asked

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-queriespoor executionquery format
2 comments
10 |1200

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

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 Likes 0 ·

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 Likes 0 ·

1 Answer

·
anthony.green avatar image
anthony.green answered

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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