How to speed up a query

[April15Hater]

Hi-
Sometimes I like to take UDF's and insert them into my queries in access. However, it seems whenever I do this, it takes and incredibly long time to process. Any idea on how to improve this? Here's a sample query that takes forever to load:

SELECT DISTINCT lastdow([FinalizeDate]) AS CompletedWeeks, IsNull([finalizedate]) AS Expr1 FROM tblProductionInput WHERE (((IsNull([finalizedate]))=False)) ORDER BY lastdow([FinalizeDate]) DESC;

Thanks,
Joe

 

[boblarson]

1. Why would you need to SELECT DISTINCT if you are using an UDF to get the lastdow?

2. I would get rid of the SELECT DISTINCT and if you have multiples coming back for an entire row, use a Grouping instead of DISTINCT.

I think the DISTINCT keyword will slow you down here immensely.

 

[April15Hater]

1. The finalizedate column has the same date listed several times so I get the same lastdow (I think you caught on, but it's last day of week just in case) listed several times.
2. Hmm, that makes a lot of sense, but I could never get the Gropu by clause to work. I have another situation too where I tried to use GROUP BY and couldn't get it to work. It says it's not part of an aggregate function.

Here's my revised code

SELECT DISTINCT lastdow([FinalizeDate]) AS CompletedWeeks, IsNull([finalizedate]) AS Expr1
FROM tblProductionInput
WHERE (((IsNull([finalizedate]))=False))
GROUP BY lastdow([FinalizeDate])
ORDER BY lastdow([FinalizeDate]) DESC;

 

[boblarson]

You have to make sure to Include your Expr1 part in the grouping if you are going to include that.

 

[April15Hater]

Great! It is much faster. Still a little slow, but at least now it's tolerable. Thanks again for your help

Final Code:

SELECT lastdow([FinalizeDate]) AS CompleteDate, IsNull([finalizedate]) AS Expr1
FROM tblProductionInput
WHERE (((IsNull([finalizedate]))=False))
GROUP BY lastdow([FinalizeDate]), IsNull([finalizedate])
ORDER BY lastdow([FinalizeDate]) DESC;

 

[DCrake]

SELECT lastdow([FinalizeDate]) AS CompleteDate, IsNull([finalizedate]) AS Expr1
FROM tblProductionInput
WHERE (((IsNull([finalizedate]))=False))
GROUP BY lastdow([FinalizeDate]), IsNull([finalizedate])
ORDER BY lastdow([FinalizeDate]) DESC;

You are calling the same function 3 times per record you can cut one out by simply ordering on the finialize date as they will be in the correct order. Also the IsNull([FinalizeDate]) As Expr1 will always be False so why not simply have Expr1:"False"

I am assuming that this line is simply stating True/False for the date.

Also what does your LastDOW() look like?

David

 

[April15Hater]

Hi David-

It doesn't return a Boolean, it returns a date. Basically it gives me the last friday of the workweek Ex. lastdow(3/3/2009) = 3/6/2009

Public Function LastDOW(ByVal dtDate As Date, Optional intWeekBegin As Integer = vbUseSystemDayOfWeek) As Date
LastDOW = DateSerial(Year(dtDate), 1, DatePart("y", dtDate, intWeekBegin) + (6 - Weekday(dtDate, intWeekBegin)))
End Function