SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why not processing any records further?


Why not processing any records further?

Author
Message
Charmer
Charmer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 1022
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
Thomas Rushton
Thomas Rushton
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: Moderators
Points: 3232 Visits: 3313
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.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16150 Visits: 19539
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
Charmer
Charmer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 1022
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
Charmer
Charmer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 1022
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16150 Visits: 19539
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
Charmer
Charmer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 1022
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39133 Visits: 38518
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).

Cool
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)
Charmer
Charmer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 1022
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search