Speed up query

Transact-SQL
1

Is there anyway to make this run faster? Keeps timing out. Takes over 5 minutes when running through the new query window.

SELECT TOP (100) PERCENT item_no, loc, ISNULL(SUM(CASE WHEN month(doc_dt) = 1 AND year(doc_dt) = year(getdate()) AND source = 'O' AND
doc_type = 'I' THEN quantity END), 0) AS SoldPrd1, ISNULL(SUM(CASE WHEN month(doc_dt) = 2 AND year(doc_dt) = year(getdate()) AND
source = 'O' AND doc_type = 'I' THEN quantity END), 0) AS SoldPrd2, ISNULL(SUM(CASE WHEN month(doc_dt) = 3 AND year(doc_dt) = year(getdate())
AND source = 'O' AND doc_type = 'I' THEN quantity END), 0) AS SoldPrd3, ISNULL(SUM(CASE WHEN month(doc_dt) = 4 AND year(doc_dt)
= year(getdate()) AND source = 'O' AND doc_type = 'I' THEN quantity END), 0) AS SoldPrd4, ISNULL(SUM(CASE WHEN month(doc_dt) = 5 AND
year(doc_dt) = year(getdate()) AND source = 'O' AND doc_type = 'I' THEN quantity END), 0) AS SoldPrd5, ISNULL(SUM(CASE WHEN month(doc_dt)
= 6 AND year(doc_dt) = year(getdate()) AND source = 'O' AND doc_type = 'I' THEN quantity END), 0) AS SoldPrd6,
ISNULL(SUM(CASE WHEN month(doc_dt) = 7 AND year(doc_dt) = year(getdate()) AND source = 'O' AND doc_type = 'I' THEN quantity END), 0)
AS SoldPrd7, ISNULL(SUM(CASE WHEN month(doc_dt) = 8 AND year(doc_dt) = year(getdate()) AND source = 'O' AND doc_type = 'I' THEN quantity END),
0) AS SoldPrd8, ISNULL(SUM(CASE WHEN month(doc_dt) = 9 AND year(doc_dt) = year(getdate()) AND source = 'O' AND
doc_type = 'I' THEN quantity END), 0) AS SoldPrd9, ISNULL(SUM(CASE WHEN month(doc_dt) = 10 AND year(doc_dt) = year(getdate()) AND
source = 'O' AND doc_type = 'I' THEN quantity END), 0) AS SoldPrd10, ISNULL(SUM(CASE WHEN month(doc_dt) = 11 AND year(doc_dt) = year(getdate())
AND source = 'O' AND doc_type = 'I' THEN quantity END), 0) AS SoldPrd11, ISNULL(SUM(CASE WHEN month(doc_dt) = 12 AND year(doc_dt)
= year(getdate()) AND source = 'O' AND doc_type = 'I' THEN quantity END), 0) AS SoldPrd12
FROM dbo.iminvtrx_sql
GROUP BY item_no, loc
ORDER BY SoldPrd1 DESC

2

Looks like you only want

    year(doc_dt) = year(getdate())
AND source = 'O'
AND doc_type = 'I'

??

in which case add a WHERE CLAUSE so that only those rows are considered.

Better still, in the where clause change

year(doc_dt) = year(getdate())

to

doc_dt >= DATEADD(Year, DATEDIFF(Year, 0, GetDate()), 0)

as that will use an index - if there is one available on [doc_dt]

You could add an index on

doc_dt, source, doc_type

I presume that [item_no] and [loc] are in the clustered index, if not add them to the index.

You could add [quantity] as an INCLUDE column, and then (I think ...) the index would then completely cover the query

3

and take the

TOP (100) PERCENT

out - its not doing anything.

If that's in there because this is in a VIEW then note that the sort won't work reliably, so take the SORT and the TOP (100) PERCENT out of the VIEW and do the SORT in the code that users the VIEW.

4

For this query, you can move all the conditions to the WHERE clause, which will limit the rows to be processed much sooner.
Also, you should very strongly consider clustering that table first on "doc_dt" if you most often / almost always read it by date, as that could help the performance of every query against the table, if you use the proper style WHERE clause, coded like the one below:

SELECT TOP (100) PERCENT 
    item_no, loc, 
    ISNULL(SUM(CASE WHEN month(doc_dt) =  1 THEN quantity ELSE 0 END), 0) AS SoldPrd1, 
    ISNULL(SUM(CASE WHEN month(doc_dt) =  2 THEN quantity ELSE 0 END), 0) AS SoldPrd2, 
    ISNULL(SUM(CASE WHEN month(doc_dt) =  3 THEN quantity ELSE 0 END), 0) AS SoldPrd3, 
    ISNULL(SUM(CASE WHEN month(doc_dt) =  4 THEN quantity ELSE 0 END), 0) AS SoldPrd4, 
    ISNULL(SUM(CASE WHEN month(doc_dt) =  5 THEN quantity ELSE 0 END), 0) AS SoldPrd5, 
    ISNULL(SUM(CASE WHEN month(doc_dt) =  6 THEN quantity ELSE 0 END), 0) AS SoldPrd6, 
    ISNULL(SUM(CASE WHEN month(doc_dt) =  7 THEN quantity ELSE 0 END), 0) AS SoldPrd7, 
    ISNULL(SUM(CASE WHEN month(doc_dt) =  8 THEN quantity ELSE 0 END), 0) AS SoldPrd8, 
    ISNULL(SUM(CASE WHEN month(doc_dt) =  9 THEN quantity ELSE 0 END), 0) AS SoldPrd9, 
    ISNULL(SUM(CASE WHEN month(doc_dt) = 10 THEN quantity ELSE 0 END), 0) AS SoldPrd10, 
    ISNULL(SUM(CASE WHEN month(doc_dt) = 11 THEN quantity ELSE 0 END), 0) AS SoldPrd11, 
    ISNULL(SUM(CASE WHEN month(doc_dt) = 12 THEN quantity ELSE 0 END), 0) AS SoldPrd12
