August 29, 2012 at 12:22 pm
If I execute the following it takes over 30 minutes to execute:
SELECT COUNT(*)
FROM OPENQUERY(LSCTLR40DAT, 'SELECT * FROM BASIC_CONTRACT')
The table listed above has just over a million records.
If I execute against a smaller table it returns the result in less than a second.
If I execute the following code I get a result in less than a second:
DECLARE @SQLSchemaName NVARCHAR(20)
DECLARE @AS400SchemaName NVARCHAR(60)
DECLARE @SQLTableName NVARCHAR(60)
DECLARE @AS400TableName NVARCHAR (20)
SET @SQLSchemaName = 'CTL'
SET @AS400SchemaName = 'CTL'
SET @SQLTableName = 'Basic_Contract'
SET @AS400TableName = 'BASIC_CONTRACT'
DECLARE @sqlquery NVARCHAR(255)
DECLARE @OpenQuery NVARCHAR(255)
DECLARE @OpenQueryPrefix NVARCHAR(4000)
DECLARE @OpenQuerySuffix NVARCHAR(12)
DECLARE @DB2RowCount INT
DECLARE @LinkedServerPre VARCHAR(10)
DECLARE @LinkedServerPost VARCHAR(10)
DECLARE @MyCnt INT;
DECLARE @SQLRowCount INT
DECLARE @ParmDefinition NVARCHAR(500);
SET @sqlquery = 'SELECT @cnt = COUNT(*) FROM ' + @SQLSchemaName + '.'
+ @SQLTableName
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @sqlquery, @ParmDefinition, @cnt = @SQLRowCount OUTPUT;
SET @LinkedServerPre = 'LS'
SET @LinkedServerPost = 'R40DAT'
SET @OpenQueryPrefix = 'SELECT @cnt = AS400TableCount FROM OPENQUERY(' -- << here
SET @OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre
+ @AS400SchemaName + @LinkedServerPost
SET @OpenQueryPrefix = @OpenQueryPrefix
+ ', ''SELECT COUNT(*) AS400TableCount FROM ' -- << and here
SET @OpenQuerySuffix = ''')'
SET @OpenQuery = @OpenQueryPrefix + @AS400TableName + @OpenQuerySuffix
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @DB2RowCount OUTPUT;
SELECT @SQLRowCount AS SQLRowCount ,
@DB2RowCount AS DB2RowCount;
GO
Any ideas on what could causing this?
I did not seem to have this problem on other large tables before.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 29, 2012 at 12:52 pm
What about having the remote server handling the count?
SELECT mycount
FROM OPENQUERY(LSCTLR40DAT, 'SELECT count(*) mycount FROM BASIC_CONTRACT')
Could also be due activity (locking...) on BASIC_CONTRACT
August 29, 2012 at 12:59 pm
Thanks Jo.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 29, 2012 at 1:25 pm
"SELECT COUNT() ..." is usually faster than "SELECT * ...", especially over a network connection.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 29, 2012 at 1:28 pm
Yes faster especially over a slow network connection.
Bad code.:w00t:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 29, 2012 at 2:17 pm
For those reading this and wondering why... It is because when you pull the data through OPENQUERY and do the count "locally," you have to move all of the data over the network before you can do the count. If you allow the "remote" server to do the count for you, all it has to do is return 1 value over the network pipeline.
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply