Multiple Result reading is very slow.

[sunilpraktan]

when i use Query Multiple with Store Procedure and read results one by one then i notice that first result get read very fast but next result takes too much time to read. because of this performance we get is poor. please advise weather i am making something wrong or is there any way.
my code is as following

using (IDbConnection conn = new SqlConnection(connectionString))
{
var reader = conn.QueryMultiple("GetSalesData", new { @request = RequestValue }, commandType: CommandType.StoredProcedure);

                        var UnitPriceList = reader.Read<GetItemDetailsEntity>().ToList();
                        MC.UnitPriceList = UnitPriceList.ToList();
                        var QFRList = reader.Read<GetItemDetailsEntity>().ToList();
                        MC.QFRList = QFRList.ToList();
                        var DispatchList = reader.Read<GetItemDetailsEntity>().ToList();
                        MC.DispatchList = DispatchList.ToList();
                        var ProjectedDispList = reader.Read<GetItemDetailsEntity>().ToList();
                        MC.ProjectedDispList = ProjectedDispList.ToList();

                        strReturnData = ObjectSerializationService.ObjectToXML(MC);
                        return strReturnData;
                 }

[mgravell]

what are the relative sizes of the different grids here? Is the second one very wide (lots of columns), very tall (lots of rows), or does it have large payloads (huge strings or byte arrays in cells)? What you have looks fine. You could change ToList to AsList, but that's about it. I suspect that this is a bandwidth issue from one of the above issues. Note that for excessively tall grids it may be useful to read it unbuffered, but that will only help if you consume it via foreach immediately. It won't help if you just read it unbuffered and shove it into a list. The point of unbuffered is to allow you to process items as they come in, overlapping IO and processing, and reduce the perceived latency of the first row. If you aren't going to process it while it is arriving, it won't help.

…

On 2 Oct 2017 5:06 a.m., "sunilpraktan" ***@***.***> wrote: when i use Query Multiple with Store Procedure and read results one by one then i notice that first result get read very fast but next result takes too much time to read. because of this performance we get is poor. please advise weather i am making something wrong or is there any way. my code is as following using (IDbConnection conn = new SqlConnection(connectionString)) { var reader = conn.QueryMultiple("GetSalesData", new { @request <https://github.com/request> = RequestValue }, commandType: CommandType.StoredProcedure); var UnitPriceList = reader.Read<GetItemDetailsEntity>().ToList(); MC.UnitPriceList = UnitPriceList.ToList(); var QFRList = reader.Read<GetItemDetailsEntity>().ToList(); MC.QFRList = QFRList.ToList(); var DispatchList = reader.Read<GetItemDetailsEntity>().ToList(); MC.DispatchList = DispatchList.ToList(); var ProjectedDispList = reader.Read<GetItemDetailsEntity>().ToList(); MC.ProjectedDispList = ProjectedDispList.ToList(); strReturnData = ObjectSerializationService.ObjectToXML(MC); return strReturnData; } — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#854>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABDsGJXgwtUI468f8hqu3wBPPMpLEegks5soGFOgaJpZM4PqLOp> .

[mgravell]

also: when you run the procedure manually, does the second grid simply take longer to process? Note that there are some subtle config differences that can make ADO.NET and SSMS have different performance in some scenarios, especially around parameter sniffing and (separately) indexed calculated columns.

…

On 2 Oct 2017 5:06 a.m., "sunilpraktan" ***@***.***> wrote: when i use Query Multiple with Store Procedure and read results one by one then i notice that first result get read very fast but next result takes too much time to read. because of this performance we get is poor. please advise weather i am making something wrong or is there any way. my code is as following using (IDbConnection conn = new SqlConnection(connectionString)) { var reader = conn.QueryMultiple("GetSalesData", new { @request <https://github.com/request> = RequestValue }, commandType: CommandType.StoredProcedure); var UnitPriceList = reader.Read<GetItemDetailsEntity>().ToList(); MC.UnitPriceList = UnitPriceList.ToList(); var QFRList = reader.Read<GetItemDetailsEntity>().ToList(); MC.QFRList = QFRList.ToList(); var DispatchList = reader.Read<GetItemDetailsEntity>().ToList(); MC.DispatchList = DispatchList.ToList(); var ProjectedDispList = reader.Read<GetItemDetailsEntity>().ToList(); MC.ProjectedDispList = ProjectedDispList.ToList(); strReturnData = ObjectSerializationService.ObjectToXML(MC); return strReturnData; } — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#854>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABDsGJXgwtUI468f8hqu3wBPPMpLEegks5soGFOgaJpZM4PqLOp> .

[sunilpraktan]

MSSQL queries working fine and taking very less time but while reading in NextResult it is taking more time. say about 3000 rows with 100 columns. sometime it is taking more than 10 seconds which should not happen.
I will try with your first advise and let you know after trials. Thanks a Million for your support.

[NickCraver]

@sunilpraktan any update here?