1

I have problem with query execution (DB2iseries V6R1 linked on SQL Server 2005)

When I execute it on DB2 time is like 0ms, but the same one on SQL Server goes endless. (I've waited 5 minutes)

Query only returns one row so I don't think it's data size problem.

What is more, most of other queries works fine..

Any ideas ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chris
  • 43
  • 1
  • 3
  • 1
    *Endless* is longer than *5 minutes* .... – marc_s Dec 30 '13 at 08:38
  • @marc_s should he have waited an /endless/ amount of time before posting this question? ;-) If it didn't end in 5 mins, does it matter how much longer it might have taken to respond? If it was nearly instantaneous on DB2 then its not simply waiting on a large complex query. If you talk to IBM's DB2 for i experts, they call 2 seconds a /long time/, so 5 minutes is /ages/ or /eons/ for small amounts of data, wouldn't you say? – WarrenT Dec 30 '13 at 18:22

3 Answers3

0

maybe table is locked.

send the request with nolock if possible (When should you use "with (nolock)")

Community
  • 1
  • 1
user3145799
  • 1
  • I've tried WITH(NOLOCK) but no better. I think that linked server might have problem with WHERE conditions. When I execute query without where clause, I see returned rows. – Chris Dec 30 '13 at 08:52
0

I've used OPENQUERY instead of classic one (SELECT * FROM MyLinkedServer.MyDatabase.DBO.SomeTable) and works like a charm.

Looks like openquery is using better indexing engine.

.

Dhanish Jose
  • 739
  • 1
  • 8
  • 19
Chris
  • 43
  • 1
  • 3
0

Occasionally, I've seen similar results with MS Access fetching data from DB2 on System i. Using System i Navigator or STRSQL the query runs very fast. However, when running the same exact query as an MS Access passthrough query or a query in Access against linked table(s) pointed at the host, either riding over ODBC, and the result is much slower performance. To fix it I typically embed the query into an SQL view on the host to push as much processing to the host, and to take away MS Access' inclination to tinker with the query or somehow mishandle the result set. Referencing the host SQL view from Access then typically fixes the performance problem.

If I were experiencing the same symptom with SQL Server fetching data from DB2 on i, I'd be inclined to do the same thing as it would likely maximize performance and minimize risk. Creating an SQL view on the host also creates an object that may come in handy elsewhere, creating code reuse opportunities.

Mike Jones
  • 532
  • 2
  • 9