Voice of the DBA

Author: way0utwest

  • Large Chunks of Data

    This editorial was originally published on Sept 17, 2010. It is being re-run as Steve is on vacation.

    I saw a post recently where someone talked about trying to get better performance from a report. They were selecting 5mm rows from a table and wanted to see if there was a more efficient way to chunk out this data so that the instance would not report memory errors.

    My first question is what kind of report has 5mm rows of data? That’s just too much data.

    How long can a report be?

    From your experience, talking with people, looking at what they analyze, how big is a report? How much data can you really display on a report and make it useful for users?

    I’m thinking here in terms of the raw data you show. A pivot table can summarize millions of records, but realistically I thin kit becomes hard to examine more than a few hundred data points on a page. Whether they are raw data or aggregates of other data, it seems there’s some limit to what a report should provide.

    After all, that’s why we have drill-down 🙂

    Let us know this Friday what you think; what you have observed? Maybe we’ll  help others to build better reports that are more practical and useful to end users, as well as easier to develop.

     

  • New Blogger Challenge 1 – Adding a Primary Key

    The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

    Primary Keys

    I firmly believe that every table should have a primary key. At least until you have a reason not to have one. If you have a reason, fine, but if you can’t explain it or convince me, then just add a primary key.

    I have tended to build tables like this:

    CREATE TABLE Users
(
  MyID int IDENTITY(1, 1)
, firstname varchar(250)
, lastname varchar(250)
, gender char(1)
, postalcode varchar(12)
, contactphone varchar(12)
);
GO
ALTER TABLE Users ADD PRIMARY KEY (MyID);

    Lately I’ve not liked that as my primary key now has a name like [PK__Users__7131A74146D2BBC1]. I’d rather have a more organized database with a touch more effort.

    The better way to add the key later is like this:

    ALTER TABLE dbo.Users 
  ADD CONSTRAINT pkUsers PRIMARY KEY (MyID);

    This way I can name the key, and I specifically note this is a constraint, and with the PRIMARY KEY option, it’s a unique constraint.

    References

    A few places I searched around to double check myself.

    Quick and Easy Blogging

    This post occurred to me while I was writing some code. I mocked up a table in about 2 minutes, and then ran a quick search on the Internet. Reading a few links was about 10 minutes and then testing the code (including dropping the table and recreating it a few times) was less than 5 minutes. All told, I solidified some knowledge and completed this in about 20 minutes. I also have drafts and ideas from this post for 2 other posts that cover this same topic in a similar way.

    Look for the other posts in the April challenge.

  • Closing Out the Winter Holiday Season

    I’ve tried to write regularly about taking vacation and getting away from work to recharge. I haven’t always done a good job managing that in my life, but I’m hoping to inspire more of you to do better.

    I’ve scheduled holiday for Friday and Monday over Easter weekend to take one last ski weekend for the 2014/2015 winter season. I’ve got family coming into town and we’ll have the chance to enjoy a little time as ski season comes to a close.

    It’s been a decent, but not great ski season for me. I didn’t get as many days scheduled, or taken, as I’d have liked with a busy work and family life, but I can’t complain. Life has been good and I’m on a better pace to use up vacation this year than last.

  • Microservices for Databases

    I ran into a talented developer last year that was talking about microservices. It’s an interesting concept, one the Netflix has adopted with success. I was excited about the possibilities of using microservices until this guy said that everything could be a microservice and the day of the RDBMS was over.

    That was silly, and I’ll admit I struggled to remain polite in the discussion. Eventually I had to walk away because the idea of no RDBMS for any application is a ludicrous as the concept of using an RDBMS for every single system dealing with data. It’s frustrating to talk with someone that views our industry as too black and white. There are many ways to solve any problem and many problems can be handled by a variety of techniques.

    However I am intrigued by microservices. It’s an area that I want to continue to research, as I suspect that the idea of small, loosely coupled applications, working in a service-oriented architecture, is a great way to scale systems.

    From scratch.

    I’m not sure that many of the monolithic, large applications we have in banking, in retail, in supply chain, in a variety of industries are worth rewriting to use SOA concepts. The return just isn’t there, as many of these systems can be served with bigger, faster hardware as they upgrade.

    Microservices are interesting, and I suspect we’ll see more of them in the future. I also think that SOA, using messaging services like Service Broker, are a very robust way to build applications. I hope that more developers out there learn about SOA and find ways to start building system that can work well together, but aren’t so highly dependent that changes are difficult or stressful on everyone.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and LibSyn.