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.

 

[stlrain95]

I have this same issue. I have an application on our server and it is very slow. When it is on my local machine it runs much faster. I would suggest that you put into SQL server platform because it is more robust and capable of running off of a server much faster.

 

[skoko]

Mine is very fast even on server. The problem is when I put on remote server (still LAN)

 

[fluteplr]

Basic Database lesson seems to be in order here.

VFP is a file based database engine. ALL the work is done on the client machine. Therefore large amounts of data need to be transfered from the server to the client when you do queries.


SQL Server is a client server database, most of the work is done on the server, therefore less data is transfered to the client machine.

Here is the example I use when I teach.

You have a used car lot and need to know how many green cars are in the lot.

VFP No Index.

Someone goes to the lot and drives every car past your window, you count how many green ones go by.

VFP With Index

Someone goes to the lot and drives all the green cars past your window you count the them as they go by.

SQL Server or Other Client Server.

You call the guy at the lot and tell him to count how many green cars there are. He calls you back and says "There are 45."

As you can see in the Cient Server model much less data is transfered around (assuming you build your queries properly.)

You are having issues because your remote server while still on a lan is probably connected at a fairly slow speed.

 

[skoko]

Fluteplr, WOW, thank you very much. That was very good example.

So, I must convert all my data to SQL. Well, that will be huge job to do. No more, seek, replace, flush.

 

[baltman]

Correct me if I'm wrong, but can't you make the server run the querry using SQLprepare()?

Brian

 

[skoko]

Baltman. Sorry, u lost me, can u explain little better?

Thanx.

 

[baltman]

I don't really work in a network environment but I've played around with this code a little bit. Check out the sqlconnect() entry in the help file. Basically, I believe that you can use "sqlconnect()" or "sqlstringconnect()" to create a remote connection over a LAN or WAN and then issue an "sqlprepare()" command which "Prepares a SQL statement for remote execution by SQLEXEC" and then make your actual SQL request using the SQLEXEC().

Check out this web site for connection strings:

http://www.connectionstrings.com

The only type of connection that I have sucessfully played around with is a VFP->Access connection:
&&&&&&&&&&&
targetfile="c:\data\northwind.mdb"
targettable="employees"
lnConnHandle = SQLSTRINGCONNECT('DRIVER=MICROSOFT ACCESS DRIVER (*.MDB);DBQ='+"c:\data\northwind.mdb&quot
lnResult = SQLTABLES(lnConnHandle, 'TABLE')
SQLPREPARE(lnConnHandle,'SELECT * FROM "employees"', 'MyCursor')
SQLEXEC(lnConnHandle)
sqldisconnect(lnConnHandle)

 

[GriffMG]

If you have an application that is coded and working, you might be better off using something like VNC to access it remotely.

VNC is free, from AT&T and is a bit like pcAnywhere, or NetOp Regards

Griff
Keep [Smile]ing

 

[AChiado]

Baltman

your use of sqlprepare have no advantage.

sqlprepare simply compile your statement in the server for a fast repeated execution.

if you run

cSql="UPDATE MyTable SET field1=999 WHERE field2='XXX"
sqlprepare(nConnHandle, cSql)
sqlexec(nConnHandle)

you only cause a double transmission of commands over the lan.

One for sqlprepare and one for sqlexec.

In this case is better

cSql="UPDATE MyTable SET field1=999 WHERE field2='XXX"
sqlexec(nConnHandle, cSql)

so you pass your statement to your database server that compile and execute it all in once.

You can take advantage of sqlprepare when you have to execute the same statement more than once like in this case:

select MyLocalCursor
cSql="UPDATE MyTable SET field1=?nMyValue WHERE field2=?cMyKey"
sqlprepare(nConnHandle, cSql)
scan
cMyKey=mylocalcursor.mykey
nMyValue=mylocalcursor.myvalue
sqlexec(nConnHandle)
endscan

In this case your DB server have to compile your statemet only once and than it have only to execute like a stored procedure

Skoko:

If you want Sql Server you have to pay a lot for licensing

You can try InterBase Open Source or (better) FireBird
They are free, very powerful and very simple to connect throught internet. They works on Win95, 98, Me, NT, 2000, XP, Linux, Solaris...

I worked with Ms SqlServer, Oracle, DB2 in the past but now I use FireBird for my new applications. I Only had to buy the ODBC driver becouse the OBDC Open Source is not so good.

Thake a look at

http://www.ibphoenix.com/

and

http://firebird.sourceforge.net/

Andrea C.P.
Italy

 

[Gandalf23]

A tip. Having an anti-virus program such as Norton anti-virus or a screen saver on a server can also slow down the application enormous.

 

[Leland123]

Hi Tnx,

Hi Ron,

I have written a Visual FoxPro app that is a front-end to a postgres back-end database. The app was written to provide an example of how VFP could be used in a client/server environment. The application is free, includes all source code, and the postgres ODBC driver is also included. I have the database used in the example app on my web server, so once you install the example app and setup the ODBC drive you can connect to my postgres server on my RedHat Linux 7.1 box to try it out. You will need VFP 7 to run the app. The app can be downloaded at:

http://www.smvfp.com

When you enter the site, go to option 19.
Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[skoko]

AChiado company already have licence for MS SQL.
GriffMG I dont think solution like VNC gonna like our users.
Gandalf23 thank you, now is twice faster.
Leland123 This url doesn't work. Who is Ron?

So, situation is:
Five servers in five different cities connected in 100mps LAN each with 15-50 users.
If we put Fox DataBase on each server, runs very fast. Problem is, how to make same data on each server, in another words, how to update between servers.

Any idea? Maybe different aproach?

 

[Gandalf23]

I think you must first ask yourself how do you want it to update (directly? once a day? once a week?...etc.) and why you want all the data to be the same on all servers?

Then we can think of a solution

 

[skoko]

All users need to share same data. If one user change something every users need to have that changes.

 

[Gandalf23]

This is very tricky if you use 5 servers. Do you have a reason not to let the users connect thru one server?

 

[skoko]

No, I can let users to connect to same server, but, that gonna be very slow.

 

[Gandalf23]

One of our customers has 4 building with 5 servers (citrix) from which 1 is the main. Everyone using our appliciation logs on the main server.

Considering your situation (in five cities) it would indeed be slow if they connect thru 1 server.

I would love to work on such a project. One question:
Do you want it to be updated directly? I mean if a change is made the everyone in the 5 cities can see the changes. Or is once a day enough?

 

[skoko]

If change is made, everyone in 5 cities MUST see the chages.

 

[chpicker]

Wow...that's a tough situation!

An ideal situation would be to have 5 servers, one of which is the master, and the other 4 just mirror it. Any changes to the 4 slaves get sent to the master, and any changes to the master get broadcast to the slaves.

Is there any server software that can do this? If there is, then you can just have your app access any of the given servers and not worry about it.

Theoretically, anyway...

Ian