asked on

SQL Server Connection Slow for VB.NET program

I have a program I use to browse records for the sake of approving, rejecting and deleting applicants.

The database is an MS SQL Server 2005 database, in a shared hosting environment.
Our website allows people to register online, but I want the review of applicants to happen inside the program I wrote.

The program works fine - except it's very slow. Accessing the information online is fast...

I've attached the code I use to do the connection. When I run this code to select all the records (currently 60), it takes 15-20 seconds to return.

Is there a faster way to connect to a remote sql server database?

Thanks for your help!!!

Thanks for your help!!!

sql = "Select * from myTable"            sqlConn.ConnectionString =  MyConnectionString
            sqlConn.Open()

            'Try
            da = New SqlDataAdapter(sql, sqlConn)
            ds = New DataSet
            da.Fill(ds, "table1")

Open in new window

lcohan

Did you tested the query directly against your SQL server first to see if is not taking indeed so much to complete in which case optimizations/indexes may be needed.

Nasir Razzaq

There is a way. Get a faster connection between you computer and the remote server.

Nasir Razzaq

> but I want the review of applicants to happen inside the program I wrote.

Is that a must? How about a secure page on the website itself as you said the access online is fast?

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightlyoff

ASKER

Thank you!

It's been a while since I was able to get to test this out. My statments were calling quite a bit. I simplified them as you suggested and it does appear to speed up a little.

Not perfect, but better than it was.

Thank you for your help!!!

lcohan

You should be able to optimize them even more (unless you get huge data sets in client) by looking at the query plans and add eventual missing indexes. I recomand using SQL performance dashboards and Missing Indexes in particular to boost your SQL performance.

http://www.microsoft.com/download/en/details.aspx?id=22602
http://www.sql-server-performance.com/2007/bm-performance-dashboard-2005/