Well with DS carts it's a bit different, because they aren't ROMs, they're a modified form of flash memory card. The DS has 4MB RAM, and depending on how large/complex the program itself is, how much game/graphics/sound data needs to be prepared etc. it could take a while to setup.
I think Animal Crossing is the longest I had to wait on DS, and it's never more than 5 - 10 seconds. And it's ONLY at startup, and then again at saving. I think AC is bad because it's probably using some very heavy data compression techniques since DS cards only have 128K of space allocated for save data.
But even ROM carts can experience "loading" - but it's not program loading, it's data loading, and setup time if the game is using complex arrays and/or heavy compression techniques for the data.
Now I'm going from memory here, from what I read long ago about the company (US based) that actually designed the DS card format. I long since forgot the name etc.
But the DS loading times really are nothing. Most games you experience no loading waits at all. And those that do, it's pretty negligible.
The PSP... Now that's loading time!