I have the task of presenting information in a grid derived from a SQL backend where the results are further processed to create insert statements.
The data is stored in Postgres for a vendor and the resultant data is inserted into a MySQL backend.
I want to keep the MySQL backend as fast as possible as this is where the grid is attached.
My problem is that multiple users will run the query with different results and I don't want to have other user's data inflate the record size. In other words, I don't want to get the results and insert them from all the different users into MySQL. I'm looking at the temporary table creation and was wondering if anyone has worked with temporary tables.
One reason why I chose to move the data from one backend to the other is that there is some date data that is stored in a "tick" format that I convert to a date and time for the user to understand. The conversion takes place via an user defined function that is not accessible at the point of query. I also didn't want to mess with the vendor's database in terms of adding tables. I may get over it soon if this app takes to long to process the results.
Anyways, any insight into PostGres or MySQL temporary tables is greatly appreciated. My app currently empties the table and inserts the results and I'm not sure this same process will affect other users when the app empties the temporary table as it appears the temporary table is unique to each session.
The data is stored in Postgres for a vendor and the resultant data is inserted into a MySQL backend.
I want to keep the MySQL backend as fast as possible as this is where the grid is attached.
My problem is that multiple users will run the query with different results and I don't want to have other user's data inflate the record size. In other words, I don't want to get the results and insert them from all the different users into MySQL. I'm looking at the temporary table creation and was wondering if anyone has worked with temporary tables.
One reason why I chose to move the data from one backend to the other is that there is some date data that is stored in a "tick" format that I convert to a date and time for the user to understand. The conversion takes place via an user defined function that is not accessible at the point of query. I also didn't want to mess with the vendor's database in terms of adding tables. I may get over it soon if this app takes to long to process the results.
Anyways, any insight into PostGres or MySQL temporary tables is greatly appreciated. My app currently empties the table and inserts the results and I'm not sure this same process will affect other users when the app empties the temporary table as it appears the temporary table is unique to each session.
Comment