Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Performance’ Category

The Myth of SQL Code Portability?

Posted by sqlwiseguy on February 25, 2009

While I have never had the “pleasure” of working on a project that required I write ANSI standard SQL so the code for portability, I have worked with products that have been written that way. Alexander Kuznetsov has an interesting blog post, Writing ANSI Standard SQL is not practical, where he addresses the issue from his experience. He notes that best practices dictate using stored procedures for data access and the big difference between SQL Server and Oracle implementation. He also links to another article he wrote on DevX, Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again., where he explains some situations where SQL Server and Oracle will return different results from the same data. Sure some of these issues are somewhat contrived, but they do have real world implications. Overall I would say that I agree with his conclusions.

I would also like to add that, in my experience with applications where the SQL was written for portability, I have seen issues with performance. This is usually because the SQL does not take advantage of some of the T-SQL extensions or uses a less than optimal (for SQL Server) query syntax. It also makes security harder because it does not use stored procedures so you have to enable direct access to base tables. One product I worked with that was written with portability would not implement Primary and Foreign Keys.

I understand trying to maximize your the profitability of your code, but with performance being such a big issue now, I don’t see how you can not write platform specific code. The other issue is that these vendors often do not have platform experts, whether SQL Server or Oracle, so when you approach them with an issue they cannot offer proper help.

One last link to a post by Brad McGahee, Challenging the Tyranny of Third-Party Vendors: A DBA’s Manifesto

Posted in Performance, Portability, SQL Server, Standards | Leave a Comment »

Measuring Developer Performance

Posted by sqlwiseguy on December 10, 2008

I just read this post on Joel Spolsky’s blog (Joel on Software) where he says, about using timesheets to measure performance:

We have a theory, here, that this is a bad idea. Using timesheets as a performance metric can lead to only one thing: bad data in timesheets.

You really need to read the whole post to understand where he is coming from, but I agree with his point. As soon as someone knows how you are measuring their performance, many people will find a way to “game” the system. What Joel doesn’t do, likely because this was not the real point of his post, is mention how he measures developer performance. So the question is:

How do you measure performance for developers?

I wish I had a good answer for this. I have always thought performance should be measured by what you accomplish, not how long it takes to accomplish it. If the task is to create three reports for the accounting department by the end of the week and one developer gets it done by Tuesday and surfs the net for the rest of the week and another developer gets it done on Friday, which one performed better? Both met the expectation, working under the assumption that the reports work the same, so how do you evaluate performance? Granted the one who finished sooner could be assigned more work so is more productive, but both met the requirement.

I had a short contract position writing SSRS reports for a company. The first day I showed up, they had a certain number of reports spec’d out for to work on and expected it to take me several days to get them done. Well, by the middle of the second day I had completed them all and the Business Analyst I was working with didn’t have anything else ready for me and I was expected to be there from 8 to 4:30. I couldn’t leave, but I wasn’t being productive, yet my employer was happy with my perfomancem and my contract, which was originally for four weeks, was extended and I ended up there for six months.

The point of the story is that I had days where I was paid for 8 hours and worked 3-4 because I was waiting on someone else. So was my performance good, they thought so. Also my timesheet showed 8 hours worked, which really isn’t accurate.

This also relates to the editorial at SQLServerCentral. Be sure to read the discussion as well.

Posted in Development, Performance, Professional Development | 4 Comments »

Disk Alignment?

Posted by sqlwiseguy on August 7, 2008

I attended the Orlando PASS meeting last night where we had 2 speakers. Dolores Tofel, a local and regular attender, who did a short presentation on using Microsoft Access as a UI for SQL Server data. Even though I have developed a distaste for Access, she did a good job showing how it can be used as a RAD tool, particularly for reporting. Andy Warren, president of OPASS, is trying to give local speakers a boost by giving them an opportunity to do a mini-presentation and not be the main draw for the meeting.

The main speaker for the evening was Amy Styers from EMC. She does performance consulting for EMC customers running Microsoft products (SQL Server, Exchange, Sharepoint). Her topic was the top ten ways to improve your SQL Server performance, and I have to admit that most of the list were practices I was already familar with. But, her #10 was new to me and I think to majority of the ~20 folks in attendance. It was to get your disks aligned. She showed us that because the Master Boot Record is 63 k and the tracks are 64k. Basically this means that you will regularly have to do 2 IOs per read or write because you have to cross tracks. Apparently you can see up to a 20% IO performance gain by aligning your partitions. Of course to do this on a production system is a BIG deal as you have to make sure you have good backups and totally wipe your disks. Then you can use the DISKPAR or DISKPART(Windows Server 2003 sp1) to align the partitions and then reformat. Apparently Windows Server 2008 no longer has this issue. Linchi Shea has done some testing of this and posted the results on his blog, here. There is another article here, that deals with the same issue for Exchange with reference to an HP whitepaper. I found this subject very interesting as I had never heard this before and neither had the server admin at my workplace. We don’t have any overburdened servers at this time so we won’t be making the change, but it is still good to know about in case I meet someone who may be able to do something with it.

This is one reason I go to the user groups meetings even when the featured presentation may not be something I think I’ll be using. Last meeting was about SQL Server Mobile and I am using it now for a personal project when I never thought I would before the meeting. I find that I learn something new every meeting and I have a good time meeting other SQL Server specialists.

The next OPASS meeting is scheduled for September 9th, 6:00pm at End to End Training, just outside Orlando. If you are going to be in the area come join us. The featured speaker is going to be great. How do I know that? It’s ME! I’ll be presenting on SQL Profiler Basics and I think you’ll get something from I it. I know I have in preparing for it. If you do come, plan on joining us for the after event as well for some informal geek talk.

Posted in OPASS, Performance, User Group | Leave a Comment »