我需要为每种类型的查询使用单独的索引，还是一个多列索引可以工作？

Question

Giri Bkm

Asked: 2018-03-27 18:42:56 +0800 CST2018-03-27 18:42:56 +0800 CST 2018-03-27 18:42:56 +0800 CST

如何知道该执行计划是否适合此查询（Sql server 2005）？

772

这是我的查询。

Declare
    @Pivv NVarchar(Max),
    @Query NVarchar(Max),
    @Pivv1 NVarchar(Max),
    @Columns NVarchar(Max)

Select
    @Pivv = Coalesce(@Pivv + ',' , '') + 
            Quotename(MatchType + '_' + '_Name'),
    @Pivv1 = Coalesce(@Pivv1 + ',' , '') + 
             Quotename('HighestRunsAndWicketsIn' + MatchType),
    @Columns = Isnull(@Columns + ',', '') +
               'Max(' + (MatchType + '_' + '_Name') + ') As ' + (MatchType + '_' + '_Name') +
               ',' +
               'Max(' + ('HighestRunsAndWicketsIn' + MatchType) + ') As ' + ('HighestRunsAndWicketsIn' + MatchType)
From
    PlayersCareerBatting t Join PlayersBioDataNew a
On
    t.Id=a.Id
Group By
    MatchType

Set @Query=
        'Select
            ''Batting'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Runs1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    RunsScored As Runs
                From ( 
                        Select 
                            FirstName, 
                            MiddleName,
                            LastName, 
                            MatchType, 
                            RunsScored
                        From (
                                Select 
                                    s.Id, 
                                    FirstName, 
                                    MiddleName,
                                    LastName, 
                                    MatchType, 
                                    RunsScored, 
                                    Row_Number() Over(Partition By MatchType Order By RunsScored Desc) rn
                                From 
                                    PlayersCareerBatting s Join PlayersBioDataNew a 
                                On s.Id=a.Id
                             ) x
                        Where rn=1
                     ) x2
             ) x3
        Pivot 
            (
                Max(Runs1) For Type1 In (' + @Pivv + ')
            ) x4
        Pivot
            (
                Max(Runs) For Type In (' + @Pivv1 + ')
            ) x5

        Union All

        Select
            ''Bowling'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Wickets1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    Wickets As Wickets
                From ( 
                        Select 
                            FirstName, 
                            MiddleName,
                            LastName,
                            MatchType, 
                            Wickets
                        From (
                                Select 
                                    s.Id, 
                                    FirstName, 
                                    MiddleName,
                                    LastName, 
                                    MatchType, 
                                    Wickets, 
                                    Row_Number() Over(Partition By MatchType Order By Wickets Desc) rn
                                From 
                                    PlayersBowlingCareer s Join PlayersBioDataNew a 
                                On s.Id=a.Id
                             ) x6
                        Where rn=1
                     ) x7
             ) x8 
        Pivot 
            (
                Max(Wickets1) For Type1 In (' + @Pivv + ')
            ) x9
        Pivot
            (
                Max(Wickets) For Type In (' + @Pivv1 + ')
            ) x10'

Exec(@Query)

查询包含 3 个表。Playersbiodatanew（5000 行），players?careerbatting（20000 行），playersbowlingcareer（20000 行）。而且，我在该查询中创建了数据透视表。现在我想知道，该查询是否更适合下面提到的执行计划

执行计划链接，https://www.brentozar.com/pastetheplan/?id=SyJPRDs9M

样本数据

对于桌上玩家BioDataNew

Id | Firstname | middlename | lastname
----------------------------------------
1  | Sachin    | Ramesh     | Tendulkar
----------------------------------------
2  | Suresh    | Kumar      | Raina
----------------------------------------

对于桌上玩家职业击球

CareerId | Id | Matchtype | runsscored
---------------------------------------
1        | 1  | Test      | 15921
----------------------------------------
2        | 1  | ODI       | 18426
----------------------------------------
3        | 1  | T20I      | 10
----------------------------------------
4        | 1  | IPL       | 2334
----------------------------------------
5        | 2  | Test      | 768
----------------------------------------
6        | 2  | ODI       | 5568
----------------------------------------
7        | 2  | T20I      | 1498
----------------------------------------
8        | 2  | IPL       | 4540
----------------------------------------

对于桌球运动员保龄球职业

