So, I’ve been looking at this database code and there is a specific section in the code where a table is created using select distinct on multiple variables and it seems to run forever when trying to get through this process on a remote computer when I run the program. Is there a better way to re-write the code to make it run faster or do I need to look into different databases other than postgresql that can get the job done?
Create Table Code:
st.execute("CREATE TABLE COMBINEDDATA AS \n" + "SELECT DISTINCT E.DATA1, E.DATA2, E.DATA3, E.DATA4, E.DATA5, E.DATA6, \n" + "E.DATA7, E.DATA8, E.DATA9, E.DATA10, E.DATA11, E.DATA12, E.DATA13, E.DATA14, E.DATA15, E.DATA16, E.DATA17, \n" + "E.DATA18, E.DATA19, E.DATA21, E.DATA26, E.DATA27, E.DATA28, E.DATA29, \n" + "E.DATA30, E.DATA31, E.DATA32, E.DATA34, E.DATA35, E.DATA36, E.DATA37, E.DATA38, \n" + " C.CHAIN20, C.CHAIN33, C.CHAIN22, \n " + "D.DAT2, D.DAT3, D.DAT4, D.DAT7, D.DAT11, D.DAT9, D.DAT5, \n " + "E.DATA39, E.DATA40, E.DATA41 FROM rawData AS E \n" + "JOIN CHAINDATA AS C \n" + "ON E.DATA7 = c.CHAIN2\n" + "AND E.DATA11 = c.CHAIN4\n" + "AND E.DATA21 = c.CHAIN10\n" + "AND E.DATA22 = c.CHAIN11\n" + "JOIN DATDATA AS D\n" + "ON E.DATA7 = D.DAT18\n" + "AND E.DATA11 = D.DAT21\n" + "AND UPPER(E.DATA6) = UPPER(D.DAT17)\n" + "AND UPPER(E.DATA10) = UPPER(D.DAT20)\n" + "AND UPPER(E.DATA5) = UPPER(D.DAT16)\n" + "AND UPPER(E.DATA9) = UPPER(D.DAT19)\n" + "AND E.DATA20 = D.DAT22");