Vous pensez qu'en ne lisant qu'un seul enregistrement sur une instance de SqlDataReader retournée par ExecuteReader vous n'allez pas provoquer le transfert sur le réseau de l'ensemble du jeu de résultats ?
Faux :-)
Mais la bonne nouvelle c'est que c'est en partie corrigeable.
La question de la lecture partielle du jeu de résultats d'une requête ne se pose généralement pas : il est toujours préférable d'utiliser des requêtes ne retournant que les données nécessaires.
Cependant l'arrêt de la lecture d'un jeu de résultats avant d'en avoir atteint la fin peut se justifier, comme par exemple dans le cas de l'arrêt d'un traitement pour cause de dépassement de délai : il n'y a aucun intérêt à finir de lire les données pour un traitement dont le résultat ne sera pas utilisé.
Si dans ce cas vous vous contentez de sortir de la boucle de lecture des enregistrements, vous avez un problème.
Contexte technique de rédaction de ce post : .NET 4.5 et SQL Server 2014 Express (mais de mémoire c'est comme ça depuis minimum .NET 2.0).
Source de données utilisée : table Person.Person de la base d'exemple Adventure Works 2014 (Adventure Works 2014 Full Database Backup.zip) du 25/07/2014, environ 19000 enregistrements pour un total de plus de 20 Mo.
Le problème
Utilisé avec une requête du type "SELECT * FROM [Person].[Person]" le code d'accès aux données suivant, tout à fait standard mais simplifié pour améliorer la lisibilité, entrainera un transfert complet des données malgré le fait qu'il ne fasse qu'un seul appel à Read :
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.CommandType = CommandType.Text;
command.CommandTimeout = 10 * 60;
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
Object result = reader.GetValue(0);
}
}
}
Ce n'est pas forcément le comportement auquel on s'attend de la part d'une API de ce type.
Creusons un peu
Instrumentons un peu le code pour relever les temps d'exécution et les volumes de données transférés en divers points du code (grâce au mécanisme de statistiques disponible depuis ADO.NET 2.0 qui sera suffisant dans le cas présent) :
Stopwatch timing = Stopwatch.StartNew();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.StatisticsEnabled = true;
connection.Open();
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.CommandType = CommandType.Text;
command.CommandTimeout = 10 * 60;
PrintTimingAndStatistics("ExecuteReader (before)", timing, connection.RetrieveStatistics());
using (SqlDataReader reader = command.ExecuteReader())
{
PrintTimingAndStatistics("ExecuteReader (after)", timing, connection.RetrieveStatistics());
PrintTimingAndStatistics("Read (before)", timing, connection.RetrieveStatistics());
reader.Read();
PrintTimingAndStatistics("Read (after)", timing, connection.RetrieveStatistics());
PrintTimingAndStatistics("GetValue (before)", timing, connection.RetrieveStatistics());
Object result = reader.GetValue(0);
PrintTimingAndStatistics("GetValue (after)", timing, connection.RetrieveStatistics());
PrintTimingAndStatistics("SqlDataReader.Close (before)", timing, connection.RetrieveStatistics());
reader.Close();
PrintTimingAndStatistics("SqlDataReader.Close (after)", timing, connection.RetrieveStatistics());
PrintTimingAndStatistics("SqlDataReader cleanup (before)", timing, connection.RetrieveStatistics());
}
PrintTimingAndStatistics("SqlDataReader cleanup (after)", timing, connection.RetrieveStatistics());
PrintTimingAndStatistics("SqlCommand cleanup (before)", timing, connection.RetrieveStatistics());
}
PrintTimingAndStatistics("SqlCommand cleanup (after)", timing, connection.RetrieveStatistics());
PrintTimingAndStatistics("SqlConnection.Close (before)", timing, connection.RetrieveStatistics());
connection.Close();
PrintTimingAndStatistics("SqlConnection.Close (after)", timing, connection.RetrieveStatistics());
}
private static void PrintTimingAndStatistics(String label, Stopwatch timing, IDictionary connectionStatistics)
{
Console.WriteLine("{0} : {1} : {2} rows / {3} bytes / {4} buffers",
timing.Elapsed,
label,
connectionStatistics["SelectRows"],
connectionStatistics["BytesReceived"],
connectionStatistics["BuffersReceived"]
);
}
A titre de comparaison, nous allons effectuer des mesures en utilisant 2 requêtes : une récupérant toutes les données de la table, l'autre ne récupérant que le premier enregistrement :
- fullTableContentQuery : environ 19000 enregistrements pour un total de plus de 20 Mo.
SELECT [BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate] FROM [Person].[Person] ORDER BY [BusinessEntityID] ASC - singleRowQuery : le premier enregistrement seulement, un peu plus de 1 Ko.
SELECT TOP 1 [BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate] FROM [Person].[Person] ORDER BY [BusinessEntityID] ASC
00:00:00.0001810 : ExecuteReader (before) : 0 rows / 0 bytes / 0 buffers
00:00:00.0163239 : ExecuteReader (after) : 0 rows / 1263 bytes / 1 buffers
00:00:00.0165160 : Read (before) : 0 rows / 1263 bytes / 1 buffers
00:00:00.0166315 : Read (after) : 0 rows / 1263 bytes / 1 buffers
00:00:00.0167334 : GetValue (before) : 0 rows / 1263 bytes / 1 buffers
00:00:00.0170658 : GetValue (after) : 0 rows / 1263 bytes / 1 buffers
00:00:00.0173759 : SqlDataReader.Close (before) : 0 rows / 1263 bytes / 1 buffers
00:00:00.0176380 : SqlDataReader.Close (after) : 1 rows / 1263 bytes / 1 buffers
00:00:00.0179771 : SqlDataReader cleanup (before) : 1 rows / 1263 bytes / 1 buffers
00:00:00.0183784 : SqlDataReader cleanup (after) : 1 rows / 1263 bytes / 1 buffers
00:00:00.0187168 : SqlCommand cleanup (before) : 1 rows / 1263 bytes / 1 buffers
00:00:00.0190573 : SqlCommand cleanup (after) : 1 rows / 1263 bytes / 1 buffers
00:00:00.0192853 : SqlConnection.Close (before) : 1 rows / 1263 bytes / 1 buffers
00:00:00.0194869 : SqlConnection.Close (after) : 1 rows / 1263 bytes / 1 buffers
On notera au passage que la valeur SelectRows des statistiques de la connexion n'est alimentée qu'une fois le DataReader clôt (comportement similaire à RecordsAffected).
Résultats pour fullTableContentQuery :
00:00:00.2026912 : ExecuteReader (before) : 0 rows / 0 bytes / 0 buffers
00:00:00.2425445 : ExecuteReader (after) : 0 rows / 8000 bytes / 1 buffers
00:00:00.2427622 : Read (before) : 0 rows / 8000 bytes / 1 buffers
00:00:00.2429330 : Read (after) : 0 rows / 8000 bytes / 1 buffers
00:00:00.2430291 : GetValue (before) : 0 rows / 8000 bytes / 1 buffers
00:00:00.2431401 : GetValue (after) : 0 rows / 8000 bytes / 1 buffers
00:00:00.2432171 : SqlDataReader.Close (before) : 0 rows / 8000 bytes / 1 buffers
00:00:34.6341411 : SqlDataReader.Close (after) : 19972 rows / 26484333 bytes / 6648 buffers
00:00:34.6344105 : SqlDataReader cleanup (before) : 19972 rows / 26484333 bytes / 6648 buffers
00:00:34.6345798 : SqlDataReader cleanup (after) : 19972 rows / 26484333 bytes / 6648 buffers
00:00:34.6347301 : SqlCommand cleanup (before) : 19972 rows / 26484333 bytes / 6648 buffers
00:00:34.6348544 : SqlCommand cleanup (after) : 19972 rows / 26484333 bytes / 6648 buffers
00:00:34.6349754 : SqlConnection.Close (before) : 19972 rows / 26484333 bytes / 6648 buffers
00:00:34.6352253 : SqlConnection.Close (after) : 19972 rows / 26484333 bytes / 6648 buffers
La fermeture du DataReader entraine clairement le téléchargement de ce qu'il restait du jeu de résultats : nous n'avions transféré que 8000 octets avant l'appel à SqlDataReader.Close, 25Mo après.
Raison de ce comportement
Ce comportement n'est pas clairement expliqué en tant que tel dans la documentation de la méthode Close de SqlDataReader, mais la section "remarques" apporte tout de même une piste :"The Close method fills in the values for output parameters, return values and RecordsAffected, increasing the time that it takes to close a SqlDataReader that was used to process a large or complex query."Ils ont juste oublié de dire clairement que récupérer les données pour les paramètres output, la valeur de retour et le nombre d'enregistrements affectés implique de transférer l'ensemble des données du jeu de résultats.
Logique dans un sens, mais le préciser clairement aurait été intéressant.
Solution
La solution au problème de transfert de données inutiles est fournie au même endroit :"When the return values and the number of records affected by a query are not significant, the time that it takes to close the SqlDataReader can be reduced by calling the Cancel method of the associated SqlCommand object before calling the Close method."Ce qui convient parfaitement à notre cas d'annulation de traitement.
En ajoutant l'appel à SqlCommand.Cancel juste après l'appel à GetValue dans notre méthode de test et en l'exécutant de nouveau avec la requête fullTableContentQuery, la différence est assez claire :
00:00:00.0629712 : ExecuteReader (before) : 0 rows / 0 bytes / 0 buffers
00:00:00.0730612 : ExecuteReader (after) : 0 rows / 8000 bytes / 1 buffers
00:00:00.0731925 : Read (before) : 0 rows / 8000 bytes / 1 buffers
00:00:00.0732918 : Read (after) : 0 rows / 8000 bytes / 1 buffers
00:00:00.0733603 : GetValue (before) : 0 rows / 8000 bytes / 1 buffers
00:00:00.0734498 : GetValue (after) : 0 rows / 8000 bytes / 1 buffers
00:00:00.0735154 : SqlCommand.Cancel (before) : 0 rows / 8000 bytes / 1 buffers
00:00:00.0736983 : SqlCommand.Cancel (after) : 0 rows / 8000 bytes / 1 buffers
00:00:00.0738457 : SqlDataReader.Close (before) : 0 rows / 8000 bytes / 1 buffers
00:00:00.0983481 : SqlDataReader.Close (after) : 0 rows / 88170 bytes / 18 buffers
00:00:00.0985409 : SqlDataReader cleanup (before) : 0 rows / 88170 bytes / 18 buffers
00:00:00.0987143 : SqlDataReader cleanup (after) : 0 rows / 88170 bytes / 18 buffers
00:00:00.0988653 : SqlCommand cleanup (before) : 0 rows / 88170 bytes / 18 buffers
00:00:00.0990717 : SqlCommand cleanup (after) : 0 rows / 88170 bytes / 18 buffers
00:00:00.0992370 : SqlConnection.Close (before) : 0 rows / 88170 bytes / 18 buffers
00:00:00.0994969 : SqlConnection.Close (after) : 0 rows / 88170 bytes / 18 buffers
Nous récupèrons toujours trop de données, mais nettement moins qu'avant (300 fois moins dans le cas présent).
On notera que la valeur SelectRows des statistiques de la connexion n'est pas alimentée.
Il est vraiment important d'appeler Cancel avant Close, surtout d'après cette remarque sur la documentation de cette dernière :
"In some, rare, cases, if you call ExecuteReader then call Close (implicitily or explicitly) before calling Cancel, and then call Cancel, the cancel command will not be sent to SQL Server and the result set can continue to stream after you call Close. To avoid this, make sure that you call Cancel before closing the reader or connection."Durant tous mes tests j'ai eu le comportement observable sur les mesures affichées plus haut : la méthode SqlDataReader.Close est bloquante jusqu'à fin de transfert des données, donc un appel à SqlCommand.Cancel après ne servirait strictement à rien.
Enfin il est à noter que ExecuteScalar présente le même comportement car son code ne fait pas appel à Cancel avant l'appel à Close, et que dans son cas il est impossible de demander l'annulation de la commande : si ExecuteScalar est utilisé pour lire une valeur sur un jeu d'enregistrements, toutes les données seront transférées au moment de la fermeture du reader utilisé en interne (voir sources de la classe disponibles dans les reference sources).
