July 28, 2014 at 5:15 am
Just upsized from Access FE/BE to Access FE and SQL Server 2014 BE. Never done this before but all seemed to go smoothly after some weeks of planning. Went live yesterday.
Today about 100 Terminal Services users logged on this morning and all are moaning about speed, which is significantly slower than Access only. We haven't made any changes to the FE.
One of the key features of the system is the display of a multi-tab form which reads data in from about 10 different tables, and it's this which is annoying them the most. It used to take 1 to 2 seconds to populate the entire form and display it - it's now between 8 and 10!!
As you can see I'm new to this forum and new to SQL Server. This performance drop from Access to SQL Server is completely unexpected. Can anyone please offer any assistance in any changes I can make, and quickly. I know this is a big ask but I'm under a lot of pressure here.
Any help really appreciated - and I do mean that. And the simpler the better as we are totally new to SQL Server. More info available if needed.
Thanks
Dave
July 28, 2014 at 11:39 am
For your connections to SQL Server, are you using passthrough queries to procedures, or are you using local views which call through the ODBC links to each table in the SQL Server? This is one of the most common pain points when upgrading from Jet to SQL Server as the storage engine.
Side Note: You REALLY want passthrough queries, even if you are just sending the T-SQL down.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 28, 2014 at 1:14 pm
Thanks Craig
We spent the day putting the client back to full Access, exactly where they were at midnight Saturday. So now I need to spend the rest of time trying to get to grips with a much better way to use SQL Server as a backend to my Access front end.
To answer your question - No! The Access FE was unchanged from when it had an Access Back End. No pass through queries, no views, no stored procedures, or anything apart from standard Access techniques, or so I thought.
I fear a big learning curve coming up but by putting the client back to where they were I have bought us a load of time!!
Cheers
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply