Slow Queries on the ALL_IMPC table

[Gramps]

I just linked the ALL_IMPC and ALL_MSAC tables and indexed many of the factors for each table. Queries designed for just the ALL_MSAC table run much faster than they used to. But queries on the ALL_IMPC table run slower, and queries on both tables run even slower. I expect queries using both tables to run slower, but why would queries to the the ALL_IMPC table run slower than those to the ALL_MSAC one?

Am I doing something wrong, or is this particular table just slow?

[MikeDee]

In general queries on joined tables run slower then queries on non-joined tables. So what I do is to create a single master table from the joined tables and run all queries on the master table.

Also when you do this you can eliminate any duplicate data fields from that are in both tables.

I can't explain this but I believe that somehow access knows that these two tables are joined and even though you are only building a query on one of them it still is slower then if they were never joined at all.

[Bernie]

Mike,

I've got the MSA and Impact tables linked up OK but am having trouble running ROI reports off of them. I tried renaming my joint query off of Impact and the pivot table to ALL_MSA misc query and then ALL_IMPC misc query (and changed the names of the original queries named this, so I could change them back later), since these are the only names that the query ROI reports will accept. But all I get are error messages. What should I do? Thanks

[MikeDee]

Berine,

I don't use the ROI report so maybe Ricks can assist as well. But one thing to look for is duplicate field names. If you are joining and bring all of the fields from each of the tables there are duplicates names in the query. The query itself is ok because it knows which table the field came from and it can display it. But The ROI report may not be able to deal with the dupliations. You might try finding fields with duplicte names and eliminaing one or the other so that the only fields that are duplicated are the joined fields.

[hurrikane]

bernie....what error message. If it is 'more that one field in table" then what mike says is correct. You can eliminate the fields or change the ROI report adding inthe table name. Things will run faster if you eliminate the extra fields. Let me know. I have my database almost back to normal....

[Bernie]

Thanks everyone. I've now got everything linked and running well.

[Gramps]

My next question is this: How do you make one master table out of the two tables (ALL_MSA and ALL_IMPC)?

[MikeDee]

You need to change your join query from the normal select query to a make table query. A normal query is called a slect query because you are "selecting" fields and various filters and then looking at the data.

In the design mode to the left of the "!" point that you use to run a query click on the down arrow. You sill see the different types of queries that you can run. Change the query to a make table query. Provide a name for your new table when access ask for it and run the make table query. This action query will make a brand new table from the tables you have joined based on the design of your join query. It will have data from msac and impc.

Once this is done and you are satisified with your new table change your make table query to a append query and save it as a append query. Now in the future when you bring in new data to the msac and impc tables you can use your append query to add more data to your master table.

(note be sure to set date, track, race no and ppo as key fields in your new master table to prevent duplications of data.)

[Donnie]

Gramps...one word of caution on indexing fields...if you index too many fields it will actually slow down your queries. Try to index only the most common fields, like date, track, distance. The program creates indexed tables in the background and too many tables confuses the issue. Indexing will speed up queries, but also can slow 'em down!

[Bernie]

I'm having a problem with some of my queries used linked tables. Sometimes I use a data field in brackets as a specified field for another data field (e.g. [nFLD] as a value for the rank of something). I get an error message saying: "The specified field could refer to more than one table listed in the FROM clause of your SQL server." What can I do to fix this?

[Carl]

Bernie,

I change the names of my fields when they are the same in both tables and I want to use both.

For example, I call the HTR trainer rating "rTRN" and the IMPAC trainer rating "TRNr".

Some of the fields from the two tables duplicate information, and you only need keep one in a table.

Although if you are specifying the table you are getting the information from, I am surprised the access gives you that error message. I thought it would only pick up from the table you asked for the information you wanted to query.

Later,
Carl

[Rick]

Until you get it cleaned up you can include the table name in front of the field name:

[ALL_MSA].[rTNR]

Sometimes I have witnessed where the brackets weren't required around the table name but I tend to include them.

[Gramps]

Thanks for the help. But even when I make a new database with the make query, it still loads very slowly whenever I use any fields from the ALL_IMPC table. I have no clue why. Loading ALL_MSAC is very fast.

[Carl]

My all IMPC table has run faster than my all MSAC table from day one, I would have expected it to, as it has fewer fields and more integer fields than MSAC.

Couple of Q's/things to look at:
1) Did you change the field types on any of the fields after you brought them in in IMPC?
2) Is it (IMPC) running more slowly in all aspects (opening etc.) or only in getting ROI queries? If the latter, it may be that you have to change the field names of some fields to get the ROI query to work right, I think I did. (It's been awhile.)
3) My ROI report will only operate off of a table called ALL_MSA. Is this true for you also? I had to re-name my IMPC table "ALL_MSA" before I could query it independently, I think I also had to rename one of the $ fields, but again I am not sure.

Later,
Carl

[Rick]

The ROI Report is keyed to the query, not the table.

You can change the table that the query is using, you can add tables to the query, and you can add queries to the query.

I found out a while back that indexing speeds up queries quite a bit. I indexed the hell out of ALL_MSA. Part of the problem is that Access only allows something like 32 indexes in a db. Not very much for us.

I would suggest to all that have created a new table from joining ALL_MSA and Impact tables to remove the indexes from ALL_MSA and Impact and add them to your new combined table.

