Record Data Query Optimization and Data Query Optimization

Record Data Query Optimization and Data Query Optimization

One day, the bricks were moving so hot that a call was suddenly called. The business department reflected that a function was very slow and it was simply unbearable. How slow is it? About 90 s. Is it suddenly slow? Or was it slow before? It was a little slow before, but not so slow. Okay. If you don't want to talk about it, check the source code directly:

   public DataSet  GetStockByUserAndTime(string warehouseID, string userID, int minutes)        {            SqlItem sqlItem = new SqlItem();            var searchTime = DateTime.Now.AddMinutes(0 - minutes);                    sqlItem.SqlStr = @"                select t.manifest_no from STOCK_IN_OUT t                 where                             t.update_time >:searchTime                            and t.update_user=:userID                            and t.warehouse_id = :warehouseID                            and t.action_type = 0   ";            sqlItem.AppendParameter("searchTime", searchTime);            sqlItem.AppendParameter("userID", userID);            sqlItem.AppendParameter("warehouseID", warehouseID);            return GetDataSet(sqlItem);        }

 public DataSet  GetStockByUserAndTime(string warehouseID, string userID, int minutes)        {            SqlItem sqlItem = new SqlItem();            var searchTime = DateTime.Now.AddMinutes(0 - minutes).ToString("yyyy-MM-dd HH:mm:ss");                    sqlItem.SqlStr = @"                select t.manifest_no from STOCK_IN_OUT t                 where                             t.update_time >to_date(:searchTime,'yyyy-mm-dd hh24:mi:ss')                            and t.update_user=:userID                            and t.warehouse_id = :warehouseID                            and t.action_type = 0   ";            sqlItem.AppendParameter("searchTime", searchTime);            sqlItem.AppendParameter("userID", userID);            sqlItem.AppendParameter("warehouseID", warehouseID);            return GetDataSet(sqlItem);        }

After debugging, only 223 ms is used, which is the reason for date format.

Why didn't this problem occur before? communicate with the database team. database team: During the date conversion, try to pass in the string type. In the SQL statement, the conversion may be oralce upgrade, driver cause. Me: Well, this pot should let the driver back it. In short, pay attention to the date format.