开发者

Querying values in multiple databases

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=" & Da开发者_JS百科tabase & ";")
    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';


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


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