We help IT Professionals succeed at work.

Why does a date criteria make a difference when used as a Parameter vs a Table

upobDaPlaya
upobDaPlaya asked
on
I have a table of over 500,000 records for widgets that have been ordered.  One of the fields is the Order date.  The table is called Orders.  If I use a criteria within a query for the Order Date then the data is retrieved extremely quick.

However, if I have another table of unique dates called Dates and try to do a join on date from the Dates table and Order Date from the Orders table then the retrieval of data is extremely slow.  How do I get around this as I do not want to manually have to put in a criteria to remove orders from the previous business day.

I simply wand to run a query on the Orders and Dates table.   Note the dates table is flagged with the date row that reflects the previous business day which is the Order date I am interested in...However, the introduction of the Dates table seems to have created a performance issue..
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Do both of these fields in each table have an Index ?

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
How are you joining the Dates table? Can you show the SQL of the query?

Author

Commented:
Its a normal join...the Date table which is local has the Date field indexed.  The Order table does not have the field called Order Date indexed but it is a primary field..  Unfortunately I can not change the Order Date Field to indexed since I am doing an ODBC connection to the Order table which is stored via Oracle.  I can talk to the person that maintains the Order table, but it seems like it would be a big deal for me to request them to change the design of the table..

Anyway around this ?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
ok, well still can you post the SQL?

mx

Author

Commented:
I will try to mock it up and send..

Author

Commented:
My tables are tOrder and tDate  The tDate table is indexed.  The tOder table is not indexed


SELECT tOrder.OrderID
FROM tDate Inner Join tOrder ON tDate.LastBizDay = tOrder.OrderDate
GroupBy tOrder.OrderID
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well, that looks pretty normal ...

Does it get even slower if you remove the one index (local) that you do have ?

mx

Author

Commented:
no its the same...essentially the Oracle Database connection times out.. if I remove the tDate table and use a criteria in the tOder table for OrderDate it works pretty quickly..  Odd...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
sure which we could try the index on the other side.

Have you tried putting the criteria against each side, ie two different tests ?

mx

Author

Commented:
do you think I am better off trying to write some vba code that run the query..i am a little weak on SQL VBA, but perhaps its worth a try..I will also try the 2 different tests
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Not sure what advantage there would be using code.  What did you have in mind ?

mx

Author

Commented:
The below seems to work for me..any ideas why quicker...

Dim.....
Set rs = dbs.OpenRecordset ("Date")

dtDate = (rs!date)
Set qdf = dbs.QueryDefs("Widget Orders By Date Query")
qdf.Paramater(0) = dtDate
qdf.Execute
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Not right off.  Where is dtDate coming from?

So, the unique Dates table is local, and the orders table is Oracle, right.

mx
Commented:
I've always avoided joining tables on a date field since it's really a date/time field and could contain time information which will break the join.  

One solution which should be fast is (join wise) is If you maintain an 8 character text field (format YYYYMMDD) in both tables which replicates your date field.  The disadvantage of course is that whenever you update the value in the date field you have to update the value in this second field.

If you want a crazy optimization you can use an long integer (well 32bit integer) as the field type for the join field and store the date as year * 10000 + month *100 + day and that will create the smallest fastest indexes for joining.
Database Architect / Application Developer
Top Expert 2007
Commented:
Just for fun, try this. Note:  You will only be able to do this in the SQL window. You will not be able to open in the query designer normal view.  Int(Date/TimeField) returns only the data portion, and does not error out if a value is Null.

SELECT tOrder.OrderID
FROM tDate Inner Join tOrder ON Int(tDate.LastBizDay) = Int(tOrder.OrderDate)
GroupBy tOrder.OrderID

mx

Author

Commented:
This is pretty quick also MX, but why...

Author

Commented:
Excellent suggestions and approach
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"This is pretty quick also MX, but why..."
Really not sure. Other than reducing the date to definitely be a number. But I think using INT() eliminates the index from being used locally ... but I guess that wasn't doing much anyway ...

mx