CODEWITHSUNDEEP
sqlperformancet-sql
Rafael Andrade
Rafael Andrade

Reputation: 505

Improving Query performance - T-SQL

I have a table that is already ordered by a column 'datetime'. Because when it is inserted I store the UTC date, so it is ordered. It's a very populated table. So I am trying to improve the query performance, if it is possible.

When I use something WHERE columnDateTime > dateToSearch it takes too long to return the rows. As my table is already ordered by columnDateTime what could I do to improve this query performance. For example, when a table is ordered by a cod and you try to search for cod > 40 T-SQL optimization will stop the search when it finds a cod = 41 and will return the rest of the table, cause it knows the table is ordered by that index. Is that a way that could tell T-SQL that my table is already ordered by that columnDateTime too?

Upvotes: 1

Views: 211

Answers (1)

EzLo
EzLo

Reputation: 14189

Inserting the data in order doesn't mean it is saved in order. Without getting too technical and for faster performance:

  • Create a CLUSTERED INDEX on that column. This requiers that there are no other clustered indexes on you table and it doesn't have a PRIMARY KEY (or it has it NONCLUSTERED which is not the default). With a clustered index, the engine will do a index scan (not a full table scan) when filtering with > datetimeValue and doesn't need to access aditional pages for the data, since a clustered index leaves are the data.

  • Create a NONCLUSTERED INDEX on that column. No restrictions on this clause (at least for this case), but for each match with your filtered date, the engine will need to access another page with the requested columns, unless you INCLUDE them when creating your index. Keep in mind that inlcuded columns will raise the size of the index and will need additional maintenance tasks like, for example, when an included column is modified.

That aside, you should check your query plan; if you have joins, function calls or additional conditions, the SQL engine might not use the indexes even if they exist. There are many things that could make a query run slow, you will have to post the full query execution plan (for a start) to check the details.

You can use this query to check if your table already has indexes:

DECLARE @table_name VARCHAR(200) = 'YourTableName'

SELECT
     SchemaName = SCHEMA_NAME(t.schema_id),
     TableName = t.name,
     IndexName = ind.name,
     IndexType = CASE ind.index_id WHEN 0 THEN 'Heap' WHEN 1 THEN 'Clustered' ELSE 'Nonclustered' END,
     Disabled = ind.is_disabled,
     ColumnOrder = ic.index_column_id,
     ColumnName = col.name,
     ColumnType = y.name,
     ColumnLength = y.max_length,
     ColumnIncluded = ic.is_included_column
FROM 
    sys.indexes ind
    INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.types y ON y.user_type_id = col.user_type_id
WHERE 
     t.is_ms_shipped = 0 AND
     t.name = @table_name
ORDER BY
    SchemaName,
    t.name, 
    ind.name, 
    ic.index_column_id

You need to make sure that there is at least one index that has your datetimeColumn with ColumnOrder = 1 and it's not disabled. If it already exists then your problem lies elsewhere and we won't be able to help much without more detail.

Upvotes: 3

Related Questions