47394


一部名前を変え忘れた箇所があったので質問を上げなおしました。申し訳ございません。

logsテーブル 500万行 30列くらい  
logs_subテーブル 500万行 20列くらい logsテーブルと1対0または1

上記のテーブルから一定の期間のレコードを取得するため  
datetime型(datetime2ではない)の列で検索を行う必要があるのですが、  
where句とorder by句を同時に指定すると100倍以上遅くなります。

[code]

    --遅くない
    select *
    from logs l
    left join logs_sub ls
    on l.logdate = ls.logdate
    and l.id1 = ls.id1
    and l.id2 = ls.id2
    and l.id3 = ls.id3
    and l.id4 = ls.id4
    where logdate between @start and @end
    
    --遅くない
    select *
    from logs l
    left join logs_sub ls
    on l.logdate = ls.logdate
    and l.id1 = ls.id1
    and l.id2 = ls.id2
    and l.id3 = ls.id3
    and l.id4 = ls.id4
    order by logdate
    
    --100倍以上遅い
    select *
    from logs l
    left join logs_sub ls
    on l.logdate = ls.logdate
    and l.id1 = ls.id1
    and l.id2 = ls.id2
    and l.id3 = ls.id3
    and l.id4 = ls.id4
    where logdate between @start and @end
    order by logdate
    
[/code]

テーブル定義

[code]

    CREATE TABLE [dbo].[logs](
        [logdate] [datetime] NOT NULL,
        [id1] [nvarchar](100) NOT NULL,
        [id2] [int] NOT NULL,
        [id3] [int] NOT NULL,
        [id4] [int] NOT NULL,
        --ほかいろいろ
     CONSTRAINT [pk] PRIMARY KEY CLUSTERED 
    (
        [logdate] ASC,
        [id1] ASC,
        [id2] ASC,
        [id3] ASC,
        [id4] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    CREATE TABLE [dbo].[logs_sub](
        [logdate] [datetime] NOT NULL,
        [id1] [nvarchar](100) NOT NULL,
        [id2] [int] NOT NULL,
        [id3] [int] NOT NULL,
        [id4] [int] NOT NULL,
        --ほかいろいろ
     CONSTRAINT [pk] PRIMARY KEY CLUSTERED 
    (
        [logdate] ASC,
        [id1] ASC,
        [id2] ASC,
        [id3] ASC,
        [id4] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
[/code]

  * 今よりも速く、datetime列でソートされた値を取得する必要があります。
  * 検索結果が100万件以上になることもありますが、そのすべてを取得する必要があります。
  * インデックスを追加することはできません。

クライアント側でソートを行う以外にできることはありますか？


CC BY-SA 4.0
2018-08-09T11:50:19.190
47383
2018-08-10T04:10:23.357
21330
question
sql sql-server
SQL Server 2016でdatetime列のwhere句とorder by句を同時に指定すると100倍以上遅くなる


クエリをどう解釈し、どう実行されたかは[実行プランを表示](https://docs.microsoft.com/ja-jp/sql/relational-
databases/performance/display-and-save-execution-plans?view=sql-
server-2016)することで確認できます。３つのクエリを比較してみてください。比較結果をふまえて効率的なクエリに書き換えることができるかもしれません。

これは実行プランを見ずにの当てずっぽうの案ですが

[code]

    SELECT *
    FROM (SELECT * FROM logs WHERE logdate BETWEEN @start AND @end) AS l
    LEFT OUTER JOIN logs_sub AS ls
    ON l.logdate = ls.logdate
    AND l.id1 = ls.id1
    AND l.id2 = ls.id2
    AND l.id3 = ls.id3
    AND l.id4 = ls.id4
    ORDER BY l.logdate
    
[/code]

と絞り込んでから結合してみるのはどうでしょうか？


CC BY-SA 4.0
2018-08-09T23:02:04.537
47394
2018-08-09T23:02:04.537
4236
47383
answer


実行プランを見ていろいろ試した結果、  
クラスター化インデックスの項目をすべてorder by句に含めることで  
order by無しと同じくらいのパフォーマンスが得られたことをここに報告します。  
ありがとうございました。

[code]

    SELECT *
    FROM (SELECT * FROM logs WHERE logdate BETWEEN @start AND @end) AS l
    LEFT OUTER JOIN logs_sub AS ls
    ON l.logdate = ls.logdate
    AND l.id1 = ls.id1
    AND l.id2 = ls.id2
    AND l.id3 = ls.id3
    AND l.id4 = ls.id4
    ORDER BY l.logdate, l.id1, l.id2, l.id3, l.id4
    
[/code]


CC BY-SA 4.0
2018-08-10T04:10:23.357
47404
2018-08-10T04:10:23.357
21330
47383
answer