Any unique Ideas to increase performance on large files 1

[nimmist]

Ok here it goes

I have a customer that has unusually large files that can only be archived or pruned after the data is 5 Years old. The tables have anywhere from 5-200 fields and average around 100,000 records and as large as 500,000 records. I have rewritten all the programs to take as much use of the rushmore optimazation as they can... I know that with large files you can wait a lot of time based on the operations that need to be applied. I am just hoping to speed up some of the reports that are taking as much as 40 Minutes to run If there are any tricks that anyone would like to share for this situation -- I WOULD greatly appreciate it.

Thanks in advance

Reggie

 

[tilltek]

Could you pull some of the data out into a cursor first and then work on that?
Say you wanted to know how many times fred loged on in june 2000. Grab all records for 2000 into a cursor, then look up fred in the cursor?

Ken F

 

[infinitelo]

without knowing any details, its hard to give advice. you might check to see if the report is regenerating information that does not change. ie skip counting stuff that doesnt change and load from a old count file. you may not be able to identify parts of the report that can't change. the other option(much harder to do) is to generate reports as data is changed. (instant report) if in a multi user enviornment really hard to pull off.
if it is to be it's up to me

 

[nimmist]

Thanks for your replies... Here is a little more info.

The slowness occurs even when I have narrowed the list down by using SQL commands to cursors or to temporary files. One of the ways that does seem to help (as you pointed out) is to hold data that does not need to be regenerated as often... unfortunately this system is running in a WAN environment with lots of users - so creating hold tables becomes a challenge. I appreciated the answers and hope this gives more insight into the problem.

Reggie

 

[rgbean]

One article you may want to read is Val Matison's "The Ultimate Power & Speed of VFP - Handling Extremely Large Data Sets" at

http://www.advisor.com/Articles.nsf/aid/MATIV02.

Note: The Euro Tunnel project was written in FPD, so most of the techniques discussed will work in all versions of FoxPro.

Rick

 

[DSummZZZ]

One option may be to build your own query server where the tables reside, and have it run the reports from there and create the output in html or something that transfers faster. Then the reports could be viewed or transferred to the local machine.
Dave S.

 

[Scott24x7]

Reggie,
There is a possibility of speeding a few things up, but may take some tweeking, and working with your data set to fully understand how to best go about this.

1) This may sound odd, but limit the number of indexes you have on the table. I've written telephone billing systems which maintain large detail files, (over 6,000,000 records!) with VERY fast results. This is the result of 2 things. a) Knowing my data very very well so that I can limit the number of indexes, b) create the indexes I need as effectively as possible.
2) Once I've done the above, (in this case, I recommend pushing to no more than 2 indexes. Compound is okay, and make sure you take as much advantage of compound indexes as possible. If you need a ACCOUNT# index, and you need a CITY index, combine the two to ACCOUNT#+CITY, then ensure you always have the account # before doing a city search. This is a bad example, but you get the idea. Again, you must know your data and what you need to achieve very well.
3) This technique starts to become very handy because you can then use the SEEK command to quickly get to the starting point that you need. No since searching the entire database when you just need to get to a start point. For instance if you have an index on DATE, you can then SEEK M.DATERANGE (some value for date), if you then have a high and low date range, you only extract records who match your date range criteria, and then STOP as SOON as you hit your exceeded range.

I put this technique to work in my call detail table, which allowed me, in a data set of over 6mm records, to pull the top 10 numbers called to and from, with the total number of calles made to/from each, regardless of the number of calls, I can retrieve this in a Fox for DOS system in under 1 second (when accessed for a single user). Very fast, but again, you must know your data.

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[nimmist]

I appreciate all the helpfull hints. There are some ideas mentioned that I will definitely use to help performance. Thanks for all your feedback