CareerId | Id | Matchtype | wickets
---------------------------------------
1        | 1  | Test      | 46
----------------------------------------
2        | 1  | ODI       | 154
----------------------------------------
3        | 1  | T20I      | 1
----------------------------------------
4        | 1  | IPL       | 0
----------------------------------------
5        | 2  | Test      | 13
----------------------------------------
6        | 2  | ODI       | 36
----------------------------------------
7        | 2  | T20I      | 13
----------------------------------------
8        | 2  | IPL       | 25
----------------------------------------

预期产出

(No Column Name) | IPL__Name | HighestRunsAndWicketsInIPL | Test__Name | HighestRunsAndWicketsInTest | T20I__Name | HighestRunsAndWicketsInT20I | ODI__Name | HighestRunsAndWicketsInODI
----------------------------------------------------------------------------
Batting | Suresh Kumar Raina | 4540 | Sachin Ramesh Tendulkar | 15921 | Suresh Kumar Raina | 1498 | Sachin Ramesh Tendulkar | 18426
----------------------------------------------------------------------------
Bowling | Suresh Kumar Raina | 25 | Sachin Ramesh Tendulkar | 46 | Suresh Kumar Raina | 13 | Sachin Ramesh Tendulkar | 154
----------------------------------------------------------------------------

我得到了每个匹配类型获得最大跑分和小门的输出。

1 个回答

Voted

KumarHarsh · Answer 1 · 2018-03-27T22:16:54+08:00

最终输出有多少行？

从您的脚本中无法确定哪一列属于哪个表。

您应该在表别名前加上列

替代解决方案，

Since you are using `PlayersBioDataNew` in 3 places.Why not insert them in temp table and use temp table instead

create table #tmp (id int, rn int,matchtype int, wickets int)

insert into #tmp
select id, Row_Number() Over(Partition By MatchType Order By Wickets Desc) rn
,MatchType,wickets
 from  dbo.PlayersBioDataNew
 -- where apply other filter
 -- may be apply rn=1 here itself if it help in all 3 places

 --so depeding upon final script we can create index on #tmp table

 Declare
    @Pivv NVarchar(Max),
    @Query NVarchar(Max),
    @Pivv1 NVarchar(Max),
    @Columns NVarchar(Max)

Select
    @Pivv = Coalesce(@Pivv + ',' , '') + 
            Quotename(MatchType + '_' + '_Name'),
    @Pivv1 = Coalesce(@Pivv1 + ',' , '') + 
             Quotename('HighestRunsAndWicketsIn' + MatchType),
    @Columns = Isnull(@Columns + ',', '') +
               'Max(' + (MatchType + '_' + '_Name') + ') As ' + (MatchType + '_' + '_Name') +
               ',' +
               'Max(' + ('HighestRunsAndWicketsIn' + MatchType) + ') As ' + ('HighestRunsAndWicketsIn' + MatchType)
From
    PlayersCareerBatting t Join #tmp a
On
    t.Id=a.Id
Group By
    MatchType

Set @Query=
        'Select
            ''Batting'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Runs1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    RunsScored As Runs
                From ( 

                                Select 
                                    FirstName, 
                                    MiddleName,
                                    LastName, 
                                    MatchType, 
                                    RunsScored
                                From 
                                    PlayersCareerBatting s Join #tmp a 
                                On s.Id=a.Id
                                Where rn=1


                     ) x2
             ) x3
        Pivot 
            (
                Max(Runs1) For Type1 In (' + @Pivv + ')
            ) x4
        Pivot
            (
                Max(Runs) For Type In (' + @Pivv1 + ')
            ) x5

        Union All

        Select
            ''Bowling'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Wickets1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    Wickets As Wickets
                From ( 

                                Select 
                                    FirstName, 
                                    MiddleName,
                                    LastName,
                                    MatchType, 
                                    Wickets
                                From 
                                    PlayersBowlingCareer s Join #tmp a 
                                On s.Id=a.Id
                                Where rn=1


                     ) x7
             ) x8 
        Pivot 
            (
                Max(Wickets1) For Type1 In (' + @Pivv + ')
            ) x9
        Pivot
            (
                Max(Wickets) For Type In (' + @Pivv1 + ')
            ) x10'

Exec(@Query)

在任何情况下都使用 sp_executesql

如何知道该执行计划是否适合此查询（Sql server 2005）？

样本数据

预期产出

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

如何知道该执行计划是否适合此查询（Sql server 2005）？

样本数据

预期产出

1 个回答

相关问题