Cleanest way to filter down to YTD across multiple years? (1 Viewer)

[gojets1721]

I have a table of complaints dating back to 2014. There's roughly 10 thousand records. I inherited this DB and there's a query which filters down to YTD across all years (i.e. it only shows records between 1/1 through today across all years).

The query takes a while to load as opposed to all other queries. I'm not sure if its just because of the number of records or if the code used to do this filtering is not the most efficient. Any thoughts on ways to improve?

Here's the query's SQL code:

SELECT ComplaintNumber, ComplaintDate, ComplaintCategory
From qryAllComplaints
WHERE (((Format([ComplaintDate],"mmdd"))<Format(Date(),"mmdd")) AND ((Year([ComplaintDate]))>=Year(Date())-10))
ORDER BY ComplaintDate;

 

[Minty]

Quick test remove the format around the initial date part of the where clause. Hard code a couple of dates in there and see if the speed improves.
It looks unnecessary to me.

I'm hoping the complaint date is indexed?

 

[Josef P.]

An index can only become effective as no result of a calculation is filtered.

1) Simplest variant: use a full calendar table (date + extra data field for year. month + day + a 3-field-index over year+month+day).

select
   ...

from
    CalenderTable Cal
    inner join
    qryAllComplaints C ON C.ComplaintDate = Cal.CalDate
where
    Cal.CalYear >= Year(DateAdd("d", -1, Date())) - 10
    and
    (
       Cal.CalMonth < Month(DateAdd("d", -1, Date()))
       and
       Cal.CalMonth = Month(DateAdd("d", -1, Date())) and Cal.Day < Day(DateAdd("d", -1, Date()))
    )

2) If no calendar table is to be maintained, at least create a numbers table (1..10 or more)

select
    ...
from
   (select Year(DateAdd("d", -1, Date())) + 1 - N as xYear from NumberTable where N <= 10) as Y,
   qryAllComplaints C ON  = Cal.CalDate
where
    C.ComplaintDate >= DateSerial(Y.xYear, 1, 1)
    and
    C.ComplaintDate <= DateSerial(Y.xYear, Month(DateAdd("d", -1, Date())), Day(DateAdd("d", -1, Date()))

/edit:

WHERE (((Format([ComplaintDate],"mmdd"))<Format(Date(),"mmdd")) AND ((Year([ComplaintDate]))>=Year(Date())-10))

Try this condition on 2024-01-01

 

[ebs17]

Performance is obtained by index usage. @Minty asks with good reason if you have an index on the date field.

But index usage does not only mean to have an index. You have to formulate the query in a way that this index can be used.

Format(ComplaintDate, "mmdd")
Year(ComplaintDate)

Calculations on the table field prevent index usage. So you should filter on a pure table field.

Try:

SELECT
   Q.ComplaintNumber,
   Q.ComplaintDate,
   Q.ComplaintCategory
FROM
   qryAllComplaints AS Q,
   (
      SELECT
         I
      FROM
         T999
      WHERE
         I < 11
   ) AS T
WHERE
   Q.ComplaintDate BETWEEN DateSerial(Year(Date()) + T.I, 1, 1)
      AND
   DateSerial(Year(Date()) + T.I, Month(Date()), Day(Date())
ORDER BY
   Q.ComplaintDate

T999 is an auxiliary table with a single field I (primary key) containing the numbers from 0 to 999.

Regarding effort and index usage, one would have to look for the given query qryAllComplaints.

 

[Pat Hartman]

When you format table columns and use that as your criteria, you prevent the query engine from using any indexes so it has to read every row in the table to return a result. Therefore, the more data in the table, the longer the process takes.

The others have given you options for creating criteria that allows the query engine to use indexes. However, we don't know what qryAllComplaints does and so that might also be a problem.

 

[ebs17]

In addition to index usage, which can have a massive impact on performance, there are other aspects.

Format(ComplaintDate, "mmdd")

... is executed for each record, i.e. for all 10 thousand records.

DateSerial(Year(Date()) + T.I, 1, 1)

... from my solution is executed for the 11 years and thus 11 times.
Since calculating costs time, logically fewer operations require less time.

The format function generates string as return value. So the filter compares 14 bytes against 14 bytes (2 bytes per character + 10 bytes management = 14 bytes).
DateSerial generates Date as return. So one compares 8 bytes against 8 bytes.
Such differences also add up to runtimes in execution.

 

[gojets1721]

(
SELECT
I
FROM
T999
WHERE
I < 11
) AS T

Hi @ebs17 I tried your solution and created a T999 table with I having records 0 through 999.

Unfortunately I'm getting a syntax error on the above piece of code. I'm not exactly sure why. I copied it exactly as you provided.

Also, complaintdate is indexed

 

[ebs17]

Of course, if you want to run the subquery by itself, you must omit parentheses and aliases, that is, the first and last lines in the code representation.

 

[gojets1721]

Of course, if you want to run the subquery by itself, you must omit parentheses and aliases, that is, the first and last lines in the code representation.

No I’m sorry. I used your entire code. I only meant that the subquery was what the syntax error was popping up on

 

[ebs17]

I can't see any error in the query. Now I am not sure if you have created the table correctly.

SELECT I FROM T999 WHERE I < 11

If you run this query by itself, you should see a field I with the numbers 0 to 10. Is this the case?

 

[gojets1721]

I can't see any error in the query. Now I am not sure if you have created the table correctly.

SELECT I FROM T999 WHERE I < 11

If you run this query by itself, you should see a field I with the numbers 0 to 10. Is this the case?

Yes, that works. Not sure what else could be wrong.

Here's an example DB. Try using your code in a new query (I can't save your code in a query because I get the syntax error whenever I try to save it)

 

[ebs17]

OK, I also get an error reported, but in a different place: Missing operator in criterion ...
The problem is a missing closing parenthesis ...
DateSerial(Year(Date()) + T.I, Month(Date()), Day(Date()) )

I can't save your code in a query

Open a new query, switch to SQL view, copy the statement into it and go directly to datasheet view. Then you get a better error message with this attempt of execution.

 

[gojets1721]

OK, I also get an error reported, but in a different place: Missing operator in criterion ...
The problem is a missing closing parenthesis ...
DateSerial(Year(Date()) + T.I, Month(Date()), Day(Date()) )


Open a new query, switch to SQL view, copy the statement into it and go directly to datasheet view. Then you get a better error message with this attempt of execution.

Hmm so the query ran, but it's only showing records for 2023.

I'm looking for records across all years from 1.1 to present day

 

[ebs17]

And caught again:

+ T.I

... adds years to the current year. But they should be subtracted.

So swap plus to minus.

This is how it can be if, like me, you only write queries from abstraction and don't test them.
Idea and implementation have to fit together, the devil is in the details.

 

[gojets1721]

And caught again:

+ T.I

... counts years to the current year. But they should be subtracted.

So swap plus to minus.

This is how it can be if, like me, you only write queries from abstraction and don't test them.
Idea and implementation have to fit together, the devil is in the details.

That worked! Thanks so much!