-
William Beale
- PowerBuilder
- Tuesday, 24 July 2018 08:12 AM UTC
I’m posting to enquire if anyone can help or advise on how to SQL code for a datawindow object using a “NOT EXISTS” or an “EXCEPT” condition – these conditions typically being part of INSERT code whereas datawindow objects are based on SELECT code.
The application has a centralised relational table called ip_to_ip with five columns. It is used to relate unique sets of primary keys of various entity types (people, address, telephone numbers, events etc) with a code value for the type of relationship.
Until now I’ve used a temporary table and a NOT EXIST condition to, first, populate the table as the application goes about randomly generating many thousands of relational rows.
Second, the EXCEPT condition is used to INSERT the accrued relationship data into the DB table to avoid replicating rows once processing has completed and the temporary table is populated.
This worked fine on a local network but a client, who is using a wide area network that can span up to 800 kilometres, encountered performance issues. The ongoing sequential addition of many thousands of relational rows across the network to the temporary table dramatically slowed the application. So, I’m reverting to a datastore.
I was hoping that if AUTOCOMMIT was set to TRUE, a suitably SQL coded datawindow could prevent replicating relational rows that were either in the datastore (without a need to use a FIND loop on the datastore to sequentially troll through accruing rows to avoid replicating rows) or conflicting pre-existing rows in DB table on Update.
For what it’s worth, this is the SQL code string currently used to populate the temporary table with an EXECUTE command …
ls_sql = "IF NOT EXISTS (SELECT project_id, entry_id, ip_no, inv_ip_no, association_type FROM " + is_temp_table_ip_to_ip WHERE project_id = " + STRING(al_projectID) + " AND entry_id = " + STRING(al_entryID) + “ AND ip_no = " + STRING(al_ipno) + " AND inv_ip_no = " + STRING(al_invipno) + " AND association_type = " + STRING(al_associationtype) + ") INSERT INTO " + is_temp_table_ip_to_ip + " VALUES (" + STRING(al_projectID) + ", " + STRING(al_entryid) + ", " + STRING(al_ipno) + ", " + STRING(al_invipno) + ", " + STRING(al_associationtype) +")"
This is the code currently used to INSERT the populated temporary table to the DB …
ls_sql = "INSERT ip_to_ip (project_id, entry_id, ip_no, inv_ip_no, association_type)" + “SELECT project_id, entry_id, ip_no, inv_ip_no, association_type FROM " + is_temp_table_ip_to_ip + " EXCEPT SELECT project_id, entry_id, ip_no, inv_ip_no, association_type FROM ip_to_ip"
I was hoping a suitably SQL coded datawindow could be implemented to, first, avoid replicating rows in the DB table.
There will invariable be replicated rows in the datastore but, in this regard, I’m then unsure of how the dw update works – whether each row is committed to the table with AUTOCOMMIT = TRUE so that the ongoing update will relying on the NOT EXISTS or EXCEPT condition to prevent replicating rows both from the datawindow and the existing rows in the table.
The client is using MS SQL Server 2012. I’m developing with PB 2017 R2.
Many thanks if you can assist.
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.