How to optimize SQL query

Question

I have this query that run on MS SQL Server:

SELECT o.phone AS Number, o.code AS Code, o.name AS Name,
o.expireDate AS ExpireDate, o.disabled AS cardStatus, a.disabled AS AccountStatus, 
MAX(j.cDate) AS LastUse 
FROM [DATAB].[dbo].[Card] AS o
LEFT OUTER JOIN [DATAB].[dbo].[journal] AS j 
ON j.phone = o.phone,
[DATAB].[dbo].[CardType] AS ct, [DATAB].[dbo].[Account] AS a, 
[DATAB].[dbo].[CardProduct] AS cp
WHERE o.accountProductId = ct.id
AND ct.accountId = a .id
AND a.number = 'XXXXXXXXXX' 
AND ct.partnerProductId = cp.id
AND cp.code = 'XXXX'
GROUP BY o.phone, o.code, o.disabled, o.name, o.expireDate, a.disabled
ORDER BY o.name ASC;

I'd like to optimize it in order to run faster, but don't how to start.

FYI, I have checked the estimated execution plan, the index analyse of DATAB].[dbo].[journal] says that it cost 70% of the query, but there is no index issue message.

First of all, change you code to proper join statements. Add indexes to foreign keys. Try to add index to a.number and cp.code also. — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Jan 10, 2017 at 9:16
Do you need a left join or do you only want phones that appear in dbo.Journal? — TJB
– TJB, Commented Jan 10, 2017 at 9:22
Giorgi: I've tested with the index you tell me to add, but the result is the same, I get 1400 rows in 5 seconds. TJB: I want the last MAX(cDate) from dbo.Journal — Marc El Bichon
– Marc El Bichon, Commented Jan 10, 2017 at 9:30
You could look at nested selects which sometimes yield faster results(check with execution plan as its not always the case). Setup indexes for fields that dont change very often. Defragment the database. I would look at the query analyser and execution plans which are the key to getting the performance you require here. On the other side, for example if you are calling this from a website then look at caching the results to prevent the database calls in the first place. Setup up a maintenance plan to ensure that the performance is maintained. — Netferret
– Netferret, Commented Jan 10, 2017 at 9:31

Pரதீப் · Accepted Answer · 2017-01-10 09:32:51Z

Here is one way using Outer Apply

SELECT o.phone      AS Number,
       o.code       AS Code,
       o.name       AS Name,
       o.expireDate AS ExpireDate,
       o.disabled   AS cardStatus,
       a.disabled   AS AccountStatus,
       LastUse
FROM   [DATAB].[dbo].[Card] AS o
       INNER JOIN [DATAB].[dbo].[CardType] AS ct
               ON o.accountProductId = ct.id
       INNER JOIN [DATAB].[dbo].[Account] AS a
               ON ct.accountId = a .id
       INNER JOIN [DATAB].[dbo].[CardProduct] AS cp
               ON ct.partnerProductId = cp.id
       OUTER apply (SELECT Max(cDate)
                    FROM   [DATAB].[dbo].[journal] AS j
                    WHERE  j.phone = o.phone) ou (LastUse)
WHERE  a.number = 'XXXXXXXXXX'
       AND cp.code = 'XXXX'
ORDER  BY o.name ASC;

Creating a Nonclustered Index on phone,cDate columns in journal table should help the query

Collectives™ on Stack Overflow

How to optimize SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related