Slow data transfer between tables on the same SQL Server instance: seeking solutions

Data and Databases
1

I’m having trouble moving data between databases on my SQL Server. I’m using SSMS to export a table with about 18,000 rows. Other tables with 20,000+ rows copied fine, but this one is taking forever.

I’ve double-checked that I’m only copying this table and made sure to enable identity insert in the edit mappings. The export dialog just says “Executing” and “In Progress…” for hours.

Here’s what my table looks like:

CREATE TABLE [dbo].[DataLog](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    [ErrorMessage] [varchar](max) NULL,
    [LogTime] [datetime] NULL,
    [ItemName] [varchar](50) NULL,
    [ItemCode] [varchar](10) NULL,
 CONSTRAINT [PK_DataLog] PRIMARY KEY CLUSTERED 
(
    [LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Any ideas why this is so slow? How can I speed it up? I’m stumped and could really use some help!

2

hmmm, have u tried using bulk insert? it’s super fast for big data moves. maybe ur ErrorMessage column is causing trouble? could u try splitting it into smaller chunks? also, whats ur server specs like? sometimes slow transfers r just a hardware thing. btw, any indexes on the table slowing things down?

3

I’ve encountered similar issues with slow data transfers between tables on the same SQL Server instance. One effective solution I’ve found is to use the SQL Server Import and Export Wizard instead of SSMS’s export functionality. This tool often performs better for larger datasets.

Another approach that has worked well in my experience is setting up a SQL Server Integration Services (SSIS) package. It offers more control over the transfer process and can significantly enhance performance, particularly when dealing with tables that include varchar(max) columns like your ErrorMessage field.

If you prefer to remain within T-SQL, consider using INSERT INTO … SELECT with batching. Transferring data in smaller chunks can be more efficient than running a single large operation. Additionally, ensuring that statistics are current on both the source and destination tables can help improve performance.

4

hey, have u checked for any locks or blocking? sometimes that can slow things down big time. also, maybe try using bcp utility? it’s pretty good for moving data fast. oh, and check ur transaction log - if it’s full, that could be the bottleneck. good luck!