Wednesday, December 24, 2008

Using triggers for performance

Can you use TRIGGERs to increase performance? Really? Isn't so that a TRIGGER on a table will reduce performance, just as any FOREIGN KEYs will? Right?

Nope, none of those statements is necessarily very true. In the case of reads, for example, neither TRIGGERs, nor FOREIGN KEYs has any negative impact at all. Then, assuming we have a read-intensive system, such as some website, then performance isn't much of an argument for not using triggers or foreign keys.

You already know I'm quite a fan of foreign keys. Let me reiterate why I like them: They keep my data integrity intact, that's why. The argument that if I have transactions, then the only thing that might cause data integrity to fail is if there is a bug in the application. There are two things I have to say against that:

  • Many, if not most, users use more than one application. For example, if nothing else, the mysql and mysqldump commandline tools are used. To me, if I use foreign keys properly, I can safely mock around manually in the database with SQL, without causing data integrity issues.
  • Foreign keys are declarative. This means that the rather strict things they enforce are easy to look up and maintain, without looking into numerous pieces of code spread all over the place.


Now, I have made my point with Foreign keys, so what about the original subject of tjis article, i.e. Triggers to increase performance, all I have said so far is that they do not at least decrease performance for reads, but can they increase performance? Yepp. Let me give you an example.

Even with semi-complex data structures, you sometimes get into a situation where even a moderately normalized RDBMS structure cause some queries to be unnecessarily complex. On the other hand, denormalizing it usually has other issues. For example a de-normalized design is often best (performance wise) for SELECTs, whereas a normalized design keeps my data integrity better maintained, in particular with INSERT, UPDATE and DELETE statements. And this anomality is where triggers help!

This is how I think: Start with a pretty normalized structure. Identify the areas where a SELECT would be unnecessarily complex, then add the "de-normalizing" columns to the tables, without removing the normalized column structure or foreugn keys. This means I will have duplicate data, but it also means that SELECTs will be performaing much better. INSERTs, DELETEs and UPDATEs will be more complex and less performant, but this is no big issue as:

  • INSERT, UPDATE and DELETE are less frequent! I said this was a mostly read-only system, right!
  • And here is the magic sauce: I can use triggers to maintain the "denormalized" columns. My INSERTs, DELETEs and UPDATEs will not change at all compared to the normalized structure, as this is still in place as it always has been.


I had this issue the other day. I run a website on my spare time where I, among other things, keep track of bluesbands and gigs with them. I figured out that the bands usually were a combination of artists, i.e. I had "Fat Foo" on one gig, and on another I had "Fat Foo and the Bar". So I ended with having the latter being a lineup and the former an artist. In an ideal world, this would have been a great candidate for a hierarcical CONNECT BY, scenario, but not so now, without that particular feature in MySQL.

All the same, I realized, for a number of reasons, that this would be a bit sluggish. Every time I looked up a gig (in the gig table), got get the name of the artist I needed at least 3 lookups: The lineup, the lineup->artist table and the artist table. A gig always references just a lineup, so every artist is part of at least one lineup, the one with just one member:
Artist "Fat Foo" is the only artist in the Lineup "Fat Foo"
but
Artist "Fat Foo" is also one the members in the Lineup "Fat Foo and the Bar", the other artist in that lineup being "The Bar".

A lot of table lookups, just to get the name of a band. So I created not a trigger here (I'll soon tell you why), but a stored function that would update the corresponding lineups everytime an artist changed. The function would simply sync the lineup, having been given an artist ID.

Now it was to to create the trigger that would call the function above. So why didn't I embed the code in the function right into the trigger? Because I also wanted to make sure that I could always redo all the lineup names, if I decided I wanted to change the scheme I use for this. If I did that, I would use the function easily for that, with a simple SELECT.

So, now you ask, how does it work in practice? And what about the case when "Fat Foo" and "The Bar" keeps their names, but the combo is renamed to "The Fat Bar". I just have two columns in the lineup table, one which contains a "generated name" by the function above, and one which is manually entered. I use the manually entered name if it exists, else I use the generated one, a simple IFNULL will do for this, no extra lookups or anything!

Look at the result on PapaBlues.

And by the way, folks, Merry Christmas to you all
/Karlsson

59 comments:

Roland Bouman said...

Hi!

nice post. What I am interested in is, can you show us the figures that prove you actually did increase performance. I mean, I'll take your word for it, but it would be even better if you'd show us ;)

kind regards and happy holidays,

Roland

Karlsson said...

Roland!

That's a good point. That performance IS increased in clear, the only table I am querying now, I would have to query anyway. With at least 2 rows less to query, it would be cool to try this out.
I'll do this after the weekends, possibly using some dummy rows. As for numbers, I have some 30 QPS in the system, and keep track of about 3000 gigs, if I include the historic ones. Then we have to look at bands and lineups, but I'll do another blog posting in a few weeks and provide some real numbers. By then the site should have stabilized.

Jacinthe said...

Good day! This post couldn't be written any better! Reading this post reminds me of my old room mate! He always kept talking about this. I will forward this article to him. Fairly certain he will have a good read. Many thanks for sharing!
Regards,
Jac@Jakarta Hotel

Gerrardz said...

I believe that is one of the so much important information for me. And i am satisfied reading your article. However want to observation on few basic issues, The web site style is perfect, the articles is in reality nice.
Gerrard@Rumah Dijual Jakarta

Tome mark said...

meaningful come again? it take to be a happy author is half the fight. Once meaningful these 4 point then just create sure that you do what you contain uk.bestessays.com review to do in arrange to get the teaching. Be the best satisfied writer on the Internet and you will be victorious.

Tome mark said...

Thanks for taking the time to converse about this, I seem powerfully about it and love finding out more on this theme. If likely, as you gain know-how, would you mind updating your blog with more minutia and numbers? greatest casinoonline777.com.br in history which always astonishing

Tome mark said...

I am not ? ure accurately why but thi? webs? te is commencing unbelievably slow for me in person. Is any person else online casino having this topic or is it a theme on my own deduction? If the adversity still lives, I’ll ascertain back later and glimpse.

john mak said...

There are three alternatives of the amusement: baccarat chem in de fer (line), baccarat banque (or a deux tableaux), and punto banco (or North American baccarat). online casino sverige

Tome mark said...

You only just comprise to connection the CIA to turn out to be telling spy. In fact, these things are going on in all facets of our general society where 在线赌场游戏 persons are utilising human understanding accumulating to get a leg up on their affray, if it be in foreign discussion, infantry effort, regulation enforcement enquiry, or even in the up to date day company.

hanks7777 said...

For these causes, numerous are inclined to request casino sites which let you play many casino games without downloading or without long remains. The online casinos that are run on reliable and stable programs can offer graphics so best online casino far sophisticated that players will nearly seem as if they are squatted in an actual land founded casino.

john mak said...

peak Online Casino Payouts for Video Poker: Jackpots in a blink Casino (99.53%), fortuitous Nugget Online Casino (99.11%), absurd Vegas Casino (99.07%), fortuitous Emperor Casino (99.03%), 7 Sultan Casino (99.00%), regal Vegas Online Casino (98.77%), Swiss Casino (98.70%), rotate castle (98.60%), RiverBelle Casino (98.59%), Roxy castle Online Casino (98.58%). casino bonus utan insättning

Tome mark said...

Not numerous persons would state what you've said and still make it intriguing. casinospiele Well, at least significant important I'm pledged. Cant hold up to glimpse more of this from you.

Tome mark said...

articulate thankfulness you, while I am experiencing adversities with your RSS. I don’t appreciate the source why I am essay writing incapable to adhere it. Is there any one-by-one supplemented getting alike RSS adversities ? any one-by-one that appreciates the answer can you caringly response ? Thanks !!

Tome mark said...

This is a scarcely anticipated innovative on the subject of a adolescent young female who swamps many hardships college paper writing services to complete achievement as an adult. She was proposed to be a healer, but does her economic accomplishment lead her off aim and avert her from experiencing factual love?

Tome mark said...

If you crave to take a snug flat you may use . Its vaporizes the vapor into the air-tight balloon and has the analog determinants of heat alignment.payment processing solutions

Speicher Spe said...

SID from alternative unsupportive hospital behaviors, like changes within the choice of patients. we tend to overcome these empirical challenges by specializing in a selected population: If you learn more about inversion tables reviews this. Please visit this website.

ASIF66 said...

Every homeland, every heritage has its own heritage, it is the identical for jewelry conventionally suggested when two persons consign for life. http://onlinecasino777.co.za The jewel weakened is not inescapably an coalition, there are many other emblems of adoring firm promise.

Tome mark said...

Anyway i went to glimpse multiple blogs except all of the sound recordings online casino bonus attribute with consider to acoustics melodies pathways living as well world wide web sheet is wholeheartedly in minutia fabulous

Ronny678 said...

Another vital facet after you build a call a few casino on-line is that the variety of games. If you're inquisitive about aposta online a selected game, you must search the casino that provides the most effective version for that game.

meruna sultana said...

I am very happy to read this. I am always searching for informative information like this! I was searching for some info to this website. Thanks a lot for providing the info.sell gift cards

Tome mark said...

The acclaimed medical able is a absolutely archetypal company with a bulk of Tv set displays thinks that the abstract helps association aggravating to abate fat to booty activity while not impacting their consistently concrete fettle routines or their diet plans.Payment Processing Solutions

Tome mark said...

hgjgjgj

Tome mark said...

VSVV

mahadi hasan said...

I like this article. It is really good. It is significant. I would like to express gratitude you for the efforts you casino suomi have made in composing this item. I want the equal best work from you in the future as well. In minutia your creative composing adeptness applies us.

dab99 said...

He charcoal an arresting best and, on balance, his positives for a angel accepting outweigh his negatives. Abounding admonition you got here. I’ve been celebration about this activity for one celebration now for,,Peoria Laptop Repair

Tome mark said...
This comment has been removed by the author.
Tome mark said...

If you are hunting for a absolutely new appearance for that hair, so back locks are beneath than lots of people, again you will be able of accept from several kinds of crew is absolute for the agrarian hair. Abbreviate bouncing styles are accepted due to a lot of women, active and old. Here's added particulars apropos the blazon of abbreviate crew styles bouncing hair.Phoenix Electriican

hanks7777 said...

Nope, none of these statements is essentially terribly true. within the case of reads, as an example, neither TRIGGERs, nor FOREIGN KEYs has any negative impact in the slightest degree. Then, assumptive we've a read-intensive system, like some web site, then performance is not a lot of of associate argument for not victimization triggers or foreign keys. utah seo

hanks7777 said...

Bricks-and-mortar casinos in Las Vegas Nevada and other states have stood by while online wagering has blossomed into a casinoonline-sverige.com multibillion-dollar business. They were unwilling to risk operating Web casinos considered illegal under federal law.

franky789 said...

SEO is a tool which helps in promoting the smart business. Denver SEO also includes Denver SEO plays a very important role in Ecommerce. Today basically every business is done through internet. denverseo.pw

william max said...

Welcome to at this site. Many companies are actually utilizing recruitment softwares to help them with the job, from collecting, tracking, storing, and sorting Glendale Blinds various information with regards to job descriptions, CV's, candidate profiles, and many others.

hanks7777 said...

First, what could be your ideal audio system for a casino? Would you like it loud or would you prefer a smooth and relaxing sound to provide to your customers? Whatever you or your customer's Jogar Bingo Online preference is, you can always arrive with the best choices with a casino audio system installed in your business.

Jordan said...

You should also look for casinos that offer the best blackjack bonuses. Learn to take advantage of these bonuses so you?d win more money. However, do not norske casino abuse these bonuses. You might get banned from your favorite casino. Do not join casinos just to get the bonuses. You should also play some games.

Jordan said...

Online pokies surveys on the web give data about distinctive online club and exceptional amusements accessible there. The greater part of the commentators pokie games compose their surveys dependent upon their particular encounter as players.

lucia789 said...

The whole web industry was started after the inauguration of the internet or the World Wide Web. Tim Berners Lee has developed internet for the first time in the early 60s. utahwebdesign.me

cathleen2014 said...

I just came out here to see if something like this has been suggested and to make the suggestion if not already made. This scenario has come up for me many times in the past. I fully understand that either triggers or defaults can be used to get some of this done; however, defaults alone don't do the whole job. click here

william max said...

The other cash you've got works extremely well regarding automating a unique system in the Peoria Electrician
firm such as in the recruitment office. A great deal of report functions, facts, report throw away, and also man electrical power.

Tome mark said...

Thanks for administration this absorbing blog with us.My amusement to actuality actuality on your blog..I wanna appear allure actuality for new column from your site. Peoria Carpet in Peoria AZ

Manik Hossain said...

Great post with a mature and informative writing. Lots of report works, information, report waste, along with man energy, usually Goblue card are are just some of stuff could be contributing to detailed fees.

Nazmul Aoto said...

What I wouldn't give to have a contention with you about this. You just state so many things that reach to from nowhere that I'm appealing certain Id have a equitable shot. Your blog is large visually, I signify persons wont be uninterested. MBR Tempe

Smith1 said...

Thanks for the fantastic post I did enjoyed reading through it. Gradual, deep breathing invokes a pleasure result, calming your body in addition to http://binaereoptionenbroker.eu/ concentrating as their pharmicudical counterpart. The item will increase the quantity of o2 inside our bloodstream, increasing our overall performance possible.

Tome mark said...

is a boyish but already acclaimed all-embracing adeptness brash accurately for alive people. So, if you do not acquire time to abode homework, coursework, assay calendar autograph or above a authentic commodity we will admonition you with this. Phoenix Computer Repair

sujan anika said...

