Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why not processing any records further? Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 5:38 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
Hi Friends,

I have come up with a weird problem....what i do is very straight forward process...
i have a table containing 4.2 million records and there is a column name called SeqNo which is '1' (for all the rows)....
what i am trying to do is inserting '0' as SeqNo into the table..

source as well as destination is same table....
4.2 million records having Sequence number 1 and now i again try to insert all the records with Sequence number 0..

In the data flow , OLEDB starts to process 50,000 records but it is not going further....it remains still....i don't know what is the problem and why it is not processing further.....the tasks are not becoming red ,and it still maintaing yellow color since it is not processing further for more than one hour....
Help me firends....


Thanks,
Charmer
Post #1346487
Posted Friday, August 17, 2012 6:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 04, 2014 5:29 AM
Points: 967, Visits: 396
Are there any locks in evidence on the source or destination tables?

It might be worth trying to reduce the transaction batch size - the number of updates / inserts that are queued before being commited.



Post #1346498
Posted Friday, August 17, 2012 6:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 6,778, Visits: 12,885
Are you inserting new rows into a table or updating existing rows? It's not clear from your description.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1346510
Posted Friday, August 17, 2012 7:06 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
ThomasRushton (8/17/2012)
Are there any locks in evidence on the source or destination tables?

It might be worth trying to reduce the transaction batch size - the number of updates / inserts that are queued before being commited.


source and destination is same table...
there are no locks....


Thanks,
Charmer
Post #1346519
Posted Friday, August 17, 2012 7:08 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
ChrisM@Work (8/17/2012)
Are you inserting new rows into a table or updating existing rows? It's not clear from your description.


yes i am inserting new rows by taking all the rows which is already inserted with modified SeqNo....


Thanks,
Charmer
Post #1346520
Posted Friday, August 17, 2012 7:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 6,778, Visits: 12,885
Charmer (8/17/2012)
ChrisM@Work (8/17/2012)
Are you inserting new rows into a table or updating existing rows? It's not clear from your description.


yes i am inserting new rows by taking all the rows which is already inserted with modified SeqNo....


Why not do it by the simplest and most transparent method available to you?

INSERT INTO Yourtable (columnlist)
SELECT Columnlist FROM Yourtable

Hardcode seqno in the SELECT list.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1346526
Posted Friday, August 17, 2012 7:50 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
ChrisM@Work (8/17/2012)
Charmer (8/17/2012)
ChrisM@Work (8/17/2012)
Are you inserting new rows into a table or updating existing rows? It's not clear from your description.


yes i am inserting new rows by taking all the rows which is already inserted with modified SeqNo....


Why not do it by the simplest and most transparent method available to you?

INSERT INTO Yourtable (columnlist)
SELECT Columnlist FROM Yourtable

Hardcode seqno in the SELECT list.


but i have to do in SSIS only....that's why...if we do in data flow task, it would be more tranparent right? or else i have to use SQL task it not works...


Thanks,
Charmer
Post #1346550
Posted Friday, August 17, 2012 8:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:44 PM
Points: 22,525, Visits: 30,290
Two questions.

First, why are you copying the records with a different sequence no (really just curious about the business requirement)

Second, why does it have to be done in SSIS? A simple T-SQL statement can do it just as easily. The only thing that bothers me is that you are doubling the number of records in the table, and that could explode you t-log on the table.

There are ways to manage this possibility, but it means knowing more about the data and the table in particular (unique or primary keys to be exact).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1346584
Posted Friday, August 17, 2012 8:42 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
Lynn Pettis (8/17/2012)
Two questions.

First, why are you copying the records with a different sequence no (really just curious about the business requirement)

Second, why does it have to be done in SSIS? A simple T-SQL statement can do it just as easily. The only thing that bothers me is that you are doubling the number of records in the table, and that could explode you t-log on the table.

There are ways to manage this possibility, but it means knowing more about the data and the table in particular (unique or primary keys to be exact).


i am sorry Lynn, i don't know why that need to do...but this is what my manger ordered me to do so..

we are going to work on the production server ....my higher authorities does not want to look any queries over there....they want everything under SSIS package(only the .dtsx file to be executed)....

that is why i am working on it now...

FYI...
the main reason behind this is , since we are dealing with million of rows in all the tables....we don't want to load any tables using direct T-SQL statements...becaue due to huge data, the sql server needs to be opened for more than a day....if some one accidently closed the server, then everything will be blown off....so they don't want anything to be transparent...so they came up with SSIS...


Thanks,
Charmer
Post #1346588
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse