Professional Web Applications Themes

Match on datetime column tooooo slow - Microsoft SQL / MS SQL Server

aleksl , Your create table and index DDL would make it easier to diagnose. You may get better performance by re-ordering the columns in your compound index. Or failing that adding an additional index for CreateDate alone - not compound, and not clustered - This will have some maintenance issues - reindex, update statistics, maintenance plan ... and will slow your inserts, but will make this particular query as fast as possible. Although 200,000 is probably a little small to gain a benefit, I have had some success in using a partioned view for similar problems. Regards AJ "A_X_L_X" <aleksl ...

  1. #1

    Default Re: Match on datetime column tooooo slow

    aleksl ,

    Your create table and index DDL would make it easier to diagnose.

    You may get better performance by re-ordering the columns in your compound index.
    Or failing that adding an additional index for CreateDate alone - not compound,
    and not clustered - This will have some maintenance issues - reindex, update statistics,
    maintenance plan ... and will slow your inserts, but will make this particular query
    as fast as possible.

    Although 200,000 is probably a little small to gain a benefit, I have had some success
    in using a partioned view for similar problems.

    Regards
    AJ

    "A_X_L_X" <aleksl at aol dot com> wrote in message news:phx.gbl... 


    Andrew Guest

  2. #2

    Default Re: Match on datetime column tooooo slow


    "A_X_L_X" <aleksl at aol dot com> wrote in message
    news:phx.gbl... 
    trying 

    Assuming that CreateDate is the _leading_ column in the clustered index, . .
    ..

    here's the deal. When optimizing this query SQL has no idea what value of
    CutOffDate you are going to pass. And SQL will "assume" an average value
    for CutOffDate, which may make using the index seem like a bad option. In
    fact, you know you will be using values of CutOffDate which make the
    CreateDate index very selective. When you know something that SQL doesn't,
    that's a good time to use a query hint. Force it to use the index, if you
    think that's the right plan. See if it's quicker.

    Also you can try hard coding your value into the query. This will allow the
    server to decide on index usage having all the facts. This is a bad idea
    for cheap queries, but for expensive queries the recompilation it forces can
    sometimes be more than paid for by getting a more appropriate execution
    plan.

    David



    David Guest

  3. #3

    Default Re: Match on datetime column tooooo slow

    Actually, it was more like 30-40 seconds.
    Hurray, I found the problem.
    Apparently, when you select data into memory table, it is OK when all data
    is part of some index.
    Once you start mixing index and non-index data, you are doomed.

    Table:

    CREATE TABLE Table1 (
    Key1 int NOT NULL,
    CreateDate datetime NOT NULL,


    Query was something like:

    DECLARE tmpTable (Key1 int, CreateDate datetime, Field1 varchar(20))
    DECLARE CutoffDate datetime

    SET CutoffDate = DATEADD(hh,-6,GETDATE())

    SELECT Key1, CreateDate, Field1
    FROM Table1
    WHERE CreateDate >= CutoffDate




    "A_X_L_X" <aleksl at aol dot com> wrote in message
    news:phx.gbl... 
    trying 



    A_X_L_X Guest

Similar Threads

  1. question about datetime column in mysql
    By nephish in forum MySQL
    Replies: 2
    Last Post: October 30th, 01:41 PM
  2. update to match 2 different column of data
    By alternative in forum Coldfusion Database Access
    Replies: 4
    Last Post: November 10th, 12:43 AM
  3. Datetime column index access
    By Octavio in forum Informix
    Replies: 16
    Last Post: January 23rd, 02:55 PM
  4. Replies: 1
    Last Post: August 6th, 04:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139