A significant number of implicit conversions are interfering with optimisation of this query. Here's a few recommendations to start with:

Cast @YearFrom and @YearTo to same datatype as BRFCLSeaWeekHis.Year

Cast @QuoteType...

Thanks.

Try placing the nonclustered index keys the other way around:

CREATE NONCLUSTERED INDEX [IX_BRFCLSeaWeekHis_QuoteType] ON [dbo].[BRFCLSeaWeekHis]

(

Year, [QuoteType] ASC

)

INCLUDE (

[CurrencyCode],

[RateTotal],

[SeaTotal],

[Total])

Any time Dale.

Check out the two APPLY articles by Paul White in my signature too, they are an excellent walkthrough with examples.

Execution plan with...

mark.kristensen (2/28/2014)

---

Hi Chris,

Thanks for the leads, but even with recompile and changed index, the time is the same.

In relation to your first post, then I will not likely get anymore...

From an earlier post: "Here's a table of the possible combinations of specialties and capabilities (the 'YES' column should be ignored):"

Do you have a table which looks similar to what...

For a quick win, change your non-clustered index like so:

CREATE NONCLUSTERED INDEX [IX_BRFCLSeaWeekHis_QuoteType] ON [dbo].[BRFCLSeaWeekHis]

(

[QuoteType] ASC, Year

)

INCLUDE (

[CurrencyCode],

[RateTotal],

[SeaTotal],

[Total])

and your query like so:

WHERE QuoteType = @QuoteType

AND FCL.Year >=...

Hi Mark

Two or three points to consider.

Firstly this is a "Catch-all query". In a nutshell, the number of rows returned could vary dramatically depending upon the parameters passed in....

Yep, sorry, my mistake.

Dale Magnant (2/27/2014)

---

Chris,

After...

UPDATE m Set

POObligations = x.POObligations

FROM MDEP m

CROSS APPLY (

SELECT

POObligations = (SUM(PO_DX.QuantityOrdered * PO_DX.Price) - SUM(Distinct PO_MX.PaymentPosted))

* SUM(Distinct(cast(PO_MX.PrimarySplit as decimal(5,2))/100))

FROM PO_MX

INNER JOIN PO_DX

ON PO_DX.PONumber = PO_MX.PONumber

WHERE PO_MX.MDEP...

Please post the actual execution plan as a .sqlplan file attachment.

It sounds as if this is in the design phase - if this is the case, then consider pivoting the table you display here as your starting point, so columns...

It's not possible to give a good example without table aliases...

Two excellent articles by Paul White covering APPLY are linked in my sig.

If you take a closer look at the SELECT query I wrote, it's designed to show the update...

Stefan Krzywicki (2/25/2014)

---

The Dixie Flatline (2/25/2014)

---

rodjkidd (2/25/2014)

---

Steve Jones - SSC Editor (2/24/2014)

---

I went to the Rock and Roll Hall of Fame in Cleveland a few weeks ago. It was pretty...