help tuning query

Posted in 2010

Topics: Performance & Tuning, SQL Development & Query Writing, Cloud, Docker & Containers

Floyd Wellershaus — Fri, 21 May 2010 05:02:27 -0500 (CDT) — source: Usenet: comp.databases.informix

I've tried and can't get it any significantly faster. I've tried with a composite index on first_name,last_name,street1 and birth_dt, which prevents a scan of the first subquery, and I've tried without it. Either way it's about the same. I've tried using hash joins and without. About the same. Maybe a little quicker with hash. Looks like they are searching for duplicates in the table, joining on itself. Is there any ideas from those really good at sql ? Any obvious flaws ? The query used to work ok when they first made it, but some of these semi-permanent submission tables get big, and the query takes 3-4 minutes. Any help would be appreciated. Thank you ! Floyd Here is the explain plan. QUERY: ------ SELECT {+USE_HASH (s701362/build)} t1.token AS __token, t1.first_name AS firstName, t1.initial as middleInitial, t1.last_name as lastName, substr(t1.street1,1,8) as streetAddress, t1.birth_dt as dateOfBirth FROM s701362 AS t1 WHERE 1 = (SELECT {+USE_HASH (s701362/build)} Count(*) FROM s701362 AS t2 WHERE NVL(UPPER(t1.first_name),'') = NVL(UPPER(t2.first_name),'') AND NVL(UPPER(t1.last_name),'') = NVL(UPPER(t2.last_name),'') AND NVL(UPPER(t1.initial),'')= NVL(UPPER(t1.initial),'') AND NVL(UPPER(substr(t1.street1,1,8)),'')=NVL(UPPER(substr(t2.street1,1,8)),'') AND NVL(t1.birth_dt,'')=NVL(t2.birth_dt,'')) AND t1.token in (select __token token from s701362_er t1 where t1.warn_id='86') --AND t1.token in (select __token token from s701362_er where warn_id='86') DIRECTIVES FOLLOWED: USE_HASH ( s701362/BUILD ) DIRECTIVES NOT FOLLOWED: Estimated Cost: 2147483647 Estimated # of Rows Returned: 5856 1) root.t1: INDEX PATH Filters: >;subquery;subquery

↪ replying to Floyd Wellershaus

thebp — Fri, 21 May 2010 11:58:26 +0100 — source: Usenet: comp.databases.informix

On 21/05/2010 11:02, Floyd Wellershaus wrote: > I've tried and can't get it any significantly faster. > I've tried with a composite index on first_name,last_name,street1 and > birth_dt, which prevents a scan of the first subquery, and I've tried > without it. Either way it's about the same. > I've tried using hash joins and without. About the same. Maybe a little > quicker with hash. > Looks like they are searching for duplicates in the table, joining on > itself. > Is there any ideas from those really good at sql ? Any obvious flaws ? > The query used to work ok when they first made it, but some of these > semi-permanent submission tables get big, and the query takes 3-4 minutes. > > Any help would be appreciated. > > Thank you ! > Floyd > > Here is the explain plan. > > QUERY: > ------ > SELECT {+USE_HASH (s701362/build)} t1.token AS __token, > t1.first_name AS firstName, > t1.initial as middleInitial, > t1.last_name as lastName, > substr(t1.street1,1,8) as streetAddress, > t1.birth_dt as dateOfBirth > FROM s701362 AS t1 > WHERE 1 = (SELECT {+USE_HASH (s701362/build)} Count(*) FROM s701362 AS > t2 WHERE NVL(UPPER(t1.first_name),'') = > NVL(UPPER(t2.first_name),'') > > AND NVL(UPPER(t1.last_name),'') = NVL(UPPER(t2.last_name),'') > AND NVL(UPPER(t1.initial),'')= NVL(UPPER(t1.initial),'') > AND > NVL(UPPER(substr(t1.street1,1,8)),'')=NVL(UPPER(substr(t2.street1,1,8)),'') > AND NVL(t1.birth_dt,'')=NVL(t2.birth_dt,'')) > AND t1.token in (select __token token from s701362_er t1 where > t1.warn_id='86') > --AND t1.token in (select __token token from s701362_er where warn_id='86') > > > DIRECTIVES FOLLOWED: > USE_HASH ( s701362/BUILD ) > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 2147483647 > Estimated # of Rows Returned: 5856 > > 1) root.t1: INDEX PATH > > Filters: <subquery> = 1 > > (1) Index Keys: token (Serial, fragments: ALL) > Lower Index Filter: root.t1.token = ANY <subquery> > > Subquery: > --------- > DIRECTIVES FOLLOWED: > USE_HASH ( s701362/BUILD ) > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 110480 > Estimated # of Rows Returned: 1 > > 1) root.t2: SEQUENTIAL SCAN > > Filters: ((((NVL (root.t2.birth_dt , '' ) = NVL (root.t1.birth_dt , '' ) > AND NVL (UPPER(SUBSTR (root.t2 > .street1 , 1 , 8 ) ) , '' ) = NVL (UPPER(SUBSTR (root.t1.street1 , 1 , 8 > ) ) , '' ) ) AND NVL (UPPER(root.t2.last_n > ame ) , '' ) = NVL (UPPER(root.t1.last_name ) , '' ) ) AND NVL > (UPPER(root.t2.first_name ) , '' ) = NVL (UPPER(root > .t1.first_name ) , '' ) ) AND NVL (UPPER(root.t1.initial ) , '' ) = NVL > (UPPER(root.t1.initial ) , '' ) ) > > > Subquery: > --------- > Estimated Cost: 187994 > Estimated # of Rows Returned: 556267 > > > > 1) root.t1: INDEX PATH > > (1) Index Keys: __token warn_id (Key-Only) (Serial, fragments: ALL) > Index Key Filters: (root.t1.warn_id = 86 ) Hi, Check out the cost : 2147483647 = 0x7FFFFFFF => Sign of an optimizer overflow :/ Version and O/S? What statistics on the table(s)?

↪ replying to thebp

Floyd Wellershaus — Fri, 21 May 2010 07:16:18 -0500 (CDT) — source: Usenet: comp.databases.informix

Never heard of an optimizer overflow. This is IDS10.0FC5 on Aix 5.3. an update stats high on the table was just done. Thanks, floyd ----- Original Message ----- From: bigpotato@usenet-news.net Sent: Fri, May 21, 2010, 7:00 AM Subject: Re: help tuning query On 21/05/2010 11:02, Floyd Wellershaus wrote: