SQL Server Back End Too Slow

[Paul H]

Our migration to a SQL Server back end has hit a snag. To most people's surprise working with the SQL Server back end has been difficult and slow. I can import a file into my Access table in less than a minute. Using SQL Server takes 10 to 20 minutes. Deleting data the same way. One of our main goals was to enhance performance. The IT guys are pointing at Access. I say they are wrong. Using a SQL Server back end has usually been dramatically faster. I think the problem is on their end.



Any thoughts? Your speedy reply will help me set them straight, maybe.

[ItsMe]

I don't have an answer. Maybe download Visual Studio Express Edition and test some SQL statements directly from the IDE in visual studio? At least you could compare and see if you need to adjust your queries in Access.

[Paul H]

There's nothing to adjust. The import is just that. No queries involved. There is a simple delete query which couldn't be simpler. This took even longer to run.

[Paul H]

Here are the kind of questions I am getting. Mostly they don't make too much sense in Access. but they show the two different worlds we are living in.

• Did you run a commit?
• Are you sure that Access is sending that one delete statement directly to the database and not sending 20K individual delete statements?
• Is there a way to turn on tracing so we can see what what SQL is being sent to the database?

They did a test using a program called DB Artisan. Which took 2 seconds to run the same SQL I am running.

[hapm]

I had equal problems when migrating a database that imports big csv files every day. My best approach was to use server side bulk import instead of loading the data up from access, what is much faster. Without knowing exactly how much data you are importing and how much indices, references and other things that influence import speed are set on the server, I can only suggestion you to set up ODBC tracing (in ODBC data source manager on the Tracing tab) to see what sql is realy send to the server, as access and odbc change the queries before they get send under some circumstances.

[Paul H]

Thanks for the tips. I've never tried a Trace before (didn't know where it was before you told me). I tried it (clicked Start Tracing Now, Ran my import, clicked Stop Tracing Now.), but it did not create a log file. I also tried Machine_Wide tracing, no luck. Would love to answer some of the DBA's questions.

[Paul H]

Our DBA guy found this

"the reason for that is that Jet/ACE tries to be a good "database server citizen" and instead of sending a SQL batch delete, it sends a separate SQL delete for each row being deleted. This allows the server to interleave the delete operation with commands from other users of the server, but it means the deletion is really slow for the end user. With a passthrough, you're telling the server to process it as a batch delete, instead of letting Jet/ACE convert your SQL DELETE into something you didn't intend."

Also this more detailed explanation to assist with tracking the SQL.

http://msdn.microsoft.com/en-us/libr...sql.90%29.aspx

I've read through it, but haven't put it to practical use yet. You might find it helpful.

[hapm]

Not sure how you delete the data, but if you use a delete query, this shouldn't happen. If so, you can always fall back to a pass-through query, avoiding all the odbc "optimizations" and give the server the exact sql to execute. Another way would be to use a transaction that gets all the deletes committed in one run, using only one lock and avoiding side effects from other connections. I for my self prefer the pass through solution in this case. Only pitfall here is that you can't use any access SQL references to form fields and VBA functions. If you want to do this, you will need to rebuild the query and replace the wanted parameters with there values using vba before it gets executed. Works quite well for me since years.

[bazianm]

Out of curiosity, what version of SQL Server and what ODBC driver are you using?