Wednesday, October 29, 2008

Visual Studio /nosplash Option

Sara's latest Visual Studio 2005 tip about the /nosplash option is really cool. I didn't expect it to improve startup performance as much as it did. I was amazed at how much faster Visual Studio fires up now.

Friday, October 17, 2008

SWFox: Retrofitting Client Server Access

I'm not much of a "live" blogger, but I thought I would post my raw notes from the first session I attended today; Retrofitting Client/Server access, by Toni Feltman. It was a good class, and one I attended in order to learn the obstacles Visual FoxPro developers face when moving to client/server, and how Advantage can better address these concerns. It was nice to see a handful of pain points mentioned when migrating to other servers that do not exist when using Advantage.

These are my raw notes and observations:

Toni M. Feltman - F1 Technologies
Roughly 28 attendees (20% of conf attendees)
Why moving to C/S
- tables too large, heard this a lot at the booth, Rick mentioned we should to show Advantage using a 6 or 7 GB table during our vendor session to drive home the point that we don't have a table size limit.
- security
- performance

Picking datasource:
- mssql
- oracle
- Advantage - she mentioned is one of the easiest ways to convert to C/S
- db2, sybase, others

Talked about the overhead in getting server set up, "tuned", hiring a DBA, etc. Need quick demo on how easy Advantage is to install and how it self tunes. Also talk about lower cost of ownership because of this.

Moving the data:
- Moving the data isn't necessary with Advantage. Our upsize wizard simply creates an Advantage .add that matches the existing DBC, it doesn't modify or import the dbf tables.

Remote Data Access Methods:

- remote views: maybe not the best, but the easiest, which we have seen with customers porting to Advantage. Those with remote views have been able to port to Advantage very easily.
- USE statment now pulls entire table to the server. Very poor performance unless you filter the data/view.
- don't have to write update/insert, normal data movement still posts records
- little more difficult to make the connection path dynamic

- cursor adapters
- Toni's favorite way to get to remote data.
- have to manually call TableUpdate to post changes
- easy to make connection path dynamic

- sql passthrough
- easy to make connection path dynamic
- stored procedure calls
- still have to pick a data access technology to execute them, but you then put all business logic in the procedures, the VFP app doesn't execute UPDATE or INSERT statements, the procedures do.

Coverage Log - logs what forms, tables, etc are used. How often lines of code are hit, etc. Basically a coverage profiler. Built into VFP. Could be useful when troubleshooting or when identifying areas of app that need tuning or have data access code that needs to be modified.

Showed cool form usage log. Windows hook to an event for formShow. Keeps log of forms used by the application. Not as nice as a full usage of menu items, but this is a quick an easy approach to get a high-level overview of form usage.

Connection sharing is important. By default you don't get this. Need to save connection handle and reuse it if you don't want to be using a lot of connections or connecting/disconnecting all the time.

She stressed remote servers don't have the concept of EMPTY(), which can make dealing with NULL values and empty values a bit of a pain when migrating to client/server. Advantage does support the EMPTY expression, however.

Monday, October 13, 2008

Southwest Fox 2008

I will be attending Southwest Fox 2008 this week with a few colleagues. If you will also be in Mesa be sure to stop by our booth in the exhibit area and say hi. In addition to product demos and answering Advantage questions, we will also be raffling off a Magellan GPS towards the end of the conference.

There will be two Advantage-specific sessions at Southwest Fox this year. There will be a vendor session presented by one of our Systems Consultants, Chris Franz. In addition there is a class titled Advantage Database Server for Visual FoxPro Developers, presented by Doug Hennig.

If you have any Advantage questions or features you would like to see demonstrated (either in person at the show, or here on my blog) let me know via a comment to this post or by e-mailing and placing "attn:JD" in the subject.

Friday, October 10, 2008

Solid State Drives

This is a guest post by Mark Wilkins, a Senior Software Engineer on the Advantage R&D team.

Solid State Drives
Solid solid state drives (SSDs) present a new storage option that is becoming increasingly economically viable. Our colleagues from the SQL Anywhere team had mentioned that they had done some testing with solid state drives. Our own team was having an informal brainstorming session one day, and we were speculating about how these drives will affect the performance and characteristics of Advantage Database Server. Being the diligent and self-sacrificing developers that we are, and because we are good at running with other peoples' ideas, we immediately chased down some cash, bought an SSD, and tested it.

We purchased an OCZ Core Series 64GB 2.5" SATA II drive. Most of the testing was done on two different machines: a Dell Precision 380 3.2GHz desktop development PC with a 7200 rpm Seagate Barracuda SATA drive and a Dell PowerEdge 1950 1.83GHz quad core 64-bit Xeon server with a 15,000 rpm SCSI drive.

When holding the SSD in your hand, the form factor is a compelling feature. It is small, light and very desirable and is fun to carry around and show off to other developers. Of course, when it is dangling off of a SATA cable with no obvious resting place inside a desktop PC that has a bunch of fans and two noisy spinning hard disks, some of its sexiness gets lost in the mix. In the rack machine, I was able to slide it carefully into the far back reaches of the second hard drive bay and click it into place. It is currently still sitting there and will be until someone with long skinny flexible fingers can pry it out.

With minimal searching, one can find dozens of videos on the Internet purporting to show that SSDs are blazingly fast and the best thing since flying toasters. The common theme for these videos is to take to identical laptops, put an SSD in one and a "traditional" hard disk drive (HDD) in the other, point a video camera at them and have the laptops "race". A person will push a button on each laptop to simultaneously boot, load programs, eat bowls of Häagen-Dazs, etc. In the videos, the laptop with the SSD is always a clear winner. In our testing the SSD does perform very well, but its benefit does not seem to be as clear-cut as the videos would have you believe. Go figure. If the HDDs in the videos were extremely full, had minimally sized swap files and were extremely fragmented, then the videos might be realistic. To be fair, though, I don't have two identically configured laptops to try similar tests on, so I could well be wrong.

