April 15, 2018 at 1:16 am
I am importing (transferring) records from one server to another server : TABLE A (SERVER 1) -> TABLE B (SERVER 2) using tool.This tool was developed by asp.net Total no of records in Table A is more then 24 lakhs. It is taking nearly 8 hours for inserting 12 lakhs records but taking more time for inserting remaining records.So whether we have to use any dbcc shrink file command or we have to rebuild an index or update the Statistics?.Please give idea and give scripts to improve performance.
April 15, 2018 at 1:30 am
jkramprakash - Sunday, April 15, 2018 1:16 AMI am importing (transferring) records from one server to another server : TABLE A (SERVER 1) -> TABLE B (SERVER 2) using tool.This tool was developed by asp.net Total no of records in Table A is more then 24 lakhs. It is taking nearly 8 hours for inserting 12 lakhs records but taking more time for inserting remaining records.So whether we have to use any dbcc shrink file command or we have to rebuild an index or update the Statistics?.Please give idea and give scripts to improve performance.
Without further information, it is impossible to even start guessing where the trouble is.
😎
Start with these questions:
What is the recovery model of the destination database?
Have you dropped or disabled indexes and constraints on the destination table?
Are there any maintenance operations on either systems?
Can you post the full DDL of the destination table?
April 15, 2018 at 4:39 am
Eirikur Eiriksson - Sunday, April 15, 2018 1:30 AMjkramprakash - Sunday, April 15, 2018 1:16 AMI am importing (transferring) records from one server to another server : TABLE A (SERVER 1) -> TABLE B (SERVER 2) using tool.This tool was developed by asp.net Total no of records in Table A is more then 24 lakhs. It is taking nearly 8 hours for inserting 12 lakhs records but taking more time for inserting remaining records.So whether we have to use any dbcc shrink file command or we have to rebuild an index or update the Statistics?.Please give idea and give scripts to improve performance.
Without further information, it is impossible to even start guessing where the trouble is.
😎
Start with these questions:
What is the recovery model of the destination database?
Have you dropped or disabled indexes and constraints on the destination table?
Are there any maintenance operations on either systems?
Can you post the full DDL of the destination table?
Not dropped any indexes and constraints in destination table.
No maintenance operation on either systems.
Both table having same structure.
we are just transferring records from one server to another server.
so whether we have to use dbbc shrink file command or rebuild index for improving performance?
any scripts for that?
April 15, 2018 at 5:37 am
Eirikur Eiriksson - Sunday, April 15, 2018 1:30 AMjkramprakash - Sunday, April 15, 2018 1:16 AMI am importing (transferring) records from one server to another server : TABLE A (SERVER 1) -> TABLE B (SERVER 2) using tool.This tool was developed by asp.net Total no of records in Table A is more then 24 lakhs. It is taking nearly 8 hours for inserting 12 lakhs records but taking more time for inserting remaining records.So whether we have to use any dbcc shrink file command or we have to rebuild an index or update the Statistics?.Please give idea and give scripts to improve performance.
Without further information, it is impossible to even start guessing where the trouble is.
😎
Start with these questions:
What is the recovery model of the destination database?
Have you dropped or disabled indexes and constraints on the destination table?
Are there any maintenance operations on either systems?
Can you post the full DDL of the destination table?
shall i use this command?
USE [TestFileShrink]
GO
DBCC SHRINKFILE (N'TestFileShrink_data', 0, TRUNCATEONLY)
GO
or shall i use index rebuild command?
April 15, 2018 at 6:09 am
jkramprakash - Sunday, April 15, 2018 5:37 AMEirikur Eiriksson - Sunday, April 15, 2018 1:30 AMjkramprakash - Sunday, April 15, 2018 1:16 AMI am importing (transferring) records from one server to another server : TABLE A (SERVER 1) -> TABLE B (SERVER 2) using tool.This tool was developed by asp.net Total no of records in Table A is more then 24 lakhs. It is taking nearly 8 hours for inserting 12 lakhs records but taking more time for inserting remaining records.So whether we have to use any dbcc shrink file command or we have to rebuild an index or update the Statistics?.Please give idea and give scripts to improve performance.
Without further information, it is impossible to even start guessing where the trouble is.
😎
Start with these questions:
What is the recovery model of the destination database?
Have you dropped or disabled indexes and constraints on the destination table?
Are there any maintenance operations on either systems?
Can you post the full DDL of the destination table?shall i use this command?
USE [TestFileShrink]
GO
DBCC SHRINKFILE (N'TestFileShrink_data', 0, TRUNCATEONLY)
GOor shall i use index rebuild command?
Without knowing a thing about the target tables I can tell you that you definitely should NOT do any of the things you listed above. It'll just make things worse... much worse. And, the TRUNCATEONLY option may not actually do a thing to help if there's even 1 byte in a single page near the logical end of the file.
You were told early in this post that there's not much we can do to help without extra information and you still haven't provided the right information to help.
1. For starters, we need to know the full structure of the target table including all constraints (PK, FKs, column constraints), indexes, triggers, indexed views, etc, that affect or point to the table in question.
2. Which Recovery Model is currently in affect for the DB the target table lives in and can we change it if it's in the full recovery model?
3. Is the target table empty when you start the inserts or does it already have data in it?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 15, 2018 at 3:52 pm
Most likely the problem is in the asp.net app that's being used to transfer the data. If it's doing it a row at a time, it will be slow and that's not SQL's fault.
Before you break anything in the DB, look at the application and see what it's doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2018 at 7:57 am
jkramprakash - Sunday, April 15, 2018 5:37 AMshall i use this command?USE [TestFileShrink]
GO
DBCC SHRINKFILE (N'TestFileShrink_data', 0, TRUNCATEONLY)
GOor shall i use index rebuild command?
You keep suggesting that SHRINKFILE will cause a performance enhancement. Where are you getting that idea? That is not a query tuning mechanism. You need to capture metrics about the query in order to understand what is happening. With metrics comes knowledge. With knowledge comes a solution. Here's a blog post and video on capturing wait statistics on a query. Combine that with capturing the execution plan. Those two sets of information will give you knowledge. How long is it running? What is it waiting on? How is the query optimizer resolving my T-SQL? Answer those questions and then you'll know what to do (and the answer still will NOT be SHRINKFILE).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2018 at 2:37 pm
Is there a reason you don't use bcp or SSIS?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply