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,
精彩评论