none
Newbie Question RRS feed

  • Question

  • I have 2 functions. Each one returns the same thing. One uses a TSQL Stmt and the other uses Entity Framework. But, the TSQL stmt runs much faster than the EF code. What can I do to speed up the EF code? Some of my code is below:

    EF Function:

            public static List<ChartHist> ListHistory()
            {
                using (var db = new LatencyDBContext())
                {
                    var loginHist = (from hist in db.LoginHistories
                                      select new { LoginHistory = hist }).ToList();
    
    
                    //PUT LOGIN HISTORY RECORDS INTO A LOCAL LIST
                    var listHistory = new List<ChartHist>();
                    foreach (var item in loginHist)
                    {
                        var localHistData = new ChartHist();
    
                        localHistData.LoginHistoryID = item.LoginHistory.LoginHistoryID;
    
                        //split up the duration for pass and fail values
                        if (item.LoginHistory.LoginStatus.ToUpper() == "PASS")
                        {
                            localHistData.LoginDuration_Pass = Convert.ToDouble(item.LoginHistory.LoginDuration);
                            localHistData.LoginDuration_Fail = 0;
                        }
                        else if (item.LoginHistory.LoginStatus.ToUpper() == "FAIL")
                        {
                            localHistData.LoginDuration_Pass = 0;
                            localHistData.LoginDuration_Fail = Convert.ToDouble(item.LoginHistory.LoginDuration);
                        }
    
                        localHistData.LoginDateTime = item.LoginHistory.LoginDateTime;
                        localHistData.LoginLocationID = item.LoginHistory.LoginLocationID;
                        localHistData.LoginUserEmailID = item.LoginHistory.LoginUserEmailID;
                        localHistData.LoginApplicationID = item.LoginHistory.LoginApplicationID;
                        localHistData.LoginEnvironmentID = item.LoginHistory.LoginEnvironmentID;
                        localHistData.LoginStatus = item.LoginHistory.LoginStatus;
                        localHistData.Reason = item.LoginHistory.Reason;
                        localHistData.ScriptFrequency = item.LoginHistory.ScriptFrequency;
    
                        listHistory.Add(localHistData);
                    }
    
                    return listHistory;
                }
            }

    TSQL Function:

            public static List<ChartHist> ListHistory_PureSQL()
            {
                List<DataRow> listDataRow = null;
                string srtQry = @"Select LoginHistoryID, 
                                           LoginDuration as LoginDuration_Pass, 
                                           0 as LoginDuration_Fail, 
                                           LoginDateTime, 
                                           LoginLocationID, 
                                           LoginUserEmailID, 
                                           LoginApplicationID, 
                                           LoginEnvironmentID, 
                                           ScriptFrequency, 
                                           LoginStatus, 
                                           Reason
                                    From LoginHistory
                                    Where LoginStatus = 'Pass'
                                    UNION
                                    Select LoginHistoryID, 
                                           0 as LoginDuration_Pass, 
                                           LoginDuration as LoginDuration_Fail, 
                                           LoginDateTime, 
                                           LoginLocationID, 
                                           LoginUserEmailID, 
                                           LoginApplicationID, 
                                           LoginEnvironmentID, 
                                           ScriptFrequency, 
                                           LoginStatus, 
                                           Reason
                                    From LoginHistory
                                    Where LoginStatus = 'Fail'";
    
                using (SqlConnection conn = new SqlConnection(Settings.ConnectionString))
                {
                    using (SqlCommand objCommand = new SqlCommand(srtQry, conn))
                    {
                        objCommand.CommandType = CommandType.Text;
                        DataTable dt = new DataTable();
                        SqlDataAdapter adp = new SqlDataAdapter(objCommand);
                        conn.Open();
                        adp.Fill(dt);
                        if (dt != null)
                        {
                            listDataRow = dt.AsEnumerable().ToList();
                        }
                    }
                }
    
    
                var listChartHist = (from p in listDataRow
                                select new ChartHist
                                {
                                    LoginHistoryID = p.Field<Int32>("LoginHistoryID"),
                                    LoginDuration_Pass = p.Field<Int32>("LoginDuration_Pass"),
                                    LoginDuration_Fail = p.Field<Int32>("LoginDuration_Fail"),
                                    LoginDateTime = p.Field<DateTime>("LoginDateTime"),
                                    LoginLocationID = p.Field<Int32>("LoginLocationID"),
                                    LoginUserEmailID = p.Field<Int32>("LoginUserEmailID"),
                                    LoginApplicationID = p.Field<Int32>("LoginApplicationID"),
                                    LoginEnvironmentID = p.Field<Int32>("LoginEnvironmentID"),
                                    ScriptFrequency = p.Field<Int32>("ScriptFrequency"),
                                    LoginStatus = p.Field<String>("LoginStatus"),
                                    Reason = p.Field<String>("Reason")
                                }).ToList();
    
                return listChartHist;            
            }

    Wednesday, February 13, 2013 6:53 PM

