May 6, 2015 at 10:40 am
I've recently migrated an Access 2010 back end DB to SQL Server 2008. I'm in a testing phase right now.
When I execute a simple delete query within Access, it takes about 2 minutes. If I execute the same query in SQL, it is instantaneous.
Here's what I've been doing in my testing:
1. Used an ODBC connection to link the tables. I've used both the SQL and SQL Server Native Client 10.0.
2. I've tried linking the tables via a DNSless connection. Created the link on the fly, executed the query, then deleted the linked table. Used the Windows authentication.
3. Within Access, if I execute a make-table query using a linked table, and the new table is placed in the local Access DB, it is instantaneous.
In closing, I've also tried append queries from Access, getting the same lengthy results. In simple terms, if Access is used to push or manipulate data to the SQL Server, it takes forever. If Access uses the SQL data, and creates or manipulates a local table, it is instantaneous.
Thanks in advance for your assistance!!
May 6, 2015 at 11:45 am
have you tried creating a stored procedure in SQL Server that does the DELETE for you and then calling it from MS Access?
I have not worked with Access in a few years but, if I'm not mistaken, when you do that the only thing that Access is doing is telling SQL to run the stored procedure then all the work is happening on SQL Server.
-- Itzik Ben-Gan 2001
May 6, 2015 at 11:54 am
Thanks for the reply.
I haven't tried that yet. Since my test is with a very simple delete query, I wanted to make sure that its not a setting within the SQL setup, or some obscure network setting.
May 6, 2015 at 1:09 pm
crowegreg (5/6/2015)
Thanks for the reply.I haven't tried that yet. Since my test is with a very simple delete query, I wanted to make sure that its not a setting within the SQL setup, or some obscure network setting.
This kind of thing can be tricky to troubleshoot which is why stored procedures are the way to go IMHO.
-- Itzik Ben-Gan 2001
May 6, 2015 at 2:57 pm
crowegreg (5/6/2015)
...In closing, I've also tried append queries from Access, getting the same lengthy results. In simple terms, if Access is used to push or manipulate data to the SQL Server, it takes forever. If Access uses the SQL data, and creates or manipulates a local table, it is instantaneous.
Thanks in advance for your assistance!!
Nothing in your post up to the point where the quote begins says anything about "lengthy results" - did something get dropped from your original post?
The other posters are correct that for simple queries, the ODBC driver takes the Access SQL statement and converts it to T-SQL and passes it on to SQL Server where it gets process, and results if any are returned to the user. But if more complex things are being done, the all of the data in all of the tables is passed back to the Access Jet engine where the query gets run, and that will always be slower. The real question is whether the difference is noticeable to the user. In many cases the answer is no - in some the performance hit can be truly awful.
The virtue of using Access as the front-end to a SQL Server database is that you can whip out forms, reports and queries faster than with any other existing technology, but if you are migrating an Access database to SQL Server, you do have to worry about performance. Simple queries with no more than three tables are often converted to T-SQL and you only get the results back. We've been doing this sort of thing for over 20 years, and have worked with databases containing multi-million row tables, and get sub-second response with the use of subforms and filters. The process we use is to simply upsize the back-end to SQL Server, and then do performance testing to see what needs attention. In those cases where performance is bad (or abysmal) we typically resort to pass-through queries, some of which are created on the fly, or to using views in SQL Server that do the joins we need, and occasionally to a stored procedure.
The bottom line is that it can be made to work and work well, but you have to pay attention to the design and the details.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
May 6, 2015 at 6:03 pm
Wendell, thank you for the detailed response.
I've kinda been taking that approach. I've upsized the back end to SQL. Everything I've read is exactly what you've written. All of my work within the front end DB(forms, modules, etc.) will need to be tested for what's the best performance now that I've upsized the back end.
After upsizing, I tried a simple delete query.
DELETE FROM dbo_PM_Temp
The table only contains 1500 rows. It takes about 2 minutes, before Access returns the confirm message. Is that normal?
If yes, how would you handle this delete process?
May 7, 2015 at 6:36 am
That does seem unusually long for a table of that size. I've not seen that behavior, but we seldom run a process that deletes all the records in a table in SQL Server on an ongoing basis. How long does it take if you run the process as a stored procedure? If it is significantly faster in SQL Server, then you have a couple of options. A pass-through query would probably be my first choice; the other option is to execute a stored procedure from VBA. You might also look at dropping the entire table if you are deleting all the records, as that would likely be quicker. To be honest, I tend to avoid using temporary tables, and tend to simply archive things. These days storage is cheap, and you never know when you might have to dig back and find some source data. But that's a personal inclination rather than any sort of design rule. 😀
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
May 7, 2015 at 7:24 am
I haven't tried any other methods of deleting the records. I figured this would be a quick method, and I'm stuck here.
I'll also try a pass through query.
One other question, why would a make table query in Access run instantly?
July 23, 2015 at 1:52 pm
crowegreg (5/7/2015)
I haven't tried any other methods of deleting the records. I figured this would be a quick method, and I'm stuck here.I'll also try a pass through query.
One other question, why would a make table query in Access run instantly?
The pass through query could just be:
TRUNCATE TABLE tablename
It will require permission to TRUNCATE within SQL, however. I almost NEVER let Access do much of anything with data on SQL, other than be the recipient of it, which it can do reasonably quickly, as long as you let SQL Server handle the query. If Access is doing a MAKE TABLE query, then it's accessing data from SQL fairly quickly, and all it has to do is drop the data into the local table and be done with it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply