Update views constantly on a network database

[phita]

Hi All,

The whole concept of views for SQL database is new for me. I have read a lot about but still have a problem with it.

I am converting a network application, which worked nicely in the past with VFP database and tables to SQL tables with views. I have a transaction table of 1 million records (29 fields).
The problem is that any time I open a form and the view is in the data environment, it does the whole SQL select again. This takes very long time (about 2 minutes) and no one can wait so long just to open a form.
I have tried to load with no data, which is naturally fast, but it's not really helping as I need to view previous data when data is entered in the form (balances etc.). Furthermore, when the user has saved his entry, and stays in the same form, the data is not updated. i.e the view saves the data to the original SQL tables, but it does not update itself with other users' new transactions. I am using local views. Should I use remote views?

In a nutshell: How is possible to work with a view the same way I used to work with a table, so that it will updated all times.

I am a bit lost here. Could you share some experience with me?

Thanks in advance

Phita

 

[fluteplr]

You need to understand the differences between client server databases (SQL Server) and File Server Databases (VFP)

In a client server enviroment the idea is to only query the records that the user needs to see at that moment.

In order to do that you need to look at using parameterized views, which allow you to select which records you need to retrieve.

When you say you need to see the previous data, what exactly does that mean? I seriously doubt the user needs to llok at all 1 million records each time.

You also need to have a look at the requery function which will update your view with the latest data from the sql server database.

 

[Mike Lewis]

Phita,

Flutepir has nicely described the strategy you should be adopting towards client/server systems. It is definitely a mistake to fetch an entire table from a server each time you open the table.

However, I am a little confused about what you are doing. When you say you are using "SQL tables", do you mean the tables are on a back-end database such as SQL Server or Oracle (strictly speaking, there is no such thing as a "SQL table"; SQL is a language, not a table format)?

If you are using a back-end database, I don't understand how you are using local views. That simply wouldn't work. You should be using remote views (or perhaps SQL pass-through).

The main thing to keep in mind with remote views is that the views should be filtered so as to return only the records you need. Use a parameter along the lines Flutepir suggested. Then open the view with NoData, and use REQUERY() when you want to fetch the specific records that match the parameter.

Hope this helps.

Mike



Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[phita]

Thanks all for your help.

Phita.