Voice of the DBA

Tag: software development

  • Large Chunks of Data

    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.

    Steve Jones

  • Developer Deployment Frustrations

    Why don’t developers like SQL Server? Probably a few reasons, but I’m sure this is one that really frustrates them. I found a Connect Item that was titled:  Why is Deploying SQL Server 2008 R2 sooooo FRUSTRATING?!! There really is a question there, asking for guidance on  which versions of SQL Server are available and recommended for developers to include in their applications.

    When SQL Server MSDE was released, it seemed that Microsoft was looking for it to be included in small applications that might then be upsized to a Standard or Enterprise edition of SQL Server. It seems to me that this is really the market for Exsoftpress (the evolution of MSDE) and that it ought to be simple for a developer to not only deploy this with their application, but also setup basic maintenance easily.

    I sometimes think that the software developers at Microsoft get lost in their own specialty and forget just how frustrating it can be for the rest of us trying to use their product in new ways. They forget that many of us want to deploy simple solutions easily, and not spend a lot of time working out the nuances of software setup.

    I’d like to see Express not only have a very simple setup that works across multiple versions of Visual Studio, but also baic maintenance plans built in that allow full and log backups (if needed), along with index rebuilds with a simple switch set as a part of setup. A few registry keys or XML config changes could set paths or frequencies.

    Making life simpler for developers is a worthwhile investment for the SQL Server team. It makes them more likely to include it in their applications. If you can add a one-switch replication to sync to a Standard or Enterprise SQL Server, they might think Express is required in every application.

    Steve Jones

  • Where Are the Programmers?

    When I was growing up, I thought I wanted to be a computer programmer. I had my own Vic-20, and various other machines, and I wrote code that was used to solve problems. I programmed the computer to handle various calculations or manipulations for school or fun and thought that was very cool. I solved many chemistry labs with the help of my computer.

    The other day someone asked me what I did for a living and I said that I used to work with computers, but now wrote about them. They asked me if I was a computer programmer in the past. That was something I hadn’t heard in a long time.

    I’m not sure when we changed from programmers to developers. Fundamentally we do the same thing, though I’ll admit that developer sounds more important, or more talented. I’m sure at some point a programmer wanted to separate their job from everyone else, called themselves a developer, and the trend caught on.

    I haven’t worried about titles for a long time, but at one point in my career I did press to get the senior DBA title after working with SQL Server for about 6 or 7 years. At that time I felt I knew more about SQL Server than the majority of people that I met, and I deserved to be “senior.” I’m still not sure if I had earned it at that time, but it was a title I was proud of.

    It seems that us data professionals have tried to separate ourselves from the average DBA with database architects and database developers and any number of other database specialties that sound more important than just DBA or developer.

    It makes me wonder what’s coming next. Will we start to advertise ourselves as DSS (data security specialist) or DTS (data transformation specialist) or even BIDS (Business Intelligence developer specialist)? Who knows, those acronyms are already well known in the SQL Server world. They might just impress a manager who knows just a little about SQL Server.

    Steve Jones

  • When To Touch

    It seems that every product that has an underlying database has documentation that says not to modify the schema?  Do you always pay attention to that warning? The PSS blog at Microsoft says that for Reporting Services you ought to definitely keep your hands off, and gives some good reasons why you should.

    However what about those other products? Dynamics, SAP, JD Edwards, and other common types of software that many companies use. Can you modify them at all? Perhaps.

    We hear about the build vs. buy debate in software, but there’s also the modify vs. workaround argument. I was reminded of that from the PSS blog. There are times that you might be tempted to change things in a database built a software company, or add objects to that database. In many cases this can be seen as action that voids any support contract with the vendor, and more often than not they won’t want to give permission to make changes.

    I can understand that, as it can substantially increase the support costs for a software company. However I also think that a blanket policy prohibiting changes is a bad idea.

    I think there are plenty of things that you can do, especially with the latest versions of SQL Server, that could be useful “touches” that improve the use of a database. Adding indexes judiciously to improve performance of some queries is one very light touch that can add tremendous benefits. Adding reporting objects in another schema could help you extend functionality to get more value from the software.

    Touching a database that you didn’t build should be done carefully, lightly, and with a lot of testing, and it can be something that creates a much more valuable system.

    Steve Jones