SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 SORT Transformation is very slow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

karmeeabi
Starting Member

India
18 Posts

Posted - 07/11/2013 :  09:11:17  Show Profile  Reply with Quote
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

Sweden
30277 Posts

Posted - 07/11/2013 :  11:18:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/11/2013 :  11:58:12  Show Profile  Reply with Quote
Duplicate of

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wowguide
Starting Member

3 Posts

Posted - 08/04/2013 :  03:50:03  Show Profile  Reply with Quote
unspammed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/05/2013 :  02:36:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000