Job is executing for long

[Latha1919]

Hi,

I have a simple job having two ODBC stages as source and target respectivly along with one transformer stage.

Within the ODBC source stage, in the outputs tab, I have select as: Native DB query and entered the query under SQL query tab. I am writing this data to a table in the ODBC target stage. When I execute the SQL query in the database its completing in 2min or so. However, for the job to complete the run, it's taking more than 30min. I am not transforming the data also.

Why is taking so long to complete.

Please advise.

Thanks

[meena]

Hi,
What is your database?

[Latha1919]

SQL Server.

[kumar_s]

Is your both source and target are in same database?
What difference it makes if you replace a sequential file instead of the target database stage?
Check if any other lock been held while you running the job.

[Latha1919]

Hi,

Both source and target are in the same database - SQL server.
Secondly, I cant use Target as sequential file stage since target table is further used as a source in other jobs.

Thanks,

[sb_akarmarkar]

Hi Latha,

What is the update action you are keeping in your target ?
If mostly update is happing for you then you must go a head with Update/Insert and if there is index on your where clause column of target that will improve performance lot....

Thanks,
Anupam

[Latha1919]

Hi Anupam,

Table is having only four columns.

Update action is : Insert rows without clearing.
Data located on File Group: Primary
clustered, unique, primary key located on PRIMARY
Index_keys: All the four columns of the table.

Thanks,

[thumsup9]

Hi,

When I execute the SQL query in the database its completing in 2min or so. However, for the job to complete the run, it's taking more than 30min.

Thanks

Hi,

Whats the SQL query you are running in the database, is it an Insert SQL or a Select clause.

I am trying understand, if its a simple select on source and an Insert on target or is there any more conditions involved in your job. Also , I assume you must have tweaked, Array size and Transaction Size to check the result.

[Latha1919]

Hi,

Query is a select statement, which will fetch the data from nearly 5 tables. Two table have data upto 4 millions. But there is lot of time variation in executing in the database directly and job run time.

Secondly, would you please through some light on: Array size and Transaction Size to check the result.

Thanks,

[ameyvaidya]

Is your both source and target are in same database?
What difference it makes if you replace a sequential file instead of the target database stage?
Check if any other lock been held while you running the job.

Latha,when Kumar asked to replace the target database with a sequential file, it was only as a temporary debugging change and not a design suggestion. That was to check which stage is responsible for if if the delay is caused by the source or the target. The Sequential file avoids 2 things: Database Overhead and the Network overhead (If the DB server is separate from the DS Server) and so can be used to check where the bottleneck is. A copy of the job can be created for testing.

IHTH

[kumar_s]

Amey Vaidya - Thanks for explaining.

[Latha1919]

Hi,

I will work on this and let you know.

Thanks,