Join IIUG
 for   
 

Informix News
18 Nov 13 - ZDNet - Top 20 mobile skills in demand... Read
09 Sep 13 - telecompaper - Shaspa and Tatung have shown a new smart home platform at Ifa in Berlin. Powered by the IBM Informix software... Read
06 Sep 13 - IBM data magazine - Mission Accomplished - Miami, Florida will be the backdrop for the 2014 IIUG Informix Conference... Read
01 Feb 13 - IBM Data Magazine - Are your database backups safe? Lester Knutsen (IBM Champion) writes about database back up safety using "archecker"... Read
14 Nov 12 - IBM - IBM's Big Data For Smart Grid Goes Live In Texas... Read
3 Oct 12 - The Financial - IBM and TransWorks Collaborate to Help Louisiana-Pacific Corporation Achieve Supply Chain Efficiency... Read
28 Aug 12 - techCLOUD9 - Splunk kicks up a SaaS Storm... Read
10 Aug 12 - businessCLOUD9 - Is this the other half of Cloud monitoring?... Read
3 Aug 12 - IBM data management - Supercharging the data warehouse while keeping costs down IBM Informix Warehouse Accelerator (IWA) delivers superior performance for in-memory analytics processing... Read
2 Aug 12 - channelbiz - Oninit Group launches Pay Per Pulse cloud-based service... Read
28 May 12 - Bloor - David Norfolk on the recent Informix benchmark "pretty impressive results"... Read
23 May 12 - DBTA - Informix Genero: A Way to Modernize Informix 4GL Applications... Read
9 Apr 12 - Mastering Data Management - Upping the Informix Ante: Advanced Data Tools... Read
22 Mar 12 - developerWorks - Optimizing Informix database access... Read
14 Mar 12 - BernieSpang.com - International Informix User Group set to meet in San Diego... Read
1 Mar 12 - IBM Data Management - IIUG Heads West for 2012 - Get ready for sun and sand in San Diego... Read
1 Mar 12 - IBM Data Management - Running Informix on Solid-State Drives.Speed Up Database Access... Read
26 Feb 12 - BernieSpan.com - Better results, lower cost for a broad set of new IBM clients and partners... Read
24 Feb 12 - developerWorks - Informix Warehouse Accelerator: Continuous Acceleration during Data Refresh... Read
6 Feb 12 - PRLOG - Informix port delivers unlimited database scalability for popular SaaS application ... Read
2 Feb 12 - developerWorks - Loading data with the IBM Informix TimeSeries Plug-in for Data Studio... Read
1 Feb 12 - developerWorks - 100 Tech Tips, #47: Log-in to Fix Central... Read
13 Jan 12 - MC Press online - Informix Dynamic Server Entices New Users with Free Production Edition ... Read
11 Jan 12 - Computerworld - Ecologic Analytics and Landis+Gyr -- Suitors Decide to Tie the Knot... Read
9 Jan 12 - planetIDS.com - DNS impact on Informix / Impacto do DNS no Informix... Read
8 Sep 11 - TMCnet.com - IBM Offers Database Solution to Enable Smart Meter Data Capture... Read
1 Aug 11 - IBM Data Management Magazine - IIUG user view: Happy 10th anniversary to IBM and Informix... Read
8 Jul 11 - Database Trends and Applications - Managing Time Series Data with Informix... Read
31 May 11 - Smart Grid - The meter data management pitfall utilities are overlooking... Read
27 May 11 - IBM Data Management Magazine - IIUG user view: Big data, big time ( Series data, warehouse acceleration, and 4GLs )... Read
16 May 11 - Business Wire - HiT Software Announces DBMoto for Enterprise Integration, Adds Informix. Log-based Change Data Capture... Read
21 Mar 11 - Yahoo! Finance - IBM and Cable&Wireless Worldwide Announce UK Smart Energy Cloud... Read
14 Mar 11 - MarketWatch - Fuzzy Logix and IBM Unveil In-Database Analytics for IBM Informix... Read
11 Mar 11 - InvestorPlace - It's Time to Give IBM Props: How many tech stocks are up 53% since the dot-com boom?... Read
9 Mar 11 - DBTA - Database Administration and the Goal of Diminishing Downtime... Read
2 Feb 11 - DBTAs - Informix 11.7 Flexible Grid Provides a Different Way of Looking at Database Servers... Read
27 Jan 11 - exactsolutions - Exact to Add Informix Support to Database Replay, SQL Monitoring Solutions... Read
25 Jan 11 - PR Newswire - Bank of China in the UK Works With IBM to Become a Smarter, Greener Bank... Read
12 Oct 10 - Database Trends and Applications - Informix 11.7: The Beginning of the Next Decade of IBM Informix... Read
20 Sep 10 - planetIDS.com - ITG analyst paper: Cost/Benefit case for IBM Informix as compared to Microsoft SQL Server... Read
20 Jul 10 - IBM Announcements - IBM Informix Choice Edition V11.50 helps deploy low-cost scalable and reliable solutions for Apple Macintosh and Microsoft Windows... Read
20 Jul 10 - IBM Announcements - Software withdrawal: Elite Support for Informix Ultimate-C Edition... Read
24 May 10 - eWeek Europe - IBM Supplies Database Tech For EU Smart Grid... Read
23 May 10 - SiliconIndia - IBM's smart metering system allows wise use of energy... Read
21 May 10 - CNET - IBM to help people monitor energy use... Read
20 May 10 - ebiz - IBM Teams With Hildebrand To Bring Smart Metering To Homes Across Britain... Read
19 May 10 - The New Blog Times - Misurare il consumo energetico: DEHEMS è pronto... Read
19 May 10 - ZDNet - IBM software in your home? Pact enables five-city smart meter pilot in Europe... Read
17 March 10 - ZDNet (blog) David Morgenstern - TCO: New research finds Macs in the enterprise easier, cheaper to manage than... Read
17 March 2010 - Virtualization Review - ...key components of Big Blue's platform to the commercial cloud such as its WebSphere suite of application ser vers and its DB2 and Informix databases... Read
10 February 2010 - The Wall Street Journal - International Business Machines is expanding an initiative to win over students and professors on its products. How do they lure the college crowd?... Read

End of Support Dates
IDS 11.10 - 30 Sep 2012
Subscribe to notifications

IIUG on Facebook IIUG on Twitter

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum

Re: IDS11 on Linux - insert into temp takes very l

Posted By: Alexandre Marini
Date: Tuesday, 21 July 2009, at 8:19 a.m.

In Response To: IDS11 on Linux - insert into temp takes very long (Nandkishor Singare)

Hello, mr Singare:
On our OLTP systems, some troubles with slow queryes were solved when
using ON clauses:
I suggest you to make two queryes with UNION, instead of using ON
clauses, and watch
the explain stats again, maybe that raises your speed, ok?

Ex: remove the section

b.bill_doc_ty in ('R', 'O')

and on the first query just put the first condition
b.bill_doc_ty = 'R'
and then make the same query again, UNION a second one with
b.bill_doc_ty = 'O'

Best regards!

Alexandre Marini

Tecnologia da Informação - DBA

SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix

Nandkishor Singare escreveu:
> Hi,
>
> Environment details -
>
> Informix 11.50.FC4
> OS Red hat linux - 2.6.18-92.el5.
>
> One query with two tables joined runs fast and gives result within 1-2
> minutes.
> But when I use same query to insert result into temp table "select... from
> ... insert into temp t1 with no log" it takes around 8-9 hours.
> Records -
> Table 1 : 32503
> Table 2 : 16827089
>
> I doubt about temp dbspace but temp dbspaces are defined in config and
> environment.
> I do not see much change in tempdbspace read/write with onstat -D.
> Also no files created in /tmp directory.
>
> What can be issue? and where to check? is it know issue with informix11?
> Please help.
>
> sorry for the irrelevent subject in earlier mail.
>
> Onstat -d output
>
> ---------------------------------------------------------------------------------------------
>
> IBM Informix Dynamic Server Version 11.50.FC4 -- On-Line -- Up 4 days
> 10:49:03 -- 7838356 Kbytes
> Dbspaces
> address number flags fchunk nchunks pgsize flags
> owner name
> 12b93d028 1 0x40001 1 1 2048 N B
> informix ibs_ia1_rootdbs
> 12f336418 2 0x40001 2 1 2048 N B
> informix ibs_ia1_plogdbs
> 12f3365b0 3 0x40001 3 3 2048 N B
> informix ibs_ia1_logdbs1
> 12f336748 4 0x40001 6 3 2048 N B
> informix ibs_ia1_logdbs2
> 12f3368e0 5 0x42001 9 1 2048 N TB
> informix ibs_ia1_tmpdbs1
> 12f336a78 6 0x42001 10 1 2048 N TB
> informix ibs_ia1_tmpdbs2
> 12f336c10 7 0x42001 11 1 2048 N TB
> informix ibs_ia1_tmpdbs3
> 12f336da8 8 0x42001 12 1 2048 N TB
> informix ibs_ia1_tmpdbs4
> 12f335cc0 9 0x48001 13 1 2048 N SB
> informix ibs_ia1_ersbs
> 12f335e58 10 0x40001 14 1 2048 N B
> informix ibs_ia1_erdbs
> 12f337028 11 0x40001 15 1 2048 N B
> informix ibs_ia1_sadmdbs
> 12f3371c0 12 0x40001 16 7 2048 N B
> informix ibs_ia1_mxdbs1
> 12f337358 13 0x40001 22 6 2048 N B
> informix ibs_ia1_mxdbs2
> 12f3374f0 14 0x40001 28 4 2048 N B
> informix ibs_ia1_mxdbs3
> 12f337688 15 0x40001 32 1 2048 N B
> informix ibs_ia1_mxinvhdr_idx1
> 12f337820 16 0x40001 33 1 2048 N B
> informix ibs_ia1_mxinvhdr_idx2
> 12f3379b8 17 0x40001 34 1 2048 N B
> informix ibs_ia1_mxinvdtl_idx1
> 12f337b50 18 0x40001 35 1 2048 N B
> informix ibs_ia1_mxinvdtl_idx2
> 12f337ce8 19 0x40001 36 1 2048 N B
> informix ibs_ia1_mxinvdtl_dbs1
> 12f337e80 20 0x40001 37 1 2048 N B
> informix ibs_ia1_mxinvdtl_dbs2
> 12f339028 21 0x40001 38 1 2048 N B
> informix ibs_ia1_mxinvdtl_dbs3
> 12f3391c0 22 0x40001 39 1 2048 N B
> informix ibs_ia1_mxinvdtl_dbs4
> 12f339358 23 0x40001 40 1 2048 N B
> informix ibs_ia1_mxinvdtl_dbs5
> 12f3394f0 24 0x40001 41 1 2048 N B
> informix ibs_ia1_mxinvdtl_dbs6
> 12f339688 25 0x40001 42 2 2048 N B
> informix ibs_ia1_mxinvhdr_dbs1
> 12f339820 26 0x40001 44 2 2048 N B
> informix ibs_ia1_mxinvhdr_dbs2
> 12f3399b8 27 0x40001 46 2 2048 N B
> informix ibs_ia1_mxinvhdr_dbs3
> 12f339b50 28 0x40001 48 2 2048 N B
> informix ibs_ia1_mxinvhdr_dbs4
> 12f339ce8 29 0x40001 50 2 2048 N B
> informix ibs_ia1_mxinvhdr_dbs5
> 12f339e80 30 0x40001 52 2 2048 N B
> informix ibs_ia1_mxinvhdr_dbs6
> 30 active, 2047 maximum
> Chunks
> address chunk/dbs offset size free bpages
> flags pathname
> 12b93d1c0 1 1 0 512000 504783
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_rootdbs.1
> 12f33b028 2 2 0 2048000 47947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_plogdbs.1
> 12f33b218 3 3 0 1024000 23947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.1
> 12f33b408 4 3 0 1024000 23997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.2
> 12f33b5f8 5 3 0 1024000 23997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.3
> 12f33b7e8 6 4 0 1024000 23947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.1
> 12f33b9d8 7 4 0 1024000 23997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.2
> 12f33bbc8 8 4 0 1024000 23997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.3
> 12f33bdb8 9 5 0 1024000 1023907
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs1.1
> 12f33d028 10 6 0 1024000 1023923
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs2.1
> 12f33d218 11 7 0 1024000 1023947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs3.1
> 12f33d408 12 8 0 1024000 1023947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs4.1
> 12f33d5f8 13 9 0 1024000 955003 955004
> POSB- /usr/informix_ia1_ibs/infdev/ibs_ia1_ersbs.1
>
> Metadata 68943 51302 68943
> 12f33d7e8 14 10 0 1024000 1023907
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_erdbs.1
> 12f33d9d8 15 11 0 1024000 1021301
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_sadmdbs.1
> 12f33dbc8 16 12 0 5120000 17037
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.1
> 12f33ddb8 17 12 0 5120000 0
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.2
> 12f33f028 18 12 0 5120000 69720
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.3
> 12f33f218 19 12 0 5120000 661308
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.4
> 12f33f408 20 12 0 5120000 5119847
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.5
> 12f33f5f8 21 12 0 5120000 0
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.6
> 12f33f7e8 22 13 0 5120000 54623
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.1
> 12f33f9d8 23 13 0 5120000 0
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.2
> 12f33fbc8 24 13 0 5120000 5192
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.3
> 12f33fdb8 25 13 0 5120000 192696
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.4
> 12f342028 26 13 0 5120000 2619997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.5
> 12f342218 27 13 0 5120000 5119997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.6
> 12f342408 28 14 0 5120000 333206
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.1
> 12f3425f8 29 14 0 5120000 0
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.2
> 12f3427e8 30 14 0 5120000 1684659
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.3
> 12f3429d8 31 14 0 5120000 2619997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.4
> 12f342bc8 32 15 0 1024000 799426
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_idx1.1
> 12f342db8 33 16 0 1024000 861518
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_idx2.1
> 12f344028 34 17 0 1024000 795128
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_idx1.1
> 12f344218 35 18 0 1024000 836397
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_idx2.1
> 12f344408 36 19 0 5120000 3839947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs1.1
> 12f3445f8 37 20 0 5120000 3839947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs2.1
> 12f3447e8 38 21 0 5120000 3839947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs3.1
> 12f3449d8 39 22 0 5120000 3839947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs4.1
> 12f344bc8 40 23 0 5120000 3839947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs5.1
> 12f344db8 41 24 0 5120000 3839947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs6.1
> 12f346028 42 25 0 5120000 1791947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs1.1
> 12f346218 43 25 0 5120000 5119997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs1.2
> 12f346408 44 26 0 5120000 1791947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs2.1
> 12f3465f8 45 26 0 5120000 5119997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs2.2
> 12f3467e8 46 27 0 5120000 1791947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs3.1
> 12f3469d8 47 27 0 5120000 5119997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs3.2
> 12f346bc8 48 28 0 5120000 1791947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs4.1
> 12f346db8 49 28 0 5120000 5119997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs4.2
> 12f349028 50 29 0 5120000 1791947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs5.1
> 12f349218 51 29 0 5120000 5119997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs5.2
> 12f349408 52 30 0 5120000 1791947
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs6.1
> 12f3495f8 53 30 0 5120000 5119997
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs6.2
> 12f3497e8 54 12 0 5120000 0
> PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.7
> 54 active, 32766 maximum
> NOTE: The values in the "size" and "free" columns for DBspace chunks are
>
> displayed in terms of "pgsize" of the DBspace to which they belong.
> Expanded chunk capacity mode: always
>
>
> ---------------------------------------------------------------------------------------------
> SQL EXPLAIN output - with (avoid execute clause.)
>
>
> ---------------------------------------------------------------------------------------------
> QUERY: (OPTIMIZATION TIMESTAMP: 07-20-2009 12:12:21)
> ------
> select b.oawb_fl from to_invacct_ro a, o_awb b where a.accnt_no =
>
> b.bill_accnt_no and b.lck_flag = 'I' and b.shp_dt <= a.shp_select_dt
> and
>
> b.bill_doc_ty in ('R', 'O') into temp t1 with no log
>
> Estimated Cost: 57451
> Estimated # of Rows Returned: 156
> 1) ibsadm.a: INDEX PATH
>
> (1) Index Keys: accnt_no shp_select_dt (Key-Only) (Serial, fragments:
> ALL)
> 2) ibsadm.b: INDEX PATH
>
> Filters: ibsadm.b.lck_flag = 'I'
>
> (1) Index Keys: bill_accnt_no shp_dt bill_doc_ty (Key-First) (Serial,
> fragments: ALL)
>
> Lower Index Filter: ibsadm.a.accnt_no = ibsadm.b.bill_accnt_no
>
> Upper Index Filter: ibsadm.b.shp_dt <= ibsadm.a.shp_select_dt
>
> Index Key Filters: (ibsadm.b.bill_doc_ty IN ('R' , 'O' ))
> NESTED LOOP JOIN
>
>
> ---------------------------------------------------------------------------------------------
>
> Thanks,
> Nandkishor
>
> --000e0cd329b4ae353a046f280145
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>

Messages In This Thread

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.