How do I speed up my application connecting to MS SQL Server?

Question

I have a Delphi application running on SQL Server 2000, but it's taking awfully long to connect to the database!

But when I run this application on my development server it connects pretty fast!

I am running on Windows 2003 server, SQL Server 2k personal edition, when I look on my MDAC version in the registry, I see version 2.8 already installed!

Any ideas why this happens on the production machine but not on the development machine?

Answer 1

Your problem most likely is network or transport layer related

1. Are you connecting through TCP, Named Pipes or another mechanism?
2. Have you tried tracing opening a connection with Microsoft SQL Profiler?

regards,
Lieven

Answer 2

Keep your connection open once you have established it. This is called connection pooling and will improve performance. I have no clue how to do it with a delphi application.

Answer 3

There's a reasonable chance that this is down to a network level issue connecting to the database. Depending on whether you're running the application and database on the same box of course.

Try connecting to the database from the same machine using a different tool. You could set up a data source and test it from the control panel as an alternative. If the connection is slow from another tool test the connectivity between the servers for other types of connection (e.g. run a ping). It may be that it's resolving the server via broadcast rather than the domain, for instance. Or any number of other issues - firewall, switch, wins etc.

If you are connecting using integrated authentication also ensure that the database can resolve the application server as well as vice versa. This is part of the authentication process and I've seen it cause slow downs in creating database connections before.

In short, I'd be confident that this isn't a problem specific to delphi / sql, but something in the communications between your production servers.

Good luck!

Answer 4

I had a problem a long time ago like this, and it came down to the workstation section of the connection string. its possible if you've copied the connection string from your dev machine that the workstation parameter is still in the connection string and pointing to your dev machine which probably does not exist on your deployment network.

In this case your connection to the database has to wait until the network tries to connect ot a non-existant machine (which obviously takes time). Remove the workstation cluse and it will speed up no end.