>>>>>hi,
>>>>>
>>>>>I always find Xbase commands faster than Sql(statement) commands.
>>>>>
>>>>>
>>>>>Any case where Sql statments are faster ?
>>>>>Also i would like to know best prsctices where to use what ??
>>>>>
>>>>>
>>>>>TIA
>>>>>suhashegde
>>>>
>>>>Post a example where XBASE is faster.
>>>
>>>I think, that indexseek/seek/KeyMatch is usually faster than Select-SQL.
>>
>>sure for 1 to 100 indexseek/seek/KeyMatch vs 1 sql select,
>>but a query's 100% optimized over a dataset with 100 or more rows it must always win,
>> otherwise is an error of the VFPT
>>
>>A great advantage of the SQL query is:
>>- if the VFP engine improves can improve a lot
>>- the time of execution is relatively high with little dataset,
>> but it improves with great amounts, therefore the time perceived from the customer is less variable.
>>A great disadvantage of the query is:
>>- the probability of having a bug in a sql engine is greater regarding that one of one simple XBASE command
>
>Hi Fabio,
>
>If I'm not mistaken, once the query is executed in SQL Server, it finds the best optimization plan and sticks to it. In VFP this is not the case AFAIK.

It find the best plan with the query optimizer rules;
of course, the MSSQL Team open the door for the manual optimization, with the Hints ( JOIN,INDEX,... )

But SQL is a remote server, then if you want 1 field value:
- build the remote languange script
- VFP parse the script
- ODBC parse the script
- send the script
- wait the engine script check time
- wait the engine optimization time
- wait the engine dataset building
- wait the engine return back the data
- wait VFP get data
- wait VFP check data
- ....

Result : you have moved a 1MB of data with 10M of processor instructions for having an integer key value!

Another example where sql is out of the game:
Build a database where the data they represent a connected graph ( not a treeview ).
Now, tries to extract the set of all row paths between two points with SQL commands.