获取xorm.TableInfo效率太低 #2059

New Issue

kevinyjn1 · 2021-10-09T08:16:02Z

kevinyjn1 commented

2021-10-09 08:16:02 +00:00

基于xorm定义的数据模型在初次被调用时触发的表结构查询出现多次且表现上是按模型字段分别查询的，程序启动后预加载数据时有大量的此类日志，此情况在使用cockroachdb集群的时候表现尤为明细，由于使用环境单个查询数据库响应慢，甚至出现了只预加载3轻量表数据花费5分钟的情况。

以下是使用sqlite数据库测试时其中一张表的表结构查询情况：

[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [sys_user] - 8.993ms
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [sys_user]
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 []
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user]
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user]
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user]
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user]
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user]
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user]
[INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user]

基于xorm定义的数据模型在初次被调用时触发的表结构查询出现多次且表现上是按模型字段分别查询的，程序启动后预加载数据时有大量的此类日志，此情况在使用cockroachdb集群的时候表现尤为明细，由于使用环境单个查询数据库响应慢，甚至出现了只预加载3轻量表数据花费5分钟的情况。以下是使用sqlite数据库测试时其中一张表的表结构查询情况： [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [sys_user] - 8.993ms [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [sys_user] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT * FROM sys_user LIMIT 0 [] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user] [INFO] 2021/10/09 11:57:51 db.go:300: [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [sys_user]

lunny commented

2021-10-09 08:49:22 +00:00

Could you paste your code and TableInfo is a method of Engine and it will retrieve information from a struct and will not read anything from database.

Could you paste your code and `TableInfo` is a method of `Engine` and it will retrieve information from a struct and will not read anything from database.

lunny added the

need

feedback

label 2021-10-09 08:49:28 +00:00

kevinyjn1 commented

2021-10-09 09:05:29 +00:00

Could you paste your code and TableInfo is a method of Engine and it will retrieve information from a struct and will not read anything from database.

ok, err = orm.IsTableExist(beanOrTableName)
if nil != err {
	logger.Error.Printf("ensuring table '%s' on database while check table exists failed with error:%v", getTableName(beanOrTableName), err)
	return err
}

if false == ok {
	err = orm.CreateTables(beanOrTableName)
	if nil != err {
		logger.Error.Printf("Create table '%s' faield with error:%v", getTableName(beanOrTableName), err)
		return err
	}

	err = orm.CreateIndexes(beanOrTableName)
	if nil != err {
		logger.Warning.Printf("Create table '%s' indexes faield with error:%v", getTableName(beanOrTableName), err)
	}
	err = orm.CreateUniques(beanOrTableName)
	if nil != err {
		logger.Warning.Printf("Create table '%s' unique indexes faield with error:%v", getTableName(beanOrTableName), err)
	}
}

> Could you paste your code and `TableInfo` is a method of `Engine` and it will retrieve information from a struct and will not read anything from database. ok, err = orm.IsTableExist(beanOrTableName) if nil != err { logger.Error.Printf("ensuring table '%s' on database while check table exists failed with error:%v", getTableName(beanOrTableName), err) return err } if false == ok { err = orm.CreateTables(beanOrTableName) if nil != err { logger.Error.Printf("Create table '%s' faield with error:%v", getTableName(beanOrTableName), err) return err } err = orm.CreateIndexes(beanOrTableName) if nil != err { logger.Warning.Printf("Create table '%s' indexes faield with error:%v", getTableName(beanOrTableName), err) } err = orm.CreateUniques(beanOrTableName) if nil != err { logger.Warning.Printf("Create table '%s' unique indexes faield with error:%v", getTableName(beanOrTableName), err) } }

lunny commented

2021-10-09 09:15:28 +00:00

This is a database schema detection and changes. If there are many records, it will spend much time.

A better method is to use Sync but not write it yourself.

For a production delopyment, I think you should not check them once start the program. Just check once something changed, you can try use xorm/migrate to add some migrations, so that only migrations will be executed.

This is a database schema detection and changes. If there are many records, it will spend much time. A better method is to use `Sync` but not write it yourself. For a production delopyment, I think you should not check them once start the program. Just check once something changed, you can try use `xorm/migrate` to add some migrations, so that only migrations will be executed.

kevinyjn1 commented

2021-10-09 09:32:28 +00:00

Is that mean xorm.IsTableExist were not recommend in production deployment?

lunny commented

2021-10-10 02:01:51 +00:00

Is that mean xorm.IsTableExist were not recommend in production deployment?

No. You should do that according your situation.

> Is that mean xorm.IsTableExist were not recommend in production deployment? No. You should do that according your situation.

Sign in to join this conversation.

No Milestone

No Assignees

2 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#2059