Can I get an explain plan from Enterprise Guide before submitting, so I can see what will actually get passed to database?

[jwycoff]

We are still tuning our Teradata system, and occasionally, a user will trash it without knowing, by submitting a simple request via Enterprise Guide.

We remembered that putting obs=0 in our SAS code

(under tools--> Options --> SAS programs --> Checkbox Submit SAS code when server is connected) will give us a nice syntax check.

Can we combine that with some version of

sastrace=',,,ds' sastraceloc=saslog nostsuffix sql_ip_trace=source msglevel=I DBIDIRECTEXEC; run;

to actually see what gets passed, before we actually pass it (and crash it)?

Thx

[ChrisHemedinger]

If you're using the SAS Enterprise Guide Query Builder and you want to see how the query might get passed, you can get some feedback in the "Validate Query" results.  This should tell you how/if your query is going to trigger pass-through to the database.  Note: you'll see pass-through messages only if something interesting is happening -- which are typically triggered by JOINs.  No news is good news, in this case.  SAS Enterprise Guide uses a combination of sastrace options to gather the messages.

You can also control some of this by using explicit pass-through mode.  To learn more, check Help->SAS Enterprise Guide Help, and search the index for "explicit pass-through mode".  The topic leads you through the steps.  Explicit pass-through will cause the Query Builder to generate syntax like:

PROC SQL;

   CONNECT TO ODBC as con1  (datasrc="mydb");

   CREATE TABLE WORK.out AS

   SELECT *

      FROM CONNECTION TO con1 (

      SELECT "t1"."activityID",

          "t1"."objectType",

          "t1"."objectID",

          "t1"."containerType",

          "t1"."containerID",

          "t1"."activityType",

          "t1"."userID",

          "t1"."creationDate"

         FROM "activity" "t1");

   DISCONNECT FROM con1;

QUIT;

This guarantees that your query will push down to the database, preventing excess data from being brought back into your SAS session.  In SAS Enterprise Guide 7.1, there is a new option that allows you to keep the results inside the database (if that's what you want), further saving on expensive I/O.

Chris

[Doc_Duke]

"trash it" is not very well defined.  There are lots of ways to write technically correct code that will cause a database server to have problems.  Chris mentioned one (writing the query so all the data must be passed to the SAS server from the Teradata server). 

Another one is to write a JOIN that is a Cartesian product (in database terms a "full outer join").   I don't think that SAS will give any sort of warning on that, as it is a legitimate query in certain situations.  In addition to I/O time, that one can fill up a disk drive in a hurry.

Yet another is to do a join using a field that is not indexed.  The database typically has to build the index, do the join, and then delete the index.  It can take a lot of time for big tables.  I'm not sure how Teradata handles that, but it can really slow down an Oracle query.  Sometimes the database can't even build an index and has to slog through multiple tables over and over again to deliver the results (">" or "<" in a join can cause this).

SAS provides us with lots of power and EGuide can help by writing technically correct code, but the user still has to think about what he or she is doing.

Doc Muhlbaier

[jwycoff]

Ok, So when I go to "modify query builder" and click Tools, the "Validate Changes Now" is greyed out. Automatically validate changes is NOT clicked. So I should at least select Automatically validate changes?  I tried making some changes to see if it would become active. Did not become active.

[jakarman]

A lot of Teradata can be found at SAS site like: https://support.sas.com/resources/papers/TroubleshootingSASandTeradataQueryPerformanceProblems.pdf

The explain command is missing in the ANSI standard. That one at theTD will teel you how TD made his plan for the work of the SQL.

The SAS Proc SQL translation is done different by the SAS/Access interface and not really predictable. Some effects of moving data to SAS or doing record updates are however logical ones. It should be helpful understanding some of the basics

With explicit SQL you are using Eguide only for a way of coding, that is Not really integration. The query builder of EGuide is very basic and not really good for advanced queries. In those cases I hope the user can code SQL and doing a better job as the gui interface.

You could use generating  SQL views (it is a setting in Eguide). The obs=... of SAS is translated (at least before last fixes) to TD Sample setting.
Functional those are the same in limiting the number of records.  

However this (obs=..  to sample ..) does not work when using datasteps or datastep views. You should use that with care understanding the impact.

The sample of CH of using explicit pass through is an invalid one. It is one still using the SAS SQL only an additional connection is being made.

See: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (TD specifics) for a correct approach that Eguide QB should do.

Setting mode=Teradata instead of mode=ansi will avoid the need of those commits. 

Take notion as with the DBIDirectexec setting active the generated SQL can traverse schema-s/databases and wiht that ignoring all libname settings.  

[jakarman]

The Eguide query builder only will validate sas sql not teradata sql. The translation between those is done by the sas access to teradata.  That one is having special functionality. It will take a loooooong time before that is solved in eguide.

The grayed out behavior can be caused by readonly setting of the eg project. An other cause can be the unavailability of the source data. You must the appserver and library active open.

Changing the appserver can be a problem