Varchar, SET ANSI, and the UT

I've been working on updating Stonefield Query for GoldMine to use the version 3.0 Stonefield Query Developer's Edition as its engine. While doing some testing, I found that one of the queries was taking significantly longer in the new version than the old version: 30 seconds rather than 3 seconds. My first thought is that it's a VFP 9 issue, since the old version uses VFP 8. I remembered from messages on the Universal Thread regarding query performance in VFP 9 that Rushmore, the key to VFP's magical speed in performing queries, is disabled if the code page of a cursor doesn't match the current code page (ie. CPDBF() <> CPCURRENT()). However, that wasn't the case here. In fact, if I ran the old version under VFP 9, it gave the same performance as VFP 8, so that wasn't the problem.

To dig into this further, I searched the Univeral Thread for messages regarding performance, and saw one where Sergey Berezniker (the king of the Universal Thread) mentioned that expressions using ALLTRIM() aren't optimized because Rushmore requires fixed length keys, but that SET ANSI ON would take care of that. Again, that wasn't the case here; the query didn't use ALLTRIM(). However, that got me to thinking: I wonder if the fields involving in the JOIN clause were Varchar fields. Sure enough, they were. Why the difference between the old and new versions? I added the following to the new version so Varchar and Varbinary fields are properly supported:

cursorsetprop('MapBinary', .T., 0)
cursorsetprop('MapVarchar', .T., 0)

This means that in the new version, the fields retrieved from the database were Varchar rather than Character as they were in the old version. Because Varchar fields could have different lengths (in fact, they were the same length for the fields involved in this join), Rushmore won't optimize them unless ANSI is set on. So, a quick SET ANSI ON added to the code, and the query is now as fast in the new version.

So, two lessons: sometimes a little change in one part of an application can cause a big change in another part, and search the Universal Thread (or other online sources) before spending hours trying to track down a problem. This one only took me about 15 minutes to fix. Thanks, Sergey!