Really Slow query when i add a query to select statement

Question

Really Slow query when i add a query to select statement

dopydb

SSCertifiable

Points: 6687
More actions
March 21, 2014 at 5:21 am

#150083

hi all
i have the below query, which runs fine until i add
(select top 1 (sm2.SalesOrder) from SorDetail sd2
left join SorMaster sm2 on sm2.SalesOrder = sd2.SalesOrder
where sd2.LoadNumber = sd.LoadNumber and sm2.Customer =ar.Customer)
to the select statement, does anyone have any tips for making this more efficient?
^{declare @date varchar(12)}
select @date = '2014-03-20'
select distinct
sd.LoadNumber,
d.SourceWarehouse,
CUSTOMER,
ltrim(rtrim(a.AlphaValue)),
ar.Name,
ar.ShipToAddr1,
ar.ShipToAddr2,
ar.ShipToAddr3,
ar.ShipToAddr4,
ar.ShipPostalCode,
convert(varchar,getdate(),111)as DespatchDate,
isnull((PickedPallets + Full_Containers),0) as Cheps,
ar.Currency,ar.Telephone
,(select top 1 (sm2.SalesOrder) from SorDetail sd2
left join SorMaster sm2 on sm2.SalesOrder = sd2.SalesOrder
where sd2.LoadNumber = sd.LoadNumber and sm2.Customer =ar.Customer)
from DESPATCH_REPORTS d
Join ArCustomer ar on ar.Customer = d.CUSTOMER
left join SorDetail sd on sd.SalesOrder = d.SALES_ORDER
join mal.dbo.[PE Pallet usage] p on p.LoadNumber = sd.LoadNumber and p.Customer = d.CUSTOMER
left join AdmFormData a on a.KeyField = d.CUSTOMER
where convert(varchar,DESPATCH_NOTE_DATE,111) >= @date
AND d.SourceWarehouse in ('PE')
and LEN(d.TargetWarehouse) < '1'
and sd.LoadNumber is not null and len(sd.LoadNumber) >'1'
and ar.Currency <> 'PLN'
AND a.FormType = 'CUS' and FieldName = 'CHE001'

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you please post the actual execution plan (of the query including the subquery) as a .sqlplan file attachment? Cheers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dopydb SSCertifiable Points: 6687 More actions · Answer 2

dopydb

SSCertifiable

Points: 6687

March 21, 2014 at 6:20 am

#1699056

hi, think thats it attached?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Yes, thanks.

(

select top 1 (sm2.SalesOrder)

from SorDetail sd2

left join SorMaster sm2

on sm2.SalesOrder = sd2.SalesOrder

where sd2.LoadNumber = sd.LoadNumber

and sm2.Customer = ar.Customer

)

Top of what exactly, it's missing ORDER BY.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

It's the estimated plan - the actual plan is more useful.

The estimated plan you've posted carries this index recommendation:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[DESPATCH_REPORTS] ([SourceWarehouse])

INCLUDE ([CUSTOMER],[SALES_ORDER],[DESPATCH_NOTE_DATE],[TargetWarehouse])

It would be helpful if you or your DBA could create this index before running the query to obtain the actual plan.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dopydb SSCertifiable Points: 6687 More actions · Answer 5

hi Chris

i ordered the statement and created the index but its still really slow,

i think i might be best rebuilding this, as the new field was a last minute requirement

thanks

mal

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Then you might want to look at this next:

where convert(varchar,DESPATCH_NOTE_DATE,111) >= @date

This expression means that any index on DESPATCH_NOTE_DATE can't help. Rather than converting DESPATCH_NOTE_DATE to match @date, do it the other way around: convert @date to match the datatype of DESPATCH_NOTE_DATE.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dopydb SSCertifiable Points: 6687 More actions · Answer 7

that has definitely helped!

i didnt even have any reason/need to convert the date, cheers for that tip ! 🙂