Making query faster?

[buddyrich2]

I have a stupid question but I'll ask it anyway because I need my query to be faster. I know to make a query faster you need to add indexes. I am not creating the tables on the fly so, does that just mean that I can add an index to the file being queried or is there actually something you do at runtime (during the query)?

 

[Mike Lewis]

Hi Buddyrich,

You're wrong. It's not a stupid question. On the contrary, many developers seem to think they have to create the indexes every time they open the table, which is time-consuming and unnecessary.

Basically, you can create the indexes when you create the table -- then forget about them. Provided the indexes are structural (that is, they are contained in a CDX file with the same name as the table -- this is the usual behaviour), VFP will take care of keeping them up to date at all times.

However, don't assume that you should create an index on every field or expression that might appear in a query. There's a trade-off at work here. The more index tags, the easier it is to optimise your queries, but the slower the updating of the table. If you have a highly volatile table that you only occasionally query, it might be better to keep the indexes to a minimum.

Hope this helps.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

A third more direct answer:

a) Yes, you'll index the table you query from before the query, not during the query.

In fact there is no such thing as indexing during a query, without some trickery like using a user defined function (udf) within the query. Plus Rushmore will sometimes create a temporay index to make a join or filter, but that's seldom, as creating an index always needs a full scan over the data indexed and you would need to get the time back by faster matching/in seaching within the temporarily built index. Anyway it's not something the database architect, administrator or programmer want to influence or do, when rushmore does so, it mainly indexes intermediate results.

Rushmore mainly makes use of preexisting indexes. Simply take a look in the help at what rushmore is and how it works and what indexes it makes use of and you'll know what and how to index for query optimisation.

Bye, Olaf.

 

[Olaf Doschke]

I forgot b), but I put that into the answer already, so simply ignore the fact that there is only an a).

In fact b) could have been, that only rushmore makes indexes on the fly during a query and it makes no sense for you to do so, if you don't have a point eg between two partly queries, where you could index an intermediate result.

As long as you get your result in one query you won't need such an index. But in fact your result will not inherit indexes of the table as long as it's in fact not a real query result, but a filter on the table, which rushmore uses as the query optimisation. Therefore in general query results, view or CA cursors even with a native data source have no index and if you want one you'd index those on the fly. But permanent tables should also be indexed permanently for optimizations.

Bye, Olaf.

 

[stfaprc]

You'd need an ... index on upper(firstname) tax ixfirst ... to be in the cdx file for the following to be optimized.

m.lcFirstName = upper(m.lcFirstName)
select * from table where upper(firstname) = ?m.lcFirstName

is that a typo - should it read
"index on upper(firstname) TAG ixfirst" ?

Depending on the situation [experiment to see if it applies] having the index on trim(upper(firstname)) or alltrim(upper(firstname)) could help.

 

[Mike Lewis]

Depending on the situation [experiment to see if it applies] having the index on trim(upper(firstname)) or alltrim(upper(firstname)) could help.

The trouble with trimming the expression is that the index will contain variable-length keys, which I've always understod to be something to avoid.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Dan Freeman]

That's correct, Mike. Index expressions MUST evaluate to a consistent length. Later editions of VFP will enforce that internally (padding automatically) but that can lead to unusual results because it may get padded to a different length than you might expect.

 

[stfaprc]

bummer, which version started padding the index expressions?

There were times when there was a significant improvement in speed, which I had supposed was gained but the vfp engine comparing the lenghts before starting to compare characters (just a guess, i don't know). It also helped in making exact matches, then I suppose that

select * from table where upper(firstname) == ?m.lcFirstName

would do only exact matches as well.