Need faster Date function

[maw230]

Is there a date function that might work more efficiently than what I have here:

Between DateAdd("yyyy",-1,DateSerial(Year(Date()),1,1)) And DateAdd("yyyy",-1,Date()-2) Or Between DateSerial(Year(Date()),1,1) And Date()-2

?

 

[BobMcClellan]

Is the column you are using to check for this indexed?

 

[maw230]

Is the column you are using to check for this indexed?

It is a primary key for the table, but no it doesn't appear to be indexed. It's a table linked through ODBC.

 

[BobMcClellan]

If it's the PK, it's already indexed. Not sure why your performance is slow. Are your returning a large amount of rows?
If you do a ctrl G to jump to the immediate window and ? any of the date strings you've posted you see they return immediately. IF you open the query from vba you can get the date params you want and store them to the query def and execute with constants.
hth,
..bob

 

[maw230]

Are your returning a large amount of rows?

Yes, it's a very large dataset. I am looking for any ways to increase performance, and the date function seemed like a good place to start.

Thanks for the help!

 

[jdraw]

It looks to me that your Between is going to process every record. Is there any way to select a subset of the data before doing the Between?

 

[maw230]

I'm not sure. Would the method that Bob suggested still need to process every record:

IF you open the query from vba you can get the date params you want and store them to the query def and execute with constants.

?

 

[jdraw]

Possibly. If you can work with some constant instead of calculating within the query it should certainly help.
What exactly are you trying to do with the Between?
Records from last year or the year before?

Can you show us the whole query?

 

[maw230]

What exactly are you trying to do with the Between?

Yes it is a Year-to-date sales query, so for example if I were to run it today I would need

between 01/01/2010 and 04/19/2010 or between 01/01/2011 and 04/19/2011.

Here is the SQL:

SELECT DWQRYDATA_SLSDSI.STORE_ID, DWDATA_KSSCAT.CATDESC, Sum(DWQRYDATA_SLSDSI.TOTAL_SALES) AS SumOfTOTAL_SALES, Sum(DWQRYDATA_SLSDSI.TOTAL_SALES_AT_COST) AS SumOfTOTAL_SALES_AT_COST, Sum(DWQRYDATA_SLSDSI.TOTAL_UNITS) AS SumOfTOTAL_UNITS, Year([DATE_ID]) AS [Year]
FROM (DWDATA_KSSPRODUCT INNER JOIN DWQRYDATA_SLSDSI ON (DWDATA_KSSPRODUCT.ITEM = DWQRYDATA_SLSDSI.ITEM) AND (DWDATA_KSSPRODUCT.LINE = DWQRYDATA_SLSDSI.LINE)) INNER JOIN DWDATA_KSSCAT ON (DWDATA_KSSPRODUCT.SUBCATNUM = DWDATA_KSSCAT.SUBCATNUM) AND (DWDATA_KSSPRODUCT.CATNUM = DWDATA_KSSCAT.CATNUM) AND (DWDATA_KSSPRODUCT.DEPTNUM = DWDATA_KSSCAT.DEPTNUM)
GROUP BY DWQRYDATA_SLSDSI.STORE_ID, DWDATA_KSSCAT.CATDESC, Year([DATE_ID]), DWQRYDATA_SLSDSI.DATE_ID
HAVING (((Sum(DWQRYDATA_SLSDSI.TOTAL_SALES))>0) AND ((DWQRYDATA_SLSDSI.DATE_ID) Between DateAdd("yyyy",-1,DateSerial(Year(Date()),1,1)) And DateAdd("yyyy",-1,Date()-2) Or (DWQRYDATA_SLSDSI.DATE_ID) Between DateSerial(Year(Date()),1,1) And Date()-2));

 

[DCrake]

You don't have a WHERE condition in your query you have HAVING. This takes extra processing.

what you need to do is to replicate the fields that you want to filter on and use the WHERE condtion.

By placing your condition undeneath the columns instgates the HAVING parameter.

 

[jdraw]

Have you tried running the query using hard coded dates as was suggested by Bob?

If you run the query from some button, you could set up the query to use hardcoded date values.

In the button's click event for example
you could use some variables whose value is the calculated the Date values to use.

Then substitute those variables into the SQL of the query, then run the query.

Whether it's quicker or not, only a test will tell for sure. But if you try the query with hardcoded values, and it is quicker, then the logic above would be one way of implementing it.

 

[Brianwarnock]

DateAdd("yyyy",-1,DateSerial(Year(Date()),1,1))
can be replaced by
DateSerial(year(date())-1,1,1)

But I doubt that saving one function call will make that much difference.

Brian

 

[Brianwarnock]

BTW He needs the Having to check on the Sum >0 but he could also use a Where to do the Date check and that could save a lot of processing as David said. You can have both Where is applied before the aggregation, Having after.

Brian

 

[maw230]

Thanks for all the help guys. Today, the query is taking ~ 30 minutes to run, so I don't need the speed boost. My guess is that our system is/was being heavily bogged down as of late.