help on Indexes

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.OccasionalWends, lcl.WendPrice,
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.OccasionalWends, lcl.WendPrice,
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
--
fiddlewidawiddumIs 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.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
"Stimp" <ren@.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.OccasionalWends, lcl.WendPrice,
> 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.OccasionalWends, lcl.WendPrice,
> 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 <Omnibuzz@.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|||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.
"Stimp" <ren@.spumco.com> wrote in message
news:slrne32cpm.c9o.ren@.carbon.redbrick.dcu.ie...
> On Mon, 3 Apr 2006 Omnibuzz <Omnibuzz@.discussions.microsoft.com> wrote:
> this seems to do the trick alright.. mucho gracias y'all!
> --
> fiddlewidawiddum|||On Mon, 3 Apr 2006 Uri Dimant <urid@.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" <ren@.spumco.com> wrote in message
> news:slrne32cpm.c9o.ren@.carbon.redbrick.dcu.ie...
>
fiddlewidawiddum|||Hi
Vist at http://www.sql-server-performance.c...performance.asp and
you'll find lots of good info
"Stimp" <ren@.spumco.com> wrote in message
news:slrne34eqg.n0v.ren@.carbon.redbrick.dcu.ie...
> On Mon, 3 Apr 2006 Uri Dimant <urid@.iscar.co.il> wrote:
> 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
>
>
> --
> fiddlewidawiddum