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.

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.