Linked Server Performance with Stored Procedure

I have set up a stored procedure to do a quick lookup similar to
dbo.get_Value_for_Id @.id int

When I invoke the stored procedure from a database on the same server instance as the stored procedure lives, it processes 10K lookups in about 4 seconds.

When I invoke the stored procedure from a different server (via MSSM with a Linked server), it took about 4 minutes to perform 10K lookups. Not good. My question is what can I do to get back to the much faster response times.

I set up a little .Net app to do the remote calls, and it was back to the 4 second range for the 10K calls, in my mind eliminating the network as the primary bottleneck.

Items I've tried:

SET XACT_ABORT is OFF on both ends. No visible results.

SET REMOTE_PROC_TRANSACTIONS OFF . This appears to have put the distributed transactions issue to bed.

Run SP_SERVEROPTION "collation compatible" option to true for the server link. Ran this on both ends. This setting supposedly tells SQL Server the collation sequence and character sets match on both ends. Probably a good idea, even though it didn't make much difference here.

Run the stored procedure remotely using OpenQuery. Took some creativity, given OpenQuery doesn't accept variables as arguments. Got this one working, with run times down to the 90-135 second range (1:30-1:45).

I'm still an order of magnitude slower than I want to be. I suppose I could implement a .NET solution (select the key out of a database on server x, lookup the key on server y), but that sounds more like a workaround than actually using SQL Server capabilities. Any suggestions?

try

exec linkedserver.mydb.dbo.sp_executesql N'exec mysp'

|||

Thanks for the suggestion, Nigel.

I setup two variations of the sp_executesql call, one calling the stored procedure, the second invoking the main select in the stored procedure. In both cases, when run during an MSSM session that was logged into the server that was doing the work, I could loop through 10000 calls in 4-5 seconds. And, in both cases, when logged into my desktop, it took about 4 and a half minutes to run the same script. So this didn't resolve the issue. Interestingly enough, I did see some surging in the activity of my desktop msdtc.exe (in windows task manager), so I'm guessing this process is somehow getting the distributed transaction server involved.

To confirm the involvement of the distributed transaction server, I went into my desktop and turned off the msdtc service. With this service turned off, the script would not run (MSG 8501 MSDTC on server xxx not available). So that seems to confirm I have not evaded the overhead of MSDTC.

To minimize confusion, following is the test script I am using...

set nocount on

set XACT_Abort off

set REMOTE_PROC_TRANSACTIONS off

declare @.Encrypt_id int

declare @.c int

declare @.t table (UnEncrypted_Card varchar(19))

declare @.sql nvarchar(500)

declare @.ParamDef nvarchar(500)

set @.c = 0

while (@.c < 10000)

Begin

set @.Encrypt_id = Rand() * 100000

set @.sql = N'exec dbo.p_Get_Card_Number_By_Id_inst @.Enc_id , ''123456'' ';

-- set @.sql = N'select Value_Encrypted as Value from dbo.tb_Encrypted_Store where Encrypt_Id = @.Enc_Id';

set @.ParamDef = N'@.Enc_Id int';

-- print @.sql

insert into @.t exec linkservername.dbname.dbo.sp_executesql @.sql, @.ParamDef, @.Enc_id = @.Encrypt_Id

set @.c = @.c + 1;

End

Linked server performance issue?

Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- HowardCan you show us the query that you are using over the linked server?
Have you tried using OPENQUERY?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"H Lee" <hlee@.prodigy.net> wrote in message
news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- Howard|||Vyas, thank you for your suggestions.
Would OPENQUERY eliminate these calls?
We have many queries executing over the linked servers. One of them is the
following:
SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
INNER JOIN
"dbo"."Purge_Tbl" pct
ON
Tbl1001.Appnumber = pct.Phase_Number
where
pct.set_id = 1 and copy_f = 1
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> Can you show us the query that you are using over the linked server?
> Have you tried using OPENQUERY?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "H Lee" <hlee@.prodigy.net> wrote in message
> news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Two SQL Server 2000 SP3a boxes are configured in a linked server
> relationship. When performing a Profiler trace on one of them, the
results
> spit out a whole slew of what appears to be system stored procedures being
> called, each having Profiler Logical Disk Reads showing values ranging
from
> 2500 - 50000-- instant targets for possible optimization.
> The biggest performance offending stored procs are:
> "sp_table_statistics_rowset;2", "sp_columns_rowset", and
> "sp_indexes_rowset."
> The "ApplicationName" associated with the Profiler rows that contain
these
> sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
> servers in a linked server relationship, and the "ServerName" value is the
> other server. These stored procedures are executed many times over and
over
> again.
> We're wondering if some component or aspect of the linked server
> relationship, or MSDTC, may be causing these stored procedures to be
> executed repeatedly without an end in sight. If linked server
relationships
> or MSDTC is in fact a very likely source of these stored procedures, then
> are they essential for the functionality of linked servers or MSDTC? If
> anyone has experienced this behavior or know of it, what might be some
other
> standard causes, other than 3rd-party application calls, that is commonly
> known to execute these sprocs? And, given that the cause is indeed linked
> servers, or MSDTC, or some other functionality built into SQL Server, is
> there any chance that the frequency of these calls can be lessened or
> eliminated?
> Thanks,
> - Howard
>
>|||When doing a normal query as shown below, you are really running the query
on your local machine rather than on the linked server.
OpenQuery allows you to do SQL Passthrough queries that will then execute on
the remote server rather than the local server.
Try it both ways and take a look at the Execution plans..
Rick Sawtell
MCT, MCSD, MCDBA
"H Lee" <hlee@.prodigy.net> wrote in message
news:ufOIfNWsEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Vyas, thank you for your suggestions.
> Would OPENQUERY eliminate these calls?
> We have many queries executing over the linked servers. One of them is
the
> following:
>
> SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
> INNER JOIN
> "dbo"."Purge_Tbl" pct
> ON
> Tbl1001.Appnumber = pct.Phase_Number
> where
> pct.set_id = 1 and copy_f = 1
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> results
being[vbcol=seagreen]
> from
> these
the[vbcol=seagreen]
the[vbcol=seagreen]
> over
> relationships
then[vbcol=seagreen]
> other
commonly[vbcol=seagreen]
linked[vbcol=seagreen]
>sql

Linked server performance issue?

Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- Howard
Can you show us the query that you are using over the linked server?
Have you tried using OPENQUERY?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"H Lee" <hlee@.prodigy.net> wrote in message
news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- Howard
|||Vyas, thank you for your suggestions.
Would OPENQUERY eliminate these calls?
We have many queries executing over the linked servers. One of them is the
following:
SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
INNER JOIN
"dbo"."Purge_Tbl" pct
ON
Tbl1001.Appnumber = pct.Phase_Number
where
pct.set_id = 1 and copy_f = 1
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> Can you show us the query that you are using over the linked server?
> Have you tried using OPENQUERY?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "H Lee" <hlee@.prodigy.net> wrote in message
> news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Two SQL Server 2000 SP3a boxes are configured in a linked server
> relationship. When performing a Profiler trace on one of them, the
results
> spit out a whole slew of what appears to be system stored procedures being
> called, each having Profiler Logical Disk Reads showing values ranging
from
> 2500 - 50000-- instant targets for possible optimization.
> The biggest performance offending stored procs are:
> "sp_table_statistics_rowset;2", "sp_columns_rowset", and
> "sp_indexes_rowset."
> The "ApplicationName" associated with the Profiler rows that contain
these
> sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
> servers in a linked server relationship, and the "ServerName" value is the
> other server. These stored procedures are executed many times over and
over
> again.
> We're wondering if some component or aspect of the linked server
> relationship, or MSDTC, may be causing these stored procedures to be
> executed repeatedly without an end in sight. If linked server
relationships
> or MSDTC is in fact a very likely source of these stored procedures, then
> are they essential for the functionality of linked servers or MSDTC? If
> anyone has experienced this behavior or know of it, what might be some
other
> standard causes, other than 3rd-party application calls, that is commonly
> known to execute these sprocs? And, given that the cause is indeed linked
> servers, or MSDTC, or some other functionality built into SQL Server, is
> there any chance that the frequency of these calls can be lessened or
> eliminated?
> Thanks,
> - Howard
>
>
|||When doing a normal query as shown below, you are really running the query
on your local machine rather than on the linked server.
OpenQuery allows you to do SQL Passthrough queries that will then execute on
the remote server rather than the local server.
Try it both ways and take a look at the Execution plans..
Rick Sawtell
MCT, MCSD, MCDBA
"H Lee" <hlee@.prodigy.net> wrote in message
news:ufOIfNWsEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Vyas, thank you for your suggestions.
> Would OPENQUERY eliminate these calls?
> We have many queries executing over the linked servers. One of them is
the[vbcol=seagreen]
> following:
>
> SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
> INNER JOIN
> "dbo"."Purge_Tbl" pct
> ON
> Tbl1001.Appnumber = pct.Phase_Number
> where
> pct.set_id = 1 and copy_f = 1
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> results
being[vbcol=seagreen]
> from
> these
the[vbcol=seagreen]
the[vbcol=seagreen]
> over
> relationships
then[vbcol=seagreen]
> other
commonly[vbcol=seagreen]
linked
>

Linked server performance issue?

Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- HowardCan you show us the query that you are using over the linked server?
Have you tried using OPENQUERY?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"H Lee" <hlee@.prodigy.net> wrote in message
news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- Howard|||Vyas, thank you for your suggestions.
Would OPENQUERY eliminate these calls?
We have many queries executing over the linked servers. One of them is the
following:
SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
INNER JOIN
"dbo"."Purge_Tbl" pct
ON
Tbl1001.Appnumber = pct.Phase_Number
where
pct.set_id = 1 and copy_f = 1
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> Can you show us the query that you are using over the linked server?
> Have you tried using OPENQUERY?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "H Lee" <hlee@.prodigy.net> wrote in message
> news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Two SQL Server 2000 SP3a boxes are configured in a linked server
> relationship. When performing a Profiler trace on one of them, the
results
> spit out a whole slew of what appears to be system stored procedures being
> called, each having Profiler Logical Disk Reads showing values ranging
from
> 2500 - 50000-- instant targets for possible optimization.
> The biggest performance offending stored procs are:
> "sp_table_statistics_rowset;2", "sp_columns_rowset", and
> "sp_indexes_rowset."
> The "ApplicationName" associated with the Profiler rows that contain
these
> sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
> servers in a linked server relationship, and the "ServerName" value is the
> other server. These stored procedures are executed many times over and
over
> again.
> We're wondering if some component or aspect of the linked server
> relationship, or MSDTC, may be causing these stored procedures to be
> executed repeatedly without an end in sight. If linked server
relationships
> or MSDTC is in fact a very likely source of these stored procedures, then
> are they essential for the functionality of linked servers or MSDTC? If
> anyone has experienced this behavior or know of it, what might be some
other
> standard causes, other than 3rd-party application calls, that is commonly
> known to execute these sprocs? And, given that the cause is indeed linked
> servers, or MSDTC, or some other functionality built into SQL Server, is
> there any chance that the frequency of these calls can be lessened or
> eliminated?
> Thanks,
> - Howard
>
>|||When doing a normal query as shown below, you are really running the query
on your local machine rather than on the linked server.
OpenQuery allows you to do SQL Passthrough queries that will then execute on
the remote server rather than the local server.
Try it both ways and take a look at the Execution plans..
Rick Sawtell
MCT, MCSD, MCDBA
"H Lee" <hlee@.prodigy.net> wrote in message
news:ufOIfNWsEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Vyas, thank you for your suggestions.
> Would OPENQUERY eliminate these calls?
> We have many queries executing over the linked servers. One of them is
the
> following:
>
> SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
> INNER JOIN
> "dbo"."Purge_Tbl" pct
> ON
> Tbl1001.Appnumber = pct.Phase_Number
> where
> pct.set_id = 1 and copy_f = 1
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> > Can you show us the query that you are using over the linked server?
> >
> > Have you tried using OPENQUERY?
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "H Lee" <hlee@.prodigy.net> wrote in message
> > news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
> > Two SQL Server 2000 SP3a boxes are configured in a linked server
> > relationship. When performing a Profiler trace on one of them, the
> results
> > spit out a whole slew of what appears to be system stored procedures
being
> > called, each having Profiler Logical Disk Reads showing values ranging
> from
> > 2500 - 50000-- instant targets for possible optimization.
> >
> > The biggest performance offending stored procs are:
> >
> > "sp_table_statistics_rowset;2", "sp_columns_rowset", and
> > "sp_indexes_rowset."
> >
> > The "ApplicationName" associated with the Profiler rows that contain
> these
> > sproc calls is "Microsoft SQL Server." The "HostName" value is one of
the
> > servers in a linked server relationship, and the "ServerName" value is
the
> > other server. These stored procedures are executed many times over and
> over
> > again.
> >
> > We're wondering if some component or aspect of the linked server
> > relationship, or MSDTC, may be causing these stored procedures to be
> > executed repeatedly without an end in sight. If linked server
> relationships
> > or MSDTC is in fact a very likely source of these stored procedures,
then
> > are they essential for the functionality of linked servers or MSDTC? If
> > anyone has experienced this behavior or know of it, what might be some
> other
> > standard causes, other than 3rd-party application calls, that is
commonly
> > known to execute these sprocs? And, given that the cause is indeed
linked
> > servers, or MSDTC, or some other functionality built into SQL Server, is
> > there any chance that the frequency of these calls can be lessened or
> > eliminated?
> >
> > Thanks,
> > - Howard
> >
> >
> >
>

Linked server performance

SQL Server 2000 Standard, SP3a. I have a linked server
defined pointing to DB2, using their DB2 Connect driver.
When I execute a single table query from SQL Server
against the DB2 linked server with a highly selective
where clause using four part naming, it appears as if the
query retrieves all rows from the remote DB2 table into
SQL Server, and then applies the filter. Is there any way
to force the filtering to occur on the remote database?
examples:
runs fast:
select * from OPENQUERY(DB2Server, 'select COL1, COL2
from SCH.TABLE where COL1 = ''4''')
runs slow:
select COL1, COL2 from DB2Server..SCH.TABLE
where COL1 = '4'
Thanks
Vern RabeI don't understand your question. You seemed to have answered it yourself.
If you use OPENQUERY() it will just pass the query through to the remote
server and return the results. If you do not use OPENQUERY it may return
lots of rows due to the way the linked server and the optimizer works. You
obviously found that to be the case so what is wrong with using the first
method?
--
Andrew J. Kelly SQL MVP
"Vern Rabe" <vern@.NO_rabe_SPAM.com> wrote in message
news:0adf01c53c93$fce71970$a501280a@.phx.gbl...
> SQL Server 2000 Standard, SP3a. I have a linked server
> defined pointing to DB2, using their DB2 Connect driver.
> When I execute a single table query from SQL Server
> against the DB2 linked server with a highly selective
> where clause using four part naming, it appears as if the
> query retrieves all rows from the remote DB2 table into
> SQL Server, and then applies the filter. Is there any way
> to force the filtering to occur on the remote database?
> examples:
> runs fast:
> select * from OPENQUERY(DB2Server, 'select COL1, COL2
> from SCH.TABLE where COL1 = ''4''')
> runs slow:
> select COL1, COL2 from DB2Server..SCH.TABLE
> where COL1 = '4'
> Thanks
> Vern Rabe

linked server performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.
It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:

>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>

linked server performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:
>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>sql

linked server performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:

>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>

Linked Server Performance

I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
DaveDoes usp_myproc only work with data on the remote server ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DaveF" <davef@.comcast.net> wrote in message
news:%23ekAsrpbDHA.3248@.tk2msftngp13.phx.gbl...
I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
Dave

Linked server for Oracle on SQL server - query performance

Hi folks,
I have created a linked server on SQL server 2000 for an Oracle 8i
database using Microsoft's OLE provider for ORACLE. The connection
works fine and I am able to run queries (select queries) agaist the
Oracle table from SQL query analyzer.
But the problem is, the query is miserably slower than if I run the
query directly on the Oracle table using either SQL*plus or TOAD. In
the former case (linked table) the query run time is upwards of 7
seconds whereas, in the latter case (directly using SQL*plus) the
query runs in 200 milli seconds. The table is indexed and I am hitting
the index columns.
Any ideas or suggestions? As always thanks in advance and your help
will be much appreciated.
Appu.
Hi everybody,
I am experiencing the same problems as explained below. Help is appriciated.
ETT
"Sat" wrote:

> Hi folks,
> I have created a linked server on SQL server 2000 for an Oracle 8i
> database using Microsoft's OLE provider for ORACLE. The connection
> works fine and I am able to run queries (select queries) agaist the
> Oracle table from SQL query analyzer.
> But the problem is, the query is miserably slower than if I run the
> query directly on the Oracle table using either SQL*plus or TOAD. In
> the former case (linked table) the query run time is upwards of 7
> seconds whereas, in the latter case (directly using SQL*plus) the
> query runs in 200 milli seconds. The table is indexed and I am hitting
> the index columns.
> Any ideas or suggestions? As always thanks in advance and your help
> will be much appreciated.
> Appu.
>
|||Depending on the capabilities of the data provider used for the linked
server and various run-time settings or details of your query, such as
collations, it may not be possible for the SQL Server query processor to
send the relevant part of the WHERE condition to the Oracle server.
Instead, the SQL Server may be requesting an entire table from the
Oracle server and filtering the rows afterwards without the benefit of
the Oracle index. There is no one way to resolve this. The semantics
of the query may mean it is simply wrong to filter the query on the
Oracle side, but if there is a solution, look at data provider settings,
ANSI compliance settings, collations, and exact matching of data types.
You might also post your question in an Oracle group, since Oracle users
might be able to help.
Steve Kass
Drew University
ETT wrote:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated.
>ETT
>"Sat" wrote:
>

