Re: help tuning query

Posted in 2010

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

Obnoxio The Clown — Fri, 21 May 2010 14:33:27 +0100 — source: Usenet: comp.databases.informix

Floyd Wellershaus wrote: > Excellent point Obnoxio. > I did this. It runs much qucker ( like only 28 seconds as opposed to 7 > minutes ) because it's doing a dynamic hash join > It seems to provide the same info, however in a different order. Not > sure if it's really the same query anymore. > > SELECT > 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 t1,s701362 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') > > > > > *----- Original Message -----* > *From:* obnoxio@serendipita.com > *Sent:* Fri, May 21, 2010, 7:00 AM > *Subject:* Re: help tuning query > > 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 > > How fast are the queries when you run them separately? > > > 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: = 1 > > > > (1) Index Keys: token (Serial, fragments: ALL) > > Lower Index Filter: root.t1.token = ANY > > > > 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 ) What happens if you rewrite the query as a join instead of a subquery? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to Obnoxio The Clown

Superboer — Tue, 25 May 2010 00:40:39 -0700 (PDT) — source: Usenet: comp.databases.informix

i could be wrong of course and the long weekend does not help either since the weather was nice and the beer tasted way too good... it is a bit of a bummer that you need t1.token otherwise you could do SELECT count(*) , NVL(UPPER(t1.first_name),'') AS firstName, NVL(UPPER(t1.initial),'') as middleInitial, NVL(UPPER(t1.last_name),'') as lastName, NVL(UPPER(substr(t1.street1,1,8)),'') as streetAddress, NVL(t1.birth_dt,'') as dateOfBirth FROM s701362 AS t1 WHERE t1.token in (select __token token from s701362_er t1 where t1.warn_id='86') group by 2,3,4,5,6 having count(*) = 1 and use a composite functional index on token, NVL(UPPER(t1.first_name),'').... (of course build a function which does an upper and treat sqlnull as '') or store the above into a temp table with no log and fetch t1.token or create an spl and do a foreach above_select -->> then fetch the t1.token and return that with resume or?? -> 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 a normal index will do you no good since it can not use it, when it does use it; it will do a sort of seq scan on the index which is SLOOOOWW. it can not use a normal index because you do a conversion from mixed case to upper Superboer. 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,'') On 21 mei, 15:33, Obnoxio The Clown <obno...@serendipita.com> wrote: > Floyd Wellershaus wrote: > > Excellent point Obnoxio. > > I did this. It runs much qucker ( like only 28 seconds as opposed to 7 > > minutes ) because it's doing a dynamic hash join > > It seems to provide the same info, however in a different order. Not > > sure if it's really the same query anymore. > > > SELECT > > 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 t1,s701362 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') > > > *----- Original Message -----* > > *From:* obno...@serendipita.com > > *Sent:* Fri, May 21, 2010, 7:00 AM > > *Subject:* Re: help tuning query > > > 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 > > > How fast are the queries when you run them separately? > > > > 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: = 1 > > > > (1) Index Keys: token (Serial, fragments: ALL) > > > Lower Index Filter: root.t1.token = ANY > > > > 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 ) > > What happens if you rewrite the query as a join instead of a subquery? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > I will now proceed to pleasure myself with this fish. > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean.