Date:         Tue, 28 Dec 1999 15:21:47 +0200
Reply-To:     Arjen.Raateland@vyh.fi
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arjen Raateland <Arjen.Raateland@VYH.FI>
Organization: Suomen ymparistokeskus
Subject:      Access to SQL Server problem
Content-Type: text/plain; charset=us-ascii

Why would it be that SAS 6.12 (under Windows NT) 'locks up' when
executing a PROC SQL step using ACCESS to SQL Server facilities?

The EXECUTE statements in the program below tell the DBMS to do
something. The only result to be passed to SAS would be an error or
success message. However, SAS becomes very sluggish as long as the
EXECUTE runs as if it's very busy doing something.

When data retrievals that take a long time for the DBMS to run, are
started from a SAS application such sluggishness is a nuisance as SAS
effectively stops responding as soon as the PROC SQL runs. E.g. the SAS
AWS will not be redisplayed after the user has temporarily changed
another Windows application to the foreground.

Other Windows programs are not affected.

Is there something I can do to improve this condition?

The program below is just part of a test comparing different ways of
specifying a retrieval. Using temporary tables is not the most effective
way, I've noticed. However, the other methods lock up SAS just as
effectively for as long as the retrieval runs on the DBMS, even though
SQL Server does all the work (or at least almost all of it).

The last phase, CREATE TABLE WORK.KOE ...., executes very quickly
returning some 14000 rows.

proc sql ;
  connect to sqlservr as pivet_
    (
      server=%sysget(_sql_server)
      database=pivet
    )
  ;
  execute (
            select distinct m.maaritys_id into #maaritys from maaritys m
            where  m.suurekoodi like 'SO4%'
          )
    by pivet_ ;
  execute (
            select distinct n.paikka_id into #paikka_id from
naytteenotto n, vedenlnayte v, vedenltulos t, maaritys m
            where      n.naytteenotto_id = v.naytteenotto_id
                   and v.vedenlnayte_id  = t.vedenlnayte_id
                   and t.maaritys_id in (select * from #maaritys)
          )
    by pivet_ ;
  create table work.koe as
    select *
      from connection to pivet_
           (
             Select p.nimi, a.nro
               from paikka p, hakemisto..vesal a
                 where p.h_vesal_id = a.vesal_id
                   and p.paikka_id in (select * from #paikka_id)
           )
    ;
  disconnect from pivet_ ;
quit ;

Thanks in advance for reading and responding.
--
Arjen Raateland
Finnish Environment Institute
SAS Support
phone +358 9 4030 0350