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.
I have always wondered if it would be possible to use Open source software in an office that heavily uses MS products and not be detected. The experiment: To replace as much of my personal desktop applications that I use on day to day basis with those that are Open Source and/or shareware.
Currently I am on assignment with a company that has a large customer base and a busy call and support center. I am doing their development, reporting and some of their IT support. From the start I had implemented Firefox, svn, and WAMP to support SVN. I had used these products and their budget was such that it wouldn’t support alot more than the bare essentials. My day to day existence was normal, but it was all contained to me and my personal development. There wasn’t any outside interaction. Fastward to last week.
In a slow day before Thanksgiving, my mind wandered back to my experiment. Could I function with opensource/shareware products and how many such products before it became noticeable. Last week I took the initiative, I installed Mozilla’s Thunderbird, OpenOffice, and gVim. I carefully configured Thunderbird to mimic outgoing mail as closely as I could to Outlook. There were some difference’s, but only one person commented on it. This week I am going to install and configure Bugzilla to track changes and requests and produce reports for management.
Things have gone well. Spreadsheets, reports, documents, Email are all going according to plan. The only complaint I have is I miss the folder view in Outlook 2002, but then this office is still on Office 2000 so it is not missed by the definition of the experiment.
Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
I am a huge fan of Robert Scoble’s. Through his work I came to realize that MS has a lot of cool and interesting research going on. With products like voice and face recognition; picture stitching and their research into UI. Their are a lot of highly talented people working their. Their jobs aren’t easy, especially given the size of the company and how compartmentalized something as big as MS can be, sometimes even out of necessity.
The whole concept of a humanized MS was a very hard sell to me, given their history of DOS, dblspace, Office, etc. I was willing to at least see what the “other side” was about. Then came Steve Balmer’s announcements and rants about Linux and the “patented infringements of linux.” How he hinted about every linux user would have to pay royalties. Now, on the coat tails of their announcement of this patent infringement and their deal with Novell, comes word that Novell is going to fork OpenOffice and add Open XML to it.
GrokLaw has a good description and on going discussion about it. This really just comes back to MS’s predatory practices: copy and/or buy out the competition. When it comes to their core products their isn’t a lot of ingenuity, just copying, buying and reselling it. Once that is accomplished make the world believe they came up with the idea and redesign it to just enough to force others to license it. Brilliant in an evil sort of way, force the world to accept your mediocrity and kill of competitive and innovative thinking.
Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
Adam Pash over at Life Hacker has a great tip for using Skype to do automated reminders. You can also use a script to send a text message to your phone as a reminder in much the same way. You just send an email to your number followed by the domain for your carrier. Here is a list of the most common ones.
- Verzion - vtext.com
- Nextel - messaging.nextel.com
- TMobile - tmomail.com
- Xingular - cingularme.com
- Virgin Mobile - vmobl.com
- Sprint - messaging.sprintpcs.com
If I have missed any carriers please let me know and I will gladly update the list. What is your favorite automated use of Skype or SMS (text messaging)? Thanks for the idea Adam!
Technorati Tags: Skype, Text messaging, Reminders, Automated alarms, Life Hack, SMS
Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
Albert Einstein once said, “Technological progress is like an axe in the hands of a pathological criminal.” I look at the progress we have made in the past 20 years and especially in the last 5. We have gone from technology being in the hands of geeky privileged few, to it being common place everywhere. It is no more apparent than watching how people view and use the web. We live in a world where people can not go from place to place without a GPS; Google is now considered a verb (much to their chagrin); email is now passe amongst 20 somethings; and anyone can interweave services from Yahoo, Amazon, Google, and the likes of Zillow to create some scarily accurate and inaccurate pictures of us.
This generation or iteration of software and the internet is one of openness and transparency. This means as technologists and corporations, we have a responsibility not only to our shareholders and employers but also to our user community. We can still be socially responsible and still make a profit. Even if that responsibility means that we only create a trail of how people are using our hardware and software. Some are going to scream about Privacy. Others will call out that we are just feeding big brother and making him into an ever more aware beast. We need to do something to protect us and yes perhaps help big brother and the authorities.
Just look at what is happening all around us. Go to MySpace, Facebook, Google a friend or colleagues name, or choose your favorite topic and find a forum on it; you’d be surprised at the amount of personal information you will find, most of it is posted out in the open. Molly H. found out that a lot of her personal info was posted on Aboutus.org. Some that she may not have chosen to be posted. My 13 yr old niece set her away message on AIM to be her cell phone number. Whether or not if its voluntary or not, there is a lot of personal information out there that can be pieced together pretty easily and quickly. Until the public and the government is fully educated and appreciates the ramifications of such transparency, then we are obligated to assist them when possible.
Should we keep detailed records, with names and personal records available? Its not necessary, but we
can keep an auditable trail for a time period and insure that it is purged on a regular basis. This we have in our individual abilities. Through my career I have emphasized this with my employers and customers. If something ever happened like on MySpace, then we should be able to help them when necessary.
Storage space is cheap. The required computing power is minimal and the technology required to create is very straightforward. In a subsequent article, I will detail some of the steps that I have done to do this (programmatically in VB) and what I am using today achieve this (SQL triggers). If we are going to make the Axe and WMD, then let’s at least try to make it difficult for the psychos to use it.
Technorati Tags: Albert Einstein, responsibility, MySpace, SQL, triggers, Google, design
Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