Linked server for Oracle on SQL server - query performance

Hi folks,
I have created a linked server on SQL server 2000 for an Oracle 8i
database using Microsoft's OLE provider for ORACLE. The connection
works fine and I am able to run queries (select queries) agaist the
Oracle table from SQL query analyzer.
But the problem is, the query is miserably slower than if I run the
query directly on the Oracle table using either SQL*plus or TOAD. In
the former case (linked table) the query run time is upwards of 7
seconds whereas, in the latter case (directly using SQL*plus) the
query runs in 200 milli seconds. The table is indexed and I am hitting
the index columns.
Any ideas or suggestions? As always thanks in advance and your help
will be much appreciated.
Appu.Hi everybody,
I am experiencing the same problems as explained below. Help is appriciated.
ETT
"Sat" wrote:

> Hi folks,
> I have created a linked server on SQL server 2000 for an Oracle 8i
> database using Microsoft's OLE provider for ORACLE. The connection
> works fine and I am able to run queries (select queries) agaist the
> Oracle table from SQL query analyzer.
> But the problem is, the query is miserably slower than if I run the
> query directly on the Oracle table using either SQL*plus or TOAD. In
> the former case (linked table) the query run time is upwards of 7
> seconds whereas, in the latter case (directly using SQL*plus) the
> query runs in 200 milli seconds. The table is indexed and I am hitting
> the index columns.
> Any ideas or suggestions? As always thanks in advance and your help
> will be much appreciated.
> Appu.
>|||Depending on the capabilities of the data provider used for the linked
server and various run-time settings or details of your query, such as
collations, it may not be possible for the SQL Server query processor to
send the relevant part of the WHERE condition to the Oracle server.
Instead, the SQL Server may be requesting an entire table from the
Oracle server and filtering the rows afterwards without the benefit of
the Oracle index. There is no one way to resolve this. The semantics
of the query may mean it is simply wrong to filter the query on the
Oracle side, but if there is a solution, look at data provider settings,
ANSI compliance settings, collations, and exact matching of data types.
You might also post your question in an Oracle group, since Oracle users
might be able to help.
Steve Kass
Drew University
ETT wrote:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated
.
>ETT
>"Sat" wrote:
>
>

Linked Server Connection to Sybase

Has anyone had problems using an OLEDB linked server connection to Sybase ASE 12.5? I'm having major performance problems when I use string criteria in the where clause. Queries that return 1 row and execute in less than a second using a native Sybase connection take 40 seconds to run using the linked server OLEDB connection. If I use ints in the where clause performance is almost exactly the same between native and linked connections.

Any ideas?Did I mention I hate sybase...

Just had a project thrown at me...never touched it before...

I know there "cousins", but man is it quirky...

Saw a developer do

SELECT Col1, MAX(Col2), SUM(Col3)
FROM Table
WHERE Col1 = 'A'

AND IT RAN!

I couldn't believe it...and it gave back complete garbage...

Can you post your statement?

I'll try and link that server up and see what happens...

(No wonder they lost market share)|||Hi, I had a similar problem with ASE6, using a linked server in SQL server 2000.
The problem was I had a very poor performance (10 secs per query), and I had to make an update in 200,000 regs using that query for every record... also I had problems in my linked server too (it worked when it wanted, i never discovered the cause...)

My solution was very desesperate: instead of connecting SQL server to the ODBC linked to the ASE server, I made a program in Visual Basic with a connection to the ODBC. I wrote the update procedure with ADO.
The results: the whole update was finished in 15 secs (remember: in SQL server each query took 10 secs, multiplied by 200,000 records...).

I dont understand why this happens, a SQL server should be faster accessing data behind an ODBC than a VB program...|||Here's the SQL:

select *
from [32tlsql2-dreamdb].dreamdb.dbo.drt_contract
where i_con_contract = '000Q019999'
and c_company = 'P00'
and c_mkt_division = 'IPD'

Here's the table DDL (please don't make jokes about the crappy design of the table. Unfortunately I got stuck with it):

