0

I'm using this function to return a value from multiple databases across three SQL instances (On the same server). I'm looping though a DataGridView that lists all the databases on one of the three SQL instances.

Is there a faster way of doing this? It's quite slow using this method.

Function DatabaseStatus(ByVal SQLServer As String, ByVal Database As String)
    Dim myConn As New SqlConnection("Server=" & SQLServer & ";User Id=USER;Password=PASSWORD;Database=" & Database & ";")
    Dim Status As String = ""
    If myConn.State = ConnectionState.Closed Then
        myConn.Open()
    End If
    Dim query As String = "SELECT STATEMENT;"
    Dim myCommand As New SqlCommand(query, myConn)
    Try
      If myCommand.ExecuteScalar().ToString.ToUpper = "OK" Then
            Status = "Ready"
        End If
    Catch ex As Exception
        Status = "Unknown"
    Finally
        myConn.Dispose()
    End Try
    Return Status
End Function

EDIT - SELECT statement example:

IF OBJECT_ID('TABLENAME') IS NOT NULL SELECT [Setting] FROM [TABLENAME] 
WHERE [Section] = 'platform' and [Setting] = 'server' ELSE SELECT 'UNKNOWN';
madlan
  • 1,387
  • 10
  • 34
  • 63
  • Side note. I would recommend taking the "SELECT STATEMENT" out of your code and use EF or LINQ instead. It will really help with maintainability. If your query's are extra complicated, you can use Stored Procedures. – Chase Florell Oct 16 '10 at 14:05

2 Answers2

1

From speed tests that have been done, using SQL Adapters (ADO.Net) is the fastest way to get your information. However, considering you're on .Net, I'd recommend using LINQ if possible - it's much much cleaner and quicker to work with. Here's a stackoverflow question that might help you.

That being said, unless your queries are massive, what you're using should be extremely fast. It's possible that the databases that you're querying against are slow (shared hosting, network latency, number of executions, hardware on the server, and other issues).

I'd recommend timing one request for the query you're trying to access to get a general gauge as to what it takes to execute it. It could also be that your query is very complex with a lot of joins, etc.

Also, AnjLab's SQLProfiler is a really good tool for dealing with MSSql database stuff

Community
  • 1
  • 1
MunkiPhD
  • 3,636
  • 1
  • 29
  • 51
  • I've added my select statement to the question, it's selecting a single numerical (integer) value from a table, the problem is I have 30+ databases being looped through (populating a DataGridView with details) – madlan Oct 17 '10 at 11:50
  • Then the problem might just end up being an issue with what you're trying to accomplish. Maybe you could use ajax calls to fill the datagridview. Maybe you're trying to loop through the databases when it's really a "it'd be nice if" feature as opposed to a requirement. Did you try timing how long each call takes? There's a lot of things that come into context, but your call seems simple enough. – MunkiPhD Oct 18 '10 at 12:11
0

Take a look at SQL Server Management Objects (SMO). It may help you do what you need and more. Hope that helps,

Abelardo Cecena
  • 23
  • 5
  • I've just moved away from SMO, I'm using .net 4 - SMO has some issues with it. Also, I'm working against SQL2000 to SQL2008R2 so having to use stored procedures for basic tasks such as creating database users. – madlan Oct 17 '10 at 11:39