If you combined things you don't need the indexes in the old tables. You should only be using the old tables to import a days/weeks worth of races with results and then run your query to join the two and append the records to your new master table. At that point you should be deleting the records from the ALL_MSA and Impact tables so you can start over again next week.

ALL_MSA with indexes will run faster than Impact without indexes.

=======================

I just went and checked the references and they say that the index limit is 32 per table not db.

[Cliff]

All,

I have a little bit of a different problem that I cannot seem to get resolved. After messing around with Access (importing, doing queries, etc.), my computer does not seem to want to shut down at the end of the session. It appears to get "hung" up when I click the "start" button to go to "shut down". No blue screen, no sand timer, no nothing.....just sits there. Ultimately, I have to do a control-alt-delete to make anything happen. The only unusual item of interest in the display box is "Back Web Agent". I'm not familiar with that program or what it does. Anybody got any hints or suggestions?

Again, it only gives me this problem after manipulating data around in Access. I have tried to run ScanDisk but it won't run due to restarting 10 times (something running in background according to the error message). Not sure if the two issues are related, or not.

Thanks,
Cliff

[Gramps]

Back Web Agent is a background application used by Compaq and Hewlett Packard (and probably other computer manufacturers who are partners with McAfee). What the program is supposed to do is get updates from the respective company and from McAfee Antivirus, as they become available.

If you don't use McAfee Antivirus, or if you can get the updates manually, you're better off just disabling Back Web Agent. To do this, go to Start/Run and enter this:
msconfig
In the popup window, tab over to "Startup" and look for this application, then uncheck the box next to it and click on "Apply", then on "OK". You'll be asked to restart your computer. Do so.

If this causes problems, just go back into msconfig and recheck the box, but from what I know, Back Web Agent just hogs resources and causes conflicts with Windows. If you aren't comfortable getting your own updates, you can go into msconfig twice a month or so and recheck the Back Web Agent box for a day and let it get whatever updates it needs.

[Cliff]

Gramps,

Thanks for the input. That didn't cure my scandisk problem, but, did get rid of those annoying pop up boxes for McAfee and HP. I'm thinking that I have some cable software running in the background that scandisk doesn't like. Will try running it w/o cable turned on later tonight.

Thanks again,
Cliff

[hurrikane]

Cliff...you need to go in and turn off anything running that isn't neccesary. Task scheduler, norton, freeze check. Something is running in the background that is changing the image of C..this causes scandisk to restart. You have to find out what it is and disable it. You can also boot at the dos prompt and run scandisk from there to solve the problem.

As for access. How much ram do you have? What version are you running? Have you downloaded anything lately? I have had a lot of problems similar to this lately. Seems some downloaded software has some conflict with windows...likely a lazy programer didn't want to find the problem.

[Gramps]

Any time a program writes to volume (to disk), Scandisk (or any disk analyzer or defragmenter) will probably restart. Software for some always-on (highspeed) ISP's, especially Cox and Roadrunner cable, occasionally pings the host ISP during inactivity to check on the status of the connection. Some of these will write the status to a logfile. If they do, Scandisk might restart. A software firewall may pick up some network "noise" or an attempted connection from one of the many hackers scanning IP blocks. If the firewall detects an unauthorized outside connection to your computer, it may write to a logfile. Zonealar, a popular freeware firewall which I recommend that anybody with a highspeed connection use (it also has a commercial version, whose features are probably not necessary for most of you), regularly writes to a logfile. There are other programs and background processes which write to volume. Any of these can cause Scandisk to restart.

The solution, as hurrikane recommended, is to reboot to DOS or a command prompt and run Scandisk from there. To do this from Windows 98, choose Start/Shut Down (or ALT + F4 from the desktop), and then "Restart in MS DOS mode".

[hurrikane]

thanks for making that clear Gramps....lately I haven't been doing to well at diseminating info.

Q....Gramps....is that because you are a new grandfather or you have some other "handle story"...you don't seem like a gramps...you seem to have tossed the buggy whips anyway. :D

[Gramps]

I was a grandfather five years ago. The older of my two daughters just had her second child recently. I just try to stay young in mind, which isn't hard for me, since my two sisters claim that I never have grown up (which is a fine thing for them to say, since they still go running around like they did when they were in college).

[hurrikane]

Thats great Gramp...my son and daughter..though just reaching maturity..insist I am one of the kids.....go figure. :D

[Cliff]

Gramps and hurrikane,

The ultimate solution was to use the msconfig to delete several programs at startup and also to go into "safe" mode to run scandisk. Not sure why, but, that worked.

For the record, I am using Windows ME and was attempting a "thorough" scandisk. Hurrikane.......am using Access 2000 and only have 64 meg of ram. That could be a potential problem. Looks like the scandisk and defrag helped me shut down properly though.

Thanks again,
Cliff

[Gramps]

Cliff: The problem is that you're running Windows ME. It's full of bugs, and Microsoft has no plans on fixing most of them, since they're pushing their XP product now. I tried Windows ME for exactly 2 days, then, about 40 driver updates and 200 blue screen fatal errors later, fdisked and formatted the hard disk to clear it and reinstalled Windows 98 Second Edition.

64 megs of RAM isn't really enough to run Windows ME and anything else at all. If your computer will support it, you should upgrade to at LEAST 128 megs and better still, 256 megs of ram. RAM is relatively cheap right now, and many places that sell it to you will install it for you free of charge or for a nominal fee. It will speed up your applications noticeably, particularly MS Access.