March 6, 2009 at 1:07 am
Hi all,
I need to do a "simple" thing. I need to transfer some data from a local SQL Server 2005 to an external SQL Server 2000. I created a simple SSIS package which worked like a charm. On my own computer... However when I moved it to the server it refused to work, due to user dredential issues (it seems like the company hosting our external server are very restrictive of which IP-addresses are allowed to access the external DB and they won't budge.
Anyway, I then decided to work with Linked Servers. Quickly ran into a problem working from our Local Server (SQL Server 2005) since not all SP's and patches seems to have been installed on our External Server (SQL Server 2000).
But we have another server running SQL Server 2000, so I linked both our local and external server from there and manage to both read and write from/to both.
But here comes my real problem. It's sooo slow doing INSERT commands on our external server.
I'm just doing a simple command like:
INSERT INTO [GLOBAL_SERVER]
([field1]
,[field2]
,[field3])
SELECT
[field1]
,[field2]
,[field3]
FROM
[LOCAL_SERVER]
WHERE
[SIMPLE WHERE CLAUSE]
It's about 16.000 rows that needs to be inserted and this took less than 1 second with the SSIS but with this solution it takes more than 20 minutes!!!
Does anyone have any idea how I can speed things up?
March 6, 2009 at 1:21 am
Please don't do double posts all replies should be here ....the active replies are here so we are continuing here...
:w00t:
March 6, 2009 at 1:29 am
I assure you that I didn't do it on purpose! The first post cause and error and I couldn't see the post in the forum, so I posted it again. The second time it worked but then I found both posts in the forum! I immediately pressed "Delete" on the post, but that fnuctionality doesn't seem to work...?
If any moderator sees this, please delete this thread and keep the other one.
March 6, 2009 at 1:39 am
Ok! I just notice that we can discuss in one thread no replies in both threads so one of them we must consider as closed and someone from moderators will clean the thread!
March 6, 2009 at 3:11 am
Hello Dennis
Maybe you have some more information. The statement you posted is not optimizable since you give more detailed information about your WHERE clause, your network connection between the servers and the types of data you want to ship.
Greets
Flo
March 6, 2009 at 3:34 am
Hi Flo and thanks for your reply!
The SELECT statement takes less than 1 second to run, against the Linked Server so I'm pretty sure that this is not the problem. And the WHERE clause is very simple, I'm just checking that one field is equal to 2, another field is IN (3, 4, 5, 6, 11) and a third (date) field is greater than DATEADD(yy, - 1, GETDATE())
The slow part seems to be the INSERT process. I can see this since I have a timestamp field in the db on the External Server and I can see that only 13-14 records gets written every second and previously, with my SSIS project, the timestamp was the same for every single record.
I'm not exactly sure what happens in the SSIS project but probably it's not inserting one record after the other, but bundles them all together and inserts all in one action.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply