SORT Transformation is very slow

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.

All Forums

SQL Server 2008 Forums

High Availability (2008)

SORT Transformation is very slow

Author

Topic

karmeeabi
Starting Member

18 Posts

Posted - 2013-07-11 : 09:11:17

Hi, i am currently trying to sort the data using Sort Transformation for a database which has 23,00,000 ( 23 Lacs) data point. below mentioned are the steps i have done so far.

Create a DFT
OLE Database Source - Selected the particular Database
Sort Transformation - Session_ID Data type : (Numeric)& Line_Time data type : (Datetime)
OLE Database Destination - Created a new table

also i tried to used the show advance editor and
ISSORTED as True
Session_ID 1
Line_time 2

but that is not sorting properly hence have choosed the sort transformation option.

currently Source table is completed and showing in GREEN
Sort Transformation is in YELLOW
Destination table is still in WHITE

nothing has moved so far

Missed to say one thing

Right clicked on the DFT clicked on Properties
Default buffer Max Row as 10000 records
Default buffer size changed to 104857600

from 15:54:12
till 18:39:00

need help to fix this . please help me.

Regards,
Karthik Shankaran (KS)
+91-9940074724

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-11 : 11:18:46

It means sorting the 2.3 millions rows are expensive and time consuming. There is no other option than wait.
Unless you can sort the data already at the source?

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 11:58:12

Duplicate of

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186754

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wowguide
Starting Member

3 Posts

Posted - 2013-08-04 : 03:50:03

unspammed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 02:36:46

quote:
Originally posted by karmeeabi

Hi, i am currently trying to sort the data using Sort Transformation for a database which has 23,00,000 ( 23 Lacs) data point. below mentioned are the steps i have done so far.

Create a DFT
OLE Database Source - Selected the particular Database
Sort Transformation - Session_ID Data type : (Numeric)& Line_Time data type : (Datetime)
OLE Database Destination - Created a new table

also i tried to used the show advance editor and
ISSORTED as True
Session_ID 1
Line_time 2

but that is not sorting properly hence have choosed the sort transformation option.

currently Source table is completed and showing in GREEN
Sort Transformation is in YELLOW
Destination table is still in WHITE

nothing has moved so far

Missed to say one thing

Right clicked on the DFT clicked on Properties
Default buffer Max Row as 10000 records
Default buffer size changed to 104857600

from 15:54:12
till 18:39:00

need help to fix this . please help me.

Regards,
Karthik Shankaran (KS)
+91-9940074724

Sort tranformation is a fully blocking transform and obviously will cause performance issues especially for large datasets.
If your source is database why cant you sort and bring the data from db itself by using ORDER BY in the query? Once you do that you can take advanced editor of the source task and in properties mark IsSorted property as true and set sortkey for columns used in ORDER BY as 1,2 etc in the same order as you've specified them in ORDER BY. Once you do this you can drop the sort transformation altogether and you should get better performance from the package while executing.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources