[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

index

farshad

3/21/2007 2:06:00 PM

Hi,
There is a table which I regularly run a select query on.
The select query always has a fixed where clause on only three of the
columns with different parameters.

This is a query that runs each time:

select * from tblData
where
PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()

This are the types of indexes the table currently has:
One index for each of these three fields
i.e. index1 for PersonNo
index2 for EmployeeType
index3 for DataDate
In addition to the above, I also have created a covering index as follows
index4 for PersonNo,EmployeeType,DataDate

Is what I have enough for indexes on this table please?
Is there anything else I have to do on indexing this table?
Thanks
6 Answers

xyb

3/21/2007 2:18:00 PM

0

On 3?21?, ??10?06?, farshad <fars...@discussions.microsoft.com> wrote:
> Hi,
> There is a table which I regularly run a select query on.
> The select query always has a fixed where clause on only three of the
> columns with different parameters.
>
> This is a query that runs each time:
>
> select * from tblData
> where
> PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()
>
> This are the types of indexes the table currently has:
> One index for each of these three fields
> i.e. index1 for PersonNo
> index2 for EmployeeType
> index3 for DataDate
> In addition to the above, I also have created a covering index as follows
> index4 for PersonNo,EmployeeType,DataDate
>
> Is what I have enough for indexes on this table please?
> Is there anything else I have to do on indexing this table?
> Thanks

you can have a look at execute plan to find which index your query use.

farshad

3/21/2007 2:30:00 PM

0

Not sure how to study the execution plan.

"xyb" wrote:

> On 3æ??21æ?¥, ä¸?å?10æ?¶06å??, farshad <fars...@discussions.microsoft.com> wrote:
> > Hi,
> > There is a table which I regularly run a select query on.
> > The select query always has a fixed where clause on only three of the
> > columns with different parameters.
> >
> > This is a query that runs each time:
> >
> > select * from tblData
> > where
> > PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()
> >
> > This are the types of indexes the table currently has:
> > One index for each of these three fields
> > i.e. index1 for PersonNo
> > index2 for EmployeeType
> > index3 for DataDate
> > In addition to the above, I also have created a covering index as follows
> > index4 for PersonNo,EmployeeType,DataDate
> >
> > Is what I have enough for indexes on this table please?
> > Is there anything else I have to do on indexing this table?
> > Thanks
>
> you can have a look at execute plan to find which index your query use.
>
>

xyb

3/21/2007 2:53:00 PM

0

On 3?21?, ??10?30?, farshad <fars...@discussions.microsoft.com> wrote:
> Not sure how to study the execution plan.
>
>
>
> "xyb" wrote:
> > On 3?21?, ??10?06?, farshad <fars...@discussions.microsoft.com> wrote:
> > > Hi,
> > > There is a table which I regularly run a select query on.
> > > The select query always has a fixed where clause on only three of the
> > > columns with different parameters.
>
> > > This is a query that runs each time:
>
> > > select * from tblData
> > > where
> > > PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()
>
> > > This are the types of indexes the table currently has:
> > > One index for each of these three fields
> > > i.e. index1 for PersonNo
> > > index2 for EmployeeType
> > > index3 for DataDate
> > > In addition to the above, I also have created a covering index as follows
> > > index4 for PersonNo,EmployeeType,DataDate
>
> > > Is what I have enough for indexes on this table please?
> > > Is there anything else I have to do on indexing this table?
> > > Thanks
>
> > you can have a look at execute plan to find which index your query use.- ??????? -
>
> - ??????? -

e:)
SET SET SHOWPLAN_ALL ON
--code here
select min(c1) from #t
union select min(c1) from #t where c1 not in (select min(c1) from #t)

OR
server2000
QA:tools-->query-->show excute plan

farshad

3/21/2007 3:07:00 PM

0

Thanks but do not understand it.
Where/how do I make sense out of this result of the execution plan please?

"xyb" wrote:

> On 3æ??21æ?¥, ä¸?å?10æ?¶30å??, farshad <fars...@discussions.microsoft.com> wrote:
> > Not sure how to study the execution plan.
> >
> >
> >
> > "xyb" wrote:
> > > On 3æ??21æ?¥, ä¸?å?10æ?¶06å??, farshad <fars...@discussions.microsoft.com> wrote:
> > > > Hi,
> > > > There is a table which I regularly run a select query on.
> > > > The select query always has a fixed where clause on only three of the
> > > > columns with different parameters.
> >
> > > > This is a query that runs each time:
> >
> > > > select * from tblData
> > > > where
> > > > PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()
> >
> > > > This are the types of indexes the table currently has:
> > > > One index for each of these three fields
> > > > i.e. index1 for PersonNo
> > > > index2 for EmployeeType
> > > > index3 for DataDate
> > > > In addition to the above, I also have created a covering index as follows
> > > > index4 for PersonNo,EmployeeType,DataDate
> >
> > > > Is what I have enough for indexes on this table please?
> > > > Is there anything else I have to do on indexing this table?
> > > > Thanks
> >
> > > you can have a look at execute plan to find which index your query use.- é?è?è¢«å¼?ç?¨æ??å­? -
> >
> > - æ?¾ç¤ºå¼?ç?¨ç??æ??å­? -
>
> e:)
> SET SET SHOWPLAN_ALL ON
> --code here
> select min(c1) from #t
> union select min(c1) from #t where c1 not in (select min(c1) from #t)
>
> OR
> server2000
> QA:tools-->query-->show excute plan
>
>

farshad

3/21/2007 3:38:00 PM

0

In sql 2005, how is it possible to turn off the execution plan tab. thanks

"xyb" wrote:

> On 3æ??21æ?¥, ä¸?å?10æ?¶30å??, farshad <fars...@discussions.microsoft.com> wrote:
> > Not sure how to study the execution plan.
> >
> >
> >
> > "xyb" wrote:
> > > On 3æ??21æ?¥, ä¸?å?10æ?¶06å??, farshad <fars...@discussions.microsoft.com> wrote:
> > > > Hi,
> > > > There is a table which I regularly run a select query on.
> > > > The select query always has a fixed where clause on only three of the
> > > > columns with different parameters.
> >
> > > > This is a query that runs each time:
> >
> > > > select * from tblData
> > > > where
> > > > PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()
> >
> > > > This are the types of indexes the table currently has:
> > > > One index for each of these three fields
> > > > i.e. index1 for PersonNo
> > > > index2 for EmployeeType
> > > > index3 for DataDate
> > > > In addition to the above, I also have created a covering index as follows
> > > > index4 for PersonNo,EmployeeType,DataDate
> >
> > > > Is what I have enough for indexes on this table please?
> > > > Is there anything else I have to do on indexing this table?
> > > > Thanks
> >
> > > you can have a look at execute plan to find which index your query use.- é?è?è¢«å¼?ç?¨æ??å­? -
> >
> > - æ?¾ç¤ºå¼?ç?¨ç??æ??å­? -
>
> e:)
> SET SET SHOWPLAN_ALL ON
> --code here
> select min(c1) from #t
> union select min(c1) from #t where c1 not in (select min(c1) from #t)
>
> OR
> server2000
> QA:tools-->query-->show excute plan
>
>

Hugo Kornelis

3/21/2007 7:29:00 PM

0

On Wed, 21 Mar 2007 07:06:05 -0700, farshad wrote:

>Hi,
>There is a table which I regularly run a select query on.
>The select query always has a fixed where clause on only three of the
>columns with different parameters.
>
>This is a query that runs each time:
>
>select * from tblData
>where
> PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()
>
>This are the types of indexes the table currently has:
>One index for each of these three fields
>i.e. index1 for PersonNo
>index2 for EmployeeType
>index3 for DataDate
>In addition to the above, I also have created a covering index as follows
>index4 for PersonNo,EmployeeType,DataDate
>
>Is what I have enough for indexes on this table please?
>Is there anything else I have to do on indexing this table?
>Thanks

Hi farshad,

For the above query, index4 will be used. Note that this is not a
covering index, though - since you use SELECT *, all columns in the
table have to be accessed, so a covering index would have to include all
columns (which goes against the base idea of a covering index). This is
but one of the many reasons why you should not use SELECT * in
production code.

>In sql 2005, how is it possible to turn off the execution plan tab. thanks

One way is to use the menu option Query | Include Actual Query Plan.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...