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..