>>>>>How can I get the Coverage / Profiler to generate a log file in a compiled
>>>>>executable in VFP 8.0?
>>>>>
>>>>>Thanks,
>>>>
>>>>May I ask why you need the profiler at run time? The primary purpose is to find performance bottlenecks during development.
>>>
>>>There is notebook with wireless connection, so I wanted to see which table is slowest when opening the form.
>>
>>Well, adding the coverage profiler at runtime will make it slower. In general you can measure it by table and index size and the distance between the notebook and the server.
>>
>>If there are large tables involved, I'd look at reducing the size of the data set being returned.
>
>I heard that Foxpro is dowloading all table content when processing SQL queries. Is it true? If yes, how it is possible to reduce the size of the data set being returnet?

As Tamar said, no it's not true. I'll add that it's important to make sure that the appropriate indexes exist on the table.

I've heard that one of the products that benefited by Microsoft's acquisition of Fox Software was SQL Server. Some of Fox's Rushmore technology found its way (not surprisingly) into SQL Server. I've heard this from a number of reliable sources, so I assume it's true. Even if it isn't, SQL Server can be used in an example I encountered first hand, regarding the necessity of the appropriate indexes.

I was given the task of coding a T-SQL statement that would return values from two different tables in two different databases. Doing an inner join to do this was kind of complex because of the way the data was stored in the second table. I only needed one value from an aggregate function. It wasn't very pretty, but because the result set was small (300+ records) it was fast enough.

When I asked my boss about whether or not the inner join was good enough, the answer was no, it had to be an outer join based on the first table (which had most of the fields). Back to the drawing board.

After several failed attempts to modify the existing statement (that contained a number of sub-queries), I had an idea. Why not put the into a function an use the result of that as the last field.

So I did. I pulled all of the sub-queries out of the original statement and put them in an SQL Server UDF. After a number of tests to verify that the function worked correctly, it was time to test it with the rest of the SQL queries.

Before continuing, I want to make a couple of thing clear. The computer I was testing against was by no means "cheap". It was a dedicated SQL Server server. It had multiple Xeon processors. A RAID array of hard disks. It had a maximum of 5 concurrent connections. Finally, it at one time had been a production server.

Now, the test. I executed it from the SQL Query Analyzer and waited...and waited...and waited. After resisting numerous desires to hit the Cancel button, I got my result set. It was 1,500+ records. After investigating, the data was correct. Unfortunately, the execution time (8 and a half minutes +) wasn't acceptable for a desktop application, let alone a web based one (which this one was). Back to the drawing board.

My first thought (happily) was to look at the indexes on the table the function was getting its result from. Was there an index on the key field that was linking the two tables? Kind of...

It was part of a compound expression that formed the primary key. As a result, I thought that I might find a performance improvement by adding an index on the key field alone. Did it work? Kind of...

Elapsed time dropped from 8:30+ to one second.

The moral is that your design of both the code and the data will ultimately dictate how well your application performs.