|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Using Clustered IndexHello there
I got sql database to optimize it. On one of the table has identity column which is the primary key There are also 6 main fields that most of the actions on the server are using them to locate data. and this table has 500000 records and more On the beginning the primary key was also clustered index. When i create new index with all the 6 fields and set it as clustered all the quries that use this table become mutch more effective. (one of the query took before 45 seconds to run and after setting the index it took 2 seconds) Can someone explain whay it become mutch more effective? Roy, shalom
It's probably because you have to range based criteria like dt>=@datestart and dt<@dateend and /or using columns in SELECT statements that an index covers them But you should continue to investigate your queries and pay attention for indexes in future becauser as time passed your data is going to be changed. Show quote "Roy Goldhammer" <r**@hotmail.com> wrote in message news:%23xzDraN8FHA.3132@TK2MSFTNGP12.phx.gbl... > Hello there > > I got sql database to optimize it. > > On one of the table has identity column which is the primary key > There are also 6 main fields that most of the actions on the server are > using them to locate data. and this table has 500000 records and more > > On the beginning the primary key was also clustered index. > > When i create new index with all the 6 fields and set it as clustered all > the quries that use this table become mutch more effective. (one of the > query took before 45 seconds to run and after setting the index it took 2 > seconds) > > Can someone explain whay it become mutch more effective? > > > Thankes uri. It exacly what i'm use (BETWEEN ... and ...)
Does the arrange of the 6 fields on the clustered index is metter? -- Show quoteøåòé âåìãäîø òúéã äðãñú úåëðä èì' 03-5611606 ôìà' 050-7709399 àéîééì: r**@atidsm.co.il "Uri Dimant" <u***@iscar.co.il> wrote in message news:%23MDTqmN8FHA.1148@tk2msftngp13.phx.gbl... > Roy, shalom > > It's probably because you have to range based criteria like > dt>=@datestart and dt<@dateend and /or using columns in SELECT statements > that an index covers them > > But you should continue to investigate your queries and pay attention for > indexes in future becauser as time passed your data is going to be > changed. > > > > > > > > > "Roy Goldhammer" <r**@hotmail.com> wrote in message > news:%23xzDraN8FHA.3132@TK2MSFTNGP12.phx.gbl... >> Hello there >> >> I got sql database to optimize it. >> >> On one of the table has identity column which is the primary key >> There are also 6 main fields that most of the actions on the server are >> using them to locate data. and this table has 500000 records and more >> >> On the beginning the primary key was also clustered index. >> >> When i create new index with all the 6 fields and set it as clustered all >> the quries that use this table become mutch more effective. (one of the >> query took before 45 seconds to run and after setting the index it took 2 >> seconds) >> >> Can someone explain whay it become mutch more effective? >> >> >> > > Roy
It's hard to suggest something without seeing your code As I understood , you have already improved the query, so just keep eye on it in the future. Show quote "Roy Goldhammer" <r**@hotmail.com> wrote in message news:u05o6gO8FHA.1864@TK2MSFTNGP12.phx.gbl... > Thankes uri. It exacly what i'm use (BETWEEN ... and ...) > > Does the arrange of the 6 fields on the clustered index is metter? > > -- > øåòé âåìãäîø > òúéã äðãñú úåëðä > èì' 03-5611606 > ôìà' 050-7709399 > àéîééì: r**@atidsm.co.il > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:%23MDTqmN8FHA.1148@tk2msftngp13.phx.gbl... >> Roy, shalom >> >> It's probably because you have to range based criteria like >> dt>=@datestart and dt<@dateend and /or using columns in SELECT >> statements that an index covers them >> >> But you should continue to investigate your queries and pay attention >> for indexes in future becauser as time passed your data is going to be >> changed. >> >> >> >> >> >> >> >> >> "Roy Goldhammer" <r**@hotmail.com> wrote in message >> news:%23xzDraN8FHA.3132@TK2MSFTNGP12.phx.gbl... >>> Hello there >>> >>> I got sql database to optimize it. >>> >>> On one of the table has identity column which is the primary key >>> There are also 6 main fields that most of the actions on the server are >>> using them to locate data. and this table has 500000 records and more >>> >>> On the beginning the primary key was also clustered index. >>> >>> When i create new index with all the 6 fields and set it as clustered >>> all the quries that use this table become mutch more effective. (one of >>> the query took before 45 seconds to run and after setting the index it >>> took 2 seconds) >>> >>> Can someone explain whay it become mutch more effective? >>> >>> >>> >> >> > > On Thu, 24 Nov 2005 11:13:32 +0200, Roy Goldhammer wrote:
Show quote >Hello there Hi Roy,> >I got sql database to optimize it. > >On one of the table has identity column which is the primary key >There are also 6 main fields that most of the actions on the server are >using them to locate data. and this table has 500000 records and more > >On the beginning the primary key was also clustered index. > >When i create new index with all the 6 fields and set it as clustered all >the quries that use this table become mutch more effective. (one of the >query took before 45 seconds to run and after setting the index it took 2 >seconds) > >Can someone explain whay it become mutch more effective? > > If the 6 columns used in your WHERE clauses were previously indexed by a nonclustered index, then the procedure was as follows: a. Use nonclustered index to locate matching rows. Fast, because matching rows can be located directly in index. b. Use key found in nonclustered index to locate the complete row in the clustered index. Very slow, because the process is non-sequential (rows that are sequential in the nonclustered index will typically not be seuqntial in any other index). And Murphy's law makes sure that the page you need is always the page that has just been swapped out of cache. If the 6 columns in your WHERE clauses were not indexed at all, the procedure was: a. Scan the complete table to find matching rows. Slow, since all data pages have to be read. After creating the clustered index on the 6 columns used in your WHERE< the process has become: a. Use clustered index to locate matching rows. Fast, because matching rows can be located directly in index. b. Nothing more, since leaf pages of nonclustered index contain the complete data. On Thu, 24 Nov 2005 13:19:14 +0200, Roy Goldhammer wrote: >Thankes uri. It exacly what i'm use (BETWEEN ... and ...) Yes, it does. Consider a phone book with entries ordered by city, last> >Does the arrange of the 6 fields on the clustered index is metter? name, street. If you have to find all people named "Brown" living in the city of Wicketshire, any street, you'll be done in an instant. If you have to find all people named "Brown" living in Main Street, any city, you'll have to leaf the entire phone book. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics