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 |
|
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 |
|
|
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
|
|
|
|
|
|
|
SORT Transformation is very slow