Jeff Atwood recently talked about Curly’s law of doing just one thing. He’s talking about simplifying your code so that it does just one thing. You can take it even one step further and know when you need to refactor and abstract your code. If you find yourself copying and pasting code from one section of code to another, then stop. If you had to do that, then you should create a new class or function that duplicates that code. You will save yourself a world of support headaches in the future.
Technorati Tags: Development, Code reuse, coding, design, refactoring, copy and paste
Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
Ado as a technology offers a lot of usefulness. It can help you manage concur connections. Lock records; ease inserting, manipulating, and deleting individual records; and overall manage database manipulation. It tries to provide a layer of abstraction between the developer/application and the database/database provider. It accomplishes this goal fairly well. Unfortunately though, it comes at a price: performance. To understand how to improve performance, we need to understand the basics of how ADO works.
ADO does a lot behind the scene. First it must be able to communicate with your database provider. To do this it has to translate all of your ADO commands into its SQL specific counter parts. There is no getting around this. But it is additional layer that needs to be accounted for. This isn’t the biggest hit in performance though. What is the foundation of how ADO works; Record sets.
Record sets are the result of your SQL command, the records that get returned. ADO returns all of the records you asked for in one big lump and loads it into memory. Memory is cheap and memory operations are fast, but the overhead of managing large amounts of data this way is costly. Operations such as sort, find, and filter can be huge if the data is as well. These commands and others are done on the client side and on the entire record set.
Transferring that large amount of data can be part of the overhead as well. Traditional client/server databases can handle this, but if you are using access which uses a file connection this can pull an application to its knees.
By default when you open up a generic ADO connection, it opens and returns the whole table. Depending upon the table this can be a long and resource intensive operation. The best way to handle these performance issues are to follow these simple steps to improve your performance.
Steps to improve ADO and query performance:
- Use the .command method. This will give you greater control over what you bring over. By limiting the amount of data to what you just need, you save network bandwidth, client memory, and client CPU time. Let the database engine do the work for you.
- Use the where clause in your SQL command. Again this goes along with #1.
- Don’t use Select * in your SQL command. Using column names gives lets you specify the order that columns get returned in. You can change the names of the columns. You don’t have to fumble with ADOx to figure out the column names (giving your code that much more readability). Finally, if the database is changed and a columns get added you aren’t suddenly faced with a degrade in performance because the added extra data is being brought over.
- Do all of your sorting, grouping, and aggregates (Min, max, sum, count, distinct, …) in your SQL command. The database is designed and optimized to do these functions. Let it do it for you.
- Be smart with your queries. Know your data and look at your most used queries and optimize them against your existing indexes and add indexes where you need them.
I followed these steps with a customer’s existing Access database and saved them from rewriting the entire application. It sped up the application from opening up this one screen in 3 min to 20 seconds. That was before optimizing the queries too.
What other tips and tricks do you have for speeding up an ADO connection/query?
Technorati Tags: MS Access, SQL, ADO, performance, databases, queries, development
Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
It is that time again. The year is nearly done and its time to look back and reap the rewards of the past year. In tip #7 I asked
What timeless core skills do you have and which ones will be your goals for next year?
You need to honestly and openly review your past year’s accomplishments, milestones and lessons. You might even partner with a peer and swap lists to see if there is something you missed.
Yearly Review Questions/Topics:
- Look at your to do lists - What projects/tasks stand out? Is there a trend?
- What new skills have you mastered? Don’t be stingy with yourself. These skills could be anything from a new language or technique to soft skills such as presentation or speaking.
- Project schedules and estimates. Did you do a project on time? Early? Late? If late what did you learn that would help in the future?
- Did a project a that you did save the company/customer time and money?
- Did you go above and beyond to help a coworker complete a major project?
- Did you make a big mistake? What did you do to correct it? What lessons did you take from it? Did it lead to a new process that could be implemented departmental or company wide?
- Did you find information that helped the company that wouldn’t have been found in day to day business?
- What skills do you feel you are weakest in?
- What skills would help you to do your job or excel at it?
- Don’t forget the small apps that might not have taken long to do, but helped someone else do their job more effectively.
These questions (and others) will help you evaluate your past year and lead you to the bigger questions of where to go next and if not next then help you when it comes time for corporate reviews and raises. Your boss will love that you are prepared and taking initiative. It will help you showcase what you have accomplished and help you plan the upcoming year.
Technorati Tags: Development, Career, Success, Planning, Yearly Review, Questions, Developer
Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
There has been some resurfacing of the importance of backing up and version control. Jeff over at Coding Horror makes a valid point of versioning your database with your application. Robert Scoble points to a friend who had Vista Beta lock him out of his PC. Not to mention the countless number of people who have lost data due to viruses.
This is all just a reminder that as developers and Sys Admins that we should be backing up and versioning as much as we can and what is important to us. Your databases and applications; configuration files and registries before you try a new piece of software, scripts, even your to do lists and resume. I recently had to go back to an old to do list to see what I had done.
My strategy is if it is important it goes onto a back up and gets rotated off site. If I need to keep track of something and may need to roll back or compare/see an older version then it goes under version control and the repository gets put on a back up tape.
Things that I like to backup: Nightly backup of documents, databases, calendars and address books, email files, and version control repositories.
Things that I use version control for: any development (documents, scripts, images for applications, application database structure and data, source code, resumes, to do lists, configuration files and registry hives, and user databases.) I sometimes also keep documents that a team works on. This way all have access to it and can refer back to originals. Its also a nice way to see how things emerged.
Technorati Tags: backup, strategy, version control, development, successful
Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
Today I had a very long and intense meeting about linking the system I built with a vendor’s system. I learned allot about their system, accounting, my customer’s processes, and generally more detail about how my customer runs it business. All in all a very knowledge intense 6 hours.
The goal of the project is to link the two systems which is now done via sneaker net, with required double and triple data entry and errors and delays that can happen with such system a labor heavy process. After going through all of it, the woman whose project this is, came up with a very insightful and poignant. She asked if only the main piece of it could be done (80%). That if we could do that much if it she could handle doing the rest of it manually until a later time in which that we’d all have more time and opportunity to attack the last 20%. It made sense. It also reminded me of an important rule. No matter how technically “right” your solution maybe, ultimately the customer is driving the project. In this particular case the customer clearly voiced what she was looking for and I had heard her underlying message loud and clear. (see Part 1: Listen) She was concerned that by the time we had cleaned up the data and figured out how to automate the whole process it would be “too late and too long.”
This woman is under a lot of pressure and really the brunt of the project isn’t to get it “completely” right, but to alleviate her from the responsibility of doing all of this manual data entry. She was right, in that we were losing sight of that main focus.
Moral: Listen to the Customer and let them steer the project.
Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
