Hello Michel!

> VFPinSQL

Yes, definitely! I imagine you had a lot of interest.

> suspect a pb of pagecode in VFP

CPDBF() says the table cp is 1252.

Attached in the zip file are two execution plans, one that has an integer field in the where clause, the other with a datetime field. As you can see, the integer query is passed on to the VFP engine and returns a single row. The datetime query brings the entire table into SQL which is then scanned.

Would you mind sending the execution plan for your query?

Thanks.

Hi William,

What are the statistics on this query? how many times did you run it?

Would you please send me (Private message if needed) your data (the dbf and its cdx) ?

For VfpInSql, we still have not found any workaround for varchar in dbf (unable to map it in SQL) but other fields run fine, for insert, update, delete (even with where type in the same way as in fox!). i need to check if all is ok for memo and blob.

I'm not sure how to look up the statistics, but I've run the query numerous (dozens of) times, all with about the same time to return the results (about 30 seconds).

Attached is a copy of the table in a new DBC. I created a new linked server in SSMS and ran this query with the same results:

SELECT *
  FROM [MYDBC]...[mytable]
  where he_tdthearing >= '20110301 00:00:00' and
  he_tdthearing <= '20110331 23:59:59'
GO

The new table/DBC query performed the same as the original.

I ran into the same VARCHAR issue with VFP Linked Server. We only use VARCHAR in 3 fields in our existing database, so we decided to go ahead and change them to CHAR fields since there would be no real impact.

I don't have a write requirement yet, but I did read somewhere that VFP linked servers do not support updates. I searched for the MS Knowledgebase article, but could not find it again. Is this true?

Thanks.

In a query in SSMS, you'll find the button for statistics near the "Real execution plan" button (see attachments)

I'll have a look to your files on Monday.

And for VFP linked servers, tou're right, they don't support update... and that's why we wrote VFPinSQL, which is a VFP linked server in SQL, that support update, insert, delete, in full respect with referential integrity triggers in VFP database.

Attached are the Statistics.

Thanks.

Bill

Hi,

I wanted to understand why you had such bad results with your VFP Linked server, so I've done a benchmark test with your table, and with other tables based on yours, but with some variations in the structure (see in MyDBC.prg.txt attached).

I got some expected results, and other unexpected!

first unexpected result was the tiny difference provided by the lack (or the presence) of the index, or the number or complexity of the tags in the cdx (you'll see it in the excel sheets named MyTable SimpleCdx and MyTableCdxNoDate).

Expected result was the benefit of a better choice of data type for numeric fields. Tou have 3 fields with N(x,0), and I've altered these fields as Integer. I was expecting an improvement, but not as important as what I found! Look at the sheet named "MyTableInt", it's amazing.

And the most unexpected result was on the datetime field. I created a new field of C(14), and update it with TTOC(he_tdthearing,1), and then delete the column "he_tdthearing". Wow! Read the results in the sheets named MyTableIdTTOC, MyTableTTOC.

So finally, the global result is in the comparison between MyTable and MyTableIntTTOC...

The complete benchmark is in the xlsx file in the attached zip.

Hello Michel,

I've reviewed the results. Thanks for taking the time to look at this with me.

> tiny difference provided by the lack (or the presence) of the index

Do you think this is because there was no WHERE clause in the SELECT statements?

> benefit of a better choice of data type for numeric fields

Yes, this makes sense (in my mind anyway). Integer fields only use 4 bytes of storage, whereas Numeric fields use 8 bytes in memory and 1 to 20 bytes in the table. 

Since the query is returning all rows, I would expect that it would finish faster if there were less total bytes to be returned. Is this the right way to look at it?

> comparison between MyTable and MyTableIntTTOC

That IS unexpected. From my perspective, the TTOC field should have had a negative impact because of the larger field size in bytes.

However, what I find more amazing is the difference between MyTableIntTTOC and MyTableInt. Going by the "it takes more time to return more bytes" theory, this result should not be.

I did the same tests between N(X,x), Int and TTOC and came up with result similar to yours:

MyTable - 75 seconds

MyTableInt - 50 seconds

MyTableIntTTOC - 41 seconds

So perhaps the VFP DateTime data type is not as efficient to retrieve via OLE DB, and/or takes more time to convert into an equivalent SQL Server datatype..?

And while the results are interesting, I am not sure how this directly applies to my original issue...

Did you get similar results as I when querying with a WHERE on the he_tDtHearing field?

In my scenario, I will be using a WHERE clause in each query. For Integer and Numeric fields, the indexes are used and the results are returned as fast as I would expect. But, for DateTime fields, I cannot find a way to optimize the WHERE clause so that an index is used.

If I were able to figure this out, I would be able to set up a view in SQL Server on the linked VFP table, then run queries on it with normal SQL syntax with parameters. Without this, I'm forced to use OPENQUERY - which works, but will be more effort to assemble each query.

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.