[Tutor] Sloooooow SQL

[Tim Golden]

Øyvind wrote:
> I have been writing some scripts that gets data from a MSSQL-database.
> However, it is very slow. If I use the script, the retrieving of data and
> displaying in html takes close to 30 seconds. If I use the SQL Server
> Management Query, the same query takes a second or two. Why this enormous
> difference? 

Well, you need to start by distinguishing one or two things.
First, forget the HTML/CGI bit of things. Now, does your
script, reduced to pure Python, still take as long.

<code>
import MSSQL
db = MSSQL.connect('10.10.10.10','admin','admin','MYDB')
ejfirma = """select x_firmanavn from ad_kopi order by 
x_firmanavn"""
cursor.execute(ejfirma)
result = cursor.fetchall()
</code>

If you run that in the interpreter, or save it as
sqlrun.py and execute it, how long does it take?

If it *still* takes a long time, then we look at
the module and/or the quantity of data and the
way in which it is being buffered, etc.

If it only takes a second or so, then the issue is
likely with the CGI startup time or some other factor
within your script. How long does the HTML take to
produce *without* the SQL? Now, how long with a
TOP 10 or some other limiting clause? It might be
that getting the connection is slow. Is the web server
the same machine you run your Query Analyser tests on?

These aren't answers, because a lot depends on your
environment, but maybe if you come back with the problem
broken down, we'll be better placed to advise you.

TJG