当前在线人数12733
首页 - 分类讨论区 - 电脑网络 - 数据库版 -阅读文章
未名交友
[更多]
[更多]
文章阅读:Re: Help on Sql server huge table performance
[同主题阅读] [版面: 数据库] [作者:xlxie] , 2007年07月17日20:25:10
xlxie
进入未名形象秀
我的博客
[上篇] [下篇] [同主题上篇] [同主题下篇]

发信人: xlxie (三老爷), 信区: Database
标  题: Re: Help on Sql server huge table performance
发信站: BBS 未名空间站 (Tue Jul 17 20:25:10 2007)

serious.
上中下三策。
上中已经被你排除了。
now, you need some serious codings and configurations to do.
idea 1) use temp table.
basically, you wanna create a view, including the report table and at least
a temp table.
when people do insert, always insert to the empty temp table first.
then you will get the row count, if the row count is small,
then insert/update it to the report table, if big, danamically create a new
temp table,
and include the 3 table into the view, so the table list in the view grows,
instead of the table itself.
you will need to have a off line time to rebuild to the table, and manage
indexs during that time as well.
positive: not difficult.
Weak point, you can not do huge update in this model, and will need system
offline time.

idea 2) use online and offline tables.
basically, the idea is you will have 2 sets of tables, for your report table.
everything insert/update will be done to temp tables first then apply to the
offline table, when that is done, switch the table sets, so people will be
seeing the most updated ones, while you are doing updates at the backend.
you will need an incremental and a update temp table sets for both online
and offline table sets to get it working. also, you will need to think about
keeping 2 tables sets consistant, that might need temp tables for temp
tables to get it work.
positive: you can do whatever you want with this model, no offline time.
you exchange the strength with the HD space.
weak point, you need at least 3 times the space you need, relative hard
business logic, and the downtime between switch tables.



【 在 mirthcyy (百无聊赖) 的大作中提到: 】
: 我们有些巨大无比的report table, 每个table有200多个columns, 所有run同样的
: report的用户share同一个report table, 用session identifier区分。 有的user不用
: filter, 可以generate出来half million records and insert into report table,
: 有的只有几千几百rows. 因为有很多的insert and update, 所以report table 没有任
: 何index. 现在问题是:
: 1 有大的insert 和 update 非常慢。
: 2 用户之间影响非常大。 如果一个用户report 结果只有几百rows, but another
: user has over half million records generated and inserted in the same
table,
:  then 那个用户即使只有几百rows, 做insert, update 也无比的慢。
: 我现在没法改table structure和logic or hardware, 请问大侠们有什么别的办法
: ...................



--

※ 来源:·WWW 未名空间站 海外: mitbbs.com 中国: mitbbs.cn·[FROM: 69.138.]

[上篇] [下篇] [同主题上篇] [同主题下篇]
[转寄] [转贴] [回信给作者] [修改文章] [删除文章] [同主题阅读] [从此处展开] [返回版面] [快速返回] [收藏] [举报]
 
回复文章
标题:
内 容:

未名交友
将您的链接放在这儿

友情链接


 

Site Map - Contact Us - Terms and Conditions - Privacy Policy

版权所有,未名空间(mitbbs.com),since 1996