46 lines
2.9 KiB
PowerShell
46 lines
2.9 KiB
PowerShell
# Lecture seule — timing des patterns SQL BATIK_TEST (OLE DB HFSQL)
|
|
$ErrorActionPreference = "Stop"
|
|
$connStr = "Provider=PCSoft.HFSQL;Data Source=127.0.0.1;Port=4900;Initial Catalog=BATIK_TEST;User ID=susana;Password=123;"
|
|
|
|
function TimeQuery($cn, $sql) {
|
|
$sw = [System.Diagnostics.Stopwatch]::StartNew()
|
|
$rs = New-Object -ComObject ADODB.Recordset
|
|
$rs.Open($sql, $cn, 1, 1)
|
|
$n = 0
|
|
while (-not $rs.EOF) { $n++; $rs.MoveNext() }
|
|
$rs.Close()
|
|
$sw.Stop()
|
|
return "$([math]::Round($sw.Elapsed.TotalSeconds,2))s rows=$n"
|
|
}
|
|
|
|
$cn = New-Object -ComObject ADODB.Connection
|
|
$cn.ConnectionTimeout = 15
|
|
$cn.Open($connStr)
|
|
Write-Host "CONNECTED"
|
|
|
|
# Full scans (reproduces the current bottleneck)
|
|
Write-Host "--- FULL SCANS ---"
|
|
Write-Host ("Intervention (25 cols) : " + (TimeQuery $cn "SELECT IN_PKID, IN_RefInterne, IN_IDClient, IN_IDLieu, IN_IDDossier, IN_IDTypeIntervention, IN_IDEtatIntervention, IN_Titre, IN_NumIntervention, IN_DateIntervention, IN_DureePrevue, IN_NumDevis, IN_NumFacture, Archive, IN_NoteInterne, IN_Description, IN_RefExterne, IN_IDSociete, IN_IDDemandeur, IN_IDSuiviPar, IN_IDImport, IN_AttestationTVAPresente FROM Intervention"))
|
|
Write-Host ("Intervention_Personne (3 cols): " + (TimeQuery $cn "SELECT IP_PKID, IP_IDIntervention, IP_IDPersonne FROM Intervention_Personne"))
|
|
Write-Host ("EtatIntervention (6 cols) : " + (TimeQuery $cn "SELECT EI_PKID, EI_IDEtat, EI_DateDebut, EI_DateFin, EI_Description, EI_IDIntervention FROM EtatIntervention"))
|
|
Write-Host ("Personne (3 cols) : " + (TimeQuery $cn "SELECT PE_PKID, PE_Nom, PE_Prenom FROM Personne"))
|
|
|
|
# LIMIT pagination test
|
|
Write-Host "--- LIMIT / TOP ---"
|
|
Write-Host ("Intervention LIMIT 0,200 : " + (TimeQuery $cn "SELECT IN_PKID, IN_RefInterne, IN_Titre FROM Intervention ORDER BY IN_DateIntervention DESC LIMIT 0,200"))
|
|
Write-Host ("Intervention TOP 200 : " + (TimeQuery $cn "SELECT TOP 200 IN_PKID, IN_RefInterne, IN_Titre FROM Intervention ORDER BY IN_DateIntervention DESC"))
|
|
|
|
# WHERE IN test (200 ids) on Intervention_Personne
|
|
$rsIds = New-Object -ComObject ADODB.Recordset
|
|
$rsIds.Open("SELECT IN_PKID FROM Intervention ORDER BY IN_DateIntervention DESC LIMIT 0,200", $cn, 1, 1)
|
|
$list = New-Object System.Collections.Generic.List[string]
|
|
while (-not $rsIds.EOF -and $list.Count -lt 200) { $list.Add([string]$rsIds.Fields.Item("IN_PKID").Value); $rsIds.MoveNext() }
|
|
$rsIds.Close()
|
|
$inClause = ($list -join ",")
|
|
Write-Host "--- WHERE IN (200 intervention ids) ---"
|
|
Write-Host ("Intervention_Personne WHERE IN : " + (TimeQuery $cn "SELECT IP_PKID, IP_IDIntervention, IP_IDPersonne FROM Intervention_Personne WHERE IP_IDIntervention IN ($inClause)"))
|
|
Write-Host ("EtatIntervention WHERE IN : " + (TimeQuery $cn "SELECT EI_PKID, EI_IDEtat, EI_DateDebut, EI_DateFin, EI_Description, EI_IDIntervention FROM EtatIntervention WHERE EI_IDIntervention IN ($inClause)"))
|
|
|
|
$cn.Close()
|
|
Write-Host "DONE"
|