how to process 12 million record

[pymach]

I need to read 12 millions records from a SQL table to store in another database after pivoting it. It works, but its taking lot time, any idea to improve the performance would be a great help.

Here is the table details:

The table structure of 12 million records table goes like this
mysourcetable (f_name varchar(100), l_name varchar(100), grade varchar(10), year varchar(10), age int, dob datetime())
Ex:
john abc, 2nd, 2020, 7,00-00-0000
rj bca, 5th, 2020, 10,00-00-0000

Here is the structure to target table - where the all the 12 million records will be saved
mytargettable(column_name varchar(100), column_value varchar(100), field_type varchar(100))

Ex:
f_name, john
l_name, abc
grade, 2nd
year, 2010
age, 7,
dob,00-00-000

f_name, rj
l_name, bca
grade, 5th
year, 2010
age, 10
dob,00-00-000


Here is the sample python code

						for row in df.itertuples(index=True):
                            # Function to create an insert statement for each column in a row
                            #def create_insert_statements(row):
                            try:
                                counts += 1
                                # Iterate over each column in the row
                                for column, value in row._asdict().items():
                                    # Create the insert statement
                                    col_type = lookup_key(tbl_col_type, column)

                                    statement = f"""INSERT INTO [mytargettable] ([Column_Name], [Column_Value], [Dictionary_Id]) 
                                        VALUES ('{column}', '{value}',{col_type}) \r\n """
                                    
                                    cursor.execute(statement)
                                    cursor.commit()
                        
                            except Exception as e:
                                print(f"Error inserting data ': {e}")

Looking forward to get some help to improve to performance.

Larz60+ write Jul-31-2024, 12:57 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Tags added for you this time. Please use BBCode tags on future posts.

[Calab]

I need to read 12 millions records from a SQL table to store in another database after pivoting it. It works, but its taking lot time, any idea to improve the performance would be a great help.

Looking forward to get some help to improve to performance.

If your data matches the table you are trying to update, look into the executemany method: ExecuteMany

[Larz60+]

why not just dump the entire table? select * from tablename
You can also use pandas to do this
Read a table
Select subset of dataframe
Create new table

[DeaD_EyE]

If it should be a copy, then use the right tools. For MySQL/MariaDB mysqldump is used to export your data as plain text. Later the mysql command can be used to put the dumped data into the other database. Also the tables are created from the dump.

[Pedroski55]

Or get handy sized chunks by using LIMIT:

From MySQL docs:

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

From stackoverflow:

SELECT * FROM table limit 100` -- get 1st 100 records
SELECT * FROM table limit 100, 200` -- get 200 records beginning with row 101

Put that in a generator somehow, you will never overtax your computer memory.