General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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
>Gabriel,
>I've never seen it take more than a fraction of a second to load a view with the "NoDataOnLoad" set. Can you repeat this in a PRG and from the command window? If it's in a form are you sure it is this command?
>
>Are these very complex views loading multiple tables?
>
>Are your indexes set to optimize Rushmore (or SQL-Server if that's where your data is)?
>
>Barbara
>
>>When attempting to use a view without downloading data either by means of the nodata clause in the use command, or by setting the cursor's NoDataOnLoad property to .T. when the view is added to a form's dataenvironment, the view is first executed.
>>
>>When dealing with large tables this can take a long time (about 3 to 4 minutes in my case). Is there any way to prevent the execution of the view.
>>
>>I could create a cursor instead of using a view, but as I understand it, a view is faster than a cursor.
>>
>>Any ideas or suggestions would be appreciated.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only