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.
Accessing few records from a bulk of lakhs of records from a
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
-
- Participant
- Posts: 50
- Joined: Fri Dec 21, 2007 3:24 am
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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.
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.
Your use of a function against Col2 in the filter means any index over Col2 cannot be used.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers