SELECT SQL speed & performance

[EmmaLu]

I have noticed and also received many questions from my clients about the variance in time to produce results from a SELECT SQL query over a network. What I observe is that the first time you perform a non-optimized query that the results may take several minutes. However the same query run a second or third time takes only seconds. As long as VFP is not restarted subsequent queries are very fast.

I have some idea about why this is the case but don't fully understand. I also wonder if there may be a way to make the first query as fast as the subsequent ones.

 

[craigber]

I assume you're asking about queries to VFP data. The first time through, the SQL parser is probably building an index. Since it still exists, it's used on subsequent queries.

The solution is to create a proper, Rushmore optimizable index and always have it updated. Then make sure the query itself is optimizable.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[Mike Lewis]

In addition to what Craig said, be aware that VFP does a lot of caching behind the scenes. In general, almost any process is likely to faster the second and subsequent times within a session.

There's no general way to "make the first query as fast as the subsequent ones". As Craig says, you should be focusing on optimising the query in the first place.

Of course, everything's a trade-off. Adding indexes could make a query faster, but might make updating slower. If you were updating the table many times per second, but only running the query once a year (admittedly an unusual situation), it might be better not to add indexes. You have to judge each case on its merits.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[EmmaLu]

Thanks guys. Yes I am searching VFP tables. The table I am searching has about 150 fields and it only becomes a big problem when the number of records gets up around 50,000.

My application requires a huge amount of data for each record so I have broken it up into multiple tables of 150 fields or less. I understand Rushmore and already have more than a dozen indexes for each table.

The problem arises when users want to search fields that are not indexed (I can't index all fields). They also want to do "Contains Text" queries and search Memo fields for words or text.

I assume the speed difference has to do with caching on the first search which takes time and then is used to speed subsequent searches. I was doubtful about a solution to this but wanted to be sure I wasn't missing something.

 

[TamarGranor]

The problem arises when users want to search fields that are not indexed (I can't index all fields). They also want to do "Contains Text" queries and search Memo fields for words or text.

This is your problem. Don't be shy about creating indexes. The marginal cost of updating indexes when adding or editing records is not that large.

Contains searches and searches in memos are more of an issue. You can look at Advantage Database Server from Sybase--it does work with DBFs and lets you do those kinds of searches. However, there is a cost in backward compatibility.

Tamar

 

[Mike Lewis]

EmmaLu,

You say you have broken the data into separate tables, of about 150 fields each. So, the total number of fields is roughly the number of tables times 150.

I don't know anything about your application, but I would be suspicious of anything that requires such a large number of fields.

Are any of those fields "repeating" fields - that is, things like Sales_January, Sales_Februrary, etc? Have you looked for opportunities to move these to a child table (containing a single Sales field and an indicator of the month, in this example)?

Before focusing on optimising the queries, I would look for ways of improving the structure of the tables along those lines, if at all possible.

Regarding the "contains" searches: The only way to handle these efficiently is have some sort of full-text indexing system. You might find a third-party product to help (Tamar mentioned Advantage Database Server; there also used to be a product named PhdBASE, but I think it's extinct). Back-end databases like SQL Server often have full-text tools as well.

But there's no easy way of doing it natively in FoxPro. The slowness of such searches is the price you pay for the flexibility.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business