Earlier I said that there were a a number of packages available for using Visual Foxpro with a Web Server, and I told you that I felt that they were all outdated and not necessary. Well today we will talk about what you should consider using Visual Foxpro for, and what not to use it for in a web page.
Here is where you will learn why. Most of the packages on the market basically replace HTML with some extension that then calls a Foxpro prg to create the HTML that is sent to the client. This is about the most inefficient use of resources that I have ever seen on a web server.
Microsoft has done some great things with IIS, and every new release seems to improve on the previous. It's a shame that both the good and the bad get improved upon, but that's for another blog.
IIS supports scripting through ASP (Active Server Pages). ASP supports a number of languages such as VBScript, C# etc. With these scripting languages you are able to embed them directly into your HTML, and the server will process the code in line as it sends the HTML to the client to be viewed.
What makes this better than the Foxpro method is that current each of the programs for Foxpro that are on the market that I have worked with have required a "Foxpro Control Panel" to be running. Basically this is a Foxpro program that runs either as a service, or as a desktop program that sits and waits for a web hit, when it received the hit from the isapi dll, it then processes a PRG that creates HTML and sends it back to the isapi which then sends it to the client.
There are several problems, first each control panel can only run a single hit at a time, yes you can have several control panels running, but each one can process only one hit at a time. The next problem is that Foxpro is not optimized or coded to take advantages of multi-processor machines. So now you have to do some fancy configuration to tell each control panel what processor you want it to run on, and it's stuck on that processor.
Next, when there are no hits, the server's memory resources are still being consumed, as well as processor cycles are being consumed while these processes wait for the next hit.
And last off, if you happen to cause a fatal error in one of your Foxpro programs, its possible to shut down the entire panel and shut down you web site. Granted most of them have very good recovery methods, but its not a sure thing.
With all that said, they do have one absolutely positive feature about them. They all take the incoming string from a user's post from a web form and parse it into an easy to work with variable set. This is one thing that I have to say is the single most useful thing about them. The other thing that's good is that they make it possible to link Foxpro functions to various buttons and links on the web site, but this is actually less impressive.
What I am going to be showing you through this series of blog entries is how to combine HTML, and ASP scripting with Foxpro DLL's to accomplish all the same things that the other packages accomplish, and still preserve the resources of your server so that you are able to handle a much larger traffic load.
If you are getting skittish about merging multiple languages into your development process, don't worry. The scripting languages are minimal, and easy to use. I reserve the scripting to strictly formatting the HTML, and some basic data calls. Visual Foxpro will still handle any of the complex work, and all the business rules. And we may introduce some javascript into the mix just to make things interesting, but for the most part I will be avoiding it as well.
So how do you determine what goes into Foxpro, and what goes in to HTML/Scripting? This is easy, if it relates to formatting the page it goes into HTML, if it relates to the business rules or the data it goes into Foxpro. Well not entirely, but we will discuss those as we approach them.
First off, all through these blogs I will assume that you are an experienced programmer, and have a good understanding of Visual Foxpro, and it's tools. I won't be discussing how to create a table, I will just tell you to create one. I won't be discussing how to format a SQL Select command, I will tell you what fields it needs, and what conditions to put in the where clause.
When it comes to the ASP Scripting, I will for the most part be using real code, but frequently I rely on suedo code to get my point across with examples, I'll try to differentiate the two. There are a number of sites out there that go into detail to teach you these languages, and I don't want to waste keystrokes talking about the details of the language. (Yes I know I waste a lot of keystrokes on other things, but it's my rambling blog so I can type what I want :)) )
Every book you read uses different types of examples for their programming project, we will be no different here. I've been trying to figure out what would be a good project that has REAL business practices, but that won't take a lot of development. I'm also trying to figure out what features I need in one of my projects that would fit into this as well. I'll get back to this because I need to do some thinking about it.
SQL Server vs Visual Foxpro
Let's take a vew minutes and talk about Visual Foxpro vs SQL Server. Age old debate right? Well perhaps I can put the debate to rest for you, or at least give you something to debate untill I give up and say "whatever" like a teenager too.
Now that Microsoft is releasing a free version of SQL Sever (MSDE, or now SQL Server Express), there is no reason not to at least consider using SQL Server. And keep this in mind as well, Microsoft has optimized IIS to work very well with SQL Server. Basically what they are doing is saying, "here's this awesome tool, and we are not going to give you an excuse not to use it."
I'm a 25+ year veteran Foxpro Developer and even I have been converted. SQL Server is all the great things from Visual Foxpro and none of the bad. They have optimized the code and the indexing so that it's both efficient and fast. But for me they have made two major things possible, the first is to be able to backup the system reliably with users still working. The second is that they have almost completely eliminated data and index corruption on reliable hardware. These two features alone should have you re-considering your thoughts of SQL Server.
Now, for performance; every Foxpro developer I know is convinced Foxpro can outperform SQL Server hands down, and in about 10 minutes I can convince them they are absolutely wrong in all but the most basic of applications.
For those of you convinced that Foxpro can outperform SQL Server, set up this environment:
SQL Server running on a web server accessed by a DSL connection
Now house your data on your local network
Create 3 tables in both places, one for names, one for address history, and one for order history.
Put 10k names, with at least 5 Addresses, and 15 orders each, so you address history will have 50k records, and you order history will have 150k records. Put all the applicable indexes on the Foxpro side. Now run the following select on both servers:
select names, address, orderhistory
where orderhistory links to address
and address to customer
and orderdate between 1/1/2006 and 2/1/2006
order by customername and orderdate
Assuming that you have populated the tables with the same data, and it's relatively random, you should receive a fair amount of data back.
What you will see is that the data you receive back over the DSL, and the data you get across the lan will take close to the same amount of time to access. Here is the reason why, Visual Foxpro uses indexes VERY VERY well, but it still has to load those indexes across the LAN which takes time. If you have any number of indexes the compound index files can get to be quite large, and often the index is actually more data than the actual query will return.
Now once VFP finds the records it needs to load, it STILL needs to pull that data across the LAN from the tables as well. Now in contrast, with SQL Server you simply send the server a question "I want all the records that meat this criteria from these tables, and ordered in this order?" and it replies OK, here are ONLY those records you asked for. So when you look at the amount of traffic on the wire you find its substantially less. When you look at workstation resources you also find substantially less processor time too getting more work from you workstation.
Now here's where this makes a huge difference, Visual Foxpro does a good job at caching indexes for the next time you access the data, BUT remember what I said about the Web being Stateless? Guess what, after that web page was built from that data, the table was closed, so the next request will need to do it all over again.
With SQL Server, that's not the case, SQL server cached as much of that data as it could, and now the next person that requests that data will have the data in memory making their query actually run slightly faster.
The other day I mentioned that Thor contained 8 gig of memory, of that 8 gig I have told SQL Server to use up to a maximum of 6 gig for it's self leaving 2 gig for IIS and the operating system. My customer's data all combined is just of 50gig. SQL Server runs just over 2.5 gig on average of memory used. What this says is that during the course of time, the users keep using the same 2.5 gig worth of data, and rarely hit older data.
Well this is true, my customers key in maybe 2-3 records per user per day, and over the course of a week, they will access those same records a dozen times each while accessing historical data maybe 2 or 3 times a week. As the records age they are accessed less and less. SQL Server realizes that and keeps only the newest information in memory for each customer.
Now what has Microsoft done with IIS that impacts this?
In IIS Microsoft has what is called Connection Pooling. What this basically does is simple, once a user has connected to SQL Server and gotten their data and closed the connection, IIS keeps that connection open for a period of time waiting for the next user that tries to connect using the same connect string which gets them to the same database/server.
If someone tries to connect before the pool is flushed of that connection, it re-uses the same connection from earlier making the connection instantaneous rather than having to go through the connect handshake again.
This process kicks ass when it comes to a web site that takes a large volume of hits per day such as Thor does. Just for your reference, Thor takes almost 35 thousand hits per hour, about 70% of those hits access the database server for at least part of the data. That's 840 thousand hits in a 24 hour period, or 588 thousand data queries per 24 hours.
Just so you know, I was working on a project written using one of the Foxpro Interface applications, and that company had 4 servers, and less than 10 gigs of data, and they could barely process 100k hits per day, and I was amazed until I started seeing where their bottle necks were.
Stay tuned, next I might finally get into what our project will be, and how to go about designing and laying out the system.