Newbie: Where is the stored procedure run

Newbie: Where is the stored procedure run

Post by Franklin S.S. L » Wed, 15 Oct 1997 04:00:00



Hi,

I have a problem where an VB application have to make query on
an access table stored in a remote NT server. The communication
line is only a slow telephone line with 33.4K. Due to budget
limit, we are not afforable to buy a SQL server.

Is there any way for us to perform the query in a client server
manner (i.e. the query is done on the server) so that we only need
to transport result data along the slow data line?
What happen if we write some stored procedure in the access
tables for the VB application to use? Where will the query
actual done: on the server side or on the workstation side?

Thanks in advance for any help!

--

Regards
Franklin Lam        

 
 
 

Newbie: Where is the stored procedure run

Post by Joel Shepher » Wed, 15 Oct 1997 04:00:00



>Hi,

>I have a problem where an VB application have to make query on
>an access table stored in a remote NT server. The communication
>line is only a slow telephone line with 33.4K. Due to budget
>limit, we are not afforable to buy a SQL server.

One approach would to be to create a remote OLE automation server that would
run on the remote computer, accepting requests to run queries, and returning
only the results. You need VB4 Enterprise (or VB5 Enterprise) to do remote
automation.

A limitation with Access, or any desktop database, is that all the work is
done by the client application, no matter where it is in relation to the
database file. If the database is on another system, then when the client
runs a query, it has to retrieve the indices, and possibly complete tables,
from the db file and process them locally. All that data must be streamed
across the network, or across the modem line, before you can even think
about seeing the query results. The database file itself has as much native
intelligence as a .txt file.

A remote OLE server can act as a "proxy" for your client. Your VB
application would send a request (over the modem) to the OLE server to run
some query. The OLE server, because it is running on the same system as the
database file, can perform the query without hitting the modem. When it's
done, it can send just the results back over the modem. Depending on the
queries and the number of records they return, you could see a huge
performance increase.

Quote:>Is there any way for us to perform the query in a client server
>manner (i.e. the query is done on the server) so that we only need
>to transport result data along the slow data line?
>What happen if we write some stored procedure in the access
>tables for the VB application to use? Where will the query
>actual done: on the server side or on the workstation side?

>Thanks in advance for any help!

>--

>Regards
>Franklin Lam


 
 
 

Newbie: Where is the stored procedure run

Post by Joel Shepher » Wed, 15 Oct 1997 04:00:00







Quote:>> >Hi,

>> >I have a problem where an VB application have to make query on
>> >an access table stored in a remote NT server. The communication
>> >line is only a slow telephone line with 33.4K. Due to budget
>> >limit, we are not afforable to buy a SQL server.

>> One approach would to be to create a remote OLE automation server that
would
>> run on the remote computer, accepting requests to run queries, and
returning
>> only the results. You need VB4 Enterprise (or VB5 Enterprise) to do
remote
>> automation.

>Geez, I don't know about this. The tests I did with Remote Automation and
>DCOM indicate a fairly high minimum overhead, about 1KB, for every method
>or property call. There's also the synchronous execution issue. I wonder
>if something like Sybase SQL Anywhere would be a better choice.

It's _another_ choice. I have an entire SQL scripting language system based
on such the scheme mentioned above, because it yields a real performance
benefit. The overhead of out-of-process OLE, the network, etc., is
negligible compared to the time and resources consumed by the database(s).
There's going to be more overhead running over a modem line, but A) The big
overhead -- getting the resultsets back -- will be roughly the same no
matter what mechanism is used to retrieve it, and B) With a decent
connection and a well designed system, you're looking at a relatively small
number of 1/2 second calls, a time, again, which may be negligible compared
to the time it takes to query for and send the data.

The big question is what kind of query activity is being considered. If it's
a slew of quick, 1-row queries, then remote OLE might _not_ be the _best_
alternative. On the other hand, if it's a bunch of big update queries, or a
bunch of fire-hose selects, the OLE overhead won't be a factor.