Small sensors can be better for macro images, the standard definition of macro means a 1:1 size ratio between subject and film, so you could project an image of a 35mm object onto a single piece of 35mm film.
Because compacts (usually) have smaller sensors a true 1:1 macro lens on a compact would be capable of filling the frame with smaller objects. A DSLR ought to be capable of sharper images, but to match the reproduction ratio you'll have to go past 1:1 and look at something like the MPE-65. A reversed wide-angle is a good budget alternative. This site provides reviews of various lenses reversed for macro work:
Things do get difficult once you get into super-macro territory though. Subject motion is a killer, depth of field is next to nothing, so focus stacking is often required and light losses and tiny apertures require lots of light, usually an external source.