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, 1708🔥, 0💬
Popular Posts:
How to execute statements in loops in SQL Server Transact-SQL? How to use WHILE ... loops? You can u...
Is PL/SQL Language Case Sensitive in Oracle? PL/SQL language is not case sensitive: Reserved words a...
Where to find reference information and tutorials on MySQL database functions? I want to know how to...
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...