>OK. I don't understand how I missed this. This obviously just went right over my head. I knew my views were not always optimized. I accepted that it would be slower but I guess the part I missed is I had no idea how much slower.
>
>So I guess that is the answer I have been seeking. VFP can be horribly slow if your views are not optimized and you have a good bit of data and you will have multiple users. Hopefully this will serve as help to keep someone else from having this issue.
>
>I would appreciate your input.
>
>As I stated earlier I am trying to move to TSQL. I am worried that doing so may not alleviate this issue. Would it be true to say that I should optimize my views (meaning make them fast whatever that consists of) BEFORE switching to TSQL? I am NOT referring to making them faster so my users have a better experience until I get to TSQL. I mean is this something I had better do because TSQL will not work much better unless I do so.

I'm not 100% sure what you mean by views being "optimized".

Getting back to basics, views are SQL (SQL engine in VFP) operations. The great beauty of SQL is that it's declarative - you just tell it what you want. You don't have to tell it how to get the results - it figures that out itself.

By far the biggest performance win is making sure indices are available such that views and other queries are fully optimized (in VFP, in the Rushmore sense). I had a case late last year where at a customer site a 1M row transaction table somehow lost all its index tags except the primary key index. Everything still ran perfectly in the logical sense but was very slow. When I restored the index tags, typical complex reporting queries sped up by a factor of about 250.

If you have 1000 views, you may well be using views that involve other views (which is 100% legitimate). But if the views are not optimized, performance problems will compound.

So as far as optimizing views or other queries go, there's not a lot to it (*):

1. Make sure the logic is correct
2. Make sure appropriate indices are available
3. In VFP, make sure those indices are used and functioning as expected: http://fox.wikis.com/wc.dll?Wiki~UnderstandingRushmore~Wiki

* The vast majority of the time with basic queries this is all you need to do. The VFP SQL engine is not super-sophisticated or super-smart so there are times such as Dragan outlined where you can do some hand-tuning to improve performance.

SQL Server is more sophisticated than VFP's SQL engine. Generally what you need to do is similar as with VFP:

1. Make sure the logic is correct. Optional - if your logic is complex, see if you can make use of SQL Server/TSQL-specific features (i.e. full ANSI SQL or TSQL syntax not supported by VFP) for performance wins

2. As above make sure indices are available

3. I don't believe there are too many cases where you have to be careful so that indices are used as expected. But if your logic is complex YMMV