sql - extremly disproportionate running time for query. Trying to understand RDBMS -

-

i working customers database (db2) our company has read access. hence don't have control on indices, schema , like. want emphasize point, because there still might indices don't know where. have optimize our query differently. anyway, i'm more interested in understanding how these things work rather in workaround (although i'm interested in too). expected dbs fast these kind of operations apparently not. or maybe running serious pitfall.

the scenario: dissected our query , stripped down following incomprehensible me. let call query q1 of form

select c.cid, c.bid, c.ryear, t.tyear, td.nr  myschema.ctable c  join myschema.dtable d on d.cid = c.cid  join myschema.iptable ip on (ip.did = d.did , ip.type = 'type_s')  join myschema.ttable t on t.xtime = ip.xtime  join myschema.tdtable td on c.tdid = 'type_'||td.oid  c.ryear = 2009  , d.rr = 'ugk'  , d.stat = 'stat#1' ;

this query retrieves 8000 entries , takes on minute execute.

now odd part: if remove single of conditions in where clause, query takes less 2 secs execute. completeness: in case remove ryear result set returns 10000 entries. if remove d.stat = 'stat#1' result set contains 45000 entries , if remove d.rr = 'ugk' exact same results if don't remove (i.e. in specific case condition redundant result set).

can explain me how/why enormous time-difference happens if have 3 conditions? if put 2 conditions , grep on cli third 1 i'm considerably faster. heck db doing?

note: i'm using dbvisualizer run queries. when execute in time x, mean execution time of query, not fetching time (see: http://www.dbvis.com/forum/thread.jspa?threadid=1536). although fetching time increases drastically if have 3 statements (about 10 minutes!) retrieve exact same results if exclude , d.rr = 'ugk' part. case have 2 conditions, fetch time @ 10 secs (namely when retrieving 45000 entries). sake of completeness, if have no where @ 130000 entries (exec: 1.8secs, fetch 28secs)

my question: going on? can single statement in where-clause blow dbs execution plan badly?

p.s.: i'm sorry name of tables/columns not more expressive, had obfuscate them bit obvious reasons. p.p.s.: i'd glad edit title if can find more suited.

a slow query indicative of full table scan. come through index rather table scan. there may tools in ibm data studio identify cause of table scan.

i suggest following indexes:

set schema myschema; create index ctable_ryear on ctable(ryear) include (cid, bid, tdid); create index dtable_rr_stat_cid on dtable(rr, stat, cid) include (did); create index iptable_did_type on iptable(did, type) include (xtime); create index ttable_xtime on ttable(xtime) include (tyear);

there's way optimize access tdtable well.


Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more