Hi,
I plan to create HelpDesk Database. One of the table will store - Response Time & Resolution Time. It in 24 hours. The value as following,
How to plan accurate datatype?
October 31, 2021 at 12:23 pm
How to plan data types? It's simple: use the minimum necessary size given your anticipated precision requirements. Aside: there are edge cases having to do with all sorts of exceptions but with SQL that's always the case. The SQL Server Docs are pretty good when it comes to factual details. For date and time there are only 6 data types. The greater the accuracy you need the more storage the type requires. Imo Help Desk response and resolution times could be stored as SMALLDATETIME which is accurate to the minute
Date and Time Data Types and Functions (Transact-SQL)
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Strong suggestion, don't store the response time. Store the start and stop times and then calculate the response times when you retrieve the data.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 1, 2021 at 2:26 pm
This will shock most but I've had to climb this hill many times in the past. These are "durations". Don't store them as date and time datatypes. Before I can tell you more on how to make this and all the future aggregations and analysis of these durations super fast with a super small footprint, I need to know what the column names and datatypes for the original start and stop columns that you use to calculate the response and resolution times. And, yeah... almost all of your duration calculations for reporting can be made without any temporal math until display time, if you do it right and for very little space.
The best thing to do would be to post the CREATE TABLE statement where ALL of this is originally stored.
--Jeff Moden
November 1, 2021 at 2:31 pm
Strong suggestion, don't store the response time. Store the start and stop times and then calculate the response times when you retrieve the data.
I have to ask, what is the advantage you're thinking about when you make this recommendation compared to storing the result of the calculation that will never change?
--Jeff Moden
November 1, 2021 at 2:43 pm
Grant Fritchey wrote:Strong suggestion, don't store the response time. Store the start and stop times and then calculate the response times when you retrieve the data.
I have to ask, what is the advantage you're thinking about when you make this recommendation compared to storing the result of the calculation that will never change?
I've seen people convert the results to text or a value, then need to format it separately, but they didn't store the start & stop time, just the start time and the end result as text. If you have the start & stop, you'll always be able to get the duration, no matter what happens in terms of format, etc.
Heck, store both, but I'd absolutely have the start & stop.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 1, 2021 at 2:46 pm
One would hope response time wouldn't change... though I've seen companies where even that might not be true.
I could see resolution time changing (e.g., help desk declares it resolved, user argues/proves it wasn't). One could reasonably argue that's a bad design and only the submitter (or a manager/proxy) should be able to define a ticket as resolved (but I believe such systems exist). Or that a new ticket should be created when resolution is challenged -- though I would argue that approach hides/obfuscates the facts, giving an impression of faster average resolution... not that anybody would every do that deliberately 🙂
Given minutes precision shown, an integer ResolutionMinutes column would seem to suffice for storing the datediff.
Let the front end format the output.
November 1, 2021 at 3:14 pm
Jeff Moden wrote:Grant Fritchey wrote:Strong suggestion, don't store the response time. Store the start and stop times and then calculate the response times when you retrieve the data.
I have to ask, what is the advantage you're thinking about when you make this recommendation compared to storing the result of the calculation that will never change?
I've seen people convert the results to text or a value, then need to format it separately, but they didn't store the start & stop time, just the start time and the end result as text. If you have the start & stop, you'll always be able to get the duration, no matter what happens in terms of format, etc.
Heck, store both, but I'd absolutely have the start & stop.
Ah... got it and I totally agree. Start, stop, and duration. Best of all worlds for just about anything anyone could ask. And I totally agree... if you can only store 2 of the 3, store the start/stop dates/times. Everything is possible from that.
--Jeff Moden
November 1, 2021 at 3:17 pm
I'd say it depends on how often you need to show the resolution duration. That's a rather complex calc to do repeatedly. And presumably the Resolution Time would only be set once and not be constantly changing.
Therefore, I'd likely calc and store the durations permanently, using a trigger. Use a smallint to store the days, and tinyint for the hours and seconds, like so: Resolution_Days smallint NULL, Resolution_Hours tinyint NULL, and Resolution_Minutes tinyint NULL.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 1, 2021 at 3:26 pm
One would hope response time wouldn't change... though I've seen companies where even that might not be true.
I could see resolution time changing (e.g., help desk declares it resolved, user argues/proves it wasn't). One could reasonably argue that's a bad design and only the submitter (or a manager/proxy) should be able to define a ticket as resolved (but I believe such systems exist). Or that a new ticket should be created when resolution is challenged -- though I would argue that approach hides/obfuscates the facts, giving an impression of faster average resolution... not that anybody would every do that deliberately 🙂
Given minutes precision shown, an integer ResolutionMinutes column would seem to suffice for storing the datediff.
Let the front end format the output.
Storing minutes sounds like a great idea but you can end up dealing with some really large numbers for aggregates that way. It also destroys the ability to use a finer resolution if a new requirement comes up.
Also, there are some times where no front end involved or reporting app is involved. For example, sometimes there's a requirement to send a file using a particular format.
--Jeff Moden
November 1, 2021 at 3:31 pm
I'd say it depends on how often you need to show the resolution duration. That's a rather complex calc to do repeatedly..
I have to disagree. If you use the correct datatype, it's not a complex calculation at all. It's simple subtraction and, no... I'm not talking about converting to minutes or seconds, which would make it all more complex everywhere else. That's why I asked the op about datatypes.
--Jeff Moden
November 1, 2021 at 4:58 pm
ScottPletcher wrote:I'd say it depends on how often you need to show the resolution duration. That's a rather complex calc to do repeatedly..
I have to disagree. If you use the correct datatype, it's not a complex calculation at all. It's simple subtraction and, no... I'm not talking about converting to minutes or seconds, which would make it all more complex everywhere else. That's why I asked the op about datatypes.
Computing days, hours and minutes separately will require separate calcs (at least off the total minutes). I would not do these calcs repeatedly if the duration needed to be displayed frequently. The space required to store them is so small I'd just store them instead.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 1, 2021 at 11:49 pm
Jeff Moden wrote:ScottPletcher wrote:I'd say it depends on how often you need to show the resolution duration. That's a rather complex calc to do repeatedly..
I have to disagree. If you use the correct datatype, it's not a complex calculation at all. It's simple subtraction and, no... I'm not talking about converting to minutes or seconds, which would make it all more complex everywhere else. That's why I asked the op about datatypes.
Computing days, hours and minutes separately will require separate calcs (at least off the total minutes). I would not do these calcs repeatedly if the duration needed to be displayed frequently. The space required to store them is so small I'd just store them instead.
Oh... you'll get no argument from me that the duration should be saved in ONE separate column. I was taken exception to someone saying that it's a "rather complex calc". It's not, especially if you used the DATETIME datatype and wait until the end of any aggregation to display the result in whatever format you need.
I also would NOT store the duration in minutes or seconds, either. Even that can require way too much complexity and minutes might be way too coarse a measurement. Like Granny used to say, "Mind the pennies and the dollars with take care of themselves.
--Jeff Moden
November 2, 2021 at 9:42 am
Thanks to all
November 2, 2021 at 2:14 pm
Thanks to all
Adelia,
Don't go yet. I asked you some questions about datatypes that you've not answered yet. If you let me know that the datatypes of the start and end date/time columns are, I can probably give you a very small bit of code to do everything you currently need with some bulletproofing for the future.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply