Assuming the webserver is configured to handle .exe, Can i compile a python CGI file into an exe for speed. What would some pros and cons be to such a desession?
There is py2exe (and a tutorial on how to use it), but there is no guarentee that it will make your script any faster. Really its more of an executable interpreter that wraps the bytecode. There are other exe compilers that do varying degrees of things to the python code, so you might want to do a general Google search.
However, you can always use psyco to boost speed of many of the most intensive operations that a python script can perform, namely looping.
Since the RDBMS and the network are the bottlenecks, I see no value in fussing around creating an EXE.
On average, most of a web site's transfers are static content (images, .CSS, .JS, etc.) which is best handled by Apache without any Python in the loop. This has huge impact.
Reserve Python for the "interesting" and "complex" parts of creating the dynamic HTML. Use a framework.
You probably don't want to run Python as a CGI if you want it fast. Look at proxies, mod_python, WSGI or FastCGI, as those techinques avoid re-loading python runtime and your app on each request.