如何更改表以获得更好的性能

本文介绍了如何更改表以获得更好的性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 大家好 我有下表没有任何主键和索引。该表包含超过2000万条记录。因此，Select语句非常慢（2012-12-01到2012-12-31的日期范围超过20秒）。 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 ] 请建议我如何优化此表。 谢谢。 Willington 解决方案 大家好， 感谢您的回复。我已经通过在Date_Time列上创建NonClustered索引来解决了这个问题，并且包含了select子句中经常使用的列。 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 keyand then apply indexingrefer this link having same problem you are facinghttp://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 IndexingSQL Server Journey with SQL AuthorityThanks 这篇关于如何更改表以获得更好的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！