The main diference between ModPerl and perl scripts running as CGIs is that ModPerl compiles perl modules and scripts the first time they are run/used, and keeps them in memory, ready to be executed in the next request.
Another diference is that ModPerl can be used also to create apache modules in Perl, that will completly handle the requests as if a normal apache module they were.