Hello.
W2K, db2 v7, fp11.
Given:
create table pays (
acode integer not null,
packno smallint not null,
sum decimal(15, 2) not null
);
create index pays1 on pays(packno);
create table abonents (
acode integer not null primary key,
name varchar(80) not null,
specauth smallint not null
);
create view abonents_spec (acode, name) as
select acode, case when specauth<2 then name else '*' end
from abonents;
I have to issue this query:
(1)
select p.acode, p.sum, a.name
from pays p
left join abonents_spec a on p.acode=a.acode
where p.packno=:packn o;
In this case I have index scan of 'pays' (as expected) and TABLE scan of
'abonents', but in case of using in (1) inner join or left join with table
'abonents' instead of view 'abonents_spec' I get expected INDEX scan of
tabel 'abonents'!!!
Tables are about 200 000 rows. Statistics is OK. There is no special
distirbution of data in these tables.
I have tried all optimization levels.
I think it is very strange behavior of the optimizer...
I had to rewrite (1) with emulation of the left join to make optimizer use
index scan of table 'abonents':
(2)
with p (acode, sum) as (
select acode, sum
from pays where packno=:packno
)
select p.acode, p.sum, a.name as name
from p
join abonents_spec a on p.acode=a.acode
union all
select p.acode, p.sum, cast(NULL as varchar(80)) as name
from p
where not exists (select 1 from abonents_spec a on p.acode=a.acode );
With (2) I had ~ 2 or 3 times worse performance then (1) with using table
'abonents' instead of view 'abonents_spec' , but it is definitely faster then
(1).
What do you think about this?
Thanks in advance,
Mark. 10 2095
"Mark Barinstein" <ma**@NO.SPAM.A NY.MOREcrk.vsi. ru> wrote in message
news:c6******** ***@serv.vrn.ru ... Hello.
W2K, db2 v7, fp11.
Given:
create table pays ( acode integer not null, packno smallint not null, sum decimal(15, 2) not null ); create index pays1 on pays(packno);
create table abonents ( acode integer not null primary key, name varchar(80) not null, specauth smallint not null );
create view abonents_spec (acode, name) as select acode, case when specauth<2 then name else '*' end from abonents;
I have to issue this query:
(1) select p.acode, p.sum, a.name from pays p left join abonents_spec a on p.acode=a.acode where p.packno=:packn o;
In this case I have index scan of 'pays' (as expected) and TABLE scan of 'abonents', but in case of using in (1) inner join or left join with
table 'abonents' instead of view 'abonents_spec' I get expected INDEX scan of tabel 'abonents'!!! Tables are about 200 000 rows. Statistics is OK. There is no special distirbution of data in these tables. I have tried all optimization levels. I think it is very strange behavior of the optimizer...
I had to rewrite (1) with emulation of the left join to make optimizer use index scan of table 'abonents':
(2) with p (acode, sum) as ( select acode, sum from pays where packno=:packno ) select p.acode, p.sum, a.name as name from p join abonents_spec a on p.acode=a.acode union all select p.acode, p.sum, cast(NULL as varchar(80)) as name from p where not exists (select 1 from abonents_spec a on p.acode=a.acode );
With (2) I had ~ 2 or 3 times worse performance then (1) with using table 'abonents' instead of view 'abonents_spec' , but it is definitely faster
then (1). What do you think about this?
Thanks in advance, Mark.
Mark,
It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.
I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the
view.
--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab
"Mark Barinstein" <ma**@NO.SPAM.A NY.MOREcrk.vsi. ru> wrote in message
news:c6******** ***@serv.vrn.ru ... Hello.
W2K, db2 v7, fp11.
Given:
create table pays ( acode integer not null, packno smallint not null, sum decimal(15, 2) not null ); create index pays1 on pays(packno);
create table abonents ( acode integer not null primary key, name varchar(80) not null, specauth smallint not null );
create view abonents_spec (acode, name) as select acode, case when specauth<2 then name else '*' end from abonents;
I have to issue this query:
(1) select p.acode, p.sum, a.name from pays p left join abonents_spec a on p.acode=a.acode where p.packno=:packn o;
In this case I have index scan of 'pays' (as expected) and TABLE scan of 'abonents', but in case of using in (1) inner join or left join with
table 'abonents' instead of view 'abonents_spec' I get expected INDEX scan of tabel 'abonents'!!! Tables are about 200 000 rows. Statistics is OK. There is no special distirbution of data in these tables. I have tried all optimization levels. I think it is very strange behavior of the optimizer...
I had to rewrite (1) with emulation of the left join to make optimizer use index scan of table 'abonents':
(2) with p (acode, sum) as ( select acode, sum from pays where packno=:packno ) select p.acode, p.sum, a.name as name from p join abonents_spec a on p.acode=a.acode union all select p.acode, p.sum, cast(NULL as varchar(80)) as name from p where not exists (select 1 from abonents_spec a on p.acode=a.acode );
With (2) I had ~ 2 or 3 times worse performance then (1) with using table 'abonents' instead of view 'abonents_spec' , but it is definitely faster
then (1). What do you think about this?
Thanks in advance, Mark.
Mark,
It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.
I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the
view.
--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab It appears that the unique index on abonets (implicitly created because of the primary key definition) isn't seen by the optimizer through the view.
I was able to reproduce this bizarre behaviour on v72 FP11. On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using
the view.
Matt,
What shall I do, wait for fp12 or forget about v7 at all?
In our project there are a lot of such queries...
Mark. It appears that the unique index on abonets (implicitly created because of the primary key definition) isn't seen by the optimizer through the view.
I was able to reproduce this bizarre behaviour on v72 FP11. On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using
the view.
Matt,
What shall I do, wait for fp12 or forget about v7 at all?
In our project there are a lot of such queries...
Mark.
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c6******* ***@hanover.tor olab.ibm.com>.. . If you have the choice you should go to V8.1 anyway. V7.2 will go out of support pretty soon.
Cheers Serge
Do you know when FP12 will come out for V7?
Scot
Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c6******* ***@hanover.tor olab.ibm.com>.. . If you have the choice you should go to V8.1 anyway. V7.2 will go out of support pretty soon.
Cheers Serge
Do you know when FP12 will come out for V7?
Scot
Probably late May or early June.
Scot wrote: Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c6******* ***@hanover.tor olab.ibm.com>.. .
If you have the choice you should go to V8.1 anyway. V7.2 will go out of support pretty soon.
Cheers Serge
Do you know when FP12 will come out for V7?
Scot This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ryan |
last post by:
Bit of an obscure one here, so please bear with me. I have two copies
of a database which should be identical. Both have a complex view
which is identical. I can open the views and the data is as expected
and match. I can query it in several ways as detailed below. The 5th
version of the simple query below based on the second copy of the view
fails, but works under the first copy.
/*1 Statement below works*/
SELECT *
FROM AgentHierarchy
|
by: Carlo Paccanoni |
last post by:
I tried this:
use northwind
go
SELECT OrderDate
FROM Orders WHERE OrderDate > '19950101'
see the query plan? ok
|
by: Vinny |
last post by:
Can anyone help me with this strange problem please?
I have a stored procedure, with a parameter defined as a
uniqueidentifier. The procedure does a select with a number of joins,
and filters within the Where clause using this parameter.
(@orderHeader_id uniqueidentifier)
SELECT *
|
by: Adam Kucharski |
last post by:
Hello !!!
IBM AIX 5.1 DB2 UDB WSE v.8.1.4 (with FP4) (Polish)
DiagLevel 4
Client: Windows XP (Polish)
Problem:
2003-12-17-17.58.50.853952 Instance:db2inst1 Node:000
PID:33528(db2tcpcm) TID:1 Appid:none
|
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:
| |
by: sql-db2-dba |
last post by:
We have DB2 UDB v8.1 fixpak3 on AIX 5. Production and Development
configuarations (at least for DB2) are identical albeit production is
a 2-way server while development has only one processor. Tables and
indexes have the same schema. In fact, the dev database was taken from
a prod backup recently. Size of the tables differ slightly. Yet, on a
given query (with 4 tables joined), it took 30-50 times longer to run
in prod than on development....
|
by: Marc Mones |
last post by:
Hello,
I'working with IBM DB2 V8.1 and CLI/ODBC. I've got a problem with the
following
statement:
********************************************************************************
SELECT S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB, S_OWNER, S_SATZ
FROM SY0001_00005
WHERE S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
|
by: Ryan |
last post by:
I came across a situation that I've been unable to explain and was
hoping somebody had an answer:
I had written an update query which was taking about 8 seconds to run
and considered it too slow. I copied the SQL statement from the query
and tried executing it from code which then ran in 1 second. To make
sure that I didn't miss anything, I copied the SQL statement back into
a query and tried running it again. It now also only took 1...
|
by: Praveen_db2 |
last post by:
Hi All
I am getting strange errors in my db2diag.log can any one tell me what
these errors mean??
Following is the code from my db2diag.log
*********************************************************************************************
2006-02-23-17.53.12.253000 Instance:DB2 Node:000
PID:1600(db2syscs.exe) TID:440 Appid:AC10E010.J70A.00E883122250
base sys utilities sqleagnt_sigsegvh Probe:1 Database:DEVM_DB
Error in agent...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |