Slow Connection/Interaction with SQL Server 2008

[markjack99]

We are developing an application which uses SQL Server 2008 R2 Express Edition. The application reads an index file, processes the lines, and inserts data into the database. On two machines, a particular import process takes 3 minutes - on two other machines, the same process takes 13 minutes. We cannot figure out what is causing the difference.

We are running the same application code on all 4 machines (so it is not a code issue).
We have tested each machine using a SQL Server instance installed locally on each machine (so it is not a network issue).
We changed to have all four machines use the same SQL Server instance on a separate server (so it is not a SQL Server version difference issue).
Since we are reading a file (4000+ lines), I temporarily stripped out all the SQL statements to see how fast the application was reading and processing the lines in the file (less than 1 second) (so it is not an issue with reading and processing the lines in the file).
All machines have the same version native client dll (sqlncli10.dll), which I believe deals with communication to SQL Server.

All machines are comparable in processor speed and RAM.

It seems to be slow when executing any code which interacts with SQL Server - but only on two machines.

Does anyone know what to check with regard to this interaction?

Thanks in advance

 

[gmmastros]

Sounds like you did a lot of things to rule out the problem.

Personally, I think 3 minutes to load 4000 rows of data seems awfully slow. I bet you can get this to be faster.

Regardless.... I'm wondering if this might be a permissions issue. Are you using Windows Authentication or SQL Authentication? Have you tried using different logins on the various machines to see if that makes a difference?

Have you looked at the performance load of the computer itself. If there are background processes running (like virus scanners), it could slow things down.

Firewalls and port scanners can slow things down too. Do all the computers have the same operating system?

What language are you writing your app in?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markjack99]

The app is written with Unify's Team Developer (this company has changed its name several times over the years: Centura, SQL Windows, Gupta, etc.). This company also has its own database engine: SQLBase. We are currently doing a rewrite of the code and changing the back-end to SQL Server - this is how we encountered this issue.

Yes - I agree - 3 minutes is slow - we were going to work on speeding up the queries after we figured out this issue. Our previous release (using SQLBase) does the import in less than 20 seconds.

We are using Window's Authentication - I did not think about logging on to the machines under different usernames - I will try that tomorrow.

I restarted one of the slow computers (not allowing all the background processes to start up) and ran the app - same slowness.

3 (2 slow, 1 fast) of the computers are XP, one is Windows 7 (fast).

I'll also bring up the firewall and port issue with the other developers - see if that could be an issue.


Thanks for the suggestions - I'll re-post when/if we discover a solution.

 

[markjack99]

Finally found it

While using a process explorer to compare dlls running on each machine, we noticed the slow machines were running an odbctrac.dll (trace file).

Our application has a sql configuration file (sql.ini) where we put such information as DSN settings. The sql.ini files on the slow machines had a trace file listed which was obviously causing the trace to be run - the fast machines had this line commented out.

Commented this line out on the slow machines and they are up to speed.

Seems simple now that we found it.

 

[gmmastros]

Seems simple now that we found it.

Amen to that! Glad you found the problem.

import process takes 3 minutes

two other machines, the same process takes 13 minutes.

Our previous release (using SQLBase) does the import in less than 20 seconds.

Do you want to talk about speeding this up now?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markjack99]

Speeding it up?

Any advice you can offer would be greatly appreciated or if you have any good links where I could go to get some tips, etc. - and just so you know, I am NOT a database admin by any means.

Also, it is not a simple, read a line from a file, process it, and put it in a database table(s). It is a rather complicated import process which indexes a list of drawings, which can be made up of multiple sheets and/or frames, each of which can be associated with a different revision of the drawing. So there are numerous queries which are executed for each imported line.