Network runs slowly when using odbc

[couchcrusader]

Hello,

I just got a complaint from a co-worker that I'm slowing down the network too much while using SAS. I am using the following syntax which only took 1 minute to run.

libname xxxxDB ODBC dsn=dsnname user=me password=password schema='dbo';

proc sql;
create table appts_show as
select xa1a.time_start, xa1a.service_unit
from xxxxDB.XA1_appointment xa1a
where xa1a.time_start>='1jan2010'D
quit;

Which statement above was causing the problems on the network? Does anybody have any suggestions about how I should program this to avoid causing problems for everyone else? How does the libname statement work? Would it be keeping the database in some kind of "open" state making the network slow for others, or does the database get closed at the end of the libname statement? Message was edited by: couchcrusader

[couchcrusader]

Hello,

I am not new to SAS, but I am new to the idea of using SAS to access SQL files. I have never worked with SQL itself either. I am using SAS version 9.1.3 ... So I was just accessing a large database using the following line of code:

libname xxxxDB ODBC dsn=dsnname user=me
password=password schema='dbo';

This worked fine to read in the SQL file and allows me to see all the data tables in it and what variables are in each table, etc. There are about 90 datatables which each have 10-20 variables.

[Cynthia_sas]

Hi:
The documentation shows the use of CLEAR to clear a librref and close the connection to the database:
http://support.sas.com/documentation/cdl/en/acpcref/61891/HTML/default/a002644677.htm#a002657071

Quote:
"To disassociate or clear a libref, use a LIBNAME statement, specifying the libref (mypclib, for example) and this CLEAR option:

LIBNAME mypclib CLEAR;You can clear a single specified libref or all current librefs.

SAS/ACCESS disconnects from the data source and closes any free threads or resources that are associated with that libref's connection."


So, it wouldn't hurt to clear the xxxxDB libref at the end of your SQL step. Otherwise, you might want to open a track with Tech Support and see whether they can help you with some performance diagnostics. To open a track with Tech Support, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem". Alternately, you can go directly to the Tech Support Problem Form here:
http://support.sas.com/ctx/supportform/createForm

cynthia