Answers


  • There are some other articles out there use Bing or Google.

    <http://msdn.microsoft.com/en-us/library/cc853327.aspx>

    Wednesday, February 13, 2013 9:48 PM
  • The link that was already provided is the best resource I know of for tuning EF. Having said that, you are not going to get EF to perform at the same speed as hand crafted SQL.

    The linked article talks about the things that EF has to do and on top of that the SQL is often not going to be as optimal as SQL that you write yourself with full knowledge of the domain. However, when using EF you get a lot of functionality that make it easier to map your domain to your database.

    In your case it doesn't look much different when using EF or not using EF, but you could change that if you structured your code differently. For example, if you move the logic that is setting the LoginDuration_Pass and LoginDuration_Fail into the properties themselves then you wouldn't need to convert to ChartHist at all. You could just select LoginHistories and return them. Which would reduce your ListHistory method to 3-4 lines. If that was the case then you might be better off with the easier to read 4 lines then the harder to read and understand raw SQL method. But if performance is really critical then maybe you do need raw sql. The key is knowing how fast you really need it to be.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Thursday, February 14, 2013 12:57 AM
    Moderator

All replies


  • There are some other articles out there use Bing or Google.

    <http://msdn.microsoft.com/en-us/library/cc853327.aspx>

    Wednesday, February 13, 2013 9:48 PM
  • The link that was already provided is the best resource I know of for tuning EF. Having said that, you are not going to get EF to perform at the same speed as hand crafted SQL.

    The linked article talks about the things that EF has to do and on top of that the SQL is often not going to be as optimal as SQL that you write yourself with full knowledge of the domain. However, when using EF you get a lot of functionality that make it easier to map your domain to your database.

    In your case it doesn't look much different when using EF or not using EF, but you could change that if you structured your code differently. For example, if you move the logic that is setting the LoginDuration_Pass and LoginDuration_Fail into the properties themselves then you wouldn't need to convert to ChartHist at all. You could just select LoginHistories and return them. Which would reduce your ListHistory method to 3-4 lines. If that was the case then you might be better off with the easier to read 4 lines then the harder to read and understand raw SQL method. But if performance is really critical then maybe you do need raw sql. The key is knowing how fast you really need it to be.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Thursday, February 14, 2013 12:57 AM
    Moderator
  • The link that was already provided is the best resource I know of for tuning EF. Having said that, you are not going to get EF to perform at the same speed as hand crafted SQL.

    The linked article talks about the things that EF has to do and on top of that the SQL is often not going to be as optimal as SQL that you write yourself with full knowledge of the domain. However, when using EF you get a lot of functionality that make it easier to map your domain to your database.

    In your case it doesn't look much different when using EF or not using EF, but you could change that if you structured your code differently. For example, if you move the logic that is setting the LoginDuration_Pass and LoginDuration_Fail into the properties themselves then you wouldn't need to convert to ChartHist at all. You could just select LoginHistories and return them. Which would reduce your ListHistory method to 3-4 lines. If that was the case then you might be better off with the easier to read 4 lines then the harder to read and understand raw SQL method. But if performance is really critical then maybe you do need raw sql. The key is knowing how fast you really need it to be.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Thank you for the reply. I really appreciate it.

    I made this posting on StackOverflow as well:

    http://stackoverflow.com/questions/14862445/convert-function-based-on-tsql-with-function-based-with-entity-framework

    Friday, February 15, 2013 4:28 PM