Optimisation des requêtes SQL

-- Index simple sur une colonne
CREATE INDEX idx_customers_email 
ON Customers(Email);

-- Index sur plusieurs colonnes (composite)
CREATE INDEX idx_orders_customer_date 
ON Orders(CustomerID, OrderDate);

-- Index unique (pour garantir l'unicité)
CREATE UNIQUE INDEX idx_users_username 
ON Users(Username);

-- Index avec colonnes incluses (covering index)
CREATE INDEX idx_orders_covering 
ON Orders(CustomerID, OrderDate)
INCLUDE (TotalAmount, Status);

-- Dans SSMS : Ctrl + L pour le plan estimé
-- Ou Ctrl + M pour l'activer, puis exécuter la requête

-- Via code SQL
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM Orders WHERE CustomerID = 123;
GO
SET SHOWPLAN_ALL OFF;
GO

-- Afficher les statistiques d'exécution
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM Orders WHERE CustomerID = 123;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- ❌ Mauvais : récupère toutes les colonnes (peut-être inutiles)
SELECT * FROM Customers WHERE City = 'Lausanne';

-- ✅ Bon : seulement les colonnes nécessaires
SELECT CustomerID, CustomerName, Email 
FROM Customers 
WHERE City = 'Lausanne';

-- ❌ Moins performant avec grandes tables
SELECT * FROM Customers 
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

-- ✅ Plus performant (s'arrête dès la première correspondance)
SELECT * FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

-- ❌ L'index ne peut pas être utilisé (fonction sur colonne)
SELECT * FROM Orders 
WHERE YEAR(OrderDate) = 2025;

-- ✅ L'index peut être utilisé
SELECT * FROM Orders 
WHERE OrderDate >= '2025-01-01' 
  AND OrderDate < '2026-01-01';

-- ❌ UNION élimine les doublons (coûteux : tri + distinct)
SELECT CustomerID FROM CustomersEU
UNION
SELECT CustomerID FROM CustomersUS;

-- ✅ UNION ALL garde les doublons (rapide : pas de tri)
SELECT CustomerID FROM CustomersEU
UNION ALL
SELECT CustomerID FROM CustomersUS;

-- Pagination efficace (SQL Server 2012+)
SELECT CustomerID, CustomerName, Email
FROM Customers
ORDER BY CustomerID
OFFSET 100 ROWS      -- Sauter les 100 premiers
FETCH NEXT 20 ROWS ONLY;  -- Prendre les 20 suivants

-- ❌ MAUVAIS : Curseur (traitement ligne par ligne, très lent)
DECLARE @CustomerID INT;
DECLARE customer_cursor CURSOR FOR 
    SELECT CustomerID FROM Customers;
OPEN customer_cursor;
FETCH NEXT FROM customer_cursor INTO @CustomerID;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Orders SET Processed = 1 WHERE CustomerID = @CustomerID;
    FETCH NEXT FROM customer_cursor INTO @CustomerID;
END;
CLOSE customer_cursor;
DEALLOCATE customer_cursor;

-- ✅ BON : Opération ensembliste (beaucoup plus rapide)
UPDATE o
SET Processed = 1
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

-- Voir les statistiques d'une table
DBCC SHOW_STATISTICS ('Customers', 'idx_customers_city');

-- Mettre à jour les statistiques d'une table
UPDATE STATISTICS Customers;

-- Mettre à jour toutes les stats de la BDD
EXEC sp_updatestats;

-- Reconstruire un index (recrée aussi les stats)
ALTER INDEX idx_customers_email ON Customers REBUILD;

-- Réorganiser un index (moins lourd que REBUILD)
ALTER INDEX idx_customers_email ON Customers REORGANIZE;

-- Voir la fragmentation des index
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC;

-- Top 10 requêtes les plus longues
SELECT TOP 10
    qs.total_elapsed_time / 1000000.0 AS [Total Time (s)],
    qs.execution_count AS [Executions],
    qs.total_elapsed_time / qs.execution_count / 1000.0 AS [Avg Time (ms)],
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2) + 1) AS [Query Text]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_elapsed_time DESC;

-- Index suggérés par SQL Server
SELECT 
    mid.statement AS [Table],
    mid.equality_columns AS [Equality Columns],
    mid.inequality_columns AS [Inequality Columns],
    mid.included_columns AS [Included Columns],
    migs.avg_total_user_cost * migs.avg_user_impact * 
        (migs.user_seeks + migs.user_scans) AS [Impact],
    'CREATE INDEX idx_' + REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.', '_') + 
    ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + 
    CASE WHEN mid.inequality_columns IS NOT NULL 
        THEN ',' + mid.inequality_columns ELSE '' END + ')' +
    CASE WHEN mid.included_columns IS NOT NULL 
        THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END 
    AS [Create Statement]
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig 
    ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs 
    ON mig.index_group_handle = migs.group_handle
ORDER BY [Impact] DESC;