Hi
I have a problem with performance when I execute proc against linked Sybase
Server.
Here is a strings I used to add a server:
sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
Execute of proc is taking a second when it run directly on Sybase, is taking
about 20 sec.
I've never noticed any network problems in our environment before.
Any ideas?
Does anybody know a better alternative to connect to Sybase?
Try the instructions on the sybase website for creating a linked server.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C26C4E8F-D554-47E8-B207-04AF2A6954A9@.microsoft.com...
> Hi
> I have a problem with performance when I execute proc against linked
Sybase
> Server.
> Here is a strings I used to add a server:
> sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
> sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
> Execute of proc is taking a second when it run directly on Sybase, is
taking
> about 20 sec.
> I've never noticed any network problems in our environment before.
> Any ideas?
> Does anybody know a better alternative to connect to Sybase?
Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts
Wednesday, March 28, 2012
Poor Linked Sybase Server performance
Hi
I have a problem with performance when I execute proc against linked Sybase
Server.
Here is a strings I used to add a server:
sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
Execute of proc is taking a second when it run directly on Sybase, is taking
about 20 sec.
I've never noticed any network problems in our environment before.
Any ideas?
Does anybody know a better alternative to connect to Sybase?Try the instructions on the sybase website for creating a linked server.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C26C4E8F-D554-47E8-B207-04AF2A6954A9@.microsoft.com...
> Hi
> I have a problem with performance when I execute proc against linked
Sybase
> Server.
> Here is a strings I used to add a server:
> sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
> sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
> Execute of proc is taking a second when it run directly on Sybase, is
taking
> about 20 sec.
> I've never noticed any network problems in our environment before.
> Any ideas?
> Does anybody know a better alternative to connect to Sybase?sql
I have a problem with performance when I execute proc against linked Sybase
Server.
Here is a strings I used to add a server:
sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
Execute of proc is taking a second when it run directly on Sybase, is taking
about 20 sec.
I've never noticed any network problems in our environment before.
Any ideas?
Does anybody know a better alternative to connect to Sybase?Try the instructions on the sybase website for creating a linked server.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C26C4E8F-D554-47E8-B207-04AF2A6954A9@.microsoft.com...
> Hi
> I have a problem with performance when I execute proc against linked
Sybase
> Server.
> Here is a strings I used to add a server:
> sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
> sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
> Execute of proc is taking a second when it run directly on Sybase, is
taking
> about 20 sec.
> I've never noticed any network problems in our environment before.
> Any ideas?
> Does anybody know a better alternative to connect to Sybase?sql
Poor Linked Sybase Server performance
Hi
I have a problem with performance when I execute proc against linked Sybase
Server.
Here is a strings I used to add a server:
sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
Execute of proc is taking a second when it run directly on Sybase, is taking
about 20 sec.
I've never noticed any network problems in our environment before.
Any ideas?
Does anybody know a better alternative to connect to Sybase?Try the instructions on the sybase website for creating a linked server.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C26C4E8F-D554-47E8-B207-04AF2A6954A9@.microsoft.com...
> Hi
> I have a problem with performance when I execute proc against linked
Sybase
> Server.
> Here is a strings I used to add a server:
> sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
> sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
> Execute of proc is taking a second when it run directly on Sybase, is
taking
> about 20 sec.
> I've never noticed any network problems in our environment before.
> Any ideas?
> Does anybody know a better alternative to connect to Sybase?
I have a problem with performance when I execute proc against linked Sybase
Server.
Here is a strings I used to add a server:
sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
Execute of proc is taking a second when it run directly on Sybase, is taking
about 20 sec.
I've never noticed any network problems in our environment before.
Any ideas?
Does anybody know a better alternative to connect to Sybase?Try the instructions on the sybase website for creating a linked server.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C26C4E8F-D554-47E8-B207-04AF2A6954A9@.microsoft.com...
> Hi
> I have a problem with performance when I execute proc against linked
Sybase
> Server.
> Here is a strings I used to add a server:
> sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
> sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
> Execute of proc is taking a second when it run directly on Sybase, is
taking
> about 20 sec.
> I've never noticed any network problems in our environment before.
> Any ideas?
> Does anybody know a better alternative to connect to Sybase?
Poor Linked Server performance when running a query with a variable
Hi
We are facing a quite strange performance issue when using a linked
server to an Oracle server.
When we run a query containing a WHERE clause using a variable, it takes
ages for SQL server to return the data. When we instead just type in the
value for the WHERE clause it returns the data as fast as expected.
The queries we have used for testing is these:
1:
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
EMPLOYEENUMBER = '06131'
This query returns 6165 rows in 5 seconds which is normal performance.
2:
declare @.medanr varchar(255)
set @.medanr = '06131'
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
EMPLOYEENUMBER = @.medanr
This query runs for approx. 14 minutes and returns the same number of rows.
We can see that the difference in execution times increases with the
number of rows the table contains. Without being sure, we suspect that
the problem is that Oracle can't use an index when the query contains a
WHERE clause using a variable, but why?
It have worked for us before (actually the code giving problems has been
running for years).
What has happened recently, is that the SQL server has been upgraded
from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
the reason, but to verify this we have installed a new server with
SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
version as we have on the affected server - 8.00.818) but we are facing
the same problem with this new server.
It could of course also be caused by the Oracle server, but we have
tried it against 2 different Oracle databases (different applications)
running on different physical servers and in both cases the problem are
the same.
Furthermore we have also tried it from a SQL2005 server against the same
Oracle databases but with same problem.
Right now we are running out if ideas and don't really know what to go
for..........
Has any one seem similar issues or has any good ideas?
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Well, I am pretty sure that I know what has happened, but not why. Of
course, every SP includes optimizer and other engine updates, so things do
change. Here is what I think is happening:
Where EMPLOYEENUMBER = '06131' -- Evaluated on the Oracle side of the link
Where EMPLOYEENUMBER = @.medanr -- Evaluated on the SQL Server side of the
link.
You should be able to trace the communication between the servers to see how
much I/O is happening between the servers. I believe that to evaluate
@.medanr the Oracle server is delivering every row from
MACONOMY..RAMAIN60.TIMESHEETL to the SQL Server so that the filter can be
applied.
To test this out, you could try first pushing the filter value to a work
table on Oracle. Then Oracle can do the whole join on its side and return
only the 6165 rows, by:
INSERT INTO MACONOMY..RAMAIN60.TIMESHEETWORKTABLE
(FilterValue) VALUES ('06131')
select T.PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE T
JOIN MACONOMY..RAMAIN60.TIMESHEETWORKTABLE W
ON T.EMPLOYEENUMBER = W.EMPLOYEENUMBER
If that gives you the performance you were used to, then the problem has
been identified. Maybe that will help you know what avenue you want to
pursue.
RLF
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1dCtMUcHHA.4388@.TK2MSFTNGP05.phx.gbl...
> Hi
> We are facing a quite strange performance issue when using a linked server
> to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER = '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr = '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER = @.medanr
>
> This query runs for approx. 14 minutes and returns the same number of
> rows.
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that the
> problem is that Oracle can't use an index when the query contains a WHERE
> clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded from
> SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be the
> reason, but to verify this we have installed a new server with SQL2000
> SP3a + MS03-031 Security Fix (which ends up with the same version as we
> have on the affected server - 8.00.818) but we are facing the same problem
> with this new server.
> It could of course also be caused by the Oracle server, but we have tried
> it against 2 different Oracle databases (different applications) running
> on different physical servers and in both cases the problem are the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator
|||I agree with Russell.
To avoid issues like this try using Openquery.
SELECT *
FROM OPENQUERY(OracleSvr, 'declare @.medanr varchar(255)
set @.medanr = '''06131'''
select PERIODSTART from RAMAIN60.TIMESHEETLINE
Where EMPLOYEENUMBER = @.medanr
')
GO
Markus
|||M A Srinivas wrote:
> On Mar 28, 7:12 pm, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> We had the same problem . If you check dynamic Properties and nested
> query in provider options , it may improve query performance for
> variables . These options are available when you create link server
>
Thanks for all the inputs. We have already been down the route with
the various providers options, and as far as I remember we have already
tried with the "Nested queries" option but to no avail.
Using OPENQUERY could be an option if it was a new Stored Proc or code
we where writing, but at the moments we have hundreds of SP's where
those linked servers are involved so it's not that easy to rewrite the
all these SP's.
Russels suspicion about how the where clause is being evaluated, is
along the lines we have found out as well based on the execution plan
for the query. I don't know if we can rely on that, but the execution
plan also indicates that when we are using a variable, all the rows from
the Oracle table is being retrieved and then the filter is being applied
on the SQL server side. This still doesn't help us a lot because it's
not that easy to rewrite the code and also it puzzles me why it has
happened in the first place. Especially because we have now set up a new
server with the same SQL server version/SP level as the affected server
had before we applied SP4, but that still causes the same problem.
I'll look into some of your ideas/suggstion tomorrow to see if anything
can point us in he right direction.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
We are facing a quite strange performance issue when using a linked
server to an Oracle server.
When we run a query containing a WHERE clause using a variable, it takes
ages for SQL server to return the data. When we instead just type in the
value for the WHERE clause it returns the data as fast as expected.
The queries we have used for testing is these:
1:
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
EMPLOYEENUMBER = '06131'
This query returns 6165 rows in 5 seconds which is normal performance.
2:
declare @.medanr varchar(255)
set @.medanr = '06131'
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
EMPLOYEENUMBER = @.medanr
This query runs for approx. 14 minutes and returns the same number of rows.
We can see that the difference in execution times increases with the
number of rows the table contains. Without being sure, we suspect that
the problem is that Oracle can't use an index when the query contains a
WHERE clause using a variable, but why?
It have worked for us before (actually the code giving problems has been
running for years).
What has happened recently, is that the SQL server has been upgraded
from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
the reason, but to verify this we have installed a new server with
SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
version as we have on the affected server - 8.00.818) but we are facing
the same problem with this new server.
It could of course also be caused by the Oracle server, but we have
tried it against 2 different Oracle databases (different applications)
running on different physical servers and in both cases the problem are
the same.
Furthermore we have also tried it from a SQL2005 server against the same
Oracle databases but with same problem.
Right now we are running out if ideas and don't really know what to go
for..........
Has any one seem similar issues or has any good ideas?
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Well, I am pretty sure that I know what has happened, but not why. Of
course, every SP includes optimizer and other engine updates, so things do
change. Here is what I think is happening:
Where EMPLOYEENUMBER = '06131' -- Evaluated on the Oracle side of the link
Where EMPLOYEENUMBER = @.medanr -- Evaluated on the SQL Server side of the
link.
You should be able to trace the communication between the servers to see how
much I/O is happening between the servers. I believe that to evaluate
@.medanr the Oracle server is delivering every row from
MACONOMY..RAMAIN60.TIMESHEETL to the SQL Server so that the filter can be
applied.
To test this out, you could try first pushing the filter value to a work
table on Oracle. Then Oracle can do the whole join on its side and return
only the 6165 rows, by:
INSERT INTO MACONOMY..RAMAIN60.TIMESHEETWORKTABLE
(FilterValue) VALUES ('06131')
select T.PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE T
JOIN MACONOMY..RAMAIN60.TIMESHEETWORKTABLE W
ON T.EMPLOYEENUMBER = W.EMPLOYEENUMBER
If that gives you the performance you were used to, then the problem has
been identified. Maybe that will help you know what avenue you want to
pursue.
RLF
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1dCtMUcHHA.4388@.TK2MSFTNGP05.phx.gbl...
> Hi
> We are facing a quite strange performance issue when using a linked server
> to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER = '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr = '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER = @.medanr
>
> This query runs for approx. 14 minutes and returns the same number of
> rows.
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that the
> problem is that Oracle can't use an index when the query contains a WHERE
> clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded from
> SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be the
> reason, but to verify this we have installed a new server with SQL2000
> SP3a + MS03-031 Security Fix (which ends up with the same version as we
> have on the affected server - 8.00.818) but we are facing the same problem
> with this new server.
> It could of course also be caused by the Oracle server, but we have tried
> it against 2 different Oracle databases (different applications) running
> on different physical servers and in both cases the problem are the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator
|||I agree with Russell.
To avoid issues like this try using Openquery.
SELECT *
FROM OPENQUERY(OracleSvr, 'declare @.medanr varchar(255)
set @.medanr = '''06131'''
select PERIODSTART from RAMAIN60.TIMESHEETLINE
Where EMPLOYEENUMBER = @.medanr
')
GO
Markus
|||M A Srinivas wrote:
> On Mar 28, 7:12 pm, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> We had the same problem . If you check dynamic Properties and nested
> query in provider options , it may improve query performance for
> variables . These options are available when you create link server
>
Thanks for all the inputs. We have already been down the route with
the various providers options, and as far as I remember we have already
tried with the "Nested queries" option but to no avail.
Using OPENQUERY could be an option if it was a new Stored Proc or code
we where writing, but at the moments we have hundreds of SP's where
those linked servers are involved so it's not that easy to rewrite the
all these SP's.
Russels suspicion about how the where clause is being evaluated, is
along the lines we have found out as well based on the execution plan
for the query. I don't know if we can rely on that, but the execution
plan also indicates that when we are using a variable, all the rows from
the Oracle table is being retrieved and then the filter is being applied
on the SQL server side. This still doesn't help us a lot because it's
not that easy to rewrite the code and also it puzzles me why it has
happened in the first place. Especially because we have now set up a new
server with the same SQL server version/SP level as the affected server
had before we applied SP4, but that still causes the same problem.
I'll look into some of your ideas/suggstion tomorrow to see if anything
can point us in he right direction.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Poor Linked Server performance when running a query with a variable
Hi
We are facing a quite strange performance issue when using a linked
server to an Oracle server.
When we run a query containing a WHERE clause using a variable, it takes
ages for SQL server to return the data. When we instead just type in the
value for the WHERE clause it returns the data as fast as expected.
The queries we have used for testing is these:
1:
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
EMPLOYEENUMBER = '06131'
This query returns 6165 rows in 5 seconds which is normal performance.
2:
declare @.medanr varchar(255)
set @.medanr = '06131'
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
EMPLOYEENUMBER = @.medanr
This query runs for approx. 14 minutes and returns the same number of rows.
We can see that the difference in execution times increases with the
number of rows the table contains. Without being sure, we suspect that
the problem is that Oracle can't use an index when the query contains a
WHERE clause using a variable, but why?
It have worked for us before (actually the code giving problems has been
running for years).
What has happened recently, is that the SQL server has been upgraded
from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
the reason, but to verify this we have installed a new server with
SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
version as we have on the affected server - 8.00.818) but we are facing
the same problem with this new server.
It could of course also be caused by the Oracle server, but we have
tried it against 2 different Oracle databases (different applications)
running on different physical servers and in both cases the problem are
the same.
Furthermore we have also tried it from a SQL2005 server against the same
Oracle databases but with same problem.
Right now we are running out if ideas and don't really know what to go
for..........
Has any one seem similar issues or has any good ideas?
--
Regards
Steen Schlüter Persson
Database Administrator / System AdministratorWell, I am pretty sure that I know what has happened, but not why. Of
course, every SP includes optimizer and other engine updates, so things do
change. Here is what I think is happening:
Where EMPLOYEENUMBER = '06131' -- Evaluated on the Oracle side of the link
Where EMPLOYEENUMBER = @.medanr -- Evaluated on the SQL Server side of the
link.
You should be able to trace the communication between the servers to see how
much I/O is happening between the servers. I believe that to evaluate
@.medanr the Oracle server is delivering every row from
MACONOMY..RAMAIN60.TIMESHEETL to the SQL Server so that the filter can be
applied.
To test this out, you could try first pushing the filter value to a work
table on Oracle. Then Oracle can do the whole join on its side and return
only the 6165 rows, by:
INSERT INTO MACONOMY..RAMAIN60.TIMESHEETWORKTABLE
(FilterValue) VALUES ('06131')
select T.PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE T
JOIN MACONOMY..RAMAIN60.TIMESHEETWORKTABLE W
ON T.EMPLOYEENUMBER = W.EMPLOYEENUMBER
If that gives you the performance you were used to, then the problem has
been identified. Maybe that will help you know what avenue you want to
pursue.
RLF
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1dCtMUcHHA.4388@.TK2MSFTNGP05.phx.gbl...
> Hi
> We are facing a quite strange performance issue when using a linked server
> to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER = '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr = '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER = @.medanr
>
> This query runs for approx. 14 minutes and returns the same number of
> rows.
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that the
> problem is that Oracle can't use an index when the query contains a WHERE
> clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded from
> SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be the
> reason, but to verify this we have installed a new server with SQL2000
> SP3a + MS03-031 Security Fix (which ends up with the same version as we
> have on the affected server - 8.00.818) but we are facing the same problem
> with this new server.
> It could of course also be caused by the Oracle server, but we have tried
> it against 2 different Oracle databases (different applications) running
> on different physical servers and in both cases the problem are the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator|||I agree with Russell.
To avoid issues like this try using Openquery.
SELECT *
FROM OPENQUERY(OracleSvr, 'declare @.medanr varchar(255)
set @.medanr = '''06131'''
select PERIODSTART from RAMAIN60.TIMESHEETLINE
Where EMPLOYEENUMBER = @.medanr
')
GO
Markus|||On Mar 28, 7:12 pm, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi
> We are facing a quite strange performance issue when using a linked
> server to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER =3D '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr =3D '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER =3D @.medanr
> This query runs for approx. 14 minutes and returns the same number of row=s=2E
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that
> the problem is that Oracle can't use an index when the query contains a
> WHERE clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded
> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
> the reason, but to verify this we have installed a new server with
> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
> version as we have on the affected server - 8.00.818) but we are facing
> the same problem with this new server.
> It could of course also be caused by the Oracle server, but we have
> tried it against 2 different Oracle databases (different applications)
> running on different physical servers and in both cases the problem are
> the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator
We had the same problem . If you check dynamic Properties and nested
query in provider options , it may improve query performance for
variables . These options are available when you create link server|||M A Srinivas wrote:
> On Mar 28, 7:12 pm, "Steen Schlüter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>> Hi
>> We are facing a quite strange performance issue when using a linked
>> server to an Oracle server.
>> When we run a query containing a WHERE clause using a variable, it takes
>> ages for SQL server to return the data. When we instead just type in the
>> value for the WHERE clause it returns the data as fast as expected.
>> The queries we have used for testing is these:
>> 1:
>> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
>> EMPLOYEENUMBER = '06131'
>> This query returns 6165 rows in 5 seconds which is normal performance.
>> 2:
>> declare @.medanr varchar(255)
>> set @.medanr = '06131'
>> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
>> EMPLOYEENUMBER = @.medanr
>> This query runs for approx. 14 minutes and returns the same number of rows.
>> We can see that the difference in execution times increases with the
>> number of rows the table contains. Without being sure, we suspect that
>> the problem is that Oracle can't use an index when the query contains a
>> WHERE clause using a variable, but why?
>> It have worked for us before (actually the code giving problems has been
>> running for years).
>> What has happened recently, is that the SQL server has been upgraded
>> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
>> the reason, but to verify this we have installed a new server with
>> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
>> version as we have on the affected server - 8.00.818) but we are facing
>> the same problem with this new server.
>> It could of course also be caused by the Oracle server, but we have
>> tried it against 2 different Oracle databases (different applications)
>> running on different physical servers and in both cases the problem are
>> the same.
>> Furthermore we have also tried it from a SQL2005 server against the same
>> Oracle databases but with same problem.
>> Right now we are running out if ideas and don't really know what to go
>> for..........
>> Has any one seem similar issues or has any good ideas?
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
> We had the same problem . If you check dynamic Properties and nested
> query in provider options , it may improve query performance for
> variables . These options are available when you create link server
>
Thanks for all the inputs. We have already been down the route with
the various providers options, and as far as I remember we have already
tried with the "Nested queries" option but to no avail.
Using OPENQUERY could be an option if it was a new Stored Proc or code
we where writing, but at the moments we have hundreds of SP's where
those linked servers are involved so it's not that easy to rewrite the
all these SP's.
Russels suspicion about how the where clause is being evaluated, is
along the lines we have found out as well based on the execution plan
for the query. I don't know if we can rely on that, but the execution
plan also indicates that when we are using a variable, all the rows from
the Oracle table is being retrieved and then the filter is being applied
on the SQL server side. This still doesn't help us a lot because it's
not that easy to rewrite the code and also it puzzles me why it has
happened in the first place. Especially because we have now set up a new
server with the same SQL server version/SP level as the affected server
had before we applied SP4, but that still causes the same problem.
I'll look into some of your ideas/suggstion tomorrow to see if anything
can point us in he right direction.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
We are facing a quite strange performance issue when using a linked
server to an Oracle server.
When we run a query containing a WHERE clause using a variable, it takes
ages for SQL server to return the data. When we instead just type in the
value for the WHERE clause it returns the data as fast as expected.
The queries we have used for testing is these:
1:
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
EMPLOYEENUMBER = '06131'
This query returns 6165 rows in 5 seconds which is normal performance.
2:
declare @.medanr varchar(255)
set @.medanr = '06131'
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
EMPLOYEENUMBER = @.medanr
This query runs for approx. 14 minutes and returns the same number of rows.
We can see that the difference in execution times increases with the
number of rows the table contains. Without being sure, we suspect that
the problem is that Oracle can't use an index when the query contains a
WHERE clause using a variable, but why?
It have worked for us before (actually the code giving problems has been
running for years).
What has happened recently, is that the SQL server has been upgraded
from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
the reason, but to verify this we have installed a new server with
SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
version as we have on the affected server - 8.00.818) but we are facing
the same problem with this new server.
It could of course also be caused by the Oracle server, but we have
tried it against 2 different Oracle databases (different applications)
running on different physical servers and in both cases the problem are
the same.
Furthermore we have also tried it from a SQL2005 server against the same
Oracle databases but with same problem.
Right now we are running out if ideas and don't really know what to go
for..........
Has any one seem similar issues or has any good ideas?
--
Regards
Steen Schlüter Persson
Database Administrator / System AdministratorWell, I am pretty sure that I know what has happened, but not why. Of
course, every SP includes optimizer and other engine updates, so things do
change. Here is what I think is happening:
Where EMPLOYEENUMBER = '06131' -- Evaluated on the Oracle side of the link
Where EMPLOYEENUMBER = @.medanr -- Evaluated on the SQL Server side of the
link.
You should be able to trace the communication between the servers to see how
much I/O is happening between the servers. I believe that to evaluate
@.medanr the Oracle server is delivering every row from
MACONOMY..RAMAIN60.TIMESHEETL to the SQL Server so that the filter can be
applied.
To test this out, you could try first pushing the filter value to a work
table on Oracle. Then Oracle can do the whole join on its side and return
only the 6165 rows, by:
INSERT INTO MACONOMY..RAMAIN60.TIMESHEETWORKTABLE
(FilterValue) VALUES ('06131')
select T.PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE T
JOIN MACONOMY..RAMAIN60.TIMESHEETWORKTABLE W
ON T.EMPLOYEENUMBER = W.EMPLOYEENUMBER
If that gives you the performance you were used to, then the problem has
been identified. Maybe that will help you know what avenue you want to
pursue.
RLF
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1dCtMUcHHA.4388@.TK2MSFTNGP05.phx.gbl...
> Hi
> We are facing a quite strange performance issue when using a linked server
> to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER = '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr = '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER = @.medanr
>
> This query runs for approx. 14 minutes and returns the same number of
> rows.
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that the
> problem is that Oracle can't use an index when the query contains a WHERE
> clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded from
> SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be the
> reason, but to verify this we have installed a new server with SQL2000
> SP3a + MS03-031 Security Fix (which ends up with the same version as we
> have on the affected server - 8.00.818) but we are facing the same problem
> with this new server.
> It could of course also be caused by the Oracle server, but we have tried
> it against 2 different Oracle databases (different applications) running
> on different physical servers and in both cases the problem are the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator|||I agree with Russell.
To avoid issues like this try using Openquery.
SELECT *
FROM OPENQUERY(OracleSvr, 'declare @.medanr varchar(255)
set @.medanr = '''06131'''
select PERIODSTART from RAMAIN60.TIMESHEETLINE
Where EMPLOYEENUMBER = @.medanr
')
GO
Markus|||On Mar 28, 7:12 pm, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi
> We are facing a quite strange performance issue when using a linked
> server to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER =3D '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr =3D '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER =3D @.medanr
> This query runs for approx. 14 minutes and returns the same number of row=s=2E
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that
> the problem is that Oracle can't use an index when the query contains a
> WHERE clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded
> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
> the reason, but to verify this we have installed a new server with
> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
> version as we have on the affected server - 8.00.818) but we are facing
> the same problem with this new server.
> It could of course also be caused by the Oracle server, but we have
> tried it against 2 different Oracle databases (different applications)
> running on different physical servers and in both cases the problem are
> the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator
We had the same problem . If you check dynamic Properties and nested
query in provider options , it may improve query performance for
variables . These options are available when you create link server|||M A Srinivas wrote:
> On Mar 28, 7:12 pm, "Steen Schlüter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>> Hi
>> We are facing a quite strange performance issue when using a linked
>> server to an Oracle server.
>> When we run a query containing a WHERE clause using a variable, it takes
>> ages for SQL server to return the data. When we instead just type in the
>> value for the WHERE clause it returns the data as fast as expected.
>> The queries we have used for testing is these:
>> 1:
>> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
>> EMPLOYEENUMBER = '06131'
>> This query returns 6165 rows in 5 seconds which is normal performance.
>> 2:
>> declare @.medanr varchar(255)
>> set @.medanr = '06131'
>> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
>> EMPLOYEENUMBER = @.medanr
>> This query runs for approx. 14 minutes and returns the same number of rows.
>> We can see that the difference in execution times increases with the
>> number of rows the table contains. Without being sure, we suspect that
>> the problem is that Oracle can't use an index when the query contains a
>> WHERE clause using a variable, but why?
>> It have worked for us before (actually the code giving problems has been
>> running for years).
>> What has happened recently, is that the SQL server has been upgraded
>> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
>> the reason, but to verify this we have installed a new server with
>> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
>> version as we have on the affected server - 8.00.818) but we are facing
>> the same problem with this new server.
>> It could of course also be caused by the Oracle server, but we have
>> tried it against 2 different Oracle databases (different applications)
>> running on different physical servers and in both cases the problem are
>> the same.
>> Furthermore we have also tried it from a SQL2005 server against the same
>> Oracle databases but with same problem.
>> Right now we are running out if ideas and don't really know what to go
>> for..........
>> Has any one seem similar issues or has any good ideas?
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
> We had the same problem . If you check dynamic Properties and nested
> query in provider options , it may improve query performance for
> variables . These options are available when you create link server
>
Thanks for all the inputs. We have already been down the route with
the various providers options, and as far as I remember we have already
tried with the "Nested queries" option but to no avail.
Using OPENQUERY could be an option if it was a new Stored Proc or code
we where writing, but at the moments we have hundreds of SP's where
those linked servers are involved so it's not that easy to rewrite the
all these SP's.
Russels suspicion about how the where clause is being evaluated, is
along the lines we have found out as well based on the execution plan
for the query. I don't know if we can rely on that, but the execution
plan also indicates that when we are using a variable, all the rows from
the Oracle table is being retrieved and then the filter is being applied
on the SQL server side. This still doesn't help us a lot because it's
not that easy to rewrite the code and also it puzzles me why it has
happened in the first place. Especially because we have now set up a new
server with the same SQL server version/SP level as the affected server
had before we applied SP4, but that still causes the same problem.
I'll look into some of your ideas/suggstion tomorrow to see if anything
can point us in he right direction.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Poor Linked Server performance when running a query with a variable
Hi
We are facing a quite strange performance issue when using a linked
server to an Oracle server.
When we run a query containing a WHERE clause using a variable, it takes
ages for SQL server to return the data. When we instead just type in the
value for the WHERE clause it returns the data as fast as expected.
The queries we have used for testing is these:
1:
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
EMPLOYEENUMBER = '06131'
This query returns 6165 rows in 5 seconds which is normal performance.
2:
declare @.medanr varchar(255)
set @.medanr = '06131'
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
EMPLOYEENUMBER = @.medanr
This query runs for approx. 14 minutes and returns the same number of rows.
We can see that the difference in execution times increases with the
number of rows the table contains. Without being sure, we suspect that
the problem is that Oracle can't use an index when the query contains a
WHERE clause using a variable, but why?
It have worked for us before (actually the code giving problems has been
running for years).
What has happened recently, is that the SQL server has been upgraded
from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
the reason, but to verify this we have installed a new server with
SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
version as we have on the affected server - 8.00.818) but we are facing
the same problem with this new server.
It could of course also be caused by the Oracle server, but we have
tried it against 2 different Oracle databases (different applications)
running on different physical servers and in both cases the problem are
the same.
Furthermore we have also tried it from a SQL2005 server against the same
Oracle databases but with same problem.
Right now we are running out if ideas and don't really know what to go
for..........
Has any one seem similar issues or has any good ideas?
Regards
Steen Schlter Persson
Database Administrator / System AdministratorWell, I am pretty sure that I know what has happened, but not why. Of
course, every SP includes optimizer and other engine updates, so things do
change. Here is what I think is happening:
Where EMPLOYEENUMBER = '06131' -- Evaluated on the Oracle side of the link
Where EMPLOYEENUMBER = @.medanr -- Evaluated on the SQL Server side of the
link.
You should be able to trace the communication between the servers to see how
much I/O is happening between the servers. I believe that to evaluate
@.medanr the Oracle server is delivering every row from
MACONOMY..RAMAIN60.TIMESHEETL to the SQL Server so that the filter can be
applied.
To test this out, you could try first pushing the filter value to a work
table on Oracle. Then Oracle can do the whole join on its side and return
only the 6165 rows, by:
INSERT INTO MACONOMY..RAMAIN60.TIMESHEETWORKTABLE
(FilterValue) VALUES ('06131')
select T.PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE T
JOIN MACONOMY..RAMAIN60.TIMESHEETWORKTABLE W
ON T.EMPLOYEENUMBER = W.EMPLOYEENUMBER
If that gives you the performance you were used to, then the problem has
been identified. Maybe that will help you know what avenue you want to
pursue.
RLF
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1dCtMUcHHA.4388@.TK2MSFTNGP05.phx.gbl...
> Hi
> We are facing a quite strange performance issue when using a linked server
> to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER = '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr = '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER = @.medanr
>
> This query runs for approx. 14 minutes and returns the same number of
> rows.
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that the
> problem is that Oracle can't use an index when the query contains a WHERE
> clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded from
> SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be the
> reason, but to verify this we have installed a new server with SQL2000
> SP3a + MS03-031 Security Fix (which ends up with the same version as we
> have on the affected server - 8.00.818) but we are facing the same problem
> with this new server.
> It could of course also be caused by the Oracle server, but we have tried
> it against 2 different Oracle databases (different applications) running
> on different physical servers and in both cases the problem are the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator|||I agree with Russell.
To avoid issues like this try using Openquery.
SELECT *
FROM OPENQUERY(OracleSvr, 'declare @.medanr varchar(255)
set @.medanr = '''06131'''
select PERIODSTART from RAMAIN60.TIMESHEETLINE
Where EMPLOYEENUMBER = @.medanr
')
GO
Markus|||On Mar 28, 7:12 pm, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi
> We are facing a quite strange performance issue when using a linked
> server to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER =3D '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr =3D '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER =3D @.medanr
> This query runs for approx. 14 minutes and returns the same number of row=
s=2E
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that
> the problem is that Oracle can't use an index when the query contains a
> WHERE clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded
> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
> the reason, but to verify this we have installed a new server with
> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
> version as we have on the affected server - 8.00.818) but we are facing
> the same problem with this new server.
> It could of course also be caused by the Oracle server, but we have
> tried it against 2 different Oracle databases (different applications)
> running on different physical servers and in both cases the problem are
> the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator
We had the same problem . If you check dynamic Properties and nested
query in provider options , it may improve query performance for
variables . These options are available when you create link server|||M A Srinivas wrote:
> On Mar 28, 7:12 pm, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> We had the same problem . If you check dynamic Properties and nested
> query in provider options , it may improve query performance for
> variables . These options are available when you create link server
>
Thanks for all the inputs. We have already been down the route with
the various providers options, and as far as I remember we have already
tried with the "Nested queries" option but to no avail.
Using OPENQUERY could be an option if it was a new Stored Proc or code
we where writing, but at the moments we have hundreds of SP's where
those linked servers are involved so it's not that easy to rewrite the
all these SP's.
Russels suspicion about how the where clause is being evaluated, is
along the lines we have found out as well based on the execution plan
for the query. I don't know if we can rely on that, but the execution
plan also indicates that when we are using a variable, all the rows from
the Oracle table is being retrieved and then the filter is being applied
on the SQL server side. This still doesn't help us a lot because it's
not that easy to rewrite the code and also it puzzles me why it has
happened in the first place. Especially because we have now set up a new
server with the same SQL server version/SP level as the affected server
had before we applied SP4, but that still causes the same problem.
I'll look into some of your ideas/suggstion tomorrow to see if anything
can point us in he right direction.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
We are facing a quite strange performance issue when using a linked
server to an Oracle server.
When we run a query containing a WHERE clause using a variable, it takes
ages for SQL server to return the data. When we instead just type in the
value for the WHERE clause it returns the data as fast as expected.
The queries we have used for testing is these:
1:
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
EMPLOYEENUMBER = '06131'
This query returns 6165 rows in 5 seconds which is normal performance.
2:
declare @.medanr varchar(255)
set @.medanr = '06131'
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
EMPLOYEENUMBER = @.medanr
This query runs for approx. 14 minutes and returns the same number of rows.
We can see that the difference in execution times increases with the
number of rows the table contains. Without being sure, we suspect that
the problem is that Oracle can't use an index when the query contains a
WHERE clause using a variable, but why?
It have worked for us before (actually the code giving problems has been
running for years).
What has happened recently, is that the SQL server has been upgraded
from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
the reason, but to verify this we have installed a new server with
SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
version as we have on the affected server - 8.00.818) but we are facing
the same problem with this new server.
It could of course also be caused by the Oracle server, but we have
tried it against 2 different Oracle databases (different applications)
running on different physical servers and in both cases the problem are
the same.
Furthermore we have also tried it from a SQL2005 server against the same
Oracle databases but with same problem.
Right now we are running out if ideas and don't really know what to go
for..........
Has any one seem similar issues or has any good ideas?
Regards
Steen Schlter Persson
Database Administrator / System AdministratorWell, I am pretty sure that I know what has happened, but not why. Of
course, every SP includes optimizer and other engine updates, so things do
change. Here is what I think is happening:
Where EMPLOYEENUMBER = '06131' -- Evaluated on the Oracle side of the link
Where EMPLOYEENUMBER = @.medanr -- Evaluated on the SQL Server side of the
link.
You should be able to trace the communication between the servers to see how
much I/O is happening between the servers. I believe that to evaluate
@.medanr the Oracle server is delivering every row from
MACONOMY..RAMAIN60.TIMESHEETL to the SQL Server so that the filter can be
applied.
To test this out, you could try first pushing the filter value to a work
table on Oracle. Then Oracle can do the whole join on its side and return
only the 6165 rows, by:
INSERT INTO MACONOMY..RAMAIN60.TIMESHEETWORKTABLE
(FilterValue) VALUES ('06131')
select T.PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE T
JOIN MACONOMY..RAMAIN60.TIMESHEETWORKTABLE W
ON T.EMPLOYEENUMBER = W.EMPLOYEENUMBER
If that gives you the performance you were used to, then the problem has
been identified. Maybe that will help you know what avenue you want to
pursue.
RLF
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1dCtMUcHHA.4388@.TK2MSFTNGP05.phx.gbl...
> Hi
> We are facing a quite strange performance issue when using a linked server
> to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER = '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr = '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER = @.medanr
>
> This query runs for approx. 14 minutes and returns the same number of
> rows.
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that the
> problem is that Oracle can't use an index when the query contains a WHERE
> clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded from
> SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be the
> reason, but to verify this we have installed a new server with SQL2000
> SP3a + MS03-031 Security Fix (which ends up with the same version as we
> have on the affected server - 8.00.818) but we are facing the same problem
> with this new server.
> It could of course also be caused by the Oracle server, but we have tried
> it against 2 different Oracle databases (different applications) running
> on different physical servers and in both cases the problem are the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator|||I agree with Russell.
To avoid issues like this try using Openquery.
SELECT *
FROM OPENQUERY(OracleSvr, 'declare @.medanr varchar(255)
set @.medanr = '''06131'''
select PERIODSTART from RAMAIN60.TIMESHEETLINE
Where EMPLOYEENUMBER = @.medanr
')
GO
Markus|||On Mar 28, 7:12 pm, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi
> We are facing a quite strange performance issue when using a linked
> server to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER =3D '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr =3D '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER =3D @.medanr
> This query runs for approx. 14 minutes and returns the same number of row=
s=2E
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that
> the problem is that Oracle can't use an index when the query contains a
> WHERE clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded
> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
> the reason, but to verify this we have installed a new server with
> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
> version as we have on the affected server - 8.00.818) but we are facing
> the same problem with this new server.
> It could of course also be caused by the Oracle server, but we have
> tried it against 2 different Oracle databases (different applications)
> running on different physical servers and in both cases the problem are
> the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator
We had the same problem . If you check dynamic Properties and nested
query in provider options , it may improve query performance for
variables . These options are available when you create link server|||M A Srinivas wrote:
> On Mar 28, 7:12 pm, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> We had the same problem . If you check dynamic Properties and nested
> query in provider options , it may improve query performance for
> variables . These options are available when you create link server
>
Thanks for all the inputs. We have already been down the route with
the various providers options, and as far as I remember we have already
tried with the "Nested queries" option but to no avail.
Using OPENQUERY could be an option if it was a new Stored Proc or code
we where writing, but at the moments we have hundreds of SP's where
those linked servers are involved so it's not that easy to rewrite the
all these SP's.
Russels suspicion about how the where clause is being evaluated, is
along the lines we have found out as well based on the execution plan
for the query. I don't know if we can rely on that, but the execution
plan also indicates that when we are using a variable, all the rows from
the Oracle table is being retrieved and then the filter is being applied
on the SQL server side. This still doesn't help us a lot because it's
not that easy to rewrite the code and also it puzzles me why it has
happened in the first place. Especially because we have now set up a new
server with the same SQL server version/SP level as the affected server
had before we applied SP4, but that still causes the same problem.
I'll look into some of your ideas/suggstion tomorrow to see if anything
can point us in he right direction.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Saturday, February 25, 2012
Please Help: Cannot Sort row of size 8104: any advice.
I have a database linked too a website that, we've just put the live data into and hay presto the thing has fallen over. The data has gone in fine but When we search on it it coming up with this error:
Microsoft OLE DB Provider for SQL Server (0x80040E14) Cannot sort a row of size 8104, which is greater than the allowable maximum of 8094.
Has anyone come across this kinda of problem before and is their any nice work arounds. Thanks EdObviously, you managed to get a row size which is 10 more than the maximum. Consider to shorten one of your VARCHAR(x) fields by 10.|||Okay I think I understand the problem now, But I'm still slightly stuck;
I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.
So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?
If it any help the SQL look like this:
SELECT DISTINCT
terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
subSections.SUBName, sections.SCTName
FROM terms INNER JOIN
linkSectorSection INNER JOIN
linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
subSections ON linkSectionSub.SubID = subSections.AutoID
WHERE (linkSectorSection.SectorID = 1)
AND (terms.TermName LIKE '%mortgage%')
OR (linkSectorSection.SectorID = 1)
AND (terms.TermShortDesc LIKE '%mortgage%')
ORDER BY terms.TermName
And the field causing the problems is:
terms.TermShortDesc
Thanks Again|||Originally posted by Nixies
Okay I think I understand the problem now, But I'm still slightly stuck;
I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.
So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?
If it any help the SQL look like this:
SELECT DISTINCT
terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
subSections.SUBName, sections.SCTName
FROM terms INNER JOIN
linkSectorSection INNER JOIN
linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
subSections ON linkSectionSub.SubID = subSections.AutoID
WHERE (linkSectorSection.SectorID = 1)
AND (terms.TermName LIKE '%mortgage%')
OR (linkSectorSection.SectorID = 1)
AND (terms.TermShortDesc LIKE '%mortgage%')
ORDER BY terms.TermName
And the field causing the problems is:
terms.TermShortDesc
Thanks Again
I'm assuming that TermShortDesc is a text field not a varchar.
You have two possible solutions:
Fix the problem short term:
SELECT terms.AutoId, LEN(terms.TermShortDesc) AS CHAR_LEN, terms.TermShortDesc
FROM TERMS
WHERE LEN(terms.TermShortDesc) >8093.
and then edit that data down in length.
The permanent solution is:
Before you get to deep into this, ask yourself these questions:
Do you have to search the entire field? Can you limit data to 8093 characters? In your search do you really have to go beyond the first 500 characters? 1000?
For a permanent solution if you only need the first 500 characters, then make an in your import that TermShortDescSort field and just take the LEFT(TermShortDesc,500) and insert them into TermShortDescSort on import. If you need to go beyond and have all 24,282, then make 3 fields TermShortDesc1, TermShortDesc2, TermShortDesc3 and on import insert into them as Substr(TermShortDesc,1,8094), Substr(TermShortDesc,8095,8094), Substr(TermShortDesc,16188,8094).
Just throwing in my 2 centavos....|||This is fantasic, we've found the offending rows and everything is up and working, Thanks for your Help, Ed
Microsoft OLE DB Provider for SQL Server (0x80040E14) Cannot sort a row of size 8104, which is greater than the allowable maximum of 8094.
Has anyone come across this kinda of problem before and is their any nice work arounds. Thanks EdObviously, you managed to get a row size which is 10 more than the maximum. Consider to shorten one of your VARCHAR(x) fields by 10.|||Okay I think I understand the problem now, But I'm still slightly stuck;
I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.
So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?
If it any help the SQL look like this:
SELECT DISTINCT
terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
subSections.SUBName, sections.SCTName
FROM terms INNER JOIN
linkSectorSection INNER JOIN
linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
subSections ON linkSectionSub.SubID = subSections.AutoID
WHERE (linkSectorSection.SectorID = 1)
AND (terms.TermName LIKE '%mortgage%')
OR (linkSectorSection.SectorID = 1)
AND (terms.TermShortDesc LIKE '%mortgage%')
ORDER BY terms.TermName
And the field causing the problems is:
terms.TermShortDesc
Thanks Again|||Originally posted by Nixies
Okay I think I understand the problem now, But I'm still slightly stuck;
I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.
So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?
If it any help the SQL look like this:
SELECT DISTINCT
terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
subSections.SUBName, sections.SCTName
FROM terms INNER JOIN
linkSectorSection INNER JOIN
linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
subSections ON linkSectionSub.SubID = subSections.AutoID
WHERE (linkSectorSection.SectorID = 1)
AND (terms.TermName LIKE '%mortgage%')
OR (linkSectorSection.SectorID = 1)
AND (terms.TermShortDesc LIKE '%mortgage%')
ORDER BY terms.TermName
And the field causing the problems is:
terms.TermShortDesc
Thanks Again
I'm assuming that TermShortDesc is a text field not a varchar.
You have two possible solutions:
Fix the problem short term:
SELECT terms.AutoId, LEN(terms.TermShortDesc) AS CHAR_LEN, terms.TermShortDesc
FROM TERMS
WHERE LEN(terms.TermShortDesc) >8093.
and then edit that data down in length.
The permanent solution is:
Before you get to deep into this, ask yourself these questions:
Do you have to search the entire field? Can you limit data to 8093 characters? In your search do you really have to go beyond the first 500 characters? 1000?
For a permanent solution if you only need the first 500 characters, then make an in your import that TermShortDescSort field and just take the LEFT(TermShortDesc,500) and insert them into TermShortDescSort on import. If you need to go beyond and have all 24,282, then make 3 fields TermShortDesc1, TermShortDesc2, TermShortDesc3 and on import insert into them as Substr(TermShortDesc,1,8094), Substr(TermShortDesc,8095,8094), Substr(TermShortDesc,16188,8094).
Just throwing in my 2 centavos....|||This is fantasic, we've found the offending rows and everything is up and working, Thanks for your Help, Ed
Subscribe to:
Posts (Atom)