/****************** CREATE ORDER ******************/
create table dbo.drt_contract
(
i_client numeric(10,0) not null ,
i_con_contract char(10) not null ,
c_company char(3) not null ,
c_mkt_division char(3) not null ,
c_level numeric(2,0) Default 1 not null ,
c_interface_company char(6) null ,
c_form char(8) not null ,
c_con_contract_type char(2) not null ,
c_agent_type char(3) null ,
c_con_status char(2) null ,
d_final_disposition datetime null ,
d_lifecom_updated datetime null ,
c_lifecom_status char(1) null ,
d_con_terminate datetime null ,
d_con_da_report_terminate datetime null ,
d_con_signed datetime null ,
d_con_effective datetime null ,
c_con_line_of_business char(1) null ,
i_con_rsm char(10) null ,
c_con_publicity char(1) null ,
c_con_uw_eligiblity char(1) null ,
c_con_premium_remission char(1) null ,
q_con_years_experience char(1) null ,
c_con_pay char(2) null ,
c_con_report char(2) null ,
c_con_review char(1) null ,
c_con_additional_aa_trailer char(8) null ,
x_con_text varchar(250) null ,
c_con_terminate_reason char(2) null ,
f_con_assign_commissions char(1) null ,
n_con_assign_commissions varchar(50) null ,
i_con_financial_owner char(10) null ,
c_con_financial_owner_level char(1) null ,
i_con_address_owner char(10) null ,
c_con_address_owner_level char(1) null ,
c_con_mail char(1) null ,
c_con_hire_type char(2) null ,
c_con_change_type char(2) null ,
c_zipcode_processing char(1) null ,
n_con_sub_name varchar(30) null ,
d_con_nasd_hire datetime null ,
c_con_nasd_status char(2) null ,
i_con_alt_rep1 char(10) null ,
i_con_alt_rep2 char(10) null ,
i_con_alt_rep3 char(10) null ,
i_con_branch char(10) null ,
c_con_rep_class char(2) null ,
i_con_old_rep_nbr varchar(10) null ,
i_con_old_branch char(10) null ,
i_user_last_change char(8) null ,
h_last_change datetime null
)
Lock Datarows on "default"
go


/****************** ALTER ORDER CONSTRAINTS ******************/
alter table dbo.drt_contract
add constraint pk_contract primary key clustered ( i_con_contract asc ,c_company asc ,c_mkt_division asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_client on dbo.drt_contract ( i_client asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_con_status on dbo.drt_contract ( c_con_status asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_financial_owner on dbo.drt_contract ( i_con_financial_owner asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_address_owner on dbo.drt_contract ( i_con_address_owner asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_mkt_division on dbo.drt_contract ( c_mkt_division asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_alt_rep3 on dbo.drt_contract ( i_con_alt_rep3 asc )
go

/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_company on dbo.drt_contract ( c_company asc )
go|||You want crappy design?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26547

It looks like it should use the clustered index...but it still has to go to the datapage because of SELECT *

Which, in an application aint a good idea...do you need all of the columns?

What data do you actually need?|||Yep, you're right but I'm doing some quick testing so I didn't want to type all of the column names. The select * won't be used by any application or process. Anyways, I've specified the all of the column names and also only the 5-6 columns that I care to review and it still executes in the same amount of time. Oh yeah, Sybase nor Microsoft will provide support for OLEDB linked server connections to Sybase...SWEET|||I don't know if sybase has index intersection, but did you try and create a covered index with the 6 columns that you need

Sybase should at least use that index....

Or, can you create a sproc on sybase and call that?

My guess is that it's dragging way mopre data across than it needs to and sql server is figuring out the final results...

I'm not sure...

Gotta read up on how linked servers manage the data...|||The SQL statement only returns 1 row. So you think that a larger set of data is being sent to SQL Server and it has to narrow the set down to the final row? Hmm... remember I'm only encountering the problem when I use a string in the where clause. Integers work fine. I'll let you know how the covered index works.|||Here's the solution, use openquery() to execute the sql.

select * from openquery(linked server connection, sql stetament)|||Thanks for the info!

Linked Server = Poor Performance

Using a query through a linked server is giving tremendously reduced performance.

Is part of the problem linking from a SQL 2000 to a SQL 7.0 database?

Are there any other tips out there?

Thanks.

The major difference between 7.0 and 2000 is DRI (declarative referential integrity) so run you code through SQL Server 2000 profiler and if you have indexes run DBCC ShowContig to see if your AIM (index allocation mapping) pages are fragmented. Hope this helps.