Your SAS programs, embedded in web apps and elsewhere

Network runs slowly when using odbc

Reply
New Contributor
Posts: 3

Network runs slowly when using odbc

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
New Contributor
Posts: 3

Re: Network runs slowly when using odbc

Posted in reply to 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.
SAS Super FREQ
Posts: 8,869

Re: Network runs slowly when using odbc

Posted in reply to couchcrusader
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
Ask a Question
Discussion stats
  • 2 replies
  • 200 views
  • 0 likes
  • 2 in conversation