This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / 请各位高手赐教 一个关于SQL SERVER编成的问题公司有一SQL SERVER 2000, 其中有一个表有200百万记录, 都是客户的股票交易记录, 客户有4万个. 这样的库有4个. 每个库, 每次计算余额都要30个小时. 这是否正常. 做这样的计算有没有什么好的方法.
先谢谢了!!!
-qzh(胡子);
2002-12-2
{195}
(#890037@0)
-
我有一些，不过可能不适用于你的情况，仅供参考。我们公司每个10x900万纪录的库的检索batch只用2分钟1优化SQL Server/NT配置，避免hot disk,network bottleneck.
2检查/tuning SQL 语句。
（以下程序改动较大，是下策）
3如果是使用C++或其他语言的batch,如果可能采用多进/线程， data catch 避免重复SQL检索.
4. 当然使用ODBC要小心。
-kkkkkkkk(Toronto123);
2002-12-2
{221}
(#890611@0)
-
多谢老吴同志不过, 还是不太明白你们具体算的是什么? 我们是要算每个帐户的余额, 每个库有4万-5万帐户, 交易记录有200万个, 一共4个这样的库. 因为, 最终要有5个库, 共100万帐户, 交易记录起码2000万, 所以, 要找更好的方法.
能否给的具体一点, 主要是T-SQL方面和表的索引方面. 结构是没法动了. 否则全身都要动了.
先多谢了!!!
-qzh(胡子);
2002-12-3
{298}
(#891116@0)
-
很久不发言了,这次说两句.你的描述太笼统.什么样的计算?仅仅是modification还是query + modification ? index是两面刃, 你可以打开execution plan看看相关的计算究竟用了index seek,index scan还是table scan?
从系统上来讲,你的计算可能会大量用到tempdb,你的tempdb用的什么raid configuration? 你的data 和transaction log又是放到什么raid level 上?
你的system memory是多少? is the box dedicated to sql server or shared with other applications?
-yangn(Raymond);
2002-12-3
{404}
(#891391@0)
-
尽量减少SQL次数！1.使用bulk fetch/update/insert来减少SQL次数,或优化算法以减少sql次数。
2.使用cost-based optimization
3.把同一table分布在不同disks
4.将access平率高的master tables cache到内存
5.优化检查每一个SQL语句
6.系统和DB级优化
我们使用的是C++/ProC，由于设计者充分考虑并行计算，batch有多个线程（意味多个数据库连接），plus data cache(应用程序级） and bulk fetch/update/insert,效果非常好。
-kkkkkkkk(Toronto123);
2002-12-3
{381}
(#892378@0)
-
great
-maggie1001(学习ing&找工ing&);
2002-12-5
(#895670@0)
-
先多谢Raymond, 不过, 还要继续问
-qzh(胡子);
2002-12-3
{1093}
(#891626@0)
-
增量余额法Amount.now = Amount.yesterday + MoneyFlowing.today :)
-ra_95(小人-拒绝老去);
2002-12-3
{53}
(#891674@0)
-
在写数据库忙的时侯，很多记录都处于锁定状态。如果计算精度允许，可以降低读取隔离等级。或把数据库备复制到另一台计算机上去统计.
-miketany(MIKE老狼);
2002-12-3
(#891715@0)
-
一点看法.
-yangn(Raymond);
2002-12-3
{591}
(#891722@0)
-
不熟悉SQL Server,但听说SQL SERVER和SYBASE想像。我原来公司用SYBASE处理百万累次你这样的记录也就a couple hours.我试着用SYBASE的概念改一下1)在做大批量时我想你不能采用日间的client/server结构，而应是batch。在SYBASE中用store procedure做batch的工作，减少中间的input/output。所以没有前面DX说的ODBC的问题。T-SQL是否就是transaction sql,只适合于日间交易。
2)综合考虑一下index.我猜想你第三个表应属于select 旧余额，再update为新余额(根据流水帐的交易金额)，所以不能没有INDEX，但INDEX又不能太多，只设ID为INDEX即可。
-ivy_sh(纸蝶);
2002-12-3
{365}
(#891747@0)
-
高手们好, 我再来点说明
-qzh(胡子);
2002-12-3
{902}
(#891887@0)
-
I guess that the stored procedure using for caculation must use the cursor, which is row by row, couple of hours is normal. Try to look the codes of the stored procedure.
-greenspring(春天);
2002-12-4
(#894746@0)
-
Thanks
-qzh(胡子);
2002-12-5
(#895610@0)
-
Try to use name (???) ( Simular one with Pro-C in Oracle), coding will be hard than stored procedure, but performance should be fast than SP.Try using the C, sorry forget the name for SQL SERVER, for oracle is Pro-C, The stence structure is add a pro-fix before the normal select statement. 'EXEC...', If will fast u process and compute but no help for your CRUP transation( This one need depend on your index).
-ling7199(Michaell);
2002-12-6
{272}
(#896861@0)
-
Another things (Commit statment)How much records do u use during the program? Or only one commit statment. If you commit for every 1000 records ( example), need to find a logic to cover the fail case.
-ling7199(Michaell);
2002-12-6
{168}
(#896871@0)
-
Use trigger on the 2nd table, for the insert operation.Use trigger on the 2nd table, for the insert operation.
Everytime a new record is inserted into the 2nd, the related record in 3nd is updated or created.
Therefore there's no need to spend hours for the daily calculation.
-sailor(野苹果);
2002-12-6
{228}
(#897121@0)
-
No, this is not a good idea. The usage of trigger will bring much overheading to the day time transaction and therefore degrade the performance of the system.
-yangn(Raymond);
2002-12-6
(#897164@0)
-
It depends on many factors, e.g. how the trigger scripts are written, how the tables are designed, the number of records.By studying this specific case, I think maybe he can try trigger.
Of course, before any offical changes, he should measure the performance of the new solutions.
-sailor(野苹果);
2002-12-6
{165}
(#897252@0)