Hi Gabriel,
Thanks for sending the code - that is a complex view!.

First, views are just cursors whose definition is stored in the DBC, so changing the code to a cursor would not speed it up appreciably.

Second, have you checked the code with Sys(3054,1) and Sys(3054,11)? That will tell you more about how VFP is looking at the code, especially if your indexes are being used as you think they are.

Third, you might consider rewriting the code

Select......;
Inner Join .....
ON ....
Right Outer Join ....
On ....
Inner Join .....
On....
Where.....

You might try changing the order of the 3 joins, also.

Fourth, I notice that the only limiting factor is the cCardAttr value. Is there any difference in speed when you run the view with NODATA, with a hardcoded value for cCarAttr that results in no data and a value that results in a reasonable (say about 100) records)?

Fifth, what is the speed (for testing purposes) of getting a with the correct subset of Transactiondetail records and then running the view?

Sixth, have you tried building the view in steps: First the TransactionDetail values, then link in Transaction, Offers, Merchant one at a time? You may see where the slowdown is. In one case I ended up breaking a view into two views, one calling the other because it was faster than linking 4 tables the way you are.

I hope some of these ideas will help. If you'd like more direct help, send me an email (the white envelope by my name) and we'll 'talk'.

Barbara

>Barbara,
>
>This is the SQL statement for the view.
>
>SELECT Transactiondetail.clinetype, Transactiondetail.cdetailtype,;
> Transactiondetail.ndetailline, Transactiondetail.caction,;
> Transactiondetail.nbeforetrx, Transactiondetail.ndetailamount,;
> Transaction.dtrxdate, Offers.cofferno, Merchantsite.csiteno,;
> Transactiondetail.ctrxid;
> FROM cyprom!transaction INNER JOIN cyprom!transactiondetail;
> RIGHT OUTER JOIN cyprom!offers;
> INNER JOIN cyprom!merchantsite ;
> ON Transaction.csiteid = Merchantsite.csiteid ;
> ON Transactiondetail.cofferid = Offers.cofferid ;
> ON Transaction.ctrxid = Transactiondetail.ctrxid;
> WHERE Transactiondetail.ccardatr = ?CardRegistry.cCardAtr;
> ORDER BY Transaction.dtrxdate, Transactiondetail.ndetailline
>
>When I issue the "USE lvTrxDtl NODATA" command from the command window I can clearly see that the statement is executed since the Running Query... window with the progress bar appears. The funny thing is that even though the CardRegistry table is not open it doesn't ask for CardRegistry.cCardAtr as it does when I issue the "USE lvTrxDtl" command. What exactly is it executing?
>
>The tables are fairly large. The transaction table has roughly 750,000 records and the transactiondetail table is about twice that.
>
>Would there be a significant difference in execution time if I used a cursor instead of a view?
>
>Thanks for your help
>Gabriel
>