sql or not 4

[skoko]

Hi,

First, sorry for my Tarzan English. I'm gonna try to explain best I can.

I'm in process of creating huge applications. More than 30 tables, five of them is 200mb each.

Application is for local college with 5 different campuses in 5 different citys. LAN 100mbs.

Now, when we test application in one campus (15 users) work fine and really fast. Everything is almost in real time. Problemm is when we try to acces to application from distanced campus, for same operation takes 3 minutes.

I try to put database on remote server and to access trought network with simple USE and take 2 seconds just for that.

Anybody have experience with something similar? Maybe SQL Server and moving data to SQL instead of using VFP database gonna help?

Tnx.

 

[skoko]

My idea is:
Five servers gonna work with Fox DataBase. Every time when user try to reach some record, application gonna chech with SQL server if is any changes on this record, if is, update, if is not continue to work. When, if, changes is made, application gonna update Fox DataBase and SQL database.

Any better idea?

 

[skoko]

p.s. SQL is 6th server.

 

[Neil Toulouse]

We have a large WAN running a non client/server VFP app and was painfully slow. We ended up using Microsoft's 'Terminal Services' and as such ran the app only on the server via its multiple seesions, and it's great!

 

[baltman]

I connect remotely (over a cable modem) from home to my work using "terminal server" and I also would vote for this solution if you don't mind having users jump through the extra hoop of loging in.

Brian

 

[PTCruiserII]

Don't give up on FOXPRO too soon - SQL Server also has its tradeoffs.

1) Much more complex environment.

2) Data base must be tuned and maintained continually - by a high priced DBA.

3) You still have performance problem of having one server handling 5 cities. So the first guy you hire to set this up will start talking about 5 servers linked somehow and replicated (very complex).

4) Foxpro is essentially free - SQL Server mucho money.

5) SQL Server will require complete rewrite of your Foxpro app to get real benefits of server speed.

Recommendation: If you want to get ahead in your career - go with SQL Server and work diligently to make this into a multi million dollar project that will take 2 to 4 years to implement. Investigate .NET and move away from FOXPRO and hire a team of MSCE gurus. For your size system (200 users) you should be able to justify a 5 million dollar project and hire about 20 consultants. You will be the CIO in 5 years.

If you continue to use Foxpro, your users will have a cheap system that works slowly but works now and will work faster in the future if you spend a little time analyzing information access and user interface. You will remain where you are and will never get ahead in your career.

I should know - that's what I've done for the last 10 years and I will never get ahead - but somehow I get the job done.

 

[skoko]

PTCruiserII, what can I say. U cover everything ))))

Well, maybe I'm gonna try to finish project with remote views instead of SQl Server.

How I c, remote views can pull only what I need from remote server, so, this is what sql server do.

 

[Mike Gagnon]

skoko

We have 2 Citrix Metaframe servers(NT 4 Terminal server) in Montreal, being accessed with ADSL 1.5mg lines, from 3 different states and one using dial-up from home (mainly me for maintenance). But the servers are super machines (DUAL PIII with 1.5g of RAM), and we rarely get the comment that it's slow.

 

[PTCruiserII]

First things first - find largest query one your system - every system is built around one killer transaction that kills system response.

Next - make sure that this transaction is done by only one user at a time. How you may ask? By creating a file on the server that just controls this one bad transaction - when a user wishes to save this transaction, his program will try to LOCK this empty table. IF he can't lock it, then try again in 5 seconds until it is locked. Once locked, this program is guaranteed to have clear path to doing this transaction. Once transaction is done, unlock this table.

Doing this will immediately clear out most and potential crashes.

Next - every night do a rebuild of all Indexes and PACK of all DBF's. This will elliminate memo problems.

Next - slim down indexes to minimum on large tables.

Next - static tables can be downloaded once a day to local server or client PC (downloading a 10 meg file once a day is smart - and downloading a 10 meg zipped file and unzipping it as part of startup is even better).

Next - this is a real time saver and what makes your system very robust - take the largest and most critical DBF and divide up its primary key so that you have 10 or more tables instead of one. EX: primary key is numeric ID - so create 10 tables with exactly the same format but name is TABLE0.DBF, TABLE1.DBF, TABLE2.DBF etc. and then if the primary key starts with 0 you know to use TABLE0.DBF etc. This sounds dumb but having ten 25 meg DBF's is better and safer than 1 250 meg DBF.

Next - denormalize tables to speed up queries by having few or no joins.

This should get you going - ofcourse remember that you will never get ahead in your career using FOXPRO this way - but you will get something done.

 

[Leland123]

Hi Tnx,

I have written an application in Visual FoxPro 7.0 that acts as a front-end to a back-end postgres database server. The application was written as an example of how to use SQL pass throughs in a client server environment. Also, just this morning I added an example of using a parameterized view to access the postgres database. It's really easy to do. It allows anyone using the Visual FoxPro front-end to access the back-end postgres database over the internet. Although the Visual FoxPro application is written to talk to a postgres back-end server, it could be easily modify to work with about any back-end that has an ODBC driver including MSSQL, mSQL, Oracle, DB2, Informix, Sybase, MySQL, etc.

This is neat for anyone that can't afford an expensive enterprise class database, as both MySQL and Postgres are open source which means they are free.

If you would like to take a look visit:

http://www.smvfp.com

After entering the site go to option 19.

With a client/server approach you would be able to centralize the entire school so everyone would be accessing a single back-end server via TPC/IP. The centralized server should of course be connected to the internet with a firewall that had the port used by the back-end server open.
This would yield many other advantages as well, like centralizing security so all username/passwords would be issued at one point to give just one example.


Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[skoko]

Results:

SEEK, SCAN: 186 seconds
CREATE SQL VIEW REMOTE: 6 seconds


Guys thanx for helping me.

 

[baltman]

Skoko,

Why don't you post an example of your code so that people who are looking for solutions to similar problems can learn from your experience.

Brian

 

[skoko]

First, I create my own ODBC Data Source,VFP database type with path to the remote server.

After that I create connection:
CREATE CONNECTION name DATASOURCE "name"

and remote view:

OPEN DATABASE \\xxx\xxx\xxx.dbc share
CREATE SQL VIEW name REMOTE CONNECTION name AS SELECT * FROM table_name WHERE table_name.field = ?variable

 

[FoxRookie]

We have a huge Fox App - some tables have over 2 million records in them. For many of our customers we have a DB server hooked to as many as 11 citrix metaframe boxes and client's are spread out over serveral counties (as many as 100 clients simultaneously).

We found that client nic cards were a major issue with speed. Some clients with great latancy had bad or $15 nic cards. Others had the card set to auto rather than full duplex. Apparently on auto, there are times the card can drop to 10MB/sec. We now recommend that all client nic cards be set to full duplex and our app runs reasonably fast.

 

[fruityBoy]

Another (slightly different) method that I've used in the past is DCOM objects using Microsoft Transaction Server.

Basically, using ADO as the transport mechanism, you can call remote foxpro DCOM objects to process and return data, and, the good thing is that the DCOM objects, because they are foxpro can use native foxpro table access.

I basically dumped most of the front -end processing code into the remote objects, and re-coded the front end to accept ado(you could use xml as well).

so basically, you can instantiate your remote foxpro program thus:

loremote = createobjectex('remote.remote_cls','server1')

(server 1 is the machine name, remote is the dcom dll, and remote_cls is the class contained within it which contains the methods that you want to call)

you can then retrieve data (in this case an ado recordset) like this:

lodata = loremote.retrieveStudentData('SMITH, JOHN')
you can then convert this to a cursor using your own, or microsoft's functions:

lccursorName = rs2dbf(lodata)

now you can manipulate the data, and to save the information:

dbf2rs(lccursorname)

lostatus = loremote.saveStudentData(lodata), where lostatus could be a recordset containing update status information, such as save failure / conflict. Note that with backend SQL databases, the data is off-line, therefore you cannot use record locks etc.

My company used this method succesfully to run a warehouse 300 miles away from the head office of a company. The server sends and receives foxpro information to a portable barcode scanners and works very fast.

Course, ado is just one transport mechanism, you could use xml also, but I've found ado to be fast and it's easy to manipulate the data objects in their own right.

Regards

Simon

 

[LCannon]

skoko,

What you are looking to do, if not all ready done, sounds quite similar to what we are setting up where I work. We currently have a main server that houses the main data and a two servers that replicate some of the tables in the SQL server so our customer service reps. can get to and edit the data.

We had to set it up in a hurry so it's not "pretty" and done, yet. But basically we use SQL Replication with Pull subscribtions. Got all the help from the online help books that come with SQL 7 and SQL 2000.

Planning on setting up a Merge replication to speed up some data access times but it's working like a champ right now.

Hope this helps....
Jason

 

[skoko]

But, that means that you need to have sql server on each server.

Like somebody say: Mucho money

 

[wgcs]

Have you investigated the DCOM solution?

For your situation, this is what would work:
All clients use your current method for Seeing information (you seem to think it's quick enough how it is.)

For updating information (ie. adding a new record, which is what gets into the RecNo() issues), what it means is that you create a VFP ActiveX object that knows how to add a record... The data for the record gets passed to it as parameters.

Register this object on the server (in this case, on the REAL MAIN server). Register this object on each client and set it to run the object on the main server. When the clients want to add a record they CREATEOBJECT() this object, (which really creates the object on the server) and call the object's AddRecord method (or whatever you call it) and it gets added on the server, and then has clear rights to the proper recNo().

When the additions get propagated back to the five cities, this new record will get propogated to the "local" servers' data mirrors, and they stay synchronized because the main server sends the same updates to all the distributed "local" servers.

 

[wgcs]

If you don't like DCOM, you could use Web services or ASP and programmatically call the webpage, or FoxISAPI and, again, programmatically call the web page ("roll your own" web services), or even have your VFP program on the main server listen on a particular port (and thereby bypass the webserver altogether).

I can point you to examples for FoxISAPI and "Listen on a port" if you're interested (I'd have to go look them up... they're not on the tip of my tongue.)

 

[skoko]

wgcs, good idea, it is something to think about.