Collections:
Index Speeding Up SELECT Statements in SQL Server
Does Index Speed Up SELECT Statements in SQL Server?
✍: FYIcenter.com
If you want to see the impact of indexes on SELECT statements, you can run the same SELECT statement on "fyi_links" and "fyi_links_indexed" tables. See the tutorial exercise below:
USE FyiCenterData; GO -- Run SELECT on the table without indexes DECLARE @start_time DATETIME, @end_time DATETIME; SET @start_time = GETDATE(); SELECT TOP 3 counts, url FROM fyi_links WHERE url LIKE 'a%' ORDER BY counts DESC; SET @end_time = GETDATE(); PRINT 'Milliseconds used: '+CONVERT(VARCHAR(20), DATEDIFF(MILLISECOND,@start_time,@end_time)); GO counts url ----------- ------------------------------------------------ 999417 a ihgu migox mlqvi gvs n nmabwdk iehu ezjjv n l 999008 agqbr gkjwfpjvp z c cq k inen j ppjsu x iuk uhp 998471 a yiu squqco eih Milliseconds used: 46 -- Run SELECT on the table with indexes DECLARE @start_time DATETIME, @end_time DATETIME; SET @start_time = GETDATE(); SELECT TOP 3 counts, url FROM fyi_links_indexed WHERE url LIKE 'a%' ORDER BY counts DESC; SET @end_time = GETDATE(); PRINT 'Milliseconds used: '+CONVERT(VARCHAR(20), DATEDIFF(MILLISECOND,@start_time,@end_time)); GO counts url ----------- ------------------------------------------------ 999417 a ihgu migox mlqvi gvs n nmabwdk iehu ezjjv n l 999008 agqbr gkjwfpjvp z c cq k inen j ppjsu x iuk uhp 998471 a yiu squqco eih Milliseconds used: 0
The test result shows indexes on "url" and "counts" did improve the SELECT statement from 46 milliseconds to almost 0 millisecond.
⇒ Adding a New Index to a Large Table in SQL Server
⇐ Index Slowing Down INSERT Statements in SQL Server
2016-11-13, 1499🔥, 0💬
Popular Posts:
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...
How To Get a List of All Tables with "sys.tables" View in SQL Server? If you want to see the table y...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...
What are single-byte character string data types supported in SQL Server Transact-SQL? Single-byte c...
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...