How to query multiple tables for a value in sharded SQL DB more efficiently

I have a script that simply executes a SQL query against a single table in a single DB that worked well, until the developers implemented DB sharding.

Now, there are 6 different tables I need to check on two different SQL servers. 

I got something working but it is a total hack job, plus if they decide to add more servers the code isn't very extensible.

Here's what I have:

$valueBeingSearchedFor = 'someData'

    #DB Query Strings
    $Query = "Select * FROM [tableWithData].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'"
    $Query1 = "Select * FROM [tableWithData_1].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'"
    $Query2 = "Select * FROM [tableWithData_2].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'"
    $Query3 = "Select * FROM [tableWithData_3].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'"
    $Query4 = "Select * FROM [tableWithData_4].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'"
    $Query5 = "Select * FROM [tableWithData_5].[dbo].[tbl_datas] where dataIsHere = '$valueBeingSearchedFor'"

    #  DB connection strings
    $connection = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR5\I01;Initial Catalog=tableWithData;User ID=user;Password=Pa55word;')
    $connection1 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR5\I01;Initial Catalog=tableWithData_1;User ID=user;Password=Pa55word;')
    $connection2 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR6\I02;Initial Catalog=tableWithData_2;User ID=user;Password=Pa55word;')
    $connection3 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR6\I02;Initial Catalog=tableWithData_3;User ID=user;Password=Pa55word;')
    $connection4 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR6\I02;Initial Catalog=tableWithData_4;User ID=user;Password=Pa55word;')
    $connection5 = New-Object System.Data.SqlClient.SQLConnection('Data Source=SQLSVR6\I02;Initial Catalog=tableWithData_5;User ID=user;Password=Pa55word;')
        

Write-host "Going to check for $valueBeingSearchedFor"
#connects to SQL and grabs the data

Write-host "Checking tableWithData"
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $Query
$dataSet = New-Object System.Data.DataSet
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
$dataExistsInDB = $dataAdapter.Fill($dataSet) #outputs a 1 if the query returned rows
$dataExistsInDB
$connection.Close()

Write-host "Checking tableWithData_1"
$connection1.Open()
$command1 = $connection1.CreateCommand()
$command1.CommandText = $Query1
$dataSet1 = New-Object System.Data.DataSet
$dataAdapter1 = New-Object System.Data.SqlClient.SqlDataAdapter($command1)
$dataExistsInDB1 = $dataAdapter1.Fill($dataSet1) #outputs a 1 if the query returned rows
$dataExistsInDB1
$connection1.Close()

Write-host "Checking tableWithData_2"
$connection2.Open()
$command2 = $connection2.CreateCommand()
$command2.CommandText = $Query2
$dataSet2 = New-Object System.Data.DataSet
$dataAdapter2 = New-Object System.Data.SqlClient.SqlDataAdapter($command2)
$dataExistsInDB2 = $dataAdapter2.Fill($dataSet2) #outputs a 1 if the query returned rows
$dataExistsInDB2
$connection2.Close()

Write-host "Checking tableWithData_3"
$connection3.Open()
$command3 = $connection3.CreateCommand()
$command3.CommandText = $Query3
$dataSet3 = New-Object System.Data.DataSet
$dataAdapter3 = New-Object System.Data.SqlClient.SqlDataAdapter($command3)
$dataExistsInDB3 = $dataAdapter3.Fill($dataSet3) #outputs a 1 if the query returned rows
$dataExistsInDB3
$connection3.Close()

Write-host "Checking tableWithData_4"
$connection4.Open()
$command4 = $connection4.CreateCommand()
$command4.CommandText = $Query4
$dataSet4 = New-Object System.Data.DataSet
$dataAdapter4 = New-Object System.Data.SqlClient.SqlDataAdapter($command4)
$dataExistsInDB4 = $dataAdapter4.Fill($dataSet4) #outputs a 1 if the query returned rows
$dataExistsInDB4
$connection4.Close()

Write-host "Checking tableWithData_5"
$connection5.Open()
$command5 = $connection5.CreateCommand()
$command5.CommandText = $Query5
$dataSet5 = New-Object System.Data.DataSet
$dataAdapter5 = New-Object System.Data.SqlClient.SqlDataAdapter($command5)
$dataExistsInDB5 = $dataAdapter5.Fill($dataSet5) #outputs a 1 if the query returned rows
$dataExistsInDB5
$connection5.Close()
     
Write-host "Done checking DBs"
# outputs the results
if ($dataExistsInDB -eq 1) #if the query returned a result, query data
    {
    $objTable = $DataSet.Tables[0]
    $objTable | Out-GridView -Title "Query Results - found in tableWithData"
                
    } # end if

elseif ($dataExistsInDB1 -eq 1) #if the query returned a result, query data
    {
    $objTable = $DataSet1.Tables[0]
    $objTable | Out-GridView -Title "Query Results - found in tableWithData_1"
                
    } # end if

elseif ($dataExistsInDB2 -eq 1) #if the query returned a result, query data
    {
    $objTable = $DataSet2.Tables[0]
    $objTable | Out-GridView -Title "Query Results - found in tableWithData_2"
                
    } # end if

elseif ($dataExistsInDB3 -eq 1) #if the query returned a result, query data
    {
    $objTable = $DataSet3.Tables[0]
    $objTable | Out-GridView -Title "Query Results - found in tableWithData_3"
                
    } # end if


elseif ($dataExistsInDB4 -eq 1) #if the query returned a result, query data
    {
    $objTable = $DataSet4.Tables[0]
    $objTable | Out-GridView -Title "Query Results - found in tableWithData_4"
                
    } # end if


elseif ($dataExistsInDB5 -eq 1) #if the query returned a result, query data
    {
    $objTable = $DataSet5.Tables[0]
    $objTable | Out-GridView -Title "Query Results - found in tableWithData_5"
                
    } # end if

else # if the query did not return results, the data doesn't exist in the DB
    {
    [System.Windows.Forms.MessageBox]::Show("This data ID does not exist in any database" , "data ID does not exist in any DB!")
    }

Considering the server name, instance, and table is a little different for each shard, is there anything I could do to make this a bit more extensible or am I stuck with adding more if statements?

August 26th, 2015 5:05pm

Hello cyberfreak

Build a function which gets the different data via parameters.

After that you could also add more performance to it by processing parallel. -> for example with jobs.

Look here at the bottom - nice C# Code for better understanding:

Li

August 26th, 2015 6:22pm

Just add the results of all of the queries together into one object.

Run you selects and connections as a function and call it 'n' times.

$data=@()
$data+Get-Data -dbname mydb1 -tablename tableWithData_1 -DataSource SQLSVR5\I01

Now just process $data as one table.

Simple and can be extended easily.

August 26th, 2015 6:29pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics