Tim
Life is good
Joined: Nov 2007
Location: Kalamazoo
Benchmarking the proposed changes..
..better, worse, and the same all at once.
In my last post Changing the Tag Cloud I discussed a design decision between my current tag cloud and what data is populated and a new design based on tag usage. One item I didn't really discuss was how this would impact the site if no tags were used for an extended period of time. While this is a concern, I'm not to worried about it because I've made it configurable.
So to get started I did a little research to find out an easy way to measure the execution of the query. I'll be using the SET STATISTICS TIME option in SQL server to figure it out, or at least give an idea. I've also made a few changes to the proposed query to match my database. The last query I showed was a hybrid from my database, and another article I'm writing about this topic. So here's the new query with the SET STATISTICS TIME turned on for testing:
SET STATISTICS TIME ON
GO
SELECT is_Tags.Tag_Id, Tag, is_Tags.TagDate,COUNT(1) [Weight] INTO #tmpTags
FROM is_Tags
LEFT OUTER JOIN is_TagsUsed ON is_TagsUsed.Tag_ID = is_Tags.Tag_Id
WHERE DATEDIFF(DAY, is_TagsUsed.TagDate, GETDATE()) <= (SELECT CONVERT(INT,SettingValue)
FROM is_SiteSettings
WHERE SettingName = 'TagCloud_DateLimit')
AND (is_Tags.TagType = 0 OR is_Tags.TagType IS NULL)
GROUP BY is_Tags.Tag_Id, Tag, is_Tags.TagDate
ORDER BY Tag
SELECT Tag_Id, Tag, TagDate, [Weight]
FROM #tmpTags
WHERE Weight > (SELECT CONVERT(INT,SettingValue)
FROM is_SiteSettings
WHERE SettingName = 'TagCloud_CountLimit')
DROP TABLE #tmpTags
SET STATISTICS TIME OFF
GO
This query also has some code to make it configurable, but it would be easy enough to swap out the SELECT statements in the WHERE clauses with static values.
When the query is executed I get the following output:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
(153 row(s) affected)
(56 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
This shows execution time of around 7ms, well that is if I'm reading it right. I'm also showing the Client Statistics in the Management Studio after running 10 times I'm now showing an execution time of 38.400ms. This is in all honesty a terrible time compared to the other query, here's those results
SQL Server parse
and compile
time:
CPU
time = 0 ms, elapsed
time = 0 ms.
(60 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
This has an average of 28.100ms over 10 executions. While there may not be a noticeable difference between the two, every millisecond of process time adds up and turns into seconds.
But now there is one more thing to consider, the time it takes to update the tags and increment that counter for the current solution. So add another 5 to 10ms for this, for every tag you enter. On average I'll add two to four tags per post, that's on the good side 20 additional milliseconds. Of course upwards of 40ms on the down side. So if I'm adding 100 new files, this time really adds up, and quick.
One other thing worth noting, this execution at most only happens every 10 minutes. This is due to me caching the tag could in process so I can access the cache rather than hit the database on every single page load. That goes something like this:
if (Cache["TagCloud"] == null)
{
isClasses.TagsCollection oTags = new isClasses.TagsCollection();
oTags.GetTags();
Cache.Insert("TagCloud", oTags, null, DateTime.Now.AddMinutes(10), TimeSpan.Zero);
}
rptTags.DataSource = Cache["TagCloud"];
rptTags.DataBind();
So doing this actually reduces the time further and really makes the 10.3ms seem even more nominal that it was before.
Once it's completed I'll be posting a break down of how to make your own Tag Cloud. Here I'll go over everythign from the 3 Tier design, right down to the CSS.