DSXchange

Hi,

could any one help me understand ,what should be taken care while accessing few records from a bulk of lakhs of records from a table using ODBC stage.

As when i tried to SELECT on a table with lots of records in it
the view data takes lots of time making the job get hanged.

Please suggest.
Also once gave timed out error.

use head stage or tail stage

Dont hit view data, if you know that your job is going to take a lot of time retrieving the results. "The job hangs" is nothing but a mirage and in reality, its struggling to select the records for your display.
Instead, run your job. This happens especially when you have custom sql with group by's and joins where the keys are not indexed are are just a part of a composite indexed key.

Your job will experience the same delay as View Data while the database is assembling the result set. During this time the stage status (as shown in Monitor) will be "Starting".

Hi,

The query i am using is

SELECT Col1 from Table1 where ADD_MONTHS(Col2,6) <= SYSDATE ."
Where Col2 can be null for mannny of the records,

This query is taking lots of time...Where as

"Select Col1 from table1" takes lesser time for view data however...

this query reads alll the records from the table along with the one which are not required for processing.

ray.wurlod wrote:Your job will experience the same delay as View Data while the database is assembling the result set. During this time the stage status (as shown in Monitor) will be "Starting". ...

Nature of the beast. Your 'lesser time' example can return records right away because it selects all records. The first example needs to filter the records you want from the same full scan of the table.

Your use of a function against Col2 in the filter means any index over Col2 cannot be used.