accesing data without importing table definition in DS

[ak77]

Hello everybody,

I made a change to an existing job
This job has a OCI stage which fetches data from the join of 2 tables
This took like 5 hrs for me to run
I added another table making this a three table join but forgot to import the table definition of this new table
FYI, i am using a User_defined SQL

this one took near about 20 hrs

Was breaking my head why this was hapening as this table is much smaller when compared to others

Atlast found this mistake when trying to break the job into smaller modules

Now the question is how it affects the processing if the table definition is not in the DS-Repository?

Any insight will be appreciated?

Thanks
Kishan

[Bala R]

Before that, did you have a chance to estimate the cost of the query using toad or quest central when you added a third table to the join?

[Bala R]

Before that, did you have a chance to estimate the cost of the query using toad or quest central when you added a third table to the join?

[chulett]

Now the question is how it affects the processing if the table definition is not in the DS-Repository?

It doesn't. Look elsewhere for your performance difference.

[vmcburney]

To expand on Craig's answer, the table definitions are really just a design tool, when the job is running it only looks at the local job column definitions. You need to compare the cost on the user defined SQL of adding the third table, whether database optimisation will improve this SQL, or whether the third table needs to be added to the job as a hash file lookup instead.

[ak77]

Thanks for the Immediate response Bala,

I didnt do any cost estimation before the first run

Since this job was taking long time to finish, I was running the estimate plan to find where I was having problem
Then I was trying to change the design to accomodate this table when i found this mistake

Now it seem to be running faster when I have the table definition in the repository

I was just wondering how this works

Thanks

Regards,
Kishan

[ray.wurlod]

When the job starts, the stage sends the SQL (whether generated or user-defined) to the database server to be "prepared". This means that the database server prepares its execution plan based on available indexes, current load and whatever other factors might be involved. A request is then sent to the database server to execute the prepared SQL. How long this takes is totally and solely dependent on the SQL query itself; whether the table definitions are in the repository or not makes no difference whatsoever. The database server stores the result set from the query, from which the stage fetches one row (or N rows, depending on buffering) at a time.

I think your observation was caused by other factors, perhaps a lighter load on the machine at the time when you ran with table definitions having been imported.

[vmcburney]

Some database engines optimise queries that are used more then once and you can find it runs a lot faster with repeated use.

[ak77]

Thanks everybody,

I got the cost from explain plan and tried to optimise the query but its still slow
I am dealing with two tables with 20 M records joined with a table with 300 records

Since this table is small, I will use a hash lookup rather than direct join

Thanks again
Kishan

[srinagesh]

Hi !

I tried doing the same mapping in two ways..

1. By using custom SQL for the Source
2. By using SQL builder for the source

It was a load with 1 lookup (hash file).
Source (oracle) table has 27,00,000 records.

It took considerably more time when I used Custom SQL than it took when I used SQL builder.

I checked the plan tables in both the cases and it was same and the Oracle server was running this job only.

It is bit strange to note the performance improvement when SQL Builder is used...

[ray.wurlod]

Which test did you run first? What steps did you take to control for the effects of caching - that is, the required rows being cached somewhere in the database server?