获取xorm.TableInfo效率太低 #2059

Open
opened 2 months ago by kevinyjn1 · 5 comments

基于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]
Owner

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 2 months ago
Poster

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) } }
Owner

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.
Poster

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

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

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

No due date set.

Dependencies

This issue currently doesn't have any dependencies.

Loading…
There is no content yet.