>The dos table has 21,000 record and memo field.
>The size is approx. 29Mb.
>I have created dbc but this table is not the part of dbc.
>
>When I create a simple view like
>Create SQL View 'Test' as select * from test
>and browse this view it takes 78 seconds.
>Then I shutdown the VFP6.0 SP3. Restart and use the same table, in command window I type Select * from test.
>and it takes hardly 3 seconds.
>What is wrong.
>I also observed whenever I select a particular field(s) with 'as' say
>select first_name as fname last_name as lname from test, then I get a thermometer saying Running Query and takes atleast 3 minutes.
>
>How do I improve the performance using view.
>Sanjay Chavda
Hi Sanjay,
I watched similar effects while optimizing SQLs, and my understanding is that
it always take longer for the first SQL run then for the next one, because the DBF (or part of it) is being loaded into RAM. Second time it takes less, because VFP looks in already existing buffers in RAM first. Even if you shut down VFP the data may stay in memory buffers. Also, if you are loading the whole table this big into the view it will take substantial amount of time anyway. Usually views are supposed to bring the subsets of records, not the whole table (however your SQL query may evaluate to the whole table sometimes, depending on it's parameters) You also didn't tell if you have indexes on first_name and last_name, and you are supposed to have indexes on fields (or expressions) which are important for your view.
HTH,
Nick
Nick Neklioudov
Universal Thread Consultant
3 times Microsoft MVP - Visual FoxPro
"I have not failed. I've just found 10,000 ways that don't work." - Thomas Edison