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.

I was poking around and decided to investigate this. Turns out William got an answer over on a msdn forum.

The answer is to use OpenQuery, which passes the query to VFP, which can optimize the query. Apparently SQL Server can't do that on datetime fields with a linked VFP server.  Here is the new code from the post:

select * from openquery(VCAL, 'select * FROM csHearin WHERE he_tdthearing = {06-02-1992 02:30 PM}')

Hi Greg,

This solution is not ideal, but yes, it works. I would much prefer finding the right view definition that would work properly with VFP DATETIME parameters (if that indeed exists).

The problem is that it precludes me from using an SQL Server View based on the linked table. The OPENQUERY method complicates passing parameters, and seems to be problematic for chained linked servers.

Thanks.

Thanks for the additional info. I don't have any solutions at this time though

Hi William.

I have seen a few references to 'Linked Servers' here and wonder what they are and in what circumstances you would use them

Hello Gerard,

Here is a link to a design schematic I'd like to develop:

http://forum.strataframe.net/Attachment1611.aspx

Here is a link to the dicussion:

http://forum.strataframe.net/Topic13841.aspx

Basically, it would be nice if I could treat a VFP database on each side of a WAN connection as a Linked Server, giving my StrataFrame application access to the VFP data on each side simultaneously.

I've run into problems attempting to "chain" linked servers (as depicted in the schematic) and the only solution I've been able to arrive at so far is to have multiple SQL connections in my SF app. One for each SQL server.

Hi William.

I ahve a problem clicking on your link. When I click, all I get up is a window with all the threads again. (I think I have got this with other links here as well). Should I be able to click on it and see an image ?

Tried it again now...and the image comes up ok.

I see in the diagram that you have VFP databases 'LINKED' to Sql database.

Does this just mean your application us using some data from Sql and some data from VFP, or is there something else 'magical' going on

Yes, the idea is to have the StrataFrame app access the VFP data via SQL Linked Servers. The trick is accessing both the Phoenix and Tucson VFP databases at the same time.

Because of the DATETIME parameter issue talked about earlier in this thread, I'm unable to use a SQL View and have to resort to OPENQUERY. If I were able to design a SQL Server View optimized for a VFP DATETIME parameter, I believe I'd be able to make the design of my schema work.

But, the only solution I've been able to come up with so far is to have two database connections in the SF app. Which looks like it will work, but I end up having to copy data from one business object to another to get a single dataset with data from both Phoenix and Tucson.

Hi William,

I'm unable to get such results with a VFP linked server...

I've create a VFP table (ID int autoinc, ColDateTime Datetime), populate it with more than 10000 records where coldatetime value is randomized on insert. A single index (primary key) on ID. No index on ColDateTime.

Querying that table from within SSMS as :

SELECT
     [id],
     [coldatetime]
FROM [VFP_Northwind]...[testdates]
      where
        coldatetime >='20090101 01:01:01'
      and
        coldatetime <='20481231 23:59:59'

GO

requires less than 1 second to get a resultset of 44 lines between the 10142 (including VFP treatment, SQL treatment, and SSMS display)

It's a little slower with a filter as
        coldatetime >='01/01/2009 01:01:01'

No difference with and without index on coldatetime

I'ld suspect a pb of pagecode in VFP

(BTW, we presented the first alpha version of VFPinSQL at our last "Rencontres AtoutFox", are you still interested with it?)