However, what we were really interested in was to see how Advantage behaved. The short answer is that Advantage performs very well with an SSD; we did not uncover any surprises. In some scenarios the HDD appears to have a slight edge and in others, the SSD wins. In many situations, the I/O caching performed by Advantage Database Server equalizes the performance of the two types of drives. In general, the test results were fairly predictable. For example, the near-zero latency and seek times of an SSD provides for the ability to read random portions of data from the drive very fast. Using this knowledge, one can construct tests that benefit from that behavior.

Some of the testing we performed was to run TPC-C transactions over some interval of time with a number of clients. These tests provide a reasonable cross section of queries with a mix of reads and writes across multiple tables. We captured a lot of different test result numbers, and there was no clear winner with the hardware we tested. For example, In one case, we ran 50 TPC-C clients against the quad core server for 5 hours. When running against the SSD, the test performed about 10% more iterations than when running against the HDD. However, when we ran the same test scenario for very short periods (e.g., 1 minute), the HDD typically outperformed the SSD by up to 10%.

In the absence of an obvious winner based on numbers, we were able to resort to trend spotting. Some situations in which the SSD seems to outperform a traditional HDD include the following.
  • Long sustained reads in natural record order.
  • Long sustained update and append operations with multiple indexes being updated.
  • The very first query against an unread (non-cached) table that involves index usage. Once indexes get cached, though, the difference disappears.
  • Reading and updating fragmented indexes.
It ultimately comes down to usage patterns and will obviously vary by application. If, though, we assume that this one SSD that we tested is comparable to other SSDs, then we can conclude that the current crop of SSDs perform very similarly to HDDs and are a valid choice for data storage for Advantage Database Server.

Some Numbers
The following are a few of the numbers obtained during the testing. The next set of simple reindex, read, and append tests were all performed on the Precision desktop workstation.

Reindex 1 million record table (2 indexes):
SSD: 2,096 ms
7200 rpm HDD: 3,573 ms

Read through 1 million record table:
SSD: 2,295 ms
7200 rpm HDD: 2,828 ms

Append 100,000 records with 3 indexes and a memo:
SSD: 27,900 ms
7200 rpm HDD: 28,100 ms

Set AOFs (filters) with 25 clients for 60 seconds:
SSD: 33,006 filters
7200 rpm HDD: 38,006 filters

The next few are some numbers from the TPC-C tests. These particular tests involved 8 tables with updates to 5 of the tables. Each "iteration" was a single transaction that involved an average of 22 queries and 22 updates/inserts. Each of the tests had 50 clients running concurrently.

60 second test on the Precision workstation:
SSD: 2425 iterations
7200 rpm HDD: 1986 iterations

60 second test on the PowerEdge server:
SSD: 3763 iterations
15,000 rpm HDD: 4176 iterations

5 hour test on the PowerEdge server:
SSD: 1,459,455 iterations
15,000 rpm HDD: 1,347,630 iterations

As you can see, the results are somewhat mixed. In general, the SSD would slightly out-perform the HDD, but it was certainly not always a given. For example, in the numbers above, the 60 second test on the PowerEdge server consistently had the HDD performing more iterations, but longer test runs usually gave the nod to the SSD. The short test runs, though, on the desktop workstation would typically place the SSD as the winner. Also, the filter (AOF) test would consistently show the HDD as the winner. I don't have any good explanations. One possibility is that the tests on the workstation were not run under ideal situations. I generally had multiple applications (documents, editors, IDEs, etc.) open while the tests were running. I did not do anything to ensure that those applications would not suddenly decide during a test run to scan for updated files or phone home to see if a critical update had just been released by its vendor. Still, though, that type of situation reflects some real world situations under which Advantage is used.

If any of you have real world results involving solid state drives, it would be interesting to hear about them.

Tuesday, October 7, 2008

Online Poll about Advantage Licensing

Please take a moment to visit my base page at and answer the survey question in the top right portion of the page.

The question:
If the Advantage server valcode changed to be a license file (as opposed to a 5 digit valcode), but provided more self-service options and license management via the web, would you consider this a positve or negative change?

This file would be a few hundred bytes. It would be slightly harder to manage. The existing 5 digit strings are easy, allow you to print labels and stick them on product, etc. With a license file approach you would have more license management options via self-service web portals to purchase licenses, expand existing licenses, print entitlement reports, etc.

Along with voting, feel free to post comments with thoughts or concerns.

I'd also like to hear your thoughts on serial numbers. Love them or hate them?

Friday, October 3, 2008

Using the DEFAULT keyword in INSERT Statements

Did you know you can use the keyword DEFAULT when inserting rows via SQL? This can be handy if your table has a read-only field, an autoinc for example, and you don't want to specify a field list when inserting a row. You can use this:

CREATE TABLE tester ( id autoinc, name char(20) );
INSERT INTO tester VALUES ( DEFAULT, 'whomever' );

instead of having to specify a field list like this:

CREATE TABLE tester ( id autoinc, name char(20) );
INSERT INTO tester ( name ) VALUES ( 'whomever' );

If a column has a default field value, the DEFAULT keyword can be used to specify that default value should be used.

CREATE TABLE tester ( id autoinc, name char(20) DEFAULT 'unknown' );