Query runs really slow

[zdog]

Hi Guys,

I have a query that runs fine but takes a while to run. As a consequence all queries and reports built on this query slow down resulting in poor peformance.

Let me first off try to explain what I'm trying to do and point out what is slowing down the query.

I have a table with about 120,000 + records. I've never dealt with such a large volume of data and the bad news is that the number of records will keep increasing to about 3 times the current number.

A couple of the fields of importance are the part_num, date, series, qty.
All these fields are in the tblshipnt table, what I am trying to do is actually sort the data into groups by date, the grouping is to be done based on the financial calendar (5-4-4) and not the regular calendar. So i have another table tblcal with fields start_dt, end_dt, yr/mnth. This specifies the start and end date for each month eg. 2/7/05 3/6/05 2005/02 so in this eg. the month of feb begins 2/7 and ends 3/6

the way my query is currently structured is that I use Between [start_dt] and [end_dt] as the criteria for the date field which comes from the tblshipnt

So the query looks like
part_num date yr/mnth series qty
rr101 2/16/05 2005/02 rr 2

I then use this query to build a crosstab query. My question being is there a faster way to compare the date field from the shipnt table against that in the cal table cause this step seems to be the bottleneck. The query currently takes about 2 minutes to display all the records and my fear is that the time will keep increasing as the shipnt table grows in size.

Appreciate any advise.

Thanks
Z

 

[Brianwarnock]

Off the top of my head and no idea if it is better how about tblshipnt being a 366 row 2 field table, field 1 being the date and field 2 the yrmnth then the yrmnth could be selected witha join.

Does this make sense? will it perform faster?

brian

 

[zdog]

Hi Brain,

I understand what you are trying to say. I'll give it a shot and is if it imporves the performance. The reason why I chose to use start and end dates is simply because its easier to update as well as enter 12 records rather than 366 records, but if that improves performance dramatically I think it will be well worth the extra effort.

Thanks for the input.
Z

 

[Brianwarnock]

Create in Excel and import that way you can fill down series and fill down to quickly create data

Brian

 

[zdog]

Brian,
I created the new calendar table and now the query executes in about 5 secs. Thanks for the tip.
Z

 

[Brianwarnock]

I'm chuffed that it was a success , wish I could claim it was a stroke of genius rather than a "a wonder if?" suggestion based on the idea that joins must be effecient since they are the essence of relational databases.

On thought tho' don't you have an ongoing maintenance problem as time goes by as long as year is included ?

Brian

 

[namliam]

How about if you put it in a function instead of a table?

Have you tried that?

Regards