|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
help on IndexesI haven't add indexes to a database for a long long time so I've completely forgotten how to go about designing an index. Say I have the following query (which is run often): SELECT bl.TotalRooms, bt.TypeName, u.UserWorkTel, p.DisplayAddress, p.idList, cy.CountyCityName, d.DistrictPostcode, p.idPublicDetails, p.PriceVal, p.Description, fr.FrequencyName, p.ModifyDate, p.ListRef, u.idUserType, lt.idAdType, pt.PTypeName, lcl.OccasionalWeekends, lcl.WeekendPrice, lc.idLetCategory, lc.CategoryName, c.CountryName, d.DistrictName, p.idPropStatus, pp.PicPath, (al.PicPath) AS 'Logo', al.idOffice, FROM Property p JOIN PropertyType pt ON p.idPType = pt.idPType JOIN Country c ON p.idCountry = c.idCountry JOIN District d ON p.idDistrict = d.idDistrict JOIN CountyCity cy ON p.idCountyCity = cy.idCountyCity JOIN Frequency fr ON p.idFrequency = fr.idFrequency LEFT OUTER JOIN BedroomList bl ON p.idList = bl.idList LEFT OUTER JOIN BedroomType bt ON bl.idBedroomType = bt.idBedroomType LEFT OUTER JOIN PropertyPicture pp ON p.idList = pp.idList AND pp.Thumbnail = 1 LEFT OUTER JOIN LetCategoryList lcl ON p.idList = lcl.idList LEFT OUTER JOIN LetCategory lc ON lcl.idLetCategory = lc.idLetCategory LEFT OUTER JOIN Agent a ON a.idUser = p.idCreatedBy LEFT OUTER JOIN AgencyLogo al ON al.idOffice = a.idOffice JOIN Users u ON u.idUser = p.idCreatedBy LEFT OUTER JOIN ListingType lt ON lt.idList = p.idList JOIN PropContactList pc ON pc.idList = p.idList JOIN Users us ON us.idUser = pc.idMainContact WHERE p.idCRule = 1 GROUP BY p.idList, bl.TotalRooms, p.ListRef, al.PicPath, bl.FiveDayPrice, bl.SevenDayPrice, bt.TypeName, p.DisplayAddress, p.PriceVal, p.Description, p.ModifyDate, u.idUserType, fr.FrequencyName, d.DistrictPostcode, pt.PTypeName, lcl.OccasionalWeekends, lcl.WeekendPrice, lc.idLetCategory, lc.CategoryName, p.idPublicDetails, u.UserWorkTel, c.CountryName, d.DistrictName, p.idPropStatus, cy.CountyCityName, pp.PicPath, al.idOffice, lt.idAdType ORDER BY lt.idAdType, p.ModifyDate desc, p.idList desc .... where do I begin looking to add indexes for this query ?? (note that I have primary keys on each table on the columns begining with 'id' so therefore I already have one clustered index on each table) Any advice much appreciated! Peter -- fiddlewidawiddum Is your p.IDCRule greatly disparate? In other words, are there more
than a couple distinct values in it (1-10 as opposed to 1-3)? If so, it might be a candidate for an index. I'd also look at indexing the foreign key columns on each table (e.g., p.idCreatedBy) Do you know how to work with execution plans? They'll reveal a lot about what indexes are being used, and how the addition of an index will alter the performance. Be aware that adding an index can impact performance on INSERTs and UPDATEs, so be careful and don't over-do it. HTH, Stu Stimp
http://www.sql-server-performance.com/clustered_indexes.asp http://www.sql-server-performance.com/nonclustered_indexes.asp Show quoteHide quote "Stimp" <r**@spumco.com> wrote in message news:slrne3206s.4or.ren@carbon.redbrick.dcu.ie... > Hi all, > > I haven't add indexes to a database for a long long time so I've > completely forgotten how to go about designing an index. > > Say I have the following query (which is run often): > > SELECT > bl.TotalRooms, bt.TypeName, u.UserWorkTel, > p.DisplayAddress, p.idList, cy.CountyCityName, d.DistrictPostcode, > p.idPublicDetails, > p.PriceVal, p.Description, fr.FrequencyName, p.ModifyDate, p.ListRef, > u.idUserType, lt.idAdType, > pt.PTypeName, lcl.OccasionalWeekends, lcl.WeekendPrice, > lc.idLetCategory, lc.CategoryName, > c.CountryName, d.DistrictName, p.idPropStatus, pp.PicPath, (al.PicPath) > AS 'Logo', al.idOffice, > FROM Property p > JOIN PropertyType pt > ON p.idPType = pt.idPType > JOIN Country c > ON p.idCountry = c.idCountry > JOIN District d > ON p.idDistrict = d.idDistrict > JOIN CountyCity cy > ON p.idCountyCity = cy.idCountyCity > JOIN Frequency fr > ON p.idFrequency = fr.idFrequency > LEFT OUTER JOIN BedroomList bl > ON p.idList = bl.idList > LEFT OUTER JOIN BedroomType bt > ON bl.idBedroomType = bt.idBedroomType > LEFT OUTER JOIN PropertyPicture pp > ON p.idList = pp.idList AND pp.Thumbnail = 1 > LEFT OUTER JOIN LetCategoryList lcl > ON p.idList = lcl.idList > LEFT OUTER JOIN LetCategory lc > ON lcl.idLetCategory = lc.idLetCategory > LEFT OUTER JOIN Agent a > ON a.idUser = p.idCreatedBy > LEFT OUTER JOIN AgencyLogo al > ON al.idOffice = a.idOffice > JOIN Users u ON > u.idUser = p.idCreatedBy > LEFT OUTER JOIN ListingType lt > ON lt.idList = p.idList > JOIN PropContactList pc > ON pc.idList = p.idList > JOIN Users us > ON us.idUser = pc.idMainContact > WHERE p.idCRule = 1 > GROUP BY p.idList, bl.TotalRooms, p.ListRef, > al.PicPath, bl.FiveDayPrice, bl.SevenDayPrice, bt.TypeName, > p.DisplayAddress, p.PriceVal, p.Description, p.ModifyDate, u.idUserType, > fr.FrequencyName, d.DistrictPostcode, > pt.PTypeName, lcl.OccasionalWeekends, lcl.WeekendPrice, > lc.idLetCategory, lc.CategoryName, p.idPublicDetails, u.UserWorkTel, > c.CountryName, d.DistrictName, p.idPropStatus, cy.CountyCityName, > pp.PicPath, al.idOffice, lt.idAdType > ORDER BY lt.idAdType, p.ModifyDate desc, p.idList desc > > > ... where do I begin looking to add indexes for this query ?? > > (note that I > have primary keys on each table on the columns begining with 'id' so > therefore I already have one clustered index on each table) > > Any advice much appreciated! > Peter > -- > > fiddlewidawiddum The rule of thumb which I've found useful with indexes is to create the
ones that allow the optimiser to quickly bound the size of the result set. So for example if you were passing a parameter such as a daterange into your where clause, this would be a good candidate for the index as it would probably cut that table down to a few rows, such that all other operations on that result set from then on would be trivial. in your case however you don't seem to be passing anything to really cut down on the size of the results, so as Stu said, it's the execution plans which will really show you where the best improvements can be made (CTRL-K in query analyser (SQL 2000) and CTRL-M in SQL 2005) Cheers Will Run the profiler with output to a file.
and run this query along with any other query that you run on these tables. Take the result file and pass it to the index tuning wizard. You should get a fair idea on what indexes to use. On Mon, 3 Apr 2006 Omnibuzz <Omnib***@discussions.microsoft.com> wrote:
> Run the profiler with output to a file. this seems to do the trick alright.. mucho gracias y'all!> and run this query along with any other query that you run on these tables. > Take the result file and pass it to the index tuning wizard. > You should get a fair idea on what indexes to use. -- fiddlewidawiddum Stimp
I'd not rely on ITW ,because on more complex (involved many tables) queries it does not provide a good advice. Instead , examine an execution plan of the query , see if the optimier was available to use idexes. Show quoteHide quote "Stimp" <r**@spumco.com> wrote in message news:slrne32cpm.c9o.ren@carbon.redbrick.dcu.ie... > On Mon, 3 Apr 2006 Omnibuzz <Omnib***@discussions.microsoft.com> wrote: >> Run the profiler with output to a file. >> and run this query along with any other query that you run on these >> tables. >> Take the result file and pass it to the index tuning wizard. >> You should get a fair idea on what indexes to use. > > this seems to do the trick alright.. mucho gracias y'all! > -- > > fiddlewidawiddum On Mon, 3 Apr 2006 Uri Dimant <u***@iscar.co.il> wrote:
> Stimp Hi Uri,> I'd not rely on ITW ,because on more complex (involved many tables) > queries it does not provide a good advice. Instead , examine an execution > plan of the query , see if the optimier was available to use idexes. I looked at the execution plan and couldn't make head-or-tail of it, and since I was on a tight schedule I didn't have time to research how to translate execution plans. Is there a tutorial on how to read execution plans, and so use them to derive indexes? Cheers, Peter Show quoteHide quote > > > > "Stimp" <r**@spumco.com> wrote in message > news:slrne32cpm.c9o.ren@carbon.redbrick.dcu.ie... >> On Mon, 3 Apr 2006 Omnibuzz <Omnib***@discussions.microsoft.com> wrote: >>> Run the profiler with output to a file. >>> and run this query along with any other query that you run on these >>> tables. >>> Take the result file and pass it to the index tuning wizard. >>> You should get a fair idea on what indexes to use. >> >> this seems to do the trick alright.. mucho gracias y'all! >> -- >> >> fiddlewidawiddum > > -- fiddlewidawiddum Hi
Vist at http://www.sql-server-performance.com/tips_performance.asp and you'll find lots of good info Show quoteHide quote "Stimp" <r**@spumco.com> wrote in message news:slrne34eqg.n0v.ren@carbon.redbrick.dcu.ie... > On Mon, 3 Apr 2006 Uri Dimant <u***@iscar.co.il> wrote: >> Stimp >> I'd not rely on ITW ,because on more complex (involved many tables) >> queries it does not provide a good advice. Instead , examine an execution >> plan of the query , see if the optimier was available to use idexes. > > Hi Uri, > > I looked at the execution plan and couldn't make head-or-tail of it, and > since I was on a tight schedule I didn't have time to research how to > translate execution plans. > > Is there a tutorial on how to read execution plans, and so use them to > derive indexes? > > Cheers, > Peter > > >> >> >> >> "Stimp" <r**@spumco.com> wrote in message >> news:slrne32cpm.c9o.ren@carbon.redbrick.dcu.ie... >>> On Mon, 3 Apr 2006 Omnibuzz <Omnib***@discussions.microsoft.com> wrote: >>>> Run the profiler with output to a file. >>>> and run this query along with any other query that you run on these >>>> tables. >>>> Take the result file and pass it to the index tuning wizard. >>>> You should get a fair idea on what indexes to use. >>> >>> this seems to do the trick alright.. mucho gracias y'all! >>> -- >>> >>> fiddlewidawiddum >> >> > > > -- > > fiddlewidawiddum  
Other interesting topics
|
|||||||||||||||||||||||
