CODEWITHSUNDEEP
sqlsql-serverquery-optimization
Kawaii-Hachii
Kawaii-Hachii

Reputation: 1061

Anyone can optimize this SQL statement?

Database server is Microsoft SQL Server, but I don't have Administrator access to it. So, I don't know which version, and I don't know which indexes exist.

To access the database, I am using ADO.

Here is the SQL statement:

-- Get master objid and order_number and activity time
SELECT A.objid,
       A.order_number,
       F.entry_time
-- From these tables
FROM   dbo.table_master as A,
       dbo.table_activity as F
-- link of the tables
WHERE  F.objid = A.objid
       -- Retrieve code = 1900 only
       AND F.code = 1900
       -- Which have info like this:
       AND F.info LIKE '%to SUPPORT.'
       -- And entry time between these times:
       AND F.entry_time >= '2011-10-01 00:00:00'
       AND F.entry_time <= '2011-12-05 23:59:59'
-- We want the earliest entry (because there might be multiple code = 900 and info like)
ORDER  by F.entry_time

Is it possible to optimize this?

Thanks

Upvotes: 1

Views: 132

Answers (3)

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

Here are things you could do differently:

  • use RIGHT(f.info, 11) = 'to SUPPORT.' instead of F.info LIKE '%to SUPPORT.'
  • use an INNER JOIN as @xQbert suggested
  • use BETWEEN for the date range instead of the combination of < and >.

In my testing, the only one that made a difference in performance was the first option. All 3 items will generate the same execution plan, however, the RIGHT function was around 10x quicker in my test in terms of actual execution time. I'm using STATISTICS TIME to test.

If possible, I would try to get access to look at the indexes. Having the tables properly indexed will make a way bigger difference than the RIGHT function.

Upvotes: 2

xQbert
xQbert

Reputation: 35323

This is a different way of writing the same thing; it may yield faster performance due to the nature of the join and the use of between. It also puts limits on the join criteria which force evaluation before the %to so your working with a smaller subset when the %to SUPPORT is evaluated.

SELECT A.objid, A.order_number, F.entry_time 
FROM dbo.table_master as A
INNER JOIN dbo.table_activity as F 
  ON F.objid = A.objid
  AND F.code = 1900
  AND F.entry_time BETWEEN '2011-10-01 00:00:00' AND '2011-12-05 23:59:59'
WHERE 
F.info LIKE '%to SUPPORT.'
ORDER by F.entry_time

EDITED After 1st comment.

Upvotes: 0

Fox
Fox

Reputation: 2368

as far as i know, the worst part of your query and also the part that cannot be easily optimized, is 

AND F.info LIKE '%to SUPPORT.'

because the indexes work only for matching from the start of the string, not from the end.

so if you want better performance, you probably need to find some way to work around this.

(oh. just realized it's not MySQL. this answer would be 100% correct for mysql, but i expect it to be correct for most of common database engines)

Upvotes: 1

Related Questions