Hi,

I'm executing this query over linked server:

INSERT INTO dbo.myTable(col1, col2,col3)
EXEC [linkedServer].[myDatabase].[dbo].[p_myStoredProcedure]

It takes very long time at night. Procedure now executes in less than second, while query now runs in 2 seconds.
The number of rows is about 100000.

How can I find out where is the problem?

Interesting is that similar query with 10 times greater load executes couple of seconds:

INSERT INTO dbo.myTable2(col1, col2, col3)
SELECT col1, col2, col3 FROM [linkedServer].[myDatabase].[dbo].[myView]

Is there some difference if I execute query as INSERT with SELECT statement vs INSERT with EXECUTE stored procedure?
That is one difference i can notice between this 2 queries.

Maybe SQL is sleeping at night and that is the reason of long running time :)

br, Simon