September 12, 2014 at 4:46 am
Hi,
I have a requirement to record all the request urls from our website in the database. I have the option to either insert the url every time a request is made, ignoring the duplicates or insert the url only once and create a column to store the number of time a url is requested.
1. I have opted for inserting the record only once and incrementing the counter column whenever a request is made for the url. Is this the right approach?
2. If yes, is there any way I can create it as a table constraint?
Background on the Requirement:
We have hosted our website in our servers and also in Cdn for caching the requests. We want to record the urls requested from Cdn. Later the recorded urls are used to clear the Cdn cache. Having the access counter column is an addition to this, we just wanted to get some statistics along with it.
Thanks,
Sarath
September 12, 2014 at 10:05 am
sarathcd (9/12/2014)
Hi,I have a requirement to record all the request urls from our website in the database. I have the option to either insert the url every time a request is made, ignoring the duplicates or insert the url only once and create a column to store the number of time a url is requested.
1. I have opted for inserting the record only once and incrementing the counter column whenever a request is made for the url. Is this the right approach?
2. If yes, is there any way I can create it as a table constraint?
Background on the Requirement:
We have hosted our website in our servers and also in Cdn for caching the requests. We want to record the urls requested from Cdn. Later the recorded urls are used to clear the Cdn cache. Having the access counter column is an addition to this, we just wanted to get some statistics along with it.
Thanks,
Sarath
That would work, yes. My preference would also to disregard duplicate data and have only a single url. You mention you'd like some additional statistics. You'd probably be better off discovering at this point what kind of reports or functions you'd like to use on the back of the statistics you are capturing.
If all you require is the number of hits, your approach would work fine. Will there be any additional tables which you may want to join to this url/counter table or will it be a standalone counter? This could influence your decision for a table constraint.
September 12, 2014 at 10:18 am
Counting it will work until the day management wants to know how many time in the month of June did URL xxx get requested. Or when was the last time URL xyz was requested. Not saying you should record every request, it depends on what the actual requirements are. If you decide to capture each request make sure you have plenty of disc space. With a busy site that will get massive very quickly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2014 at 11:10 am
Sean Lange (9/12/2014)
Counting it will work until the day management wants to know how many time in the month of June did URL xxx get requested. Or when was the last time URL xyz was requested. Not saying you should record every request, it depends on what the actual requirements are. If you decide to capture each request make sure you have plenty of disc space. With a busy site that will get massive very quickly.
Sean hit the nail on the head. There are a lot of metrics like this management will ask for. They'll say they won't, but they will eventually.
How did this June compare with last June?
What are the busiest days of the month?
What are the busiest days of the week?
What times of day do most of our hits fall into?
Can you rank the hours of the day with percentages of total hits for the day across a 2-week time period in July following a production release?
When would you recommend having a maintenance window based on traffic?
On my biggest applications, I store every hit. I don't store the whole URL, but every page that's hit and when.
I don't do this, but you could normalize this into a Pages table and a PageHits table. The Pages table stores an integer primary key and the URL (or just page name). The PageHits table has a foreign key to Pages and stores the datetime of the hit. That would reduce duplicate data storage and still afford you the level of detail you're going to need someday.
September 15, 2014 at 2:50 am
Thanks for your response guys.
Our requirement is simple we need to clear the Cdn cache for the Url's logged in the table. We do not have to worry too much about statistics, there is a different team which does takes care of it. And they do it directly from server.
In general our application will not go through the Cdn, only under certain conditions we are passing it through Cdn. The team which works on the statistics does not know much about it. And we just want to keep a count temporarily.
One thing we wanted to find out was whether there is any constraint that can do this automatically. At the moment we are incrementing manually on insert and update.
Thanks again for your replies
Sarath
September 16, 2014 at 12:51 pm
You could start with a url summary table and add a details table later if you need to.
I'd still strongly urge you to use an int to represent the url, then use the int in the summary table. That reduces the size and complexity of the other tables that use url.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply