The basic differences are the process model and the API. The process model for CGI is to fork a new perl interpreter every time a request comes in. For mod_perl, the interpreter is loaded into the web server process and stays there, which means there is no fork, and your scripts can stay in memory, and you can do things like keep database connections open to save time.

The other difference is the API. You have full access to the Apache API from mod_perl, which means you can do anything that other Apache modules in C can do: apply some filtering after a PHP script runs, use a custom data source for authentication, do a custom proxy to some internal server, etc.