Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2007-06-01 : 07:37:22
|
| Should I change my current design which has a Lookup Child Table and a Parent Table containing a ID link tot he Child Table, and instead just putting a varchar for the Value in the Parent Table?The issue is that the value is unique for a large percentage of cases (although some specific values reoccur very frequently).However, the parent table has a high number of inserts, and that in turn often requires inserts to the Child Table, which in turn pushes the Child Table out of shape, and performance suffers.The data is very seldom used (usually for debugging only)Should I de-Normalise this for performance?Details of the actual circumstances:I have a table that Logs each "page hit" on our web site. That stores the "Referrer".We store the Referrer for the SESSION separately (the first page viewed will have an external referrer, thereafter all pages will have an "internal" referrer).This Referrer data is used VERY LITTLE - just for the occasional debugging issue.Within some URLs are frequently used (www.mydomain.com/HOME.ASP) and others contain &Parameters and stuff and are either relatively unique(www.mydomain.com/ViewBasket.ASP&BasketID=1234) or variable (www.mydomain.com/ViewProduct.ASP&ProductID=1234)Currently I store the Referrer in a Child "Lookup" Table, and the PK ID in the "Logging" Table.Some analysis of the data shows:119,509 rows (pages logged [purged after a couple of days, only includes data where the Referrer is not blank!])Of those there are 10,760 distinct Referrer values. An average of 11 Page Hits per Referrer value.On the face of it that suggests that using a child table is good! However, for each row inserted into the Page Hit table I have to do the following to get the Referrer ID:SELECT @MyReferrerID = ID FROM MyLookupTable WHERE MyValue = @MyReferrerValueIF @@ROWCOUNT = 0INSERT ... @MyReferrerValue ...SELECT @MyReferrerID = @@IDENTITY(MyLookupTable has a PK on [ID] and a Unique Index on [MyValue] )(In a busy month we will add 100,000 rows to this Lookup table, in a slack month 20,000. These get "purged" after a while, so the table size stays about the same. The table is defragged [if required] daily and the Stats updated. There are 350,000 rows in MyLookupTable)Obviously some values in MyLookupTable reoccur, others are unique - and get purged after a while. Inserting these new entries pushes MyLookupTable out of shape, the SHOW_STATISTICS start to look awful, an sp_recompile sorts it out (Why? The query plan doesn't change ... although obviously the "shape" of the table has, maybe the Stats got auto-updated, would that change anything?)So I'm thinking about just storing @MyReferrer as a VARCHAR in the Logging table, and throwing away MyLookupTable.So really I'm just looking at performance, not Normalisation ...What do you think?Kristen |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-01 : 07:53:30
|
if i understand you correctly you have a 1-1 relationsip here?denormalizing sounds ok to me...why don't you TEST it?   _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-01 : 11:04:54
|
Thanks, but no its not 1:1For example you are on the Home page and then visit the About page.I have a unique ID (and a lookup table) for the About page, so that's OK.But I want to store the Referrer when you arrive on the About page. That's probably:"http://www.MyDomain.com/HOME.ASP"but it might be"http://www.MyDomain.com/HOME.ASP#LatestNews"or somesuch.I probably have lots and lots of logging entries where the referrer is:"http://www.MyDomain.com/HOME.ASP"and probably very few where it is"http://www.MyDomain.com/HOME.ASP#LatestNews"(and even fewer where the previous page's URL included something unique like an Order ID)I hope I'm explaining it a bit better?  Kristen |
|
|
|
|
|
|
|
|
Store as VARCHAR or Normalise in a Child Table?