What Advantage Do Local Views Offer Over Tables? 2

[drosenkranz]

Hello There,

I guess I'm just one of those "thick headed" VB guys but I'm having some trouble grasping the up and down sides of using local views vs. using the table and row/table buffering.

Since an SQL Select statement executed in code creates a "read-only" cursor, it isn't any use to me to create cursors on the fly to use in a data entry/edit form. I have used a parameterized version of the view with some success. I change the variable and issue a Requery and I still have an updateable cursor-

This works but why not just use the table and row/table buffereing with a filter rather than the local view?

I haven't rolled out any large scale multi-user apps yet but I have a feeling that is where I'm going to "get my education" in VFP performance design. I feel like a deer standing in the headlights of an oncoming truck! I can hear it coming and I can see it coming- but I just don't know how to get out of the way!

Can anyone give me the basic design/performance guidelines (choosing between tables and local views) before that truck hits me?

A simple philosophical overview on performance would help a great deal.

Thank you again for all your help.
The 2nd mouse gets the cheese.

 

[TomasDill]

Hi!

1. SET FILTER-ed record sources work quite slow.
2. View returns a little record set that is better to handle by users than look through thousands of records - messy. Good interface do not ever allow users to see more than 1000 records simultaneously.
3. The main reason - plans to move to SQL Server or other remote server. That time you will require throw away all commands that do use tables directly. When you have views, you're much closer because you just change these views to remote views.
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[GrigoreDolghin]

Hello.

Suppose that you use only local views in your app. Even if the views are SELECT * FROM table.

Now, assuming that you have only local views, is easy to see that if you define a connection to a remote data source, then rename the local views and create remote views with the same name as the local ones, the app won't know that it isn't a local one anymore. The app will be a client/server one, without altering a line of code!

Basicly, this job is done by the MS SQL Upsizing wizard and the Oracle Upsizing Wizard. I've made an MySQL Upsizing Wizard. But I'm pretty sure if you'll study the source code delivered in the XSOURCE directory, you'll figure out how this work.

Now, for the finish:

If you are using only views in app, the the conversion to a client/server app can be done in no time. (minutes).

Hope this helps. Grigore Dolghin
Class Software
Bucharest, Romania

 

[TomasDill]

Hi!

Grigore, on the other side, the waste amount of views cause application slow down a lot because large database. In addition, there is an sequrity problems with remote views - for best sequrity database, connection and all views should be created in run-time that takes a lot of time too. Take a look to the MoreOnRemoteViews at the fox.wikis.com site. All is depended on the size of the application. Large applications are very hard to maintain with large number of views. When moving such applications to the remote server database, better to re-create them again with combined use of remote views and SQL Pass-Through functions use. Remember also that interface of the client-server application is also quite different, if you did not made it from start.
For small applications it is just fine.



Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[madcomputer]

hey hey dont be like that
there is no other way to do it in vfp
but remote views

 

[fluteplr]

Vlad, you do not need to create the views at runtime only the security for the connection. I just copy the base dbc to a temp directory and then use that one. When the user exist the app, the temp dbc is deleted. The process takes less than one second. SQL passthrough is highy overrated. And yes I program large systems.

 

[TomasDill]

Hi!

madcomputer, did you read the article I mentioned at the fox.wikis.com? THere are also such functions as SQLConnect(), SQLStringConnect(), SQLEXEC() and similar to work with data through ODBC without any database and view.

fluteplr, what you meant as SQL passthrough is highy overrated? I did used it in many my applications and can say that I like it much more than remote views. SQL Pass-Through recordset could be made as updatable for ANY data and ANY query, and it will work similar way as remote view. Thus I consider it is more flexible and in many cases it is more beneficial for large applications from the performance point of view. Opening a view from database with large number of views takes a lot of time even with NODATA option, I know this from my experience.

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[ChrisCarroll]

To throw in a comment on using tables instead of remote views:

No, you're not a thick headed VB developer - your comments are entirely valid, it is easier and faster to develop with tables, there are plenty of working apps out there that work that way, and furthermore I'd guess there are rather more FP apps out there using tables than local views (anyone like to disagree!?)

The big reason for choosing local views would be advance planning for a move to a server back end - although I take Vlad's point about UI design, which kind of implies that for apps with big tables you should design 'as if' you're running client server, even if you're not.

So no, I wouldn't be afraid of using tables at all, unless you expect to run to several hundred users, or you have some other reason for wanting a server backend.

 

[fluteplr]

Vlad,

When you are using sql passthru you have to hand code all inserts, updates and deletes, or spend the time to do all the dbsetprops to make it work (wait for it) just like a remote view. I use remote views except where I have a situation that does not allow them, and have written generic transactional update code that works in about 95% of all my scenarios and has not had any more than a minor tweak to the code in over 3 years. This allows me to code most data entry screens without even worrying about updating the data back to the server. Including child, grandchild, sibling and other relations.

 

[TomasDill]

Hi!

fluteplr, NO. This is very simple. With SPT you query data using your own function. For example, RunQuery('CustomersList','CursorName',.T.{make it updatable},{parameters for Filtering},). The function look up to the queries list table where all queries are stored, run it and take reault into the alias with name passed as parameter, than make it updatable if needed. Nothing complex. In the UI you work with such cursor by the same way as with updatable remote view - no real difference. In my App I always have such function and open record sets ONLY using that function, even when back end is a VFP database. Later, when I move to SQl Server, I need to change only this function, not the entire App. More, I can make it universal, say, when installing application, user can choose what type of database they use - VFP, SQL Serevr, Oracle or whatever. Beleive me, I already made aplications using this way withou a single problem related to hardcoding SQL commands.

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs