rushmore query optimization?

[kenndot]

Does anyone have an opinion on this? Does it really speed things up enough to worry about doing it? Any tips/tricks?

I'm just curious to hear people's opinions on it ... good or bad, so I can determine whether I should jump into changing all (and that's a lot) our apps to run this way. I read that it should be determined query to query and that's about all I can seem to dredge up from the MS website, other than what the definition is.

Any input?

 

[Peping]

HI,

Does anyone have an opinion on this? Does it really speed things up enough to worry about doing it? Any tips/tricks?

The VFP Help File has some good example and explanation to better understand this technique. On Help Index, look for Rushmore technology, optimizing expressions and you will find a paragraph "Understanding When Queries Are Optimized".

Peping

 

[kenndot]

I have read that, I was just interested in hearing "real" people's opinions vs. MS opinions of it's own product...

but thanks and if you know of any other useful docs concerning this, please let me know

 

[danceman]

the general rule for indexes is to have one for every field that is going to be used for a selection critiea. this speeds up querys and most foxpro commands. Attitude is Everything

 

[satyenshanker]

It is great and you have use it on large data sets. Watch out for simple queries FoxPro rushmore tends to use the original table as the result set.
Ex:
SELECT * from sydtabl ORDER BY tablid WHERE tablid = "AP"
? dbf()
You will notice it returns the original table. To fix it use
NOFILTER Clause
SELECT * from sydtabl ORDER BY tablid WHERE tablid = "AP" NOFILTER

Satyen
satyenshanker@hotmail.com

 

[rgbean]

It's not really an "opinion" matter - it's just a methodology. If it wasn't really worthwhile, why would MS have added it's concept to both the Jet engine (Access) AND SQL Server?

I actually developed a similar technique for working with large data queries on a mainframe back around 1975! (I guess I should have patented the idea then, but since I was working for a state government agency at the time, you just didn't think in those terms. <s>)

Rick

 

[kenndot]

Thanks rick, Satyen... I get what you're saying about not really being an opinion matter, and to a certain extent I agree with you, but even so... everybody has an opinion and that's what I was after. I wasn't too familiar with it and am playing around w/some of the 'things' that are recommended in the help files to try to determine the best way to 'optimize' a table that I use frequently, which has about 2.9 mil records and currently takes an EXTREMELY long time to query (by extremely I mean about 10-20 minutes for one query), so that's why I was asking around

but again, I thank you.

 

[danceman]

if your query is taking that long, then you really need to create indexes. I have seen dramatic increases on querys when an index was made.

As state ealier, an index on fields that will be used in the selection critiea of querys will make a differance. Attitude is Everything

 

[kenndot]

and it did, dancemen... thanks. Here's what was happening.

A long time ago, in the beginning of this IT dept. someone asked for one report. Now, it's blossomed into a nightly process that produces lots of reports and new reports were not always built ideally, but just thrown into the program haphazardly. NOW, we're beginning to see the results of that lack of planning. We used to convert a date to a string IN the index and that is apparently a big processing hog, too, for when I removed that and just made the index on the field itself and took all functions OUT of the query statements, the query went from 20 mins to about 3-4 seconds.

I appreciate everyone's input, it's just going to be a huge overhaul should we determine that we need to really look into this, and indeed we will be looking into this now, especially with the dramatic difference I just saw. I just wanted people's opinions/tips beforehand to take into consideration.