Time to run ROI Analysis Report?

[JohnB]

Hello all -

First time poster, long time lurker on this board.

First let me say how fortunate I am to have found HTR. I am totally impressed by all of the HTR community, especially your willingness to share! This board is one of the main reasons I ended up joining HTR. I hope to be able to contribute once I have a little more "HTR time" under my belt.

Now that I am done sucking up to the board, I do have a question.

How long should it normally take to run an ROI Analysis report on a query in Access? When I loaded my first couple of months of data into Access, the report used to run quickly. I am now finding that when I run a query on a larger table, it takes quite a bit longer to run. I am not complaining - I am very thankful to the person who desgined this report (MikeDee?). Just want to make sure I am not doing anything wrong in Access.....

Here is what I do - I follow this procedure when I am running an ROI analysis report on one of my queries.

1) Save a query as "ROI analysis"
2) Go to the "Reports" section under "Objects" and click on "Reports"
3) Double click on "ROI Reprt"
4) At the bottom of the screen, the message "Formatting page, press CTRL+Break to stop" comes up pretty quickly, within a matter of seconds. After that message pops up, the computer then "hangs" for about 120-150 seconds before popping up the report. Is that normal?

Anyone have any ideas on how I can improve this speed? Am I doing anything wrong, or is this perfectly normal? Thanks for your time.

John

[Donnie]

Welcome aboard John!

Concerning Access...a couple of things to make sure of:

1. Make sure you have the "proper" fields indexed in your tables. Indexed fields are fields you plan to include in your reports and more so, in your queries, that you plan to run criteria against or sort upon. Fields with redundant data many times make good index candidates (ie: TRK, DATE, RACE). An index field is assigned by going into the table properties and clicking on the field you wish to index, and then in the bottom pane choose Yes(duplicates ok) in the Indexed property. Access will create "invisble" tables in the backgound that it uses when you have these indexed fields in your queries. Be carefull though. Too many fields indexed will actually slow the process down more than if you had none indexed.

2. Try to make sure that your tables are as small as possible. In other words, if weight is not important to you, don't include it in your import. Remove any fields and/or any records you do not need in your tables or queries. If you are in the habit of downloading all tracks every day, but only play major tracks, either stop downloading and filling your tables with data you don't need, or do a delete query first to remove the "extra" records. Normalizing tables is something you really never see on the board here, but take a look in Access Help files to read up on normalizing the tables.

3. The more links you are using between tables the more time it will take to run your queries.

I am sure others have tricks they have learned over the years. Hopefully they will post away!

Again, welcome! Fire away if you have any other questions!

BOL

-Donnie

[Rick]

John,
Several things can help. Some times is can be a question of cpu and memory.

One thing that will speed query processing up is indexing some of the fields you are using in your queries. You should try to look up in Access help on indexing.

Just be mindful that the is a limit of 32 total index fields in a db (I think). I use to use indexing when I had a 400 mhz machine. I stopped using indexing after I got above 1500 mhz. It use to take me 45 seconds to run queries on the 400 mhz machine without indexing.

But when you use indexing it takes longer to add new records to your tables. It takes awhile for Access to redo all the indexes.

What type of machine are you using and how much memory?

[Rick]

The limit is 32 indexes per table not per db.

[CC Brown]

My machine now takes that long also since I now have 2+months of data. I have 800Mhz laptop.

[JohnB]

Thanks for all your help guys - I will try your suggestions!

I have a Pentium 4 CPU, 2.80 GHz with 248 MB of RAM.

John

[Rick]

You have enough CPU.

Double the memory will help.

Indexing some fields that are used in the query would help. The Primary Key already counts as an index so you don't need to include date, track, race or program # as an index field.

[Rick]

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.rKrat, ALL_HX4.nWKSC, ALL_HX4.xFIN, ALL_HX4.xWIN, ALL_HX4.rMLO FROM ALL_HX4 WHERE (((ALL_HX4.rKrat)=1) AND ((ALL_HX4.nWKSC)>=85) AND ((ALL_HX4.rMLO)>1));

I ran the above query on a 12 month db from which I just removed the races shorter than 4.5 fl.

I am running a computer that I put together: AMD XP 2700 cpu and 1 GB of memory.

ROI report took about 15 seconds to run.

I then indexed the nWKSC field. The ROI report took 5 seconds to run.

I then added indexes to rKrat, rMLO, xFIN and xWIN. The ROI report took about 1 second to run.

[Rick]

I just tried converting my db to 2002-2003 format to see if it would make any difference in report speed.

I will have to say the only thing I accomplished by converting was to loose compatibility with 2000 format. No gain in speed at all.

[Rick]

Some settings I use that may or may not help performance:

I am using WinXP Pro. Right click on My Computer and select properties. Under Advanced, Performance, click on Settings and click on Adjust for best performance. On the same Performance Options click on Advanced tab. I have both on top set for Programs. On Virtual memory I have mine set at 3000 mb. I have plenty of hard disk space.

[Donnie]

Rick-
I would assume that most people have WinXP Home edition which comes with most home systems now. Lots of fun things you can do with Pro that you can't do with Home...but most people here would never need the controls available in the Pro version!

[JohnB]

Indexing was the answer! I took Rick's suggestion and indexed the rKrat, nWKSC, rMLO, xWIN, xFIN fields and the same report that was taking 120-150 seconds to run now runs in 2 seconds.

Thanks to all for your help! I really appreciate it!

John