Code improvement SELECT often same source

[SaschaD]

Hello,

my code below works fine so far, but it takes so much time.

It is possible to write this code more comfortable to speed up?

All lines have the same source.

UPDATE GRV_Export_Dummy D
		SET
			'Supervisor'n = (SELECT 'Supervisor__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Account'n = (SELECT 'Account__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Street'n = (SELECT 'Street__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Country'n = (SELECT 'Country__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Postcode'n = (SELECT 'Postcode__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'City'n = (SELECT 'City__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_start'n = (SELECT 'Contract_start__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_form'n = (SELECT 'Contract_form__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_level'n = (SELECT 'Contract_level__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR)
		WHERE D.Contract IN (SELECT V.VTGONR FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR);

Thanks,

Sascha

[Shmuel]

What is the full code of this updating step ?

May be it can be rewritten using different procedure ?

It seems to me that the bottle neck is I/O, network and system overhead, connecting to server.

Describe the environment you work on.

[Kurt_Bremser]

---

@SaschaD wrote:

Hello,

my code below works fine so far, but it takes so much time.

It is possible to write this code more comfortable to speed up?

All lines have the same source.

UPDATE GRV_Export_Dummy D
		SET
			'Supervisor'n = (SELECT 'Supervisor__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Account'n = (SELECT 'Account__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Street'n = (SELECT 'Street__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Country'n = (SELECT 'Country__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Postcode'n = (SELECT 'Postcode__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'City'n = (SELECT 'City__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_start'n = (SELECT 'Contract_start__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_form'n = (SELECT 'Contract_form__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_level'n = (SELECT 'Contract_level__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR)
		WHERE D.Contract IN (SELECT V.VTGONR FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR);

Thanks,

Sascha

---

Use a data step hash object to load the secondary table:

data GRV_Export_Dummy_new;
set GRV_Export_Dummy;
if 0 then set SERVER.VIEW_TABLE (drop=VTGONR); /* sets variable attributes */
if _n_ = 1
then do;
  declare hash lookup(dataset:"SERVER.VIEW_TABLE (rename=(VTGONR=Contract))");
  rc = lookup.definekey("Contract");
  rc = lookup.definedata(
    "Supervisor__c","Account__c","Street__c","Postcode__c","City__c",
    "Contract_start__c","Contract_form__c","Contract_level__c"
  );
  rc = lookup.definedone();
end;
if lookup.find() = 0
then do;
  Supervisor = Supervisor__c;
  Account = Account__c;
  Street = Street__c;
  Country = Country__c;
  Postcode = Postcode__c;
  City = City__c;
  Contract_start = Contract_start__c;
  Contract_form = Contract_form__c;
  Contract_level = Contract_level__c;
end;
drop
  Supervisor__c
  Account__c
  Street__c
  Country__c
  Postcode__c
  City__c
  Contract_start__c
  Contract_form__c
  Contract_level__c
  rc
;
run;

[Patrick]

Isn't that just a case of "update table with values from another table"? If so then SQL code could look like below:

proc sql;
  UPDATE GRV_Export_Dummy D
    SET
      d.Supervisor = v.Supervisor__c,
      d.Account = v.Account__c,
      ....
    from GRV_Export_Dummy D, SERVER.VIEW_TABLE V
    WHERE D.Contract = V.VTGONR
  ;
quit;

IF table SERVER.VIEW_TABLE is big and table GRV_Export_Dummy is not on the same server then a multi step approach would likely perform better. ....but you would need to tell us a bit more what you're dealing with - volumes included.

[SaschaD]

Hi Patrick,

You are right, its just an update from another table.

SERVER.VIEW_TABLE has 10.000 lines / DUMMY 6.000 lines

Both tables on the the same Server.

 I tried the Code which you have posted, but I got the error below:

141        proc sql;
142          UPDATE GRV_Export_Dummy D
143            SET
144              D.Supervisor= v.Supervisor__c
                  _
                  73
                  76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

CODE:

proc sql;
  UPDATE GRV_Export_Dummy D
    SET
      D.Supervisor= v.Supervisor__c
    from GRV_Export_Dummy D, SERVER.VIEW_TABLE V
    WHERE D.Contract= V.VTGONR
  ;
quit;

Regards,

Sascha

[Kurt_Bremser]

SQL is not really good at this, because it syntactically needs a sub-select for every variable.

Since your datasets are really TINY, the hash approach should work, barring any syntax error caused by unavailable example data.