0

I have a performance problem with SqlDataReader object.

I am used to use Entity Framework but for a specific reason, I should use ADO.NET queries in my app.

The problem is, when I execute my query, it takes almost 10 seconds.

I run exactly the same query in SQL Server Management Studio, it takes only 1 second.

I thought that maybe it's the SqlDataReader and I tried to fill a DataTable with a SqlDataAdapter, but I get the same result.

What is wrong with my code? Is there any way to improve the performance of SqlDataReader?

Here is the query and C# code.

string commandText = "SELECT Pati_FirstName, Pati_LastName, Pati_Gender AS Gender, "
        + " Pati_HeadQuarterID, Pati_AgencyID, Pati_Address1, Pati_Address2, Pati_BirthDate, Pati_FullName, Pati_FirstNameIfDiff, Pati_LastNameIfDiff, Pati_FootSize, Pati_ID, Pati_Height, Pati_MailAddress,"
        + " Pati_MobileNumber, Pati_PhoneNumber, Pati_PostCode, Pati_SecurityNumber, Pati_Weight,"
        + " Agen_Name, Head_Name, pat.Path_Name AS Pati_Pathologie,"
        + " gender.Capt_CulturInfo AS Pati_Gender, prof.Capt_CulturInfo AS Pati_Profession, title.Capt_CulturInfo AS Pati_Title, sport1.Capt_CulturInfo AS Pati_Sport1, sport2.Capt_CulturInfo AS Pati_Sport2,"
        + " p1.Pres_FullName AS Prescriber1, p2.Pres_FullName AS Prescriber2, pSender.Pres_FullName AS SentBy, Prac_FullName, City_Name"

        + " FROM Patient INNER JOIN HeadQuarter ON Head_ID = Pati_HeadQuarterID INNER JOIN"
        + " Agency ON Agen_ID = Pati_AgencyID LEFT OUTER JOIN"
        + " CustomCaption AS gender ON gender.Capt_Family = 'Pati_Gender' AND gender.Capt_Code = Pati_Gender LEFT OUTER JOIN"
        + " CustomCaption AS prof ON prof.Capt_Family = 'profession' AND prof.Capt_Code = Pati_Profession LEFT OUTER JOIN"
        + " Pathology AS pat ON pat.Path_ID = Pati_Pathologie LEFT OUTER JOIN"
        + " CustomCaption AS title ON title.Capt_Family = 'title' AND title.Capt_Code = Pati_Title LEFT OUTER JOIN"
        + " CustomCaption AS sport1 ON sport1.Capt_Family = 'sports' AND sport1.Capt_Code = Pati_Sport1 LEFT OUTER JOIN"
        + " CustomCaption AS sport2 ON sport2.Capt_Family = 'sports' AND sport2.Capt_Code = Pati_Sport2 LEFT OUTER JOIN"
        + " Prescriber AS p1 ON p1.Pres_ID = Pati_Doctor1 LEFT OUTER JOIN"
        + " Prescriber AS p2 ON p2.Pres_ID = Pati_Doctor2 LEFT OUTER JOIN"
        + " Prescriber AS pSender ON pSender.Pres_ID = Pati_SentBy LEFT OUTER JOIN"
        + " Practitioner ON Prac_ID = Pati_PracticionerID LEFT OUTER JOIN"
        + " City ON City_ID = Pati_CityID"
        + " WHERE Pati_Deleted IS NULL AND Pati_AgencyID = @AgencyID ORDER BY Pati_LastName, Pati_FirstName"; 

List<VPatient> retVal = new List<DataRepository.VPatient>();

try
{
    using (SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
    {
        using (SqlCommand sqlComm = new SqlCommand("", sqlConn))
        {
            sqlComm.CommandText = commandText.Replace("_CulturInfo", "_" + currentCultur);
            sqlComm.Parameters.AddWithValue("@AgencyID", agencyID);

            sqlComm.Connection.Open();

            // This line takes 10 seconds. 
            SqlDataReader sqlRd = sqlComm.ExecuteReader();

            // I tried this as an alternative, it doesn't change anything.
            // SqlDataAdapter sqlAdp = new SqlDataAdapter(sqlComm);
            // System.Data.DataTable dtPatients = new System.Data.DataTable();
            // sqlAdp.Fill(dtPatients);

            VPatient pat;

            while (sqlRd.Read())
            {
                pat = new VPatient();
                pat.Pati_HeadQuarterID = sqlRd["Pati_HeadQuarterID"].ToString();
                pat.Pati_AgencyID = sqlRd["Pati_AgencyID"].ToString();
                ....

                retVal.Add(pat);
            }
        }
    }
}
catch (Exception ex)
{
    CoreMethods.ParseError(ex, "GetPatientList");
}

And if I execute the same query in SQL Server Management Studio, it takes only 1 second.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Coskun Ozogul
  • 2,389
  • 1
  • 20
  • 32
  • 1
    That's a lot of joins. – Robert Harvey Feb 05 '20 at 16:26
  • It works fine on management studio and on the application when the database is on local – Coskun Ozogul Feb 05 '20 at 16:27
  • Which makes me think it's the SqlReader that is slow. – Robert Harvey Feb 05 '20 at 16:28
  • If I was working with that SQL I'd create a stored proc and execute it from C#. Much easier to maintain and debug than magic SQL strings in C# code, IMO. – haldo Feb 05 '20 at 16:28
  • Yeah, I don't think that's going to fix his performance problems, though. The speed hit is not related to where the SQL is stored. As the OP points out, the same SQL string works just fine in SSMS. – Robert Harvey Feb 05 '20 at 16:29
  • SqlReader is really old-school. I would look into using something like Dapper or [raw queries in Entity Framework](https://learn.microsoft.com/en-us/ef/core/querying/raw-sql) for this. See also the duplicate post I linked. – Robert Harvey Feb 05 '20 at 16:31
  • Can you call `sqlComm.ExecuteNonQuery` does it somewhat return quickly? On another note, the flagged duplicate doesn't even have an accepted answer, closing this one is a little too quick IMHO. – Trevor Feb 05 '20 at 16:45
  • Robert Harvey, I tried the responses on the duplicate, it doesn't works. And as there is not accepted answer, I had to try all. I have no answer for instance. – Coskun Ozogul Feb 05 '20 at 17:03
  • Çöđěxěŕ, Execute non query doesn't take time. – Coskun Ozogul Feb 05 '20 at 17:03
  • @CoskunOzogul ok that's good to hear, I will have something for you here hopefully. – Trevor Feb 05 '20 at 17:26

0 Answers0