There are some comments about your post from a friend Gar. For some reasons, including medical disorders, s/he requested me to post this. These are his/her comments but I agree with him/her. I make this explanation just to notify that s/he should be credited for corrections. I agree with comments and can reply further.
Cetin
>>I have a table with 1.5 mil records. When I browse or query it is slow. I have an index on every field I query on. I already ran sys(3054,11) and I am fully optimized. Help!!!!
>
>One comment -- Too many indexes can actually slow you down. This could be part of your slow open. Better to index the most frequently queried on fields only.
Also, she needs to realize that indexes using FOR clauses won't be used, and that the expressions must exactly match the index expression - IOW, if she has an index on UPPER(cBletch) she needs to check WHERE UPPER(cBletch) = ?cFooArg
>
>Remember -- a partially optimized query will run almost as fast a fully optimized one if the partially opimized field alone is enough to reduce your result set to something reasonable. In other words if you are querying by last name and date, and only last name is indexed but you are search for "mehagahigle" and you only have a few hundred "mehagahigles" in the database, your query shold still execute in seconds.
>
>If you have enough indexs to make sure that 90%+ of your queries are partially optimized you have enough indexes. Eliminate all beyond this need and you will see a speed improvement (though not neccesarily all you need).
If she's running under NT, she probably also needs to curb VFP's use of memory with SYS(3050), especially if she's allocated too much swap space - under NT, VFP sets its default buffer space based on total system memory, not physical RAM, and may be carrying stuff "cached" in the swap file, making her thrash out.
>
>More questions:
>
>Is the table on a server/station separate from the one on which it is being opened?
Loading on the cable plant should be examined. 10Mbit EtherNet is only good for about 300-400KB/sec with no contention, and it drops like a rock with contention eating lots of bandwidth. If 10BaseT, a switch rather than a hub may help.
>
>What is the amount of Ram/ Processor/ disk controller/ Disk type on the station where you are executing the "Use" and other commands.
More importantly, where's VFP putting her tempfile space? If she's IDE, are both the CD-ROM and hard drive on the same shannel? If NT, does she have multiple swap files allocated on several logical drives built from one physical drive?
>
>If the table is on a different workstation or server, what is the answer to above questions for the workstation or server. And (again assuming this is a separate machine) what is the server/stations OS. What is the speed of the connection.
>
>I'd need the answer to these questions to know for sure -- but when you work with large tables you have to throw hardware at them.
>
>You should have at 512 meg of Ram on the client machine (a gig would be better).
BIG, BAD MISTAKE. She should have no more than the largest amount of memory on the PC that the processor/chipset can cache. Slot 1 (PII/PIII) can't support >512MB directly; a Celeron has an even lower limit, and older Pentium class systems may have limits as low as 64MB. To support larger than 512MB of RAM, you need Socket 8 (Pentium Pro), Slot 2 (Xeons) or some Slot A (Athlon) processors with an appropriate chipset. Too much memory really hurts under NT, since NT allocates top down rather than bottom up, so the first memory used will not be cachable.
>
>If you are pulling this from a server or if the file is on a shared drive, them machine which it is on should again have at least 512 meg 1 gig of memory would be better.
Same issues, plus depending on the OS, the cost of managing a large address space may be very high.
>
>If this table is being opened from machines other than the one on which it resides, you should have at least 100MPS of network speed, 100 Base-T ethernet for example or high speed Token ring.
>
>Another point-- you might get some results by playing with the sys(3050) function. Use help to find out how this works. Default VFP settings are almost never optimal and are usually too high. You'll have to play with it to find out how and if this applies in your case. If your hardware is below what I've suggested, this will probably give you some improvement, almost certainly not as much as you need.
>
>Hope this helps.
