Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VFP9: Heavy traffic/transaction

Status
Not open for further replies.
Kurtwood

Kurtwood

Programmer
Sep 7, 2002
39
BS
A potential client wishes an app to handle 50,000 transactions in 30 minutes with 20 remote users/sessions using TS over broadband.

If all is well with the server, communications, etc, can VFP9 handle this amount of transactions and the short time? The program has not performed well in Access and SQL server is not desired because of the cost.

Data integrity is a must in that short time-frame.

Any thoughts would be appreciated.


Kurtwood L Greene
The Bahamas
 
Sort by date Sort by votes
Mike, thank you for your response and valuable suggestions.

There will be 9 fields approx. 100 in length.

Thanks again
 
Upvote 0 Downvote
Kurtwood,

As Mike has already shown, there are a lot of variables at play here. However, Visual FoxPro can handle tens of millions of transactions in the time you are allotting here on a decent machine. This of course does not take into account different users trying to access the same records that are being modified. But run the following code (takes 10 seconds to complete) to give yourself some idea of how fast VFP can begin and then commit a transaction (feel free to uncomment the 3 lines of code I've commented to provide for table level buffering).

Code: 
CLOSE DATABASES all
SET DEFAULT TO SYS(2023) && temporary directory
CREATE DATABASE MyTest && deleted later
SET DATABASE TO MyTest
CREATE TABLE tblTest (Field1 c(10), Field2 c(10),;
    Field3 c(10), Field4 c(10), Field5 c(10), Field6 c(10),;
    Field7 c(10), Field8 c(10), Field9 c(20)) && deleted later
APPEND BLANK
*!*    SET MULTILOCKS ON
*!*    =CURSORSETPROP("Buffering", 5, "tblTest")
LOCAL lcTemp, lnStart, lnEnd, lnTotal
lnTotal = 0
lnStart = SECONDS()

DO WHILE (SECONDS() - lnStart) < 10 && Run it for 10 seconds
    BEGIN TRANSACTION
    lcTemp = SYS(2015)
    REPLACE Field1 WITH lcTemp, Field2 WITH lcTemp, ;
        Field3 WITH lcTemp, Field4 WITH lcTemp, Field5 WITH lcTemp, ;
        Field6 WITH lcTemp, Field7 WITH lcTemp, Field8 WITH lcTemp, ;
        Field9 WITH lcTemp IN tblTest
*!*            =TABLEUPDATE(.T., .T., "tblTest")
    lnTotal = lnTotal + 1
    END TRANSACTION
ENDDO

lnEnd = SECONDS() - lnStart
?"Seconds Run: " + TRANSFORM(lnEnd)
?"Total transactions: " + TRANSFORM(lnTotal)
?"Approx. Transactions possible in 30 mins: " + TRANSFORM((lnTotal/lnEnd) * 1800)
CLOSE DATABASES all

*!* Clean up after ourselves
ERASE MyTest.dbc
ERASE MyTest.dct
ERASE MyTest.dcx
ERASE tblTest.dbf

boyd.gif

SweetPotato Software Website
My Blog
 
Upvote 0 Downvote

Craig,

Interesting piece of code.

For what it's worth, I got results varying between 40 million and 42 million (transactions per 30 mins) without buffering, but only around 25 million with buffering.

This was on a local hard drive. It would also be interesting to run it across a network.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
Upvote 0 Downvote
Have you looked at MySql. It is a free GNU type licensed database engine that is very robust and scalable. I would avoid foxpro tables over a broadband connection. The biggest limitation you have is the reliability of the broadband connection. Broadband is in some cases unstable and prone to ups and downs. So one minute you may have your desired transaction rate and not achieve due to have traffic. It also depends on whether you have a business class broadband or a standard home user type account which is more likely a shared bandwidth connection. But anyway I would focus on finding a database that is reliable and secure. You can also use Microsoft's free sql server with limitations. (Sql Server Express). I would take a look at MySql as it has not limitations and is free. Here are some links to help you out. Don't make decisions based merely on cost. But rather find the right solution level then find the free or cheaper ones to meet the need, ie MySql.


Regards,

Rob
 
Upvote 0 Downvote

Don,

That wasn't my experience. I found VFP 9 produced around 40 million, compared to 7's 15 million.

There are many factors that could affect the result. For example, I would guess that having the status bar switched off would make it faster (because VFP isn't displaying progress messages).

What readings did other folk get?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
Upvote 0 Downvote

Interesting.

My VFP6 produced on average 10.8 million transactions per 30 min. without buffering, and about 10.35 million with buffering.

 
Upvote 0 Downvote
robsuttonjr,
MySQL is "free" if you make your own code open-source under the GPL license. But if you sell or distribute a product with MySQL without revealing your code under GPL, then you should get the commercial license.

That said, I'm not sure which license to use when MySQL is used in-house within a company. Their site says, "To anyone in doubt, we recommend the commercial license. It is never wrong."

In any case, Visual FoxPro can still be the front end to a SQL server database.
 
Upvote 0 Downvote

OK, same machine, same code, same VFP6, but with SET STATUS BAR OFF and SET TALK OFF.

With this set up, results averaging to about 55.6 million per 30 min. without buffering and 30.7 million per 30 min. with buffering.
 
Upvote 0 Downvote
myearwood said:
Wouldn't a more realistic test involve insert-sql and buffering and only doing the begin transaction, end transaction around the tableupdate line alone and checking aerror to rollback or end transaction?
Click to expand...

Absolutely. I thought about putting the whole thing in a Try...Catch...Finally block with Rollback in the catch. But, I was feeling lazy and felt that the code was good enough to give Kurtwood something to play with for some benchmarks. More information on the operations to be performed and other factors would be needed from Kurtwood to set up something closer to what will actually be happening in the app (inserts, deletes, replaces, etc. - server specs - server load). There's a lot that is unknown here.

However, I can't think of a scenario given the information we do have here where 50,000 transactions will be any problem for the app. If the users will be accessing the same records that other users are modifying that could slow it down significantly... but even with tons of collisions, I can't really see 50,000 transactions in 30 minutes being a problem for a VFP app.

boyd.gif

SweetPotato Software Website
My Blog
 
Upvote 0 Downvote

Mike, that's exactly what I am saying.

I just rerun the tests this morning for 'control numbers' - same.

I didn't mention one intermediate test, though - with SET SATATUS BAR OFF and SET TALK ON. When all the talk was output right on the screen, the number of transaction in 30 min. fell to mere 85 thousand (not million!) on average!

And another one I tried this morning and was very surprised: with SET SATATUS BAR ON and SET TALK OFF I get slightly better results than when both are off - 56.6 or so on average.


 
Upvote 0 Downvote
What's wrong with my hardware? I ran your program, Mr. Boyd, on my W2K, P4 1.6GHz, 512MB machine and I can only get up to a little over 15 million per 30 min.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Scott24x7
  • Locked
  • Question
VFP9 and OpenAI 1
Replies
5
Views
829
Scott24x7
Scott24x7
ggreen61
  • Locked
  • Question
NAS for Shared Files Access via VFP9
Replies
3
Views
333
Drainie
Drainie
BugZap13
  • Question
Quickbooks QBFC16 SDK 64 bit usage from VFP9/VFPA
Replies
1
Views
162
GTGeek88
GTGeek88
mohinder singh
  • Locked
  • Question
IN VFP9 EXE FLICKER (BLINKING)
Replies
1
Views
254
Scott24x7
Scott24x7
USMA99
  • Question
VFP9 to run external command as admin and capture exit code, WScript, ShellExecute 1
Replies
5
Views
789
USMA99
USMA99

Part and Inventory Search

Sponsor

Back
Top