FROM dbo.iminvtrx_sql
WHERE doc_dt >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND
    doc_dt < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) AND
    source = 'O' AND
    doc_type = 'I'
GROUP BY item_no, loc
ORDER BY SoldPrd1 DESC
5

I assumed (maybe wrongly?!!) that there would be no doc_dt after today's date ... although ... I then thought it odd that the query would tally "all months in current year" when, for most of the year, most of them would be zero ...

6

Why make that assumption? It's easy enough, and makes the code more self-documenting, to code the correct ending date. Why build in a bug if future-year data is for some reason added to the underlying table later?

7

Scott and Kristen have a very viable solution, I just want to suggest a different viewpoint, and that is from one of a report writer. In many cases the SQL servers are hammered mercilessly day in and day out, so we try to minimize processing when we can- get in, get the data we want, get out. Format and calculate in the reporting interface. A simple Cross Pivot in Excel or Matrix in SSRS can take the following SQL and turn it into just the result set you need:

SELECT item_no
	,loc
	,ISNULL(quantity, 0) As Quan
	,month(doc_dt) As Month
FROM dbo.iminvtrx_sql
WHERE 	source = 'O'
		AND doc_type = 'I'
		AND doc_dt Between DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
			AND DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)

Just a thought.

8

Either its true, or not (in which case my misunderstanding ... mea culpa)

For me: if it were true I would want to enforce it (via constraints / whatever), not have reports that work based on a maybe-future different date concept. Otherwise IMHO the chances are that some reports would adhere, and some would not, as it would not be something that was tested before release (unless it was in the spec to allow for it, in which case there I wouldn't be creating a constraint to enforce it either :slight_smile: )

9

It could change tomorrow or next week. Maybe you need to start allowing future dates. If I know that I need only the current year's data, it's bizarre to me to do anything except code that requirement. Why assume there will never be a future date? Why would I ever want to build such a fixed restriction into my code?? Again, why deliberately build in future bugs for no real reason??

10

My assumption was that [doc_dt] was "today's date" at the point of creation. New DOCs might be entered into the system "some time after creation", but would not be valid future-dated.

Of course that assumption may be incorrect, but if it is right I would want to enforce it as it would not make sense for Documents to be created with future-dates (time-zone variations apart).

OTOH [doc_dt] might mean "Date document expires" or somesuch ...

11

Again, that is at this moment in time. I'm not going to pretend to be omniscient and know what date requirements may come up next week, next month or next year.

If my query is to return dates for the current year, that is what I will code, not just ignore the part about future dates because I just don't think it will be needed anyway, so there! That's like a tantrum, not a professional coding approach.

If a CHECK condition is needed to make sure the date is not a future date, that should be coded in the table. But that's still no reason for me not to write my code to match the data my query needs. Again, I don't know what changes will be made in the future.

12

Interesting to hear your viewpoint, thanks.

My thought is that if the requirement for the data changes from "at this moment in time" to allow "any future date" there are all sorts of things that will need changing in the APP and I'm not worried to ensure that this particular piece of code would be failsafe if the underlying rules change, in fact I might consider that that is actually an advantage (as it would "alert" to the fact that there was a problem, and that may well be the first such alert that anyone gets to know about ... :frowning: )

For example: I use LEFT OUTER JOIN "just in case" when I know that there is Foreign Key in place and the Parent record could never!! be missing, Maybe it is irrational to take those sort of safeguards?. My worry is that the FKey gets dropped e.g. by accident, and I would like the user to know that there is some data, now orphaned, rather than just silently hiding the problem.

I am inclined to take the same view for this date. (On the assumption that the intention is to only store "current dates") then IF a future date gets into the system I'd like to include it in the report. The user is then made aware and, hopefully, someone will scream and that may well be the first that anyone gets to know that there is a problem. If the Server Date is set into the future, or the Requirements change to allow future-dates, then the fact that the database is, now, holding future-dates comes to light.

(Different topic, but I don't know about you? but I do find that Server date being wrongly set does seem to happen; we have client-owned servers that are surprisingly unreliable for "now" date/time, even though they are hooked up to time servers and the Client's IT people swear blind that "it could never happen"!)