×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

MS SQL Performance问题

一个表table有以下fields:

teamA, teamB, score

teamA 和 teamB是多对多的关系。需要一个query能找出对应于teamA成员分数最高的teamB成员。如果teamB中有多个成员对于同一teamA成员并列有最高分,都选用。

暂时我有两个query:
query 1:
    SELECT    teamA,
            teamB,
            score
    FROM (    SELECT    teamA,
                    teamB,
                    score,
                    MAX(score) OVER(PARTITION BY teamA)    AS HighestScore
            FROM    table
        ) #output
    WHERE    score = HighestScore
    ORDER BY teamA, teamB

query 2:
    SELECT    t1.teamA,
            t1.teamB,
            t1.Score
    FROM    tablet1
    JOIN    tablet2
        ON    t1.teamA = t2.teamA
    GROUP BY t1.teamA, t1.teamB, t1.Score
    HAVING t1.Score = MAX(t2.Score)
    ORDER BY t1.teamA, t1.teamB

从Execution Plan来看,传统方式query2要比query1省4个步骤,但那不一定说明问题。想知道哪个方案performance好,或者有没有更好的方案。
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / MS SQL Performance问题
    一个表table有以下fields:
    
    teamA, teamB, score
    
    teamA 和 teamB是多对多的关系。需要一个query能找出对应于teamA成员分数最高的teamB成员。如果teamB中有多个成员对于同一teamA成员并列有最高分,都选用。
    
    暂时我有两个query:
    query 1:
        SELECT    teamA,
                teamB,
                score
        FROM (    SELECT    teamA,
                        teamB,
                        score,
                        MAX(score) OVER(PARTITION BY teamA)    AS HighestScore
                FROM    table
            ) #output
        WHERE    score = HighestScore
        ORDER BY teamA, teamB
    
    query 2:
        SELECT    t1.teamA,
                t1.teamB,
                t1.Score
        FROM    tablet1
        JOIN    tablet2
            ON    t1.teamA = t2.teamA
        GROUP BY t1.teamA, t1.teamB, t1.Score
        HAVING t1.Score = MAX(t2.Score)
        ORDER BY t1.teamA, t1.teamB
    
    从Execution Plan来看,传统方式query2要比query1省4个步骤,但那不一定说明问题。想知道哪个方案performance好,或者有没有更好的方案。
    • 第一种方法好。我的习惯是用 RANK 函数并在 WHERE 中取出 RANK=1 的行。
      • RANK的方案也有,跟上面query1大相径庭。见内。考虑到RANK函数里面还要再sort一遍,觉得可能比Max要慢。能说说你的理由么?SQL Server的同学们的意思如何?
        Query3:
        SELECT teamA,
        teamB,
        score
        FROM ( SELECT teamA,
        teamB,
        score,
        RANK() OVER(PARTITION BY teamA ORDER BY score DESC) AS rank
        FROM table
        ) #output
        WHERE rank = 1
        ORDER BY teamA, teamB
        • 呵呵,你这“大相径庭”让我有点费解……在我看来是不相上下。用RANK只不过是我个人的理解习惯,TOP N 就是 RANK<=N 么。ORACLE在做MAX的时候也排序,两者开销一样,也许在MS SQL中用MAX更高效吧。
          • 羞愧呀。中文退步,用错成语。本来想说大同小异的。不知怎么就打成这个了。:(
    • My opinion
      It is said that join operation have better performance over sub-query. However, only in huge data (close or over million records) we can find difference. I never try the difference.

      In real world, especially in web application, there are many factors to affect performance.

      In terms of database itself, better database structure, better data process plan have better results to performance.
      • 这不是web application,也不属于任何application,纯DB问题。我现在的sample data size不大,看不出区别来,而期望的数据或者未来可能的数据会非常大,所以在犹豫到底哪个会好些。
        • 如果你能估计出数据的分布情况,那么可以按照预计的数据量造一些测试数据。我假设每队各有100名队员,分数有90种,在10万条记录的情况下,ORACLE的执行计划表明Q2的代价是Q1/Q3的六倍多。
          • 没法估计,至少目前无法估计。因为数据源会有多个,现在的sample data只是N个数据源中的一个的其中一小部分。
        • It might be helpful to use SQL Server Query Execution Plan Analysis.
          • try过,useless。
    • 我研究过SQL优化问题,只要索引正确, database都会优化query, 比自己优化的好,基本都结果差不多, 索引建不好,差异就大了。
      • 你的意思是,如果索引正确了,前面三种方式结果一样?不太可能吧?
        • 我的意思是,不用花太多时间在sql语句上,sql server都会优化,执行结果应该相差不大,我当时测试数据是20W-30W条记录,一个存储过程执行一次3个小时,最终结论是sql语句如何写差异不大,要提高性能,只能优化数据结构,表的结构。索引建好可以显著提高性能,
          • 在没有修改任何表结构、不增加任何索引的情况下,把别人写的一个SQL重写后执行时间由几小时缩短为几分钟,这样的事情我做过好几次。你说说看,你的存储过程花3小时在做什么?处理二三十万行数据是不用这么长的。
            • 是个电信计费项目,存储过程就4K,用到cursor,临时表,一般是下班前开始执行,第二天检查log, 我优化前,执行一次要9个小时,我开始接手优化它,发现修改Sql提升的性能非常有限,只能修改库结构。
              • 估计是循环套循环,否则二三十万行数据不会这么慢。如果去掉一层循环,用一些case语句来代替的话,有可能十几分钟就完事了。
                • 用cursor循环,非常慢,只能用一层,要计算费用,折扣, 项目多,最后结果都要插入。
                  • Using cursor is a slow process. So use it as less as possible.
                  • 那就真是设计太差了。大胆猜一下,估计你那公司也是搞97的,把97那套东西拿过来搞计费,最后就搞成了这个样子。厚厚
                    • 计费系统,相对独立,把数据从交换机上copy出来,导入database,在统计总费用,统计的项目多,市话,长话,优惠,免费电话,全在一起搞,最后生成账单,核心用sql写,不容易,cursor那是没办法。
                      • 没有不得不用Cursor的,试试用临时表吧。
                      • Better to looking for other solution
                        What you mean to have to use cursor actually means data have to be processed one record by one record.

                        SQL’s strength is dataset (multi-records) processing. Looping one record by one record and calculation are not its strength.

                        If you have to process one record by one record, you should think other solution, for example, putting task in a program outside database. Many program’s loop is faster than database cursor loop. And a big database job runs for hours will hang on other data processes. It wastes expensive database resource.
            • 一般数据库在直行select时,先判断有无index, 没有index,就会table扫描,这个非常慢,有index就很快。index建多了,insert,delete又很慢,要写log,这东西是矛盾的,但库结构设计是绝对性的,比其他优化都有效,当你感觉走投无路时,只能重新设计库结构。
              • 索引不是万能的。像楼主这种题目并不是为了过滤少数数据,索引很难用上。我原以为索引对PARTITION 和 ORDER BY 有帮助,但后来优化器还是选择了全表扫描。
                • I agree. My testing shows when quering large portion of data, index even makes the thing worse.
            • 如果是1000条记录的小表,建不建索引,都一样很快,如果是几十万的大表,索引很有效, 所以要避免大表的扫描,