msde slower than Jet?

I have an VBA application which queries data from an msde server. Then
converts the data into an array and passes the array to a com server which
does computations.
This procedure worked failry fast under Jet (on the local client), but it is
noticeably slower with msde server residing on another machine.
During this process I see that the CPU on the client PC (which runs the VBA
and later the com-dll) is very busy while the data is retrieved.
Could it be that recordset-to-array conversion is the culprit?
or is the network transfer of a few Mb of data the problem?
If the latter, why is the client so busy dyring data retrieval?
If the former, is there a better way of transferring the data to a DLL
written in a C-like compiled/optmiized language?
Thanks in advance for any hints.
To perform a fair test, put MSDE local and compare Access local, or put
Access on the remote machine and test that way. Comparing one database over
the network to one that is on the local machine is not a fair test at all.
http://www.aspfaq.com/
(Reverse address to reply.)
"Ernesto" <tsh@.mathematicuslabs.com> wrote in message
news:W4qdnZ3DZKHbbW_dRVn-gg@.speakeasy.net...
>I have an VBA application which queries data from an msde server. Then
> converts the data into an array and passes the array to a com server which
> does computations.
> This procedure worked failry fast under Jet (on the local client), but it
> is
> noticeably slower with msde server residing on another machine.
> During this process I see that the CPU on the client PC (which runs the
> VBA
> and later the com-dll) is very busy while the data is retrieved.
> Could it be that recordset-to-array conversion is the culprit?
> or is the network transfer of a few Mb of data the problem?
> If the latter, why is the client so busy dyring data retrieval?
> If the former, is there a better way of transferring the data to a DLL
> written in a C-like compiled/optmiized language?
> Thanks in advance for any hints.
>
>
|||Hi Ernesto,
In addition to what Aaron mentioned, I've found jet to be faster in
applications that involve significant amounts of data accessed sequentially.
However, as soon as any sort of random access (or multiple users) gets
involved, the MSDE usually wins every time, often by a large mark.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Ernesto" <tsh@.mathematicuslabs.com> wrote in message
news:W4qdnZ3DZKHbbW_dRVn-gg@.speakeasy.net...
> I have an VBA application which queries data from an msde server. Then
> converts the data into an array and passes the array to a com server which
> does computations.
> This procedure worked failry fast under Jet (on the local client), but it
is
> noticeably slower with msde server residing on another machine.
> During this process I see that the CPU on the client PC (which runs the
VBA
> and later the com-dll) is very busy while the data is retrieved.
> Could it be that recordset-to-array conversion is the culprit?
> or is the network transfer of a few Mb of data the problem?
> If the latter, why is the client so busy dyring data retrieval?
> If the former, is there a better way of transferring the data to a DLL
> written in a C-like compiled/optmiized language?
> Thanks in advance for any hints.
>
>
|||Actually my questions did not have anything to do with the comparison, per
se.
As it turns out Jet does run faster on the same computer, but I"m not too
concerned about it since the setup is not of interest.
I did want to know if anyone had any suggestions on where the "bottlenecks"
may be, and if there are any ways to do this faster.
Even a "No, it-can't -be-done" answer would be useful.
Greg's comment about sequential access does give the possible reason since
that is roughly all I ever do, as the data are all time series and retrieved
one whole series at a time.
Still if there is a better way (like somehow skipping the recordset to array
conversion, assuming that IS the slow part) would be most appreciated.
|||Perhaps when Access talks to Jet, it knows how to get "pages" of data at a
time.
But when talking to SQL Server/MSDE, it instead tries to query for
everything - which is not very optimal.
Access is not the ideal tool for a database front-end. You need to write
queries yourself to get good design (No use a query builder, and controls
that show everything).
"Ernesto" <tsh@.mathematicuslabs.com> wrote in message
news:W4qdnZ3DZKHbbW_dRVn-gg@.speakeasy.net...
> I have an VBA application which queries data from an msde server. Then
> converts the data into an array and passes the array to a com server which
> does computations.
> This procedure worked failry fast under Jet (on the local client), but it
is
> noticeably slower with msde server residing on another machine.
> During this process I see that the CPU on the client PC (which runs the
VBA
> and later the com-dll) is very busy while the data is retrieved.
> Could it be that recordset-to-array conversion is the culprit?
> or is the network transfer of a few Mb of data the problem?
> If the latter, why is the client so busy dyring data retrieval?
> If the former, is there a better way of transferring the data to a DLL
> written in a C-like compiled/optmiized language?
> Thanks in advance for any hints.
>
>
|||If not written correctly, Access treats SQL Server like a file server.
Access/JET does all of the physical IO against the local MDB file which
helps in some cases, but when you try to stretch this approach to multiple
users, the model falls apart quickly.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Ian Boyd" <admin@.SWIFTPA.NET> wrote in message
news:%23$KOg7ZaEHA.2364@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Perhaps when Access talks to Jet, it knows how to get "pages" of data at a
> time.
> But when talking to SQL Server/MSDE, it instead tries to query for
> everything - which is not very optimal.
> Access is not the ideal tool for a database front-end. You need to write
> queries yourself to get good design (No use a query builder, and controls
> that show everything).
>
> "Ernesto" <tsh@.mathematicuslabs.com> wrote in message
> news:W4qdnZ3DZKHbbW_dRVn-gg@.speakeasy.net...
which[vbcol=seagreen]
it
> is
> VBA
>