如何更改表以获得更好的性能 [英] How to change the table for better performance

问题描述

大家好



我有下表没有任何主键和索引。该表包含超过2000万条记录。因此，Select语句非常慢（2012-12-01到2012-12-31的日期范围超过20秒）。

  CREATE   TABLE  tblDataLog（
 [DATE_TIME] [ datetime ]  NOT   NULL ，
 [ 5 ] [ float ]  NULL ，
 [< span class =code-digit> 6 ] [ float ]  NULL ，
 [ 7 ] [ float ]  NULL ，
 [ 8 ] [ float ]  NULL ，
 [ 9 ] [ float ]  NULL ，
 [ 10 ] [ float ]  NULL ，
 [ 11 ] [ float  ]  NULL ，
 [ 12 ] [ float ]  NULL ，
 [ 13 ] [ float ]  NULL ，
 [ 14 ] [ float ]  NULL ，
 [ 15 ] [ float ]  NULL ，
 [ 16 ] [ float ]  NULL ，
 [ 17 ] [ float ]  NULL ，
 [ 18 ] [ float ]  NULL ，
 [ 19 ] [ float ]  NULL ，
 [ 20 ] [ float ]  NULL ，
 [ 21 ] [  float ]  NULL ，
 [ 22 ] [ float ]  NULL ，
 [ 23 ] [ float ]  NULL ，
 [ 24 ] [ float ]  NULL ，
 [< span class =code-digit> 25 ] [ float ]  NULL ，
 [ 26 ] [ float ]  NULL ，
 [ 27  ] [ float ]  NULL ，
 [ 28 ] [ float ]  NULL ，
 [ 29 ] [ float ]  NULL ，
 [ 30 ] [ float ]  NULL ，
 [ 31 ] [ float ]  NULL ， 
 [ 32 ] [ float ]  NULL ，
 [ 33 ] [ float ]  NULL ，
 [ 34 ] [ float ]  NULL ，
 [ 35 ] [ float ]  NULL ，
 [< span class =code-digit> 36 ] [ float ]  NULL ，
 [ 37 ] [ float ]  NULL ，
 [ 38 ] [ float ]  NULL ，
 [ 39 ] [ float ]  NULL ，
 [ 40 ] [ float ]  NULL ，
 [ 41 ] [ float ]  NULL  
 ） ON  [ PRIMARY ] 

请建议我如何优化此表。



谢谢。

Willington

解决方案

大家好，



感谢您的回复。我已经通过在Date_Time列上创建NonClustered索引来解决了这个问题，并且包含了select子句中经常使用的列。

 < span class =code-keyword> CREATE   INDEX  NDX_tblTestData 
  ON  tblTestData 
（DATE_TIME）INCLUDE（Col1，Col2，Col7）; 

你应该添加auto-incr主键

然后申请索引



请参阅此链接，您面临同样的问题



http://stackoverflow.com/questions/1716798/sql-server-2008-ordering -by-datetime-too-slow [ ^ ]



快乐编码！

：）

将ID列保留为主键，并在任意两列上使用索引来检索记录。

请参阅以下链接进行索引。



CREATE INDEX（Transact-SQL）

SQL Server 2005/2008表索引概述

带SQL权限的SQL Server之旅



谢谢

Hi All

I have the following table without any primary key and Index. This table contains more than 20 Million records. Hence the Select statement is very slow (more than 20 sec for a date range 2012-12-01 to 2012-12-31).

CREATE TABLE tblDataLog(
    [DATE_TIME] [datetime] NOT NULL,
    [5] [float] NULL,
    [6] [float] NULL,
    [7] [float] NULL,
    [8] [float] NULL,
    [9] [float] NULL,
    [10] [float] NULL,
    [11] [float] NULL,
    [12] [float] NULL,
    [13] [float] NULL,
    [14] [float] NULL,
    [15] [float] NULL,
    [16] [float] NULL,
    [17] [float] NULL,
    [18] [float] NULL,
    [19] [float] NULL,
    [20] [float] NULL,
    [21] [float] NULL,
    [22] [float] NULL,
    [23] [float] NULL,
    [24] [float] NULL,
    [25] [float] NULL,
    [26] [float] NULL,
    [27] [float] NULL,
    [28] [float] NULL,
    [29] [float] NULL,
    [30] [float] NULL,
    [31] [float] NULL,
    [32] [float] NULL,
    [33] [float] NULL,
    [34] [float] NULL,
    [35] [float] NULL,
    [36] [float] NULL,
    [37] [float] NULL,
    [38] [float] NULL,
    [39] [float] NULL,
    [40] [float] NULL,
    [41] [float] NULL
) ON [PRIMARY]

Please suggest me how to optimize this table.

Thanks.
Willington

解决方案

Hi All,

Thanks for reply. I have solved the issue by creating NonClustered index on Date_Time Column and included the columns which are frequently used in the select clause.

CREATE INDEX NDX_tblTestData
     ON tblTestData
        (DATE_TIME) INCLUDE (Col1, Col2, Col7);

you should add auto-incr primary key
and then apply indexing

refer this link having same problem you are facing

http://stackoverflow.com/questions/1716798/sql-server-2008-ordering-by-datetime-is-too-slow[^]

Happy Coding!
:)

Hi,

Keep an ID column as primary key and use indexing on any two columns for retrieving the records.
See the following link for indexing.

CREATE INDEX (Transact-SQL)
Overview of SQL Server 2005/2008 Table Indexing
SQL Server Journey with SQL Authority

Thanks

这篇关于如何更改表以获得更好的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！