SQL update statement efficiency dilema

[SushilNayak]

Hi All,
I was trying to update DB2 table update using PROC SQL pass through facility. I had a temporary table whose data i needed to refer to update my table. Now when the rows in temp table were like 40-60 rows with the indexed primary key ( ppt in our case) , the updation went perfectly fine and within less than 2 CPU minute the updations were in place. But when the data on the temp table increased to 2000+ rows with the indexed primary key ( ppt in our case) started taking more than normal time. As i was working on mainframes so TIME=30 caused my job to abend because of time out (SEC6). When I asked one of my seniors in the office, he said i need to restructure my query as my query is not that efficient and hence was taking longer than usual time. Below is what my code looked like.

update &database..updating_table a
set var='newvalue'
where a.ppt exists ( select 1 from mytemp.table x
where x.ppt=a.ppt
and x.var=a.var
and x.somecondition=a.somecondition
)

I tried restructuring the code ( had no clue what to restructue, i think i was probably not convinced or understood what difference it would make), eventually did got his help to restructure the sql , but the code still did not work with TIME=30. As the restructuring didn't help so i took my original code and increased the time to a good number. The update did got completed but with increased time. The project is over, but I still don't understand and have this question still lingering in my head, if restructuring the above sql could decrease the time. and is the above code really inefficient and if it is then what should be the code like for such case.

Thanks!

[sbb]

With SAS ability to access your DB2 table, I recommend building a SAS file from the table, selecting only the columns needed. Then, perform your SQL code to identify the rows needing update, then follow that code with a DB2 table update, considering any table/column serialization necessary to execute the entire process. Also, have you considered any performance impact when there are multiple rows meeting the sub-query " select 1 from", such that you may not need to process each row from mytemp.table - possibly to consider DISTINCT in a select, for efficiency (a consideration since we do not see the code used to build mytemp.table)?

Bottom-line - suggest you re-think the problem and your intended solution to derive a more efficient processing flow, only accessing DB2 resources minimally when necessary, while performing as much processing within the SAS environment.

Also, your TSO (mainframe - interactive) session will likely not be getting the expected service, and so you should consider converting the task to a batch TSO request instead, if the processing time duration is a factor when accessing the DB2 resources for serialization.

Scott Barry
SBBWorks, Inc.

[SushilNayak]

Hey Scott,
Thanks for looking into my problem. Really appreciate the answer but im not looking for an alternative solution to what i have coded. I wanted to knw about the piece of code written in the SQL which uses the pass through facility technique specified in my original post. The temporary table that you asked about is table with unique primary key created with no issues and Im executing the code using a Batch job.

Thanks!

[sbb]

You may want to consider these SAS support http://support.sas.com/ website resources you may find useful with debugging the pass-through performance, otherwise consider contacting SAS support by opening a track at the support for further help and guidance.

Google advanced search argument:
db2 pass through performance site:sas.com


Scott Barry
SBBWorks, Inc.

[LinusH]

If I understand this right, this is SQL entirely runs in DB2, so I don't think it's fair having SAS tech support try to resolve performance issues within DB2...
In SAS (SQL and data), by indexing the temp table usually makes this type of queries to run in a descent period of time.

I like SQL a lot, but updating a master table using a transaction table is not what SQL is best at. I many situations I found that data step with modify by outperforms the SQL counterpart.

/Linus