Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2008-10-20 : 04:46:43
|
| Hi All,Hi Pals,I have query which does the aggregations and loads data into a datawarehouse table.If i run the query in SSIS Package in BIDS then it is taking 30 mins.Is there any difference if we run the query from Query Analyser and Bids Application?Any help would be appreciated.Thank You. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-20 : 05:14:46
|
| Did you try running it in Query Analyser? how long did it take? |
 |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2008-10-20 : 08:22:39
|
| I got the point. SELECT inside the query Analyzer is a simple SELECT.But whereas within the SSIS package it is acting as input for BULK INSERT (i.e SELECT INTO ...) and within the data flow task i am also using a Script component to modify each row coming from the Source Connection Manager and moreover there are 6 indexes on the table.so i dropped the indexes and tried to run the ETL and then worked fine. Previously with indexes it took me 30 mins and after dropping the indexes it took me 6 mins.Again i re-created all the indexes on the destination table.Thank You! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-20 : 08:37:09
|
quote:
Originally posted by frank.svs
I got the point. SELECT inside the query Analyzer is a simple SELECT.But whereas within the SSIS package it is acting as input for BULK INSERT (i.e SELECT INTO ...) and within the data flow task i am also using a Script component to modify each row coming from the Source Connection Manager and moreover there are 6 indexes on the table.so i dropped the indexes and tried to run the ETL and then worked fine. Previously with indexes it took me 30 mins and after dropping the indexes it took me 6 mins.Again i re-created all the indexes on the destination table.Thank You!
yeah...thats understandable. presence of lots of indexes can slow down bulk inserts as it has to update index informations also. so its better to drop it before insertion and recreate afterwards. |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2008-10-20 : 08:46:31
|
| Thank You. |
|
|
|
|
|
|
Query Tuning!