SQL-Server-R-Services-Samples/SQLOptimizationTips-Resume-Matching/SQLR/experiment.ps1 at master · microsoft/SQL-Server-R-Services-Samples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
$count = 1
$TotalRows = 1100000
$vServerName = "SQLRTUTORIAL"
$vDatabaseName = "ResumeMatching"

$num_workload_group = 4
$batch_per_load = 2
$model = "rxBTrees"
$projectId = 1000001

$Start = 0
$Increment = $TotalRows/($num_workload_group*$batch_per_load)
$End = $Start+$Increment-1
$EndCtr = 0


Write-Host "Starting the prediction jobs for Project [$projectId]..."
while (($EndCtr -le $TotalRows) -and ($count -le $num_workload_group))
{
    # Set application name for SQLCMD command using the loop counter.
    # In SQL Server side, the resource governor classifier function will
    # assign the correct workload group (which represents a resource pool and
    # external resource pool pair)
    [string] $AppName = "$count - PredictionJob"

    # Generate two script blocks containing the SQLCMD command.
    # The wrapper stored procedure simply invokes the scoring procedure in a loop.
    $SqlScript = [ScriptBlock]::Create("Invoke-SqlCmd -Server `"" + $vServerName + "`" -Query `"EXEC score_for_matching_batch " + $model + ", " + $projectId + ", " + $Start + ", " + $End + ", 0.51`" -Database `"$vDatabaseName`" -QueryTimeout 0 -HostName `"$AppName`"")
    Start-Job -ScriptBlock $SqlScript
    $EndCtr += $Increment
    $Start += $Increment
    $End += $Increment

    if ($batch_per_load -ge 2) {
        $SqlScript = [ScriptBlock]::Create("Invoke-SqlCmd -Server `"" + $vServerName + "`" -Query `"EXEC score_for_matching_batch " + $model + ", " + $projectId + ", " + $Start + ", " + $End + ", 0.51`" -Database `"$vDatabaseName`" -QueryTimeout 0 -HostName `"$AppName`"")
        Start-Job -ScriptBlock $SqlScript
        $EndCtr += $Increment
        $Start += $Increment
        $End += $Increment
    }

    if ($batch_per_load -ge 3) {
        $SqlScript = [ScriptBlock]::Create("Invoke-SqlCmd -Server `"" + $vServerName + "`" -Query `"EXEC score_for_matching_batch " + $model + ", " + $projectId + ", " + $Start + ", " + $End + ", 0.51`" -Database `"$vDatabaseName`" -QueryTimeout 0 -HostName `"$AppName`"")
        Start-Job -ScriptBlock $SqlScript
        $EndCtr += $Increment
        $Start += $Increment
        $End += $Increment
    }

    if ($batch_per_load -ge 4) {
        $SqlScript = [ScriptBlock]::Create("Invoke-SqlCmd -Server `"" + $vServerName + "`" -Query `"EXEC score_for_matching_batch " + $model + ", " + $projectId + ", " + $Start + ", " + $End + ", 0.51`" -Database `"$vDatabaseName`" -QueryTimeout 0 -HostName `"$AppName`"")
        Start-Job -ScriptBlock $SqlScript
        $EndCtr += $Increment
        $Start += $Increment
        $End += $Increment
    }

    if ($batch_per_load -ge 5) {
        $SqlScript = [ScriptBlock]::Create("Invoke-SqlCmd -Server `"" + $vServerName + "`" -Query `"EXEC score_for_matching_batch " + $model + ", " + $projectId + ", " + $Start + ", " + $End + ", 0.51`" -Database `"$vDatabaseName`" -QueryTimeout 0 -HostName `"$AppName`"")
        Start-Job -ScriptBlock $SqlScript
        $EndCtr += $Increment
        $Start += $Increment
        $End += $Increment
    }

    $count += 1
}