Inserting Records into SQL Server - is there a faster interface thanADO

[Tim Golden]

[geskerrett at hotmail.com]

> I have a program that reads records from a 
> binary file and loads them into an MS-SQL Server 
> database.  It is using a stored proc, passing the
> parameters.

> I am using pywin32 to create a connection object.  
> Once the connection is open I simple pass the 
> SQL formatted commands using cnx.Execute(sqlstring).

> So my questions is ....
> Is there a "faster" method I can use to connect 
> to the SQL server ? Or does anyone have any 
> "optimization" tips the can offer ?

If you haven't, try to form your SQL statement
so it is parameterised. ie do this:

<unreal code>

db = <get connection>
q = db.cursor ()

list_of_data = [...]
for row in list_of_data:
  q.execute ("INSERT INTO <table> (x, y, z) VALUES (?, ?, ?)", row)

</unreal code>

rather than this:

<unreal code>
...
for row in list_of_data:
  q.execute ("INSERT INTO <table> (x, y, z) VALUES (%s, %s, %s)" % row)
...
</unreal code>

In theory, that should be more efficient, as the RDBMS can
optimise the SQL once. Don't know if it'll really make a 
difference.

TJG

________________________________________________________________________
This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________