果糖网

mysql一张表中有40多万条数据,查询一次要90多秒。 返回

SqlSugar
15 208

 protected SqlSugarClient Db;

public DbContext()
        {
            Db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = DataSetting.MySqlstring,
                DbType = DbType.MySql,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute,
                AopEvents = new AopEvents()
                {
                    OnLogExecuting = (sql, p) =>
                    {
                       //Console.WriteLine(sql);
                    }
                }
            });
        }

Db.Queryable<TEntity>().ToList();

查询方式 大致就是这种方式,有没有办法提到效率。

热忱回答15

  • 未标题-1.png

    0 回复
  • 21.png

    0 回复
  • 数据库全部设为非 null    ,    实体字段不要加 用? 把int?改成 int   这种  可以提升一点, 其它没办法了 sqlsugar已经是海量数据查询 最快ORM  在一次查几十万条没有败绩

    0 回复
  • @fate stay night:我的数据库没有null 数据和int? 这种类型。

    我测试了原生sql 查询为DataTable 和sqlsugar 查询为DataTable;

    原生sql 查询为DataTable也就10秒左右

    sqlsugar还是90多秒。


    0 回复
  • @fate stay night:  这种方式

    public static DataTable ExecuteDataTable(string SQLString)
            {
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        using (MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection))
                        {
                            command.Fill(ds, "ds");
                        }
                        return ds.Tables[0];
                    }
                    catch (MySqlException e)
                    {
                        return null;
                    }
                    catch (Exception e)
                    {
                        return null;
                    }
                    finally
                    {
                        connection.Close();
                    }
                    
                }
            }

    0 回复
  • @fate stay night:按这样的思路,我认为在返回DataTable类型数据时,sqlsugar还有很大的优化空间

    0 回复
  • @「繁华」:理论上Datatable不会快 , 不清楚你的实体和表 正常来说要快于DataTable 你可以用最简单的结构进行测试 

    0 回复
  • 或者在实体类中用注释加排除

    0 回复
  • @fate stay night:[Serializable]
        [SugarTable("sys_alarm_records", "告警信息")]

        public class Alarm
        {
            [SugarColumn(IsIdentity =true,IsPrimaryKey =true)]
            public int ID { get; set; }

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "报警类型")]
            public EAlramType AlarmType { get; set; } = EAlramType.Undefine;

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "报警级别")]
            public EAlarmLevel AlarmLevel { get; set; } = EAlarmLevel.NORMAL;

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "报警码")]
            public int AlarmID { get; set; } = -1;

            [SugarColumn(IsNullable = false, DefaultValue = "0", ColumnDescription = "父报警ID")]
            public int ParentAlarmID { get; set; } = 0;

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "报警状态")]
            public EAlarmState Status { get; set; } = EAlarmState.OCCUR;

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = ("设备ID,小车对应小车ID,业务数据对应被检测设备ID,配件设备对应配件ID"))]
            public int DeviceID { get; set; } = -1;

            [SugarColumn(IsNullable = false, DefaultValue = "''", ColumnDescription = "告警发生的X坐标")]
            public string X { get; set; } = "";
            [SugarColumn(IsNullable = false, DefaultValue = "''", ColumnDescription = "告警发生的Y坐标")]
            public string Y { get; set; } = "";

            [SugarColumn(IsNullable = false, DefaultValue = "''", ColumnDescription = "告警所在线")]
            public string EdgeNo { get; set; } = "";
            [SugarColumn(IsNullable = false, DefaultValue = "''", ColumnDescription = "告警所在点")]
            public string VertexNo { get; set; } = "";

            [SugarColumn(IsNullable = false, DefaultValue = "''", ColumnDescription = "告警车ID")]
            public string CarrierID { get; set; } = "";

            [SugarColumn(IsNullable = false, DefaultValue = "1900-01-01 00:00:01", ColumnDescription = "报警发生时间")]
            public DateTime ReportTime { get; set; } = DateTime.Parse("1900-01-01 00:00:01");

            [SugarColumn(IsNullable = true, DefaultValue = "1900-01-01 00:00:01", ColumnDescription = "报警解除时间")]
            public DateTime RecoveryTime { get; set; } = DateTime.Parse("1900-01-01 00:00:01");

            [SugarColumn(IsIgnore =true)]
            public string AlarmLevelName { get; set; }

            [SugarColumn(IsIgnore = true)]
            public string CarrierAlarmCodeName { get; set; }

            [SugarColumn(IsIgnore = true)]
            public string TypeName { get; set; }

            [SugarColumn(IsIgnore = true)]
            public string StateName { get; set; }


            #region 小车告警
            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "告警产生门限")]
            public int OccurThreshold { get; set; } = -1;

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "警恢复门限")]
            public int RecoveryThreshold { get; set; } = -1;

            [SugarColumn(IsNullable = false, DefaultValue = "0", ColumnDescription = "是否为故障")]
            public int IsFaultEnum { get; set; } = 0;
            [SugarColumn(IsIgnore = true)]
            public bool IsFault { get => IsFaultEnum == 0 ? true : false; set => IsFaultEnum = value ? 1 : 0; }

            public ECarrierAlarmCode CarrierAlarmCode { get; set; }

            [SugarColumn(IsIgnore = true)]
            public bool NeedRemove { get; set; }
            #endregion

            #region 巡检告警
            [SugarColumn(IsNullable = false, DefaultValue = "''", ColumnDescription = "任务ID")]
            public string TaskID { get; set; } = "";

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "报警设置ID")]
            public int AlarmSetID { get; set; } = -1;

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "区域")]
            public int AreaId { get; set; } = -1;

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "数据记录ID")]
            public int RecordID { get; set; } = -1;

            [SugarColumn(IsNullable = false, DefaultValue = "-1", ColumnDescription = "巡检明细记录ID")]
            public int RecordDetailId { get; set; } = -1;

            [SugarColumn(IsNullable = true, DefaultValue = "-1", ColumnDescription = "巡检报警值")]
            public double PatrolValue { get; set; } = -1;

            [SugarColumn(IsNullable = true, DefaultValue = "-1", ColumnDescription = "监测点编码")]
            public int DetectPointCode { get; set; } = -1;
            #endregion

            public Alarm Clone()
            {
                BinaryFormatter bf = new BinaryFormatter();
                MemoryStream ms = new MemoryStream();
                bf.Serialize(ms, this);
                ms.Position = 0;
                return (Alarm)bf.Deserialize(ms);
            }

            public Alarm()
            {
                AlarmID = 0;
                AlarmLevel = EAlarmLevel.INFORMATION;
                Status = EAlarmState.OCCUR;
                OccurThreshold = 0;
                RecoveryThreshold = 0;
                IsFault = false;
            }

            public Alarm(int alarmID, EAlarmState status, string param1, string param2, string param3, string param4, string param5, string param6)
            {
                CarrierID = param1;
                VertexNo = param2;
                X = param3;
                Y = param4;
                EdgeNo = param5;

                AlarmID = alarmID;
                AlarmLevel = EAlarmLevel.NORMAL;
                Status = status;
            }

        }

    0 回复
  • 找出那个慢的字段 

    0 回复
  • 你用db.first生成实体,然后在查询 ,像枚举这种肯定会影响性能的 ,用dbfrist生成的实体是和驱动最符合的 不存在装箱和拆箱操作

    0 回复
  • @fate stay night:尝试过后 还是没有效果。但是还是很感谢您的帮助。

    0 回复
  • 和你差不多 不过我用了索引

    0 回复
  • @阿西吧:我单独建了一个项目,用同样的方法查询,也就10s左右,放到真实项目中就有问题,请问你解决了没有,在数据库中show PROCESSLIST,大部分时间是消耗在了Writing to net,这个过程,我怀疑这个和GC的自动回收有关系,但是还是没有解决

    0 回复
  • @fate stay night:作者 我找到原因了,是我的代码中有一个模块引用了8.0.23的Mysql.data 的模块,sugar中默认的是8.0.21版本的。两个版本同时存在的时候 查询速度就从9秒左右变成了90多秒。

    0 回复

学习文档