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

LVL 1
slightlyoffAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
Did you tried "Select * from myTable" in your SQL?
I have the feeling that's your problem if myTable is huge....no matter what you do to fill in the New DataSet may take some time for large volumes of data.

0
 
lcohanDatabase AnalystCommented:
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.
0
 
CodeCruiserCommented:
There is a way. Get a faster connection between you computer and the remote server.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
CodeCruiserCommented:
> 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?
0
 
slightlyoffAuthor Commented:
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!!!
0
 
lcohanDatabase AnalystCommented:
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/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.