When times get robust and you would like further funds, a Title Loan could also be the most effective form of loan for you. You get the money you need; you retain your automotive and that we merely hold the title to the automotive till the loans is paid fully, at which period the title is came back to you free and clear
payday loan. it's that simple. All you would like to try and do is are available along with your vehicle and a couple of straightforward documents.

Jaidev Lochi said...

Not sure about the RDDBMS technology but anything that leads to better performance is always welcome. price4india also has similar topics for Indian users.

Legal Herbal Online said...

Wonderful submit having a fully developed along with beneficial producing. Many report functions, facts, report waste, in conjunction with guy electricity,Ibeacons for sale

get rid of cellulite said...

I have visit this blog first time and totally enjoyed it really.
schifffonds

Speicher Spe said...

Gravitate Online has been doing corporate SEO for over 3 years, with many of on our team who have been in the industry for almost 10 years. utah seo firm

Thomas Braylen said...

Some beginners come across MS Excel daunting, but it really do not need to be for a new Excel for beginners course. This sort of course will take an individual by the give along with direct an individual through the basic principles of this effective app.

devon broad said...

The monthly payments is typically startup in relevancy eight thus you may twelve bi-weekly obligations, or presumably if your home fast cash advances montgomery mortgage is about by your cluster stability earnings, later you may terribly just got wind of your repayments for being caused by 3 to four month-to-month payments. systematically planned obligations aid to form a lessened investigate total that is the larger go at the side of your cash.

devon broad said...

The particular Pay day enhance Dimensions Loan from the bank can apparently be our greatest home mortgage product and in addition solutions. the foremost like having obtaining some style of Pay day enhance Dimensions Loan from the bank on the one examine advance, area unit reaching to be Associate payday advance in Nursing Dimensions Loan from the bank shows you may be worthy this sort of home mortgage in relevancy a drawn-out stretch of a while. These kinds of winds up throughout this being many easier you may store repayments a most of getable.

shahbaz said...

hanks, I've been searching for facts about this subject matter for years and yours is the best I have found so far.Bank Jobs.invitations click here

shafin77 said...

Have you been pondering about the most ideal approach to win cash online? Have you been advised that it most likely is not difficult to profit online however in the meantime, did your companion neglect to let you know how to profit? Well if these and more are your worries then stress not on account of I am going to let you know about simple approaches to gain cash from home.If you want to know more information,please click here
best way to earn money.

halum99 said...

The Internet is loaded with circumstance. There are such a variety of approaches to profit quick. On the off chance that you power the kind of individuals that you might want to focus to the extent that you can, the Internet can profit on programmed. The way to running an effective online business descends to dealing with your crusades accurately. On the off chance that you have somebody who can control you on your way while you utilize these approaches to profit quick you will be significantly more inclined to succeed and quicker as well. Thus, please take in more data, please click here... binary options trading signals

halum99 said...

It's mid-summer in Romania and I'm going to bite the dust. Storm power winds are screaming between the lampposts and lashing me with even rain-shots as I battle to control my intensely laden Honda Parader bike over broken cobbles. I'm en route into Bucharest downtown area and longing.. Best binary trading software

halum99 said...

A short while back, I catch an alternate web website about picking up six figure maverick yearly and We continued thinking about whether this wage creating tips is genuine or just an alternate sham. You have seen all the alleged telecommute programs that guarantee that you vast pack from exceptionally straightforward writing, insight this connection... Safe Trader App Review

Tome mark said...

This is the first moment I even have seen your substance and do esteem any extremely to let you know – its horrendously satisfying to sight that i recognize your work. However in the event that you apparently destroyed it relate exceedingly} exceptionally clear approach that is prepared to be extremely influencing. however over all i genuinely anticipated you and positive can contain up for any sends like this. Particular feeling you most.check cashing corona

Sohidul Islam said...

Simply yesterday Amazon CEO Jeff Bozos proclaimed the arrival of their most up to date tablet, the Kindle Fire. This new tablet has some really focused gimmicks: a low $199 sticker, a super quick program called Amazon Silk access to huge amounts of motion pictures, music, applications, and books. It has a 7 Inch touch screen runs a determination of 1024×600 14 Fast Income App Review.

Tome mark said...

As of late I unearthed and have now joined Empower Network's as of late propelled Viral Blogging System stage and required to give a decent audit over it. I've been offering Click bank together with other comparable items online for a long while. Presently I have gone over and assessed the Viral Blogging System and had the chance to think of it as, I think it is an incredible instrument for On-line advertisers, for example, Fast Income App review-legit or scam system

hanks7777 said...

"It was extremely tumultuous (in secondary school), however it most likely taught me how to deal with my time," said Piampiano, 18, who played soccer and lacrosse and workedtwo employments. copy essay