Pass Through Query

[Tim Tafflinger]

I am using a pass through query to copy data between a table located in a SQL
Server box local to the desktop running the Access application, and a remote
SQL Server box. The copy goes both ways, first local to remote then remote
to local. The local to remote only takes a couple minutes but remote to
local takes about 10 minutes. FYI - In both cases the query connection
string is to the remote box because the local box can't see the remote but
the remote can see the local. That's another problem for another post.

The only theory I can come up with as to why the difference is that even
though Access doesn't interpret the query, it is still involved in the data
transfer. So when doing local to remote the data transfer is: local sql
server > desktop > remote sql server. When doing remote to local the data
transfer is: remote sql server > desktop > remote sql server > local sql
server.

Does that make any sense? If so, would a stored procedure running on the
remote box that is executed from a pass through still do the same thing or
would it just be sql server to sql server then?

 

[Stefan Hoffmann]

hi Tim,

I am using a pass through query to copy data between a table located in a SQL
Server box local to the desktop running the Access application, and a remote
SQL Server box. The copy goes both ways, first local to remote then remote
to local. The local to remote only takes a couple minutes but remote to
local takes about 10 minutes.

Netwrok: Asymmetric connection, Digital Subscriber Line?


mfG
--> stefan <--

 

[Tim Tafflinger]

It's a 3.5 mbit line both directions.

hi Tim,


Netwrok: Asymmetric connection, Digital Subscriber Line?


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Tim,

It's a 3.5 mbit line both directions.

First of all, verfiy using NetIO or any other tool for measuring network
througput.

Despite that, you may explain your two tasks in a different manner, as I
don't get the full meaning of it so far.


mfG
--> stefan <--

 

[Tim Tafflinger]

The question I have at this point really only has to do with how a pass
through query operates. So, forgetting all the specifics of my particular
situation, here is the setup and questions:

SETUP
There are 3 computers. SQL Server 1, SQL Server 2 and a Desktop computer.
The Desktop computer is running a pass through query that transfers data from
SQL Server 1 to SQL Server 2.

QUESTIONS
Does the data flow directly from SQL Server 1 to SQL Server 2, or does it go
from SQL Server 1 to the Desktop then to SQL Server 2?

Would a stored procedure executed by the pass through query cause the data
to flow directly from SQL Server 1 to SQL Server 2?

Tim

 

[Stefan Hoffmann]

hi Tim,

SETUP
There are 3 computers. SQL Server 1, SQL Server 2 and a Desktop computer.
The Desktop computer is running a pass through query that transfers data from
SQL Server 1 to SQL Server 2.

QUESTIONS
Does the data flow directly from SQL Server 1 to SQL Server 2, or does it go
from SQL Server 1 to the Desktop then to SQL Server 2?

A passthrough query is executed on the SQL Server, so the flow is
between the servers. This means also that your second server is a linked
server or you use OPENROWSET in your passthrough query.

You should post your query to clarify this.

Would a stored procedure executed by the pass through query cause the data
to flow directly from SQL Server 1 to SQL Server 2?

A stored procedure would do the same.

mfG
--> stefan <--

 

[Tim Tafflinger]

The second server is a linked server. The query is always executed by the
remote server because the local server can't see the remote but the remote
can see the local. So when the remote server is pulling data it's pretty
quick, but when pushing data it's slow. Here is the SQL:

Local > Remote
INSERT INTO
tblResponses(CaseID,QuestionID,Response,Followup,UniqueID,ChangeType) SELECT
CaseID,QuestionID,Response,Followup,UniqueID,ChangeType FROM
[laptop\sql2000].hpmcm_dev.dbo.tblResponses WHERE ChangeType=1

Remote > Local
INSERT INTO
[laptop\sql2000].hpmcm_dev.dbo.tblResponses(CaseID,QuestionID,Response,Followup,UniqueID,ChangeType)
SELECT CaseID,QuestionID,Response,Followup,UniqueID,ChangeType FROM
tblResponses WHERE ChangeType=1

ChangeType is just a field that defaults to 1 for new records so I know
which ones to transfer. Now that I'm looking at the statements I wondering
if the speed difference is because the remote box isn't as powerful as the
local box and maybe the select itself is slower. I'm also updating changed
records in a similar manner and have the same issue which could also be
attributed to the speed of the boxes. I didn't consider that before because
the query was always being executed on the remote box so I was thinking the
other box didn't matter. It helps to see the statements layed out in front
of me. I'll do some testing after I post this but let me know if you see
anything inherently wrong in how I'm doing this.

 

[Stefan Hoffmann]

hi Tim,

It helps to see the statements layed out in front
of me.

They are quite symmetric.

I'll do some testing after I post this but let me know if you see
anything inherently wrong in how I'm doing this.

This problem really looks like SQL Server optimization. A few hints:

Check Service Packs. Check firewall/antivirus. DBCC CHECKDB and DBCC
REINDEX may solve some problems. Check HDD speed.


mfG
--> stefan <--