Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Use of NOT EXISTS or EXCEPT in datawindow SQL

  1. How-to
0
Votes
Undo
  1. William Beale
    William Beale
  2. PowerBuilder
  3. 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.

 

Distributed power builder aplication
Sybase ODBC connect on Powerbuilder 2017 R2
Responses (4)
Bernhard Giesriegl
Bernhard Giesriegl Accepted Answer Pending Moderation
  1. Monday, 30 July 2018 14:01 PM UTC
  2. PowerBuilder
  3. # 1

Hi William,

 

did you think about using

SELECT T2.ID FROM T2 LEFT OUTER JOIN T1 ON T2.T1ID=T1.ID WHERE T2.T1ID=NULL

(Relation: T2.T1ID relates to T1.ID)

instead of NOT EXIST?

Could be much faster for the SQL Server.

 

Regards,

Bernhard

Comment
William Beale
  1. William Beale
  2. Tuesday, 31 July 2018 07:30 AM UTC
Thanks Bernhard - I'll have a play with this
  1. Helpful
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Friday, 27 July 2018 15:49 PM UTC
  2. PowerBuilder
  3. # 2

PowerBuilder v12.1, build 7055
Oracle 12C


I work with Oracle SQL regularly, and as you pointed this out in your initial post I've also found that in many cases the way the SQL is written has a profound effect on its performance. 

In my experience, NOT and EXISTS are both grouped functions and as such are best used in the WHERE clause of a SQL statement. In many cases, they can be replaced with other logic that accomplishes the same task.

Is there a way to rewrite your SQL? Maybe something like this:

// Assuming the temp table has these five colulmns only
// ----------------------------------------------------
INSERT INTO is_temp_table_ip_to_ip
SELECT S.project_id, S.entry_id, S.ip_no, S.inv_ip_no, S.association_type
FROM    source_table S, is_temp_table_ip_to_ip P
WHERE  S.project_id          = " + S.al_projectID
AND      S.entry_id             = " + S.al_entryID 
AND      S.ip_no                 = " + S.al_ipno 
AND      S.inv_ip_no           = " + S.al_invipno 
AND      S.association_type = " + ST.al_associationtype
WHERE NOT EXISTS (select project_id, entry_id, ip_no, inv_ip_no, association_type
                               from   P
                               where S.project_id = P.al_projectID
                               and    S.entry_id = P.al_entryID
                               and    S.ip_no = P.al_ipno
                               and    S.inv_ip_no = P.al_invipno) ;


// Syntax not guaranteed in above example!  :) 

Comment
William Beale
  1. William Beale
  2. Monday, 30 July 2018 07:56 AM UTC
Thank you Olan. My ultimate objective was to see if I could get the INSERT / NOT EXIST as the SQL code to a datawindow object, but when I've tried this I get the error that a SELECT is required. Changing the datawindow SQL dynamically, as the function name, 'SetSQLSelect', also requires a SELECT as the function name indicates. At some internal point within the datawindow it converts the SELECT to an INSERT or UPDATE on an Update() but I don't know if or how one can 'hack' this internal conversion.
  1. Helpful
There are no comments made yet.
Markus Eckert
Markus Eckert Accepted Answer Pending Moderation
  1. Thursday, 26 July 2018 15:15 PM UTC
  2. PowerBuilder
  3. # 3

Hi William,

Have you considered using a Stored Procedure for the Insert? (Rows->Stored Procedure Update)

Your Stored Procedure could then check if a value exists before inserting so you don't get duplicates.

Regards,
Markus

 

Comment
William Beale
  1. William Beale
  2. Friday, 27 July 2018 07:02 AM UTC
Thanks for the interest, Marcus ,it is another option I'll need to consider along with the suggestion of Chris Pollock.

A difficulty I have is that several of my clients have little in the way of IT support, so asking all clients to add stored procedures could be a challenge for some. While I code to effect database changes dynamically in each new version of my application, as far as my research and limited knowledge goes, I do not know how, or if, stored procedures can be added dynamically to the DB from the application.



In addition to considering the advice kindly offered so far, what I'm currently testing to avoid replicating rows already in the DB is to trap any the 'violation of primary key’ error in the dberror of the pfc datastore object, n_ds, then get the problem row number, and set its itemstatus to Datamodified!, and then re-process.



While this is probably a dirty solution, so far it is testing ok. Because there does not seem to be a way to manipulate the SELECT SQL code required by a datawindow to an INSERT with NOT EXISTS, I might have to proceed to use it.

  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 24 July 2018 15:33 PM UTC
  2. PowerBuilder
  3. # 4

Hi William;

  FWIW: It sounds like an SQL Server issue to me - maybe even in the way your DB Schema was designed. Have you attempted to do any performance monitoring on the DML interactions between your PB App and SS and also within SS as well when these SQL statements are executed?

   Moving from a LAN to a WAN should only affect the performance if large result sets are being exchanged between the PB Apps and SS. If this is not the case, then I would focus on the SS instance that that customer is using as being the "suspect".

HTH

Regards ... Chris

 

Comment
William Beale
  1. William Beale
  2. Wednesday, 25 July 2018 02:03 AM UTC
Thanks for your interest, Chris, I'll pass these comments on to my client as I'm unable to do on-site testing.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.