Desktop productivity for business analysts and programmers

How to take advantage of SQL table indexes?

Reply
Occasional Contributor
Posts: 9

How to take advantage of SQL table indexes?

I have a set of SQL tables with defined indexes on an iSeries. The DB2 library they are in is defined to SAS and I can access the data. When I use PROC SQL statements to query the data, I can see that the DB2 engine on the back-end is able to use the correct index on the SQL table to access the data. However, when I switch over to EG4 and use the query builder the process seems to ignore the indexes and reads through the entire table. Do I need to identify the indexes to EG or is there a way to force EG to make use of the indexes?

Thanks!
Trusted Advisor
Posts: 2,114

Re: How to take advantage of SQL table indexes?

Have you tried to define a LIBNAME explicitly for the database? If you just use Open --> Data and select a data source, EGuide generally brings all the data to the client and then sends it to the SAS server for processing.
Community Manager
Posts: 2,887

Re: How to take advantage of SQL table indexes?

The query builder also generates PROC SQL code. We would have to compare what EG generates with your hand-written code to see what the difference might be.

The EG query builder does not currently leverage the index information when you design the query, but the PROC SQL that results is processed by SAS (and in turn, by the database), so the indexes should be used as appropriate.

Chris
Ask a Question
Discussion stats
  • 2 replies
  • 97 views
  • 0 likes
  • 3 in conversation