Slow response with large datasets.

G

Guest

Hi,

I run MS-Access 2003 over a network that connects us in MA to our database
in the CA office. When I run my query on a dataset that for over one month's
worth of data Access becomes unresponsive and 9 out of 10 times won't finish
the query. One month's worth of data for us is in the order of thousands of
records.

I was just reading another post in this thread titled "slow speed on
network" from 11/15/2006 and it makes reference to
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html.

My backend is a database on SQL server and currently I'm using an ODBC
driver titled "SQL Server". After reading the above article I'm wondering if
I should use the Microsoft Data Engine. The network is running just fine. I
had one of our administrators test the throughput and he doesn't find any
issues with it. This leaves me to think that this issue has to do with my
Access setup.

What would you recommend I do?
 
A

aaron.kempf

Access MDB is completely unusable across ANY network; let alone a WAN.

I would reccomend rewriting your frontend as an Access Data Project; it
is a lot easier to deal with.. and it won't COPY THE WHOLE TABLE ACROSS
THE WIRE every time you hit it.

That is what MDB does.. it copies the WHOLE TABLE and then filters it
locally.

Anyone using MDB anywhere should be SPIT ON and FIRED.


Access Data Projects made all you kids obsolete along time ago.

-Aaron
 
V

Van T. Dinh

Are you using Access Query on ODBC-linked Tables?

If you do, try using the Pass-Through Query since processing Pass-Through
queries are done by the SQL Server and only the result set is passed back to
your Front-End.

(My understanding is that in normal Access queries, Access / JET will try to
pass on to the SQL Server what it thinks SQL Server can recognize for
processing and the rest done locally in JET, i.e. JET involves in
processing. This means that data required to go through the network will be
more than the equivalent Pass-Through query.)


The bad the T-SQL is different from JET SQL we use in Access. Most
of the basic constructs will be similar, though. In addition local Access
Queries can use VBA functions while in Pass-Through Queries, T-SQL doesn't
know VBA functions so you may have to scrabble around to find equivalent
functions (or constructs that perfom the same functions).
 
A

aaron.kempf

yeah.. in other words; lose the ****ing training wheels and use Access
Data Projects.

it is RIDICULOUS to expect NEWBIES to learn 2 flavors of SQL
WHO THE **** DO YOU THINK THAT YOU ARE?


-Aaron
 
T

Tony Toews

Jim Moberg said:
My backend is a database on SQL server and currently I'm using an ODBC
driver titled "SQL Server". After reading the above article I'm wondering if
I should use the Microsoft Data Engine.
Click to expand...

No, because the MSDE, Microsoft Data Engine, is a subset of SQL
Server. So that won't make a performance difference.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
T

Tony Toews

Jim Moberg said:
Hi,

I run MS-Access 2003 over a network that connects us in MA to our database
in the CA office. When I run my query on a dataset that for over one month's
worth of data Access becomes unresponsive and 9 out of 10 times won't finish
the query. One month's worth of data for us is in the order of thousands of
records.
Click to expand...

But how many records are in the base table? Are all the fields in
the criteria for the queries indexed? For example the date field.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
G

Guest

Tony Toews said:
But how many records are in the base table? Are all the fields in
the criteria for the queries indexed? For example the date field.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Click to expand...

As of now there are 1,027,326 records in the base table and not all of the
fields are indexed.
 
A

aaron.kempf

you dont need to index all the fields.. you just want to index the
fields that you're filtering / sorting on
 
T

Tony Toews

As of now there are 1,027,326 records in the base table and not all of the
fields are indexed.
Click to expand...

Then you'll need to ask the SQL Server DBA to index all the fields
used in your query's criteria. And also any fields used in the
sorting order

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
G

Guest

Actually I have been playing around with pass through queries today and so
far the performance is MUCH faster than queries created using the design
mode. Thanks for your help.
 
A

aaron.kempf

yeah totallly

SQL-passthru are CLOSE to how fast Access Data Projects are.

but the bottom line is that MDB is just a headache and a nightmare..
Access Data Projects are a LOT simpler to deal with.

you can write a query-- in design view-- in an Access Data Project; and
it won't pull the whole table across the wire.

After years and years of workarounds and bugs; I gave up on MDB almost
a decade ago.

It is a ridiculous platform and only halfwits would use MDB for
anything.

Halfwits and retards.
I mean; these fools think that we should bring back DAO.. I mean; whats
next a major 'Windows 98 revival'?

-Aaron
 
T

Tony Toews

Jim Moberg said:
Actually I have been playing around with pass through queries today and so
far the performance is MUCH faster than queries created using the design
mode. Thanks for your help.
Click to expand...

That would make sense. The SQL Server box is doing all the work
rather than moving the data across the network and your system.
Especially if the fields in your criteria aren't indexed.

I'd still suggest talking to the DBA and getting those fields indexed.
It would lessen the load on the SQL Server system. Although if you
only run the queries once a month then that likely isn't a big deal.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
A

aaron.kempf

I'd suggest learning more about it yourself.

Just because someone has a SQL Server doesn't mean that they require a
DBA.

DBA are overworked; learn this shit yourself.
Maybe if these MDB-wimps weren't dependent on a DBA then maybe they'd
see the light.

YOU _ARE_ THE DBA.

-Aaron
 
T

Tony Toews

Aaron said:
I'd suggest learning more about it yourself.

Just because someone has a SQL Server doesn't mean that they require a
DBA.

DBA are overworked; learn this shit yourself.
Maybe if these MDB-wimps weren't dependent on a DBA then maybe they'd
see the light.

YOU _ARE_ THE DBA.
Click to expand...

This assumes that Jim Moberg has that access to the SQL Server database. He may not
have any permisions to create any objects.

However the original poster stated he had "administrator" look at things. So I
assumed he was dealing with a DBA.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
A

aaron.kempf

yes; it does you ****ing retard

have you ever sent moderately-complex queries against MDB?

ROFL
what a waste of time; SQL Server doesn't just flake out on complex sql
statements
 
T

Tony Toews

Aaron wrote:

Clean up your language and your derogatory comments and we can have a meaningful
discussion on this topic.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access FE, SQL Server BE - suddenly very slow with ODBC errors -HELP! 5
Slow response after move to SQL back end 7
printing reports = super slow 3
Access via ftp 3
Database Corruption? 5
Slow Query 1
Slow connection thru VPN 6
Use of Virtual Machine 4

Top