April 4, 2019 at 1:36 pm
You have a table called table1, it has 2 columns ID and Name, no indexes
RUN 1
select ID, Name from Table1
RUN 2
add an index to ID
Select ID, Name from Table1
which RUN would be quicker 1 or 2?
thanks in advance
April 4, 2019 at 1:59 pm
My guess, the same. Test it yourself and see, that is the best way to answer this question.
April 4, 2019 at 2:01 pm
The index won't be used so both the same.
Thanks
April 4, 2019 at 2:04 pm
what if the ID field wasn't unique? would that make a difference?
April 4, 2019 at 2:06 pm
Nope, put a where clause in there using the ID field and you'll probably see a difference but as Lynn said why don't you test it yourself?
Thanks
April 4, 2019 at 3:03 pm
Run 1, because the index is useless. Indexes are for reducing the rows read, they have no value at all when you don't have a where clause filtering the rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2019 at 3:22 pm
Not necessarily true. Indexes can also allow SQL to avoid a sort (and sorts are expensive operations).
For example:
Add a clustered index on id to the table.
Then this query should run faster:
SELECT ID, Name
FROM Table1
ORDER BY ID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 4, 2019 at 5:46 pm
Not necessarily true. Indexes can also allow SQL to avoid a sort (and sorts are expensive operations).
Which is completely irrelevant, as neither of the queries given in the OP have a sort.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2019 at 6:19 pm
But you also made the blanket statement that:
Indexes are for reducing the rows read, they have no value at all when you don’t have a where clause filtering the rows.
And that is not true. Indexes can have value solely by preventing a sort, whether a WHERE is present or not.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 5, 2019 at 10:04 pm
Timing without the index was 7.54 seconds
Timing with the index was 7.37333 seconds
1 000 000 rows, cache cleared. Tested in SSMS.
In both cases the table is scanned and the index is not used. So the time is spend on reading the pages from disk and displaying the result on screen.
Although a simple question, this does not anwser the question for other situations. For a full table scan or a full index scan, the index scan can be faster if all needed columns are present in the index and the index consists of less blocks than the table. Here reading only the ID takes 6.20666 seconds. The index of the id consists of less pages than the table itself.
Without the output using (first clearing the cache):
Without the output using (first clearing the cache):
SELECT sum(id) FROM TABLE1
The timing is 0.47666 secs
Not clearing the cache does it in 0.103 secs.
The timing is 0.47666 secs
Not clearing the cache does it in 0.103 secs.
While
While
SELECT sum(ID), max(TEKST) FROM TABLE1
The timing is 1.42666
Not clearing the cache does it in 0.03666 secs.
The timing is 1.42666
Not clearing the cache does it in 0.03666 secs.
So most of the time is spend displaying.
Not clearing the cache gives the supprising result:
The difference between the sum(id) at consistently around 0.10
And the sum(id) plus the max(TEKST) at consistently around 0.04
Getting more information is less expensive.
This shows that timing is not very predictable.
Ben
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply