I use MSSQL MS for most administrative tasks on SQL Server 2008 R2 databases, but I find using Access (via an ADP, not linked tables) to view and edit raw table data to be much more efficient. (This is for sysadmin use only, of course, user access is via a web app, and the tables in question are relatively small, maybe a few thousand rows.)
However, since switching to Access 2010, performance tanked when filtering and sorting tables in an ADP, and watching it via Profiler, I know why -- Access is not only not preparing a WHERE clause for MSSQL to implement the filters, it is asking for the raw table multiple times, regardless which filters are in place. In other words, it does something like this:
SET ROWCOUNT 10000
SELECT "dbo"."MYTABLE".* FROM "dbo"."MYTABLE"
SET ROWCOUNT 0
SELECT "dbo"."MYTABLE".* FROM "dbo"."MYTABLE"
SET ROWCOUNT 10000
SELECT "dbo"."MYTABLE".* FROM "dbo"."MYTABLE"
SET ROWCOUNT 0
SELECT "dbo"."MYTABLE".* FROM "dbo"."MYTABLE"
(I left out a few extra statements where it is reading extended column properties.)
What the heck is Access doing here, and how can I make it use SQL Server to perform the filtering and sorting, and to not make multiple trips every time the table filters are changed?
