MS Access 2010 / MSSQL 2012 connection painfully slow

I took an old database of mine (Backend = MSSQL 2005 / Front End = MS Access 2003 MDB file), and transferred a Dell Inspiron laptop intended for demonstration purposes. The laptop is running MSSQL 2012 SP1 (the Business Intelligence version) and MS Access 2010. It works, but it is SLOW.
Form renders on the production version of the database (which runs on MSSQL 2008 standard with Access 2007 and 2010 clients) are close to instantaneous.
Here, on the Dell Inspiron, they take 5 to 10 seconds. Reports are similarly slow.
While the Dell Inspiron, aint exactly a screaming fast machine. I've seen this database run far faster on far slower & older machines.  CPU, memory, and I/O all looked reasonable when I was running the database.
The one new factor on this machine is Dell is MSSQL 2012 SP1. I'm inclined to think the slowness is associated with MSSQL 2012 either directly or indirectly.
Is running an old MSSQL 2005 database on a MSSQL 2012 server a problem?
Is it the ODBC connection between the Access file and the MSSQL 2012 server. Is MSSQL 2012 geared for OLE DB rather than ODBC?
Is Access 2010 struggling to connect an old 2003 MDB file with MSSQL 2012?
I ran out of the time with the machine and didn't check the SQL Server Error Log or the Windows Event Log. I should have.
I did a quick web search. I found some other folks with similar issues:
http://dba.stackexchange.com/questions/35579/sql-server-2012-slower-than-2008
http://stackoverflow.com/questions/10926841/sql-server-2012-slower-than-2005
http://forums.asp.net/t/1796853.aspx
jdanaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
How are you connecting?  Which driver are you using?

Jim.
0
jdanaAuthor Commented:
an ODBC driver
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:

an ODBC driver

Which one? Version, bits etc.
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
I had the exact same experience using an older Access + SQL application, after I changed the back-end to SQL 2008 R2.  The problem was that merely opening a linked table took multiple seconds.  Changing the driver from:

{SQL Server}

to

{SQL Server Native Client 10.0}

made a huge difference, from intolerable to instantaneous.

You could also try {SQL Server Native Client 11.0}, which is even newer.

However, you need to make sure that the newer drivers are installed on the PCs that will be running your app.  The old {SQL Server} driver is always there.

Also, I discovered that I needed to enable TCP/IP connections for “SQL Server Network Configuration” in the SQL Server Configuration Manager.

Hope this helps,
Armen Stein
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdanaAuthor Commented:
Armen,

Thanks for coming through! Great response.

J
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.