Remote views and databases 2

[phita]

Hi,
I am working on an application with tables having millions of records. I intend to upsize the database to MSDE2000 that comes with VFP7.
My questions are:

1. Does the speed of data retrieval using a remote view depend on the speed of the underlying database? And if yes, which among SQL(MSDE2000), ORACLE and mySQL is the fastest to use when working with remote views?

2. Which is the fastest technology to access big volumes of remote data, remote views or SPT (SQL Passthrough Technology)?

Thanks in advance.

Peterson.

 

[Mike Lewis]

Phita,

First, be cautious about MSDE. Unlike with the full SQL Server, there is still a 2 GB limit on each database. Given the number of records you mentioned, you might have to go up to the full SQL Server.

Does the speed of data retrieval using a remote view depend on the speed of the underlying database?

Only up to a point. The biggest factor performance-wise is how well you design the views. The aim should to push as much as possible of the processing on the server, and to have the server return the smallest possible subset of records.

The above observation is true regardless of the back end. That said, my guess is that SQL Server will be faster than Oralce and MySQL. But it will depend on lots of factors.

Which is the fastest technology to access big volumes of remote data, remote views or SPT (SQL Passthrough Technology)?

SPT is usually slightly faster than remote views. But the difference is pretty negligible compared to the benefit you will get by doing a good design, as per my remarks above.

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[phita]

Mike,
Thanks for your advice and insight into the issues I had. Let me digest your answers and see what I come up with. Incase of any other question, I will post it again.

Thanks.

Phita.

 

[craigsboyd]

Star for the sound advice.

[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]