SQL Server query improve performance on linked server

Question

How can I improve the next SQL Server query?

SELECT SUM(Qty) 
FROM Products
WHERE Type = 'SODA'
AND (Code LIKE 'A5%'
OR Code IN('DHA2','JHU8','KML2','LQA1','ZSX2'))

Takes a lot of time in execution.

Thanks in advance.

I'm getting data from a DB2 database through Linked Server. I can't build indexes just read.

Id   int
Name nvarchar(100)
Type nvarchar(100)
Code nvarchar(100)
Qty  int

Is there an index on the column Code? If not, the Like operator will force a serial read. Creating an Explain Plan will help determine where the performance issue is. — ron tornambe
– ron tornambe, Commented Jun 24, 2014 at 17:12
Can you post the execution plan along with any indexes you already have. For what it's worth if you run this query in SQL Server Management Studio with the option "Include Actual Execution plan" enabled, SQL Server will suggest indexes that will improve your query. This is probably going to be far more useful than any answer given here, since we do not know anything about your table, just that it contains at least 3 columns (Qty, Type and Code). — GarethD
– GarethD, Commented Jun 24, 2014 at 17:15
The probable answer is create the following index CREATE NONCLUSTERED INDEX IX_Products_Code ON Products (Code) INCLUDE (Qty) WHERE Type = 'SODA' - However that index is geared entirely towards this query, and probably not much help on your other queries. — GarethD
– GarethD, Commented Jun 24, 2014 at 17:18

Gordon Linoff · Accepted Answer · 2014-06-24 17:24:21Z

5

First, try creating an index on products(type, code, qty). That might greatly improve performance.

Then, if that doesn't work, try rewriting the query as:

select sum(qty)
from ((select qty
       from products
       where type = 'SODA' and
             code like 'A5%'
      ) union all
      (select qty
       from products
       where type = 'SODA' and
             code IN ('DHA2','JHU8','KML2','LQA1','ZSX2'))
      )
     ) t;

This may look more complicated but sometimes or interferes with query optimization.

answered Jun 24, 2014 at 17:24

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Baltico Over a year ago

The person who asked stated that he cannot create indexes. So this is not the right answer because splitting the query into two queries on a table without indexes will cause two complete table scans instead of one. So the query will perform even slower.

Gordon Linoff Over a year ago

@Baltico . . . If you look at the edit history, you will see that this comment was inserted into the question after this answer was completed.

Baltico Over a year ago

Thanks Gordon, I did not know that was possible. Of course without that restriction adding indexes will be the best advice.

user3727926 · Accepted Answer · 2014-06-24 18:47:39Z

3

This is a linked server query. You don't state how you're executing it so I'm assuming it is using the linked server name in the FROM. If you look at the query plan, all you will see is Remote Query --> Computer Scalar --> Select. That basically means all of the data you are wanting to compute is copied into tempDB then calculated.

If you do the query as an EXEC at or OPENQUERY the query is executed remotely then the only thing to return is the result.

Try this:

select *
from 
openquery(LinkedServerName, '   
SELECT SUM(Qty) Total
FROM Products
WHERE Type = ''SODA''
AND (Code LIKE ''A5%''
OR Code IN(''DHA2'',''JHU8'',''KML2'',''LQA1'',''ZSX2'')) ')

answered Jun 24, 2014 at 18:47

user3727926

1062 bronze badges

Comments

Baltico · Accepted Answer · 2014-06-24 19:03:57Z

Since you using a linked server and also you are string comparisons, I am pretty sure your slow performance is because SQL Server has to bring the whole Product table from the DB2 database to a local copy, then compare the strings according to your current collation settings. You see, comparing strings may have different results on different collation settings, and on different database systems. When linking between Microsoft SQL Servers databases you can enable Collation compatibility so when the query is executed it will trust the linked server decision about string comparison and sorting. I don't know if collation compatibility is possible between Microsoft SQL Server and DB2. So the solution to the problem will be to use OPENROWSET instead of a linked server.

Something like this:

 SELECT * FROM OPENROWSET 
 ('DB2OLEDB',Netlib=SNA;NetAddr=;NetPort=;RemoteLU=OLYMPIA;LocalLU=LOCAL;ModeName=QPCSUPP;User ID=WNW3XX;Password=WNW3XX;InitCat=OLYMPIA;Default Schema=WNW3XX;PkgCol=WNW3XX;TPName=;Commit=YES;IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=Sample',
 'SELECT SUM(Qty) 
FROM Products
WHERE Type = ''SODA''
AND (Code LIKE ''A5%''
OR Code IN(''DHA2'',''JHU8'',''KML2'',''LQA1'',''ZSX2'')) ' )

See more info in linked server to DB2 using Microsoft OLE DB provider for DB2

And in OPENROWSET (Transact-SQL)

Alireza · Accepted Answer · 2014-06-24 20:25:40Z

0

If it takes that long, I suggest you create a table with the same structure, then build some helpful indexes, then execute your query against this local table:

DELETE FROM Products

INSERT INTO Products
SELECT * FROM RemoteServer.database_name..Products

SELECT SUM(Qty) 
FROM Products
WHERE Type = 'SODA'
AND (Code LIKE 'A5%'
OR Code IN('DHA2','JHU8','KML2','LQA1','ZSX2'))

answered Jun 24, 2014 at 20:25

Alireza

5,0561 gold badge26 silver badges37 bronze badges

Collectives™ on Stack Overflow

SQL Server query improve performance on linked server

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related