Hello,

This is not a SF question, but I'm hoping someone may have run into the same issue...

I have a VFP linked server in SQL2008 (the databases/tables are all on the same workstation)and am trying to optimize a query that filters on a datetime field. The VFP table has an index on this field, but I'm not certain how to speed up my query.

For example:

SELECT *
FROM MyVFPLinkedServer...MyTable
WHERE
MyDateTimeField >= '03/01/2011 00:00:00' AND
MyDateTimeField <= '03/31/2011 23:59:59'

This returns 75 rows and takes 30 seconds to execute.

Filtering on an integer field in the same table is very fast, so I guess I'm just not formatting the WHERE clause properly to take advantage of the DATETIME field index.


Can anyone suggest how I can speed up the linked server query with DATETIME values?

Thanks.

Hi William,

• about the integers:
  
  The difference of length in memory is unsufficent to explain the difference of performance.
  More than the length of the storage in memory, I believe that there is an automatic cast (made by VFPOLEDB or by SQL Server, I don't know). When the VFP field is N(x,0), it seems that it is cast in Integer, and consequently, you benefit of a good initial data typing, not having to cast.
  I say "I believe...", I'm not sure nor I have any proof!
  
• about TTOC:
  
  here again, we probably have an implicit cast, because we give the date value to SQL server as a string. When we query, the internal parser transforms the string in a datetime in the best way for a SQL native table. Does SQL transmit the query to VFPOLEDB before parsing (in the original string format)? It would explain why we get the best results with a TTOC datetime in VFP field, because no implicit cast would be required. But once more, it's only an assumption.

Now I need to test with a WHERE clause.