August 18, 2004 at 2:45 pm
The question I have is "Is the number of reads in any way responsible for the time a query would take to execute?"
In our system, I have this table which when I query with the FK takes 68k+ reads and after deleting a particular index the number of reads drops to 58k+. Also when I convert a third index into a composite index with the one i deleted it further drops the read count by a couple of hundred records.
Any inputs?
Thanks.
August 19, 2004 at 1:14 am
"Is the number of reads in any way responsible for the time a query would take to execute?"
YES. Time spent on too many reads on the index (and data) add to the the query time. As simple as that. It is one of the most influential parameters. Other causes for affecting the query time also exist, like locking, large amounts of adta buffered, slow devices, record and/or file fragmentation etc.
For B-Tree indexes the rule (a simplified way) usually goes like each level of the B-tree index translates to an I/O. Each I/O has a specific cost (time). The deeper the B-Tree the more I/Os you need to do and thus more query time.
August 19, 2004 at 8:20 am
Reads is one of the major factors which will impact the execution time of the query
You can use the following also to give you more information about what specific table is resulting in more reads.
set statistics io on.
August 19, 2004 at 10:07 am
Isn't it true that if most of the indexes of a table in the where clause the dataset will pull up faster? Or is it that the dataset has to be large to take advantage of more than one index?
Here's what am doing. The table has 250k+ records over 50 different users. What I am trying to figure out if I need to have the identity column as a Clustered Index or not?
As a Clustered Index:
Reads 35 (Without any of the other indexed columns in the where condition)
Reads 65 (With three (2) other indexed columns in the where condition)
As a Non-Clustered Index:
Reads 35 (Without any of the other indexed columns in the where condition)
Reads 65 (With three (2) other indexed columns in the where condition)
These results are quite the contrary.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply