RE: slow DSS large dataset query, help

Instead of doing the "radical" drop of indexes, force a hash join by adding "+0" to the equijoin criteria: select A.c1, A.c2, A.c3, C.c1 from Table_A A, Table_B B, Table_C C where month(A.create_timestamp) = 11 and year(A.create_timestamp) = 1995 and A.c1 = (B.c1 + 0 )and B.c2 = (C.c2 + 0 ) and B.c3 = (C.c3 + 0 ) Bill > -----Original Message----- > From: Obnoxio The Clown [SMTP:obnoxio@hotmail.com] > Sent: Thursday, September 09, 1999 7:02 AM > To: sjsyau@my-deja.com; informix-list@iiug.org > Subject: Re: slow DSS large dataset query, help > > This might seem like a rather bizarre idea, but try dropping the indexes > so > you get a hash join, rather than a nested loop join. > > You could also probably get a bit of a lift by splitting out the month and > > year so you don't have to do any computation when reading the table. > > Have you run UPDATE STATISTICS recently? (Probably best to do this first > if > you haven't! :-) > > What are the data types of the joining columns? > > From: sjsyau@my-deja.com > > > >Informix 7.3 UC3 on SUN. > > > >This is a DSS system, mostly for query. > > > >We have this query runs for 4 hours, looking for > >methods to improve performance. > > > >Please help. > > > >number 0f rows: > >Table_A : 30000000 > >Table_B : 2400000 > >Table_C : 5000 > > > >INDEX: > >Table_A.c1 > >Table_B.c1 > >TAble_B.(c2,c3) > >Table_C.(c2,c3) > > > >There is no index on Table_A.create_timestamp > >because there are only 6 different values. > > > > > >select A.c1, A.c2, A.c3, C.c1 > >from Table_A A, Table_B B, Table_C C > >where month(A.create_timestamp) = 11 > >and year(A.create_timestamp) = 1995 > >and A.c1 = B.c1 > >and B.c2 = C.c2 > >and B.c3 = C.c3> > > >The Explain OUT: > > > > > >Estimated Cost: 170427 > >Estimated # of Rows Returned: 1457 > > > >1) c: SEQUENTIAL SCAN > > > >2) b: INDEX PATH > > > > (1) Index Keys: c2 c3 > > Lower Index Filter: (b.c3 = c.c3 AND > > b.c2 = c.c2 ) > >NESTED LOOP JOIN > > > > > >3) a: INDEX PATH > > > > Filters: (MONTH (a.create_timestamp ) = 11 AND > >YEAR (a.create_timestamp ) =1995 ) > > > > (1) Index Keys: c1 > > Lower Index Filter: a.c1 = b.c1 > >NESTED LOOP JOIN > > > >Onconfig file: > > > ># Physical Log Configuration > > > >PHYSDBS dbs_plog # Location > >(dbspace) of physical log > >PHYSFILE 100000 # Physical log > >file size (Kbytes)> > > ># Logical Log Configuration > > > >LOGFILES 20 # Number of> >logical log files > >LOGSIZE 40000 # Logical log size > >(Kbytes) > > > >TBLSPACE_STATS 1> > > ># System Configuration > > > >SERVERNUM 2 # Unique id> >corresponding to a Dynamic Server in > >stance > >DBSERVERNAME prod2 # Name of default> >database server > >DBSERVERALIASES prodshm # List of> >alternate dbservernames > >NETTYPE ipcshm,1,20,NET # Configure poll> >thread(s) for nettype > >NETTYPE tlitcp,1,20,CPU # Configure poll> >thread(s) for nettype > >DEADLOCK_TIMEOUT 60 # Max time to> >wait of lock in distributed env. > >RESIDENT 1 # Forced residency > >flag (Yes = 1, No = 0) > >MULTIPROCESSOR 1 # 0 for> >single-processor, 1 for multi-processor > >NUMCPUVPS 6 # Number of user > >(cpu) vps > >SINGLE_CPU_VP 0 # If non-zero,> >limit number of cpu vps to one > >NOAGE 0 # Process aging > >AFF_SPROC 0 # Affinity start> >processor > >AFF_NPROCS 0 # Affinity number> >of processors > > > ># Shared Memory Parameters > > > >LOCKS 300000 # Maximum number> >of locks > >BUFFERS 300000 # Maximum number> >of shared buffers > >NUMAIOVPS 6 # Number of IO vps > >PHYSBUFF 32 # Physical log > >buffer size (Kbytes) > >LOGBUFF 32 # Logical log > >buffer size (Kbytes) > >LOGSMAX 30 # Maximum number> >of logical log files > >CLEANERS 50 # Number of buffer > >cleaner processes(ST 9/5/99) > >#CLEANERS 10 # Number of> >buffer cleaner processes > >SHMBASE 0xa000000 # Shared memory> >base address > > # (Release Notes > >recommend "0x0A000000L") > >SHMVIRTSIZE 2000000 # initial virtual> >shared memory segment size > >SHMADD 8192 # Size of new> >shared memory segments (Kbytes) > >SHMTOTAL 0 # Total shared > >memory (Kbytes). 0=>unlimited > >CKPTINTVL 300 # Check point > >interval (in sec) > >LRUS 50 # Number of LRU > >queues(ST 9/5/99)> >#LRUS 8 # Number of LRU > >queues > >LRU_MAX_DIRTY 30 # LRU percent> >dirty begin cleaning limit > >LRU_MIN_DIRTY 20 # LRU percent> >dirty end cleaning limit > >LTXHWM 40 # Long transaction> >high water mark percentage > >LTXEHWM 50 # Long transaction> >high water mark (exclusive) > >TXTIMEOUT 0x12c # Transaction > >timeout (in sec) > >STACKSIZE 32 # Stack size > >(Kbytes)> > > ># System Page Size > ># BUFFSIZE - Dynamic Server no longer supports > >this configuration parameter. > ># To determine the page size used by > >Dynamic Server on your platform > ># see the last line of output from the > >command, 'onstat -b'. > > > > > > > > > >OFF_RECVRY_THREADS 10 # Default> >number of offline worker threads > >ON_RECVRY_THREADS 1 # Default number> >of online worker threads > ># Data Replication Variables > ># DRAUTO: 0 manual, 1 retain type, 2 reverse type > >DRAUTO 0 # DR automatic> >switchover > >DRINTERVAL 30 # DR max time> >between DR buffer flushes (in sec) > >DRTIMEOUT 30 # DR network > >timeout (in sec) > >DRLOSTFOUND > >/usr/informix/7.30.UC3/etc/dr.lostfound > > # DR lost+found> >file path > > > ># CDR Variables > >CDR_LOGBUFFERS 2048 # size of log > >reading buffer pool (Kbytes) > >CDR_EVALTHREADS 1,2 # evaluator > >threads (per-cpu-vp,additional) > >CDR_DSLOCKWAIT 5 # DS lockwait > >timeout (seconds) > >CDR_QUEUEMEM 4096 # Maximum amount> >of memory for any CDR queue (Kb > >ytes) > > > > > ># Read Ahead Variables > >RA_PAGES 64 # Number of pages> >to attempt to read ahead > >RA_THRESHOLD 50

In article <7r8bsv$kgn$1@news.xmission.com>, William Raper <WilliamR@catmktg.com> wrote: > > Instead of doing the "radical" drop of indexes, force a hash join by adding > "+0" to the equijoin criteria: > > select > A.c1, > A.c2, > A.c3, > C.c1 > from > Table_A A, > Table_B B, > Table_C C > where > month(A.create_timestamp) = 11 and > year(A.create_timestamp) = 1995 and > A.c1 = (B.c1 + 0 )and > B.c2 = (C.c2 + 0 ) and > B.c3 = (C.c3 + 0 ) > > Bill > > > -----Original Message----- > > From: Obnoxio The Clown [SMTP:obnoxio@hotmail.com] > > Sent: Thursday, September 09, 1999 7:02 AM > > To: sjsyau@my-deja.com; informix-list@iiug.org > > Subject: Re: slow DSS large dataset query, help > > > > This might seem like a rather bizarre idea, but try dropping the indexes > > so > > you get a hash join, rather than a nested loop join. > > > > You could also probably get a bit of a lift by splitting out the month and > > > > year so you don't have to do any computation when reading the table. > > > > Have you run UPDATE STATISTICS recently? (Probably best to do this first > > if > > you haven't! :-) > > > > What are the data types of the joining columns? > > > > From: sjsyau@my-deja.com > > > > > >Informix 7.3 UC3 on SUN. > > > > > >This is a DSS system, mostly for query. > > > > > >We have this query runs for 4 hours, looking for > > >methods to improve performance. > > > > > >Please help. > > > > > >number 0f rows: > > >Table_A : 30000000 > > >Table_B : 2400000 > > >Table_C : 5000 > > > > > >INDEX: > > >Table_A.c1 > > >Table_B.c1 > > >TAble_B.(c2,c3) > > >Table_C.(c2,c3) > > > > > >There is no index on Table_A.create_timestamp > > >because there are only 6 different values. > > > > > > > > >select A.c1, A.c2, A.c3, C.c1 > > >from Table_A A, Table_B B, Table_C C > > >where month(A.create_timestamp) = 11 > > >and year(A.create_timestamp) = 1995 > > >and A.c1 = B.c1 > > >and B.c2 = C.c2 > > >and B.c3 = C.c3> > > > > >The Explain OUT: > > > > > > > > >Estimated Cost: 170427 > > >Estimated # of Rows Returned: 1457 > > > > > >1) c: SEQUENTIAL SCAN > > > > > >2) b: INDEX PATH > > > > > > (1) Index Keys: c2 c3 > > > Lower Index Filter: (b.c3 = c.c3 AND > > > b.c2 = c.c2 ) > > >NESTED LOOP JOIN > > > > > > > > >3) a: INDEX PATH > > > > > > Filters: (MONTH (a.create_timestamp ) = 11 AND > > >YEAR (a.create_timestamp ) =1995 ) > > > > > > (1) Index Keys: c1 > > > Lower Index Filter: a.c1 = b.c1 > > >NESTED LOOP JOIN > > > > > >Onconfig file: > > > hash join is better than nested loop join? SJ Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.