Remote SQL2005 Connection VERY Slow

I recently signed up with DASP and added an SQL2005 db. The db was to be used for demonstrating some software. It is only about 3 megs, about 15 tables.

I am able to connect just fine with MSStudio Express and the program. Everything works as expected, apart from the speed issue.

My db is on sql2k505 .

I am wondering if slow speed on sql2k505 is a known issue, or if I am just expecting too much. My internet connection is nice an fast. But a screen that I would expect to show within about 2 to 3 seconds takes about 20 seconds.

Either the speed may be improved somehow, or I will need to get off a shared host situation and find a Win VPS.

In the meantime I am trying to optimize my code and perhaps add a few more indexes in an effort to get faster responses.

Am I expecting too much?

KeyMan

 

sql2k505 is not showing up in the monitor as busy, and I don't see any recent (since the last SQL update 88 days ago) notifications in the history.

Speed is a difficult issue to get a handle on in SQL as delays could be caused by a lot of factors that are not server-related. You may be on the right track with indexing and optimization.

 

Working Much Better Now

Thanks mjp, I have added a few indexes and fiddled with some of my programs source code.

I am now getting better than expected response times

The biggest delay now is just obtaining the first connection to the DB. That is taking about 20 seconds from a remote program, but 20 seconds is OK so long as the user is made aware when first connecting.

KeyMan

 

Glad you were able to speed it up. A lot of people overlook proper indexing, but it is really a critical factor is database performance.

Is that 20 seconds for the initial response every time, or after a period of inactivity?

 

It is just the initial connection. Thereafter I get 0 to two second responses before getting data back. More complex joins taking a little longer. But most SELECT statements show sub-second responses.

For the initial connection I have even tried using the IP address in lieu of the sub.domain.net addressing. Made no determinable difference. I will experiment further as time permits.

KeyMan

 

A short update:

I tried running my program from a small e-book PC with an ATOM ( processor about 0 Mhz ) on a local wireless network and to my surprise, it connected within about 4 seconds. OS is WinXP.

Now I need to find out what slows down the init connect from my development PC. Dev PC is running MSSQL 2000 and MSSQL 2005. But even with all the SQL and attendant services stopped, it still takes 20+ seconds to connect!

Testing now on more PCs and configs.

KeyMan

 

Slow Connect Solved: "Native Client"

SOLVED: The problem turned out to be the SQL Native Client on my development PC. Once I disabled it, I could connect in about 4 seconds.

Once I re-enabled it, connection time went back to 20+ seconds. To disable/enable it I was just renaming the sqlncli.dll in my System32 folder.

Next step was to un-install, and re-install SQL Native Client (2005). Once re-installed it turned out to be the exact same DLL as I had previously. But now I could connect in 4 seconds again.

Whatever else the Native Client .MSI file does, it fixed the problem.

KeyMan

 

Ah! Very good. Thanks for posting the follow up!