Turbo Charge Your Queries !

Rules of Thumb for Faster Queries:

1) Select from the file where the data lives.

SELECT STUDENTS WITH STU.ACTIVE.DEGREES = 'BA'

SELECT ACAD.CREDENTIALS WITH ACAD.DEGREE = 'BA'

2) Use Saved List Algebra (SAAL) instead of complex Queries

3) With Multiple Selects – use Data and then Virtual Fields

4) With Multiple Selects – Use the one that results in smallest results first.

SELECT STUDENT.ACAD.CRED WITH X.STC.ENROLL.STATUS = ‘CRS’

SELECT STUDENT.ACAD.CRED WITH STC.COURSE.NAME = ‘GENS-146’

SELECT STUDENT.ACAD.CRED WITH STC.COURSE.NAME = ‘GENS-146’

SELECT STUDENT.ACAD.CRED WITH X.STC.ENROLL.STATUS = ‘CRS’

** Both bring back 1313 records – the second is approx. 7 X faster

5) With Multiple Selects – Combine in one select (using AND)

SELECT STUDENT.ACAD.CRED WITH STC.COURSE.NAME = ‘GENS-146’ AND X.STC.ENROLL.STATUS = ‘CRS’

6) Use And instead of With

SELECT STUDENT.ACAD.CRED WITH STC.COURSE.NAME=’GENS-146’

WITH STC.TERM=’2002SP’

SELECT STUDENT.ACAD.CRED WITH STC.COURSE.NAME=’GENS-146’

AND STC.TERM=’2002SP’

** Both bring back 390 records – the second is approx. 40% faster

Special Thanks to Don Prezioso of Baldwin-Wallace Collage for his 2002 DUG presentation!

Ó Copyright 2002