What is the most performance-efficient and memory-efficient way to copy a SQL Server query result of > 600,000,000 rows to a local .txt file? You may assume that I do not have have user permissions to export from SQL Server Management Studio. For this reason, Python seems to be my best option.
I am currently using the Python pyodbc package:
connection = pyodbc.connect('Driver=DRIVER;' \
'Server=SERVER;' \
'Database=DATABASE;' \
'uid=USERNAME;' \
'pwd=PASSWORD')
cursor = connection.cursor()
try:
cursor.execute("SELECT * FROM %s" % table)
except:
print('===== WAITING ===== EXECUTE ERROR =====')
time.sleep(15)
cursor.execute("SELECT * FROM %s" % table)
try:
data = cursor.fetchall()
except:
print('===== WAITING ===== FETCH ERROR =====')
time.sleep(15)
data = cursor.fetchall()
with open(output_file, 'w', newline='', encoding='utf-8') as f:
writer = csv.writer(f, delimiter=delimiter)
writer.writerow([x[0] for x in cursor.description]) # column headers
for row in data:
writer.writerow(row)
cursor.close()
Side note: my goal is to transfer several hundred SQL tables as .txt files to an Amazon S3 bucket. Is there a better way to do that instead of downloading the file to a local drive and then uploading to S3?
1 Answer
It depends on the result set, but as a general rule, I'd use fetchmany to grab a bunch of rows at a time instead of pulling everything into memory:
fetch_rows = 1000
rows = cursor.fetchmany(fetch_rows)
while rows is not None:
for row in rows:
do_something()
rows = cursor.fetchmany(fetch_rows)
Good luck!
