General discussion


data retrieval from SQL DB

By life_reinvented ·
i've been using sql server 2005 where a placed my databases and as front end i am using Ms access 2007 i have the tables linked..

one of the tables contains 3000 records
when i want to see all records it takes a long time .. but it makes it easier to filter data on the spot as we dont always know what the filter criteria would be upfront.. my database here is in ".accdb" format.

if i switch to .adp format it works much faster .. but the filtering with the autofilter int the colmn headers doesn't make sense and the totals in the totals row(new feature of ms access 2007) still displays all records count and totals rather than the filterd totals like in the .Accdb format

i would like to see the data load faster and these filters working properly with the totals also working poperly..

how to do this while still keeping frontend in MS Access 2007??

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Well if it's slowing down on 3000 records

by Tony Hopkinson In reply to data retrieval from SQL D ...

There may be other issues, are we talking a lot of columns, filtering on strings, or worse still text blobs? How remote is the client from the SQL 2005 machine, network latency is always a possibility. How much room is there on the client, particularaly memory.

If you are filtering in access, that means it sucks everything in into memory, and then sorts it out from there unavoidable in a desktop database, totally non optimal for client server.

The trick with client server is to do as much processing as you can on teh server and suck the minimum amount of data on to the client.

Something you might want too look at, initially selecting only the data you want to filter on , PersonID and Name for instance, if you were filering on surname. The filter string then becomes part of the where clause for the next filter say zip/post code.

Normalising might help, intaht it will force more keys and indexes to built on the sever which access will make use of.

Just doing something like a ColourTable
1 Red
2 Blue

Display colour from a look up, and filter based on colournumber, can be a surprising performance improvement.

Hope this give you a few clues.

PS recordcount is a very expensive animal.

Select Count(ID) from IDs is way faster, than Select * From IDs and then counting them in memory and displaying that.

Collapse -


by life_reinvented In reply to Well if it's slowing down ...

ok let me try to do what u said.. and post back any results.. or diffrences..


Related Discussions

Related Forums