query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
Wang
What happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang
|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:

> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> performance
> pos_seq_no
> and
>
>

query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
WangWhat happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:
> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> > Hi,
> >
> > My query 1 needs to narrow down a time range. I index the column
> > (pos_trandate_time, plus the other on the where clause), but the
> performance
> > still very slow, around 1 minutes. Wherease the query on date only is much
> > faster. What should I do?
> >
> > --query 1, cost 99%
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> > pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> > as tx_msg_type, pos_trans_code as tx_tran_type
> > from pos_txn_log (index=pk_pos_txn_log)
> > where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> > and pos_trandate_time <= '2004-07-05 23:59:59'
> > order by pos_trandate_time
> >
> > -- query 2, cost %1
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time,
> > pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
> pos_seq_no
> > as tx_seq_no, pos_msg_type as tx_msg_type,
> > pos_trans_code as tx_tran_type
> > from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
> and
> > CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> > order by pos_trandate_time
> >
> >
> > Thanks
> > Wang
>
>

query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
WangWhat happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:

> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> performance
> pos_seq_no
> and
>
>

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?
For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
...
and many other questions need to be asked and answered to come up with the
best choices.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>
|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>
|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
..
and many other questions need to be asked and answered to come up with the
best choices.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Select col1 from table1
>> where col2 < 100
>> In this case, if we have a covered index on col1 and col2,
>> would the index on (col1,col2) or (col2,col1) perform better and faster ?
>> I am leaning towards this order : (col2,col1) as col2 is the leading
>> column and col1 data is just stored along with col2 in the leaf page
>> although col1 would be stored in an ordered fashion.
>> Am i correct in my theory ?
>>
>|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> For this particular query, an index on (col2, col1) would most likely be
>> faster. But index tuning isn't done one query at a time.
>> What other queries reference table1 and access col1 and col2?
>> You need to design indexes so that all the queries that access the table
>> will perform reasonably well. There may be other choices of indexes that
>> work well for this query, and also work well for many other queries.
>> What percentage of the table have col2 values less than 100?
>> How many rows fit per page?
>> Are you also performing updates to the table?
>> ..
>> and many other questions need to be asked and answered to come up with
>> the best choices.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Select col1 from table1
>> where col2 < 100
>> In this case, if we have a covered index on col1 and col2,
>> would the index on (col1,col2) or (col2,col1) perform better and faster
>> ?
>> I am leaning towards this order : (col2,col1) as col2 is the leading
>> column and col1 data is just stored along with col2 in the leaf page
>> although col1 would be stored in an ordered fashion.
>> Am i correct in my theory ?
>>
>>
>

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
..
and many other questions need to be asked and answered to come up with the
best choices.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (> ), less than (< ), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>

Query Analyzer: Execution Plan: Clustered Index Seek: I/O Cost

Can anyone explain how I/O cost is calculated?
My example:
Physical operation: Clustered Index Seek
Logical operation: Clustered Index Seek
Row count: 1
Estimated row size: 76
I/O cost: 0.00632
CPU cost: 0.000080
Number of executes: 1
Cost: 0.006408(100%)
Subtree cost: 0.00640
Estimated row count: 1
Argument: ...
If you really want to know some of the details about query costs, you should
read some of Joe Chang's articles at www.sql-server-performance.com . This
is an excellent site for performance tips, and Joe has several detailed
articles about calculating the cost of a query.
"Dave Owen" <DaveOwen@.discussions.microsoft.com> wrote in message
news:811149F2-25E0-43BA-B3A6-43B17D7014F4@.microsoft.com...
> Can anyone explain how I/O cost is calculated?
> My example:
> Physical operation: Clustered Index Seek
> Logical operation: Clustered Index Seek
> Row count: 1
> Estimated row size: 76
> I/O cost: 0.00632
> CPU cost: 0.000080
> Number of executes: 1
> Cost: 0.006408(100%)
> Subtree cost: 0.00640
> Estimated row count: 1
> Argument: ...