 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Ingres Topics / July 2004[Info-Ingres] insert performance
Hello, This insert is causing a table scan locking the destination table for several minutes. Of course the query was designed and tested when carton_scan was much smaller. Any ideas? Do I need to make a temporary table, then insert? insert into carton_scan select * from wang2red_carton_scan where carton_no not in (select carton_no from carton_scan) right join PSM Join(carton_no) Heap Pages 1 Tups 1 D9521 C1138 / \ Proj-rest Proj-rest Partial(carton_no) Sort on(carton_no) Pages 15 Tups 1012 Pages 31 Tups 332 D9204 C1012 D310 C3 / / carton_scan wang2red_carton_scan Isam(NU) cHeap Pages 9207 Tups 101245 Pages 1240 Tups 332 I get much the same query plan after changing the query around, optimizedb and putting a primary key on the source table. eg: insert into carton_scan select wang2red_carton_scan.* from wang2red_carton_scan left join carton_scan on wang2red_carton_scan.carton_no = carton_scan.carton_no where carton_scan.carton_no is null right join PSM Join(carton_no) Heap Pages 1 Tups 1 D9235 C2103 / \ Proj-rest Proj-rest Partial(carton_no) Sorted(carton_no) Pages 1220 Tups 101245 Pages 31 Tups 332 D9204 C1012 D24 C3 / / carton_scan wang2red_carton_scan Isam(NU) B-Tree(NU) Pages 9207 Tups 101245 Pages 47 Tups 332 Hi, using an ISAM structure on a table where you do many inserts doesn't seem to be a very good idea to me. The PSM Join propably kills any performance. Modify it to heap and use a btree index on carton_no. The proj_rest on the btree index will be real fast and you'll get a FSM instead of a PSM which speeds up the query a second time. Alternatively, if you don't like the idea having a heap table, make it btree. Using the Join-version of your query is propably better. Although the query optimizer is quite intelligent, it's always a good idea to help him. HTH Ronald > Hello, > [quoted text clipped - 44 lines] > Isam(NU) B-Tree(NU) > Pages 9207 Tups 101245 Pages 47 Tups 332  SignatureindependIT Integrative Technologies GmbH Sitz der Gesellschaft: Schrobenhausen HRB Neuburg B 1.521 Geschäftsführer: Dieter Stubler, Dipl. Inform. (FH) Ronald Jeninga, Diplom Mathematiker Are my postings visible to anyone ? or is my english so bad that all I say is incomprehensible? (The problem now of course is, if noone answers, I still don't know whether I am invisible or incomprehensible). Ronald > Hi, > [quoted text clipped - 62 lines] > > Isam(NU) B-Tree(NU) > > Pages 9207 Tups 101245 Pages 47 Tups 332 SignatureindependIT Integrative Technologies GmbH Sitz der Gesellschaft: Schrobenhausen HRB Neuburg B 1.521 Geschäftsführer: Dieter Stubler, Dipl. Inform. (FH) Ronald Jeninga, Diplom Mathematiker Hi Ronald, Your first message did not get through. Thanks. I've never considered using heap as a primary storage structure except for audit type logs. Paul ps. You can be invisible and incomprehensible if you pick wingdings with white foreground and background. Masterpiece Consultants P/L ABN 25 955 099 147 Shop 3, Pakenham Arcade, 116 Main St, Pakenham PO Box 332, Gembrook, 3783 Phone: (+61 03) 5941 4711 Fax: (+61 03) 5941 3576 Paul White mailto:paulwh@mpiece.com.au mobile: 0414 681 799 Debbie White mailto:debbiewh@mpiece.com.au mobile: 0412 581 799 Visit our web site http:\\www.mpiece.com.au -----Original Message----- From: info-ingres-bounces@ams.org [mailto:info-ingres-bounces@ams.org]On Behalf Of Ronald Jeninga Sent: Wednesday, 28 July 2004 4:40 PM To: info-ingres@ams.org Subject: Re: [Info-Ingres] insert performance Are my postings visible to anyone ? or is my english so bad that all I say is incomprehensible? (The problem now of course is, if noone answers, I still don't know whether I am invisible or incomprehensible). Ronald > Hi, > [quoted text clipped - 71 lines] > Dieter Stubler, Dipl. Inform. (FH) > Ronald Jeninga, Diplom Mathematiker -- independIT Integrative Technologies GmbH Sitz der Gesellschaft: Schrobenhausen HRB Neuburg B 1.521 Geschäftsführer: Dieter Stubler, Dipl. Inform. (FH) Ronald Jeninga, Diplom Mathematiker _______________________________________________ Info-ingres mailing list > Hi Ronald, > Your first message did not get through. > Thanks. I've never considered using heap as a primary storage structure > except for audit type logs. Ronald's suggestion of using a heap with a B-tree secondary would have solved the problem at hand just as well as what I suggested. And provided the heap only ever grows (i.e. no deletes on it), and provided very little insert concurrency were required, it would be a good solution. Roy Hi, thank you for your prompt answer, now I have to figure out why I see my postings and some others apparently do not, at least (and that's actually even stranger) not all of my messages. In my point of view you can perfectly use heap, with an index of course, on tables where you don't delete rows on a large scale. Such tables tend to grow very large. Large tables also tend to have many access paths, so it'll be difficult to decide on some physical key anyway. Scanning such tables entirely will be fastest if they are heap. Reorganizing the indexes will cost the minimum temporary space. Row retrieval is only slower compared to a btree structure if you query the physical key. Key lookups will be faster in an index than in an entire table. I do not say it is always the best choice, I just say, it's a possibility which can be considered seriously. Ronald > Hi Ronald, > Your first message did not get through. [quoted text clipped - 25 lines] > > Ronald SignatureindependIT Integrative Technologies GmbH Sitz der Gesellschaft: Schrobenhausen HRB Neuburg B 1.521 Geschäftsführer: Dieter Stubler, Dipl. Inform. (FH) Ronald Jeninga, Diplom Mathematiker > Hello, > [quoted text clipped - 20 lines] > Isam(NU) cHeap > Pages 9207 Tups 101245 Pages 1240 Tups 332 I bet the cost of repeatedly restarting the PSM merge when it finds an out-of-order row is absolutely killing you here. Try making carton_scan into a B-tree instead. (In fact you'd probably do better off even if it was a heap, because that would at least force a sort!) Better still, a secondary index on carton_scan, keyed on carton_no, would probably make a huge difference because you are presently expected to do at least a minute and a half of disk I/O even if the merege doesn't have to restart, just to get carton_no. A nice dense index will reduce that to just a second or two. Roy Hann (rhann at rationalcommerce dot com) Rational Commerce Ltd. www.rationalcommerce.com "Ingres development, tuning, and training experts" |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.