In our application,Many pages includes "update" and when we update a table,we update unnecessary columns,which dont change,too. i want to know that is there a way to avoid unnecessary column updates?We use stored procedures in .net 2003.In Following link,i found a solution but it is not for stored procedures.
在我们的应用程序中,许多页面包含“更新”,当我们更新表时,我们也更新不必要的列,这些列也不会改变。我想知道有没有办法避免不必要的列更新?我们在.net 2003中使用存储过程。在以下链接中,我找到了一个解决方案,但它不适用于存储过程。
Thanks
2 个解决方案
#1
When I was working at a financial software company, performance was vital. Some tables had hundreds of columns, and the update statements were costly. We created our own ORM layer (in java) which included an object cache. When we generated the update statement, we compared the current values of every field to the values as they were on load and only updated the changed fields.
当我在一家金融软件公司工作时,表现至关重要。有些表有数百列,更新语句代价很高。我们创建了自己的ORM层(在java中),其中包含一个对象缓存。当我们生成update语句时,我们将每个字段的当前值与加载时的值进行比较,并仅更新已更改的字段。
Our db was SQLServer. I do not remember the performance improvement, but it was substantial and worth the investment. We also did bulk inserts and updates where possible.
我们的数据库是SQLServer。我不记得性能的提升,但它是实质性的,值得投资。我们还尽可能地进行批量插入和更新。
I believe that Hibernate and the other big ORMs all do this sort of thing for you, if you do not want to write one yourself.
我相信Hibernate和其他大型ORM都会为你做这类事情,如果你不想自己写一个。
#2
You can really only accomplish this with a good ORM tool that generates the update query for you. It will typically look at what changed and generate the query for only the columns that changed.
您实际上只能通过一个为您生成更新查询的良好ORM工具来实现此目的。它通常会查看更改内容并仅为更改的列生成查询。
If you're using a stored procedure then all of the column values get sent over to the database anyway when you call the stored procedure so you can't save there. The SP will probably just execute a run-of-the-mill UPDATE statement. The RDMS then takes over. It won't physically change the data on disc if it's not different. It's smart enough for that.
如果您正在使用存储过程,那么当您调用存储过程时,无论如何都会将所有列值发送到数据库,因此您无法保存。 SP可能只是执行一个普通的UPDATE语句。然后RDMS接管。如果它没有不同,它将不会在物理上更改光盘上的数据。它足够聪明。
So my answer in short: don't worry about it. It's not really a big deal and requires drastic changes to get what you want and you wont even see performance benefits.
简而言之,我的回答是:不要担心。这并不是什么大不了的事情,需要进行大幅度的改变才能得到你想要的东西,你甚至看不到性能优势。
#1
When I was working at a financial software company, performance was vital. Some tables had hundreds of columns, and the update statements were costly. We created our own ORM layer (in java) which included an object cache. When we generated the update statement, we compared the current values of every field to the values as they were on load and only updated the changed fields.
当我在一家金融软件公司工作时,表现至关重要。有些表有数百列,更新语句代价很高。我们创建了自己的ORM层(在java中),其中包含一个对象缓存。当我们生成update语句时,我们将每个字段的当前值与加载时的值进行比较,并仅更新已更改的字段。
Our db was SQLServer. I do not remember the performance improvement, but it was substantial and worth the investment. We also did bulk inserts and updates where possible.
我们的数据库是SQLServer。我不记得性能的提升,但它是实质性的,值得投资。我们还尽可能地进行批量插入和更新。
I believe that Hibernate and the other big ORMs all do this sort of thing for you, if you do not want to write one yourself.
我相信Hibernate和其他大型ORM都会为你做这类事情,如果你不想自己写一个。
#2
You can really only accomplish this with a good ORM tool that generates the update query for you. It will typically look at what changed and generate the query for only the columns that changed.
您实际上只能通过一个为您生成更新查询的良好ORM工具来实现此目的。它通常会查看更改内容并仅为更改的列生成查询。
If you're using a stored procedure then all of the column values get sent over to the database anyway when you call the stored procedure so you can't save there. The SP will probably just execute a run-of-the-mill UPDATE statement. The RDMS then takes over. It won't physically change the data on disc if it's not different. It's smart enough for that.
如果您正在使用存储过程,那么当您调用存储过程时,无论如何都会将所有列值发送到数据库,因此您无法保存。 SP可能只是执行一个普通的UPDATE语句。然后RDMS接管。如果它没有不同,它将不会在物理上更改光盘上的数据。它足够聪明。
So my answer in short: don't worry about it. It's not really a big deal and requires drastic changes to get what you want and you wont even see performance benefits.
简而言之,我的回答是:不要担心。这并不是什么大不了的事情,需要进行大幅度的改变才能得到你想要的东西,你甚至看不到性能优势。