SQL Server decimal precision issue when multiplying

Scenario: Your client needs 29 significant digits and 10 places of decimal point precision in the data that's stored in your system. So you set up your fields to be decimal(29,10) and go about your merry way. But then you run into this:

Hey, what happened to my ten decimal places? And why is it suddenly able to give me the right answer if I use actual values instead of variables?

The answer, of course, is described here and here. What these posts don't tell you is what to do with your original problem and how to get 10 decimal places in your result. What you have to do is increase the scale of your inputs without increasing the precision, and to determine how much to increase the scale the SQL_VARIANT_PROPERTY function you saw in that second post is going to be your best friend.

SQL_VARIANT_PROPERTY is a function that allows you to inspect the properties of a scalar value, including its datatype and precision. Here I've created a small script with two variables @A and @B, both decimal(29,10). I can call SQL_VARIANT_PROPERTY and pass in the product of @A and @B, and SQL Server will tell me what datatype I can expect in return.

Now all I have to do is ratchet up the scale until I get enough decimal points in my product, and then explicitly cast the two operands to the higher decimal precision when you do your calculation. The magic number, it turns out for me, is decimal(29,16).

Now, you're probably going to say, "Why don't you just store all your values in the database as decimal(29,16) instead of decimal(29,10)?". Well, that's not always going to work. Look at the second part of my UNION statement above. If you have to divide one decimal into another and your precision is 29, you are always going to end up with decimal(38,9) if both numbers have the same precision. What you have to do is set the numerator to a higher scale and leave the denominator alone. This will slowly increase the scale of our answer.

You may also encounter situations where you need to multiply three decimal(29,10) values together. In that situation, your precision and scale for all three numbers is going to be more like (29,21).

UPDATE: One thing to be cautious of: if you're converting a decimal(29,10) to a decimal(29,21), then you're going from 19 digits available left of the decimal point to 8 digits available left of the decimal point. Consequently, if you have values greater than 99,999,999, you will get an arithmetic overflow error when trying to upcast. The best thing to do would be to profile the size of your existing data values and determine if future data values will get much bigger, and then use that as a guide for how much you can upcast (e.g. if your values are in the trillions, then the difference between your precision and scale must be at least 13 digits, so your maximum cast will be to decimal(29,16)).

WCF Fire and Forget Method Not Returning

Here's something that might trip you up if you're using a WCF Fire and Forget method to exit early from a long-running method.


Scenario: You create an operation contract and set the IsOneWay attribute to true. Then you implement the method and let's say it takes thirty seconds to finish.










Then on your client when you call this method you create your proxy inside a using statement because you want to dispose of the proxy after the call (when the 'using' statement exits if all goes well).













And when you run your code, the code finishes immediately and and all is well.


Oh, wait. Your client is hanging. It's taking...thirty seconds to finish, which is about as long as it would take if it were a request/response method.


It turns out, if you step into the client code, your proxy call is returning immediately. What's taking the thirty seconds to complete is the disposal of the proxy. The client can't exit from the using statement until the proxy is disposed of, and the proxy will not dispose of itself until the fire and forget method completes.


So what are your options?

1. Reuse the proxy and/or keep the proxy alive indefinitely.
2. Make your method request/response and call it asynchronously instead

My Favorite Shortcuts

I remember at the beginning of my career when I was temping as an administrative assistant ("fossoribus orti") for a large telecom. Another, far superior administrative assistant was showing me something on her computer, and she had to switch between programs. I watched as a box popped up in the middle of her screen with icons for all the programs she was working with. Then the box disappeared and she was in another program.

"What did you just do?" I said.

"Alt+Tab. It saves a lot of time."

And so began my quiet, torrid affair with shortcut keys. To this day, one of my favorite simple pleasures is discovering a shortcut key for something I do countless times throughout the day.

Here is a list of my favorite shortcut keys. This list is not exhaustive or well-rounded, but it does highlight the tools with the most wear in my toolbelt. May they bring you lots of productivity during the new year.

All-purpose shortcut keys:

• Alt+Tab - Use for switching between programs. If you can work your index finger right, Shift+Alt+Tab will cycle through programs in the other direction.
• [Windows] + R - opens the Run prompt. An excellent shortcut for opening programs. (NOTE: If you open regedit and navigate to the folder HKLM\Software\Microsoft\Windows\CurrentVersion\App Paths\, all of the subfolders contain the names of the programs that can be summoned by program name from a command prompt. You can also add a subfolder for a program that doesn't currently have a shortcut. For example, I have multiple versions of Visual Studio on my machine so I created a vs2005.exe registry subfolder and a vs2008.exe registry subfolder with keys pointing to the actual programs.)
• [Windows] + E - Opens Windows Explorer. I use this shortcut at least twenty times a day.
• Shift+F10 - Mimics a right-mouse click. I just learned about this shortcut on twitter a couple days ago. Most of my keyboards have a key for this, but the netbook I was playing with a month ago didn't. This would have saved me lots of time had I known about it then.
• Alt+F4 - Closes an open program. Hitting Alt+F4 twice in rapid succession will open the shutdown screen for your computer, so if you're just closing down programs make sure you pause a half second between uses.
• Ctrl+F4 - For programs that allow multiple documents or tabs to be open, Ctrl+F4 will close the window or tab with the current focus.
• Ctrl+Tab - Cycles between tabs or documents in a program. Works for Chrome, IE, Visual Studio, and other programs.

Chrome/Firefox/IE shortcuts

• Ctrl+T - Creates a new tab. Doesn't always work in Chrome or in IE, depending on the objects that are running on the current page. Sometimes if a media file has the focus it won't open up a new tab.
• Alt+LeftArrow - Mimics the back button
• Alt+RightArrow - Mimics the forward button

Microsoft Outlook

• Ctrl+1 - Switches to the Mail view
• Ctrl+2 - Switches to the Calendar view
• Ctrl+Shift+I - Switches to Inbox view, regardless of where you are
• Ctrl+Shift+M - creates a new email message, even if you're not in Mail view
• Ctrl+Shift+Q - creates a new meeting, even if you're not in Calendar view
• Ctrl+D - If you have an email open, deletes the email
• Ctrl+. - If you have an email open, moves to the next email in the list
• Ctrl+, - If you have an email open, moves to the previous email in the list

Visual Studio (too many shortcut keys to mention. Here's what I use on a regular basis):

Solution Management

• Ctrl+Shift+B - Builds your solution
• Ctrl+Shift+N - Creates a new solution and project
• Ctrl+Shift+O - Opens an existing solution or project

Outlining (I make heavy use of the #region keyword in my .cs files as

a way to collapse and expand logically related sections of code like

private fields, public properties, interface implementations, etc.

These keys come in very handy when trying to read a big .cs file)

• Ctrl+M, Ctrl+O - When your cursor is in a .cs file, this combination will collapse all outlining, all the way up to the namespace level
• Ctrl+M, Ctrl+M - this combination will collapse or expand a single section of code. For example, if your cursor is on the class declaration line, this combination will collapse or expand the class. Same for methods, properties, and regions. Basically, it looks for the next highest collapsible section and collapses it.

Testing

• Ctrl+R,Ctrl+T: If your cursor is inside a test method, this shortcut will run your test. R -> T. Run -> Test. Makes perfect sense. I think I discovered this in Scott Guthrie's excellent ASP.NET MVC Tutorial.
• Ctrl+R,Ctrl+A: Runs all of the tests in a project.
• F9 - sets a breakpoint a the cursor location
• F10 and F11, the debugging twins - when debugging, F10 steps over a method call, F11 steps into the highlighted method call (if it can). Another handy one to know is Shift+F11. If you step into a method that has a loop and you want to test the first few iterations of the loop and move on, Shift+F11 will step OUT of the current method back to the calling method.
• F12 - moves to the definition of the item in which the cursor is currently placed

A good day

Today was a good day. I love walking out of work having solved more problems than I caused.

Today there were two staring me in the face. First, my WPF client was timing out calling a long-running WCF method over NetTcpBinding. This is actually the second or third time this problem has reared its ugly head. I was calling the method asynchronously (client-side asynchronously), and I was hoping that would let me circumvent the timeouts like it does with traditional web services. No dice. Then I tried increasing the ReceiveTimeout, which several posts recommended (and which appears to have worked for the NetNamedPipesBinding call I'm making inside the service), but it still failed. In the end, Juval Lowy was my personal Jesus once again (if I run into him at PDC this year, I'm going to buy him a beer). I pulled Programming WCF off of the shelf and found the section that talked about session timeouts in the NetTcpBinding. It turns out that you need to enable ReliableMessaging (on both sides of the pipe, or you'll get a Contract mismatch error) and set the InactivityTimeout to override the idle timeout for NetTcp services. And since the long-running method was part of a big service contract that we used frequently, I decided to move the method to another service contract so the reliable messaging wouldn't add too much overhead. This was a big win for us, because this method kicks off an ETL package, reprocesses our cube, and pushes out all the report subscriptions.

The next problem we had was an OLAP-sourced report that's had terrible performance ever since we started accruing data snapshots in the cube. The report has two different measures that share two common dimensions, and then I have a third dimension that is only related to one of the two measures. The report is structured as a two-tier report with the two shared dimensions
on the top level and the third dimension as a drilldown into Measure Group 2. But the SSRS-generated MDX was a hot mess. For a long time, I thought it was the fact that it was trying to pull back Dimension 3 and Measure Group 1 in the same report and it was doing some kind of weird cross-join that was killing my performance. But a more astute cube jockey took a look at it and pointed out that it was trying to return back way too many rows. All of the measures have to be on the same axis in your cube, and my Measure Group 1 was returning a value for every Dimension 1/Dimension 2 combination. Result: 350,000 rows, and a memory spike in SSMS of 1 GB (yikes). So I sat down and figured out what rows I really needed and got it knocked down to about 30,000 rows and 200 MB.

Enough challenges for one day. Tomorrow is another full day.

Reporting Services 2008 API

This afternoon I dug once again into the Reporting Services API for SSRS 2008 in SharePoint Integrated Mode. And boy, let me tell you, that is FUN. I've been working on integrating schedules and subscriptions into my application that are event-driven rather than schedule-driven.

Currently in Reporting Services 2008 you can set up a subscription and have it run at a particular interval (e.g. every morning at 8 am) and deliver a report to your inbox. If you then want to make sure all of these subscriptions fire off together and all change together (in case you need your subscriptions to run at 6 am instead of 8 am), you can create a shared schedule (let's call it "First Thing In The Morning") and associate all of your subscriptions with this shared schedule.

What you can't do easily is say "I want all of the subscriptions on this shared schedule to run right after my OLAP cube refreshes, no earlier and no later". At least not from the front end.

So we turn to the Web Service API built into Reporting Services.

The first nightmare is permissions. If you're calling a method like FireEvent, your calling identity needs to have the right permission on the Report Server. This was my three months ago headache. Apparently someone thought it was a good idea to remove the Permission notes from the SQL Server 2008 and 2005 Books Online. You can find it for SQL 2000, sure. But do you really want to hang your hat on that? No. Let's pull out Reflector.

And even after you find the right Reporting Services permission, you realize that this permission can't be configured because you're running in SharePoint Integrated Mode, which means that all reporting services permissions are now packaged up into corresponding SharePoint permissions (there's a really good reference for that here). This was my two months ago headache.

Today's headache came from trying to figure out how to locate a shared schedule and programmatically launch it. You can't. So here's how you work around it:

1) Call ListSchedules to get the set of all schedules on your site, each of which is associated with a GUID. Find the GUID for the schedule you want to fire off.
2) Call ListAllSubscriptions to get all the subscriptions on your site.
3) Loop through the subscriptions and for each one call GetSubscriptionProperties to get the MatchData property (which, incidentally, is the only missing property from your Subscription instance. How conveeenient). The MatchData property (string) will either be a GUID or an XML string. If it's a GUID and it matches the GUID from your schedule object, you know that the subscription belongs to that schedule object. Call FireEvent and pass in the subscription GUID to launch the subscription.

Wouldn't it be nice if there was a ListAllSubscriptionsForSchedule method in the API?

The Best Laid TimeKeys

This is what I get for trying to be clever. See my date key post for background.

I'm writing this post from the Central Standard Time Zone in the US. It's March 11, 2009 at 2:30 pm. Here's what I'm writing into my Management Studio instance. Watch closely:

And now:

Apparently when you cast a datetime to an int, it rounds the int up to the next day if it's past noon. Argh. So I've ripped out my clever TimeKey logic and replaced it with the YYYYMMDD format as an int (easily done with "CAST(CONVERT(char(8), @date, 112) as int)"). I guess I deserve this one.

Time Dimension Key Field

So I was building this OLAP cube for a new application and looking at how to populate the time dimension, which is on a date level. I was sure I wanted to have an integer key on the table to make joining to the fact table as painless as possible. But the problem is I also wanted all of my dates in key order. If I used an Identity for my key field, I'd never be guaranteed that my dates were in key order. I could insert 12/31/2007 today and it would take key 1, and then I could insert 12/30/2007 tomorrow and it would take key 2. How would I ever sleep at night?

What I realized I could do, though, is cast my datetime field to an int when I inserted.

SQL Server 2005 will give you the number of days between 1/1/1900 and the date you specify in your cast. Now I can ensure that each row represents a different date and still not be tied to joining to another table while running ETL jobs or digging in the data.

It's the little things.