Hello, again. Here is my trace with P6Spy. I have to delete some lines to reduce the size of email.
1075494173971|4527|0|statement|SELECT A0.MSVBY,A0.MSDBY,A0.MSTIME,A0.MSDTIME,A0.MSCHTM,A0.MSPACCT#,A0.MSTO,A0.MSVT IME,A0.MSTEXT,A0.MSRSTS,A0.MSFROM,A0.MSFLAG,A0.MSSTS,A0.MSPREL,A0.MSCHDT,A0. MSDATE,A0.MSID,A0.MSDDATE,A0.MSCHBY,A0.MSVDATE,A0.MSPUREF,A0.MSTYPE FROM JMPIBMS2 A0|SELECT A0.MSVBY,A0.MSDBY,A0.MSTIME,A0.MSDTIME,A0.MSCHTM,A0.MSPACCT#,A0.MSTO,A0.MSVT IME,A0.MSTEXT,A0.MSRSTS,A0.MSFROM,A0.MSFLAG,A0.MSSTS,A0.MSPREL,A0.MSCHDT,A0. MSDATE,A0.MSID,A0.MSDDATE,A0.MSCHBY,A0.MSVDATE,A0.MSPUREF,A0.MSTYPE FROM JMPIBMS2 A0 1075494174041|-1||resultset|SELECT A0.MSVBY,A0.MSDBY,A0.MSTIME,A0.MSDTIME,A0.MSCHTM,A0.MSPACCT#,A0.MSTO,A0.MSVT IME,A0.MSTEXT,A0.MSRSTS,A0.MSFROM,A0.MSFLAG,A0.MSSTS,A0.MSPREL,A0.MSCHDT,A0. MSDATE,A0.MSID,A0.MSDDATE,A0.MSCHBY,A0.MSVDATE,A0.MSPUREF,A0.MSTYPE FROM JMPIBMS2 A0|MSCHBY = , MSDBY = , MSFLAG = P , MSFROM = FROM_ZZ , MSID = 1, MSPACCT# = 2729473, MSPREL = 1 , MSPUREF = 2003, MSRSTS = A, MSSTS = N , MSTEXT = TEXT , MSTO = DR_JONES , MSTYPE = O , MSVBY = 1075494174041|-1||resultset|SELECT A0.MSVBY,A0.MSDBY,A0.MSTIME,A0.MSDTIME,A0.MSCHTM,A0.MSPACCT#,A0.MSTO,A0.MSVT IME,A0.MSTEXT,A0.MSRSTS,A0.MSFROM,A0.MSFLAG,A0.MSSTS,A0.MSPREL,A0.MSCHDT,A0. MSDATE,A0.MSID,A0.MSDDATE,A0.MSCHBY,A0.MSVDATE,A0.MSPUREF,A0.MSTYPE FROM JMPIBMS2 A0|MSCHBY = , MSDBY = , MSFLAG = F , MSFROM = FROM_B , MSID = 2, MSPACCT# = 117, MSPREL = 1 , MSPUREF = 2004, MSRSTS = A, MSSTS = N , MSTEXT = TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT , MSTO = DR_JONES , MSTYPE = O , MSVBY = 1075494174051|-1||resultset|SELECT A0.MSVBY,A0.MSDBY,A0.MSTIME,A0.MSDTIME,A0.MSCHTM,A0.MSPACCT#,A0.MSTO,A0.MSVT IME,A0.MSTEXT,A0.MSRSTS,A0.MSFROM,A0.MSFLAG,A0.MSSTS,A0.MSPREL,A0.MSCHDT,A0. MSDATE,A0.MSID,A0.MSDDATE,A0.MSCHBY,A0.MSVDATE,A0.MSPUREF,A0.MSTYPE FROM JMPIBMS2 A0|MSCHBY = , MSDBY = , MSFLAG = P , MSFROM = FROM_C , MSID = 3, MSPACCT# = 281, MSPREL = 2 , MSPUREF = 2005, MSRSTS = A, MSSTS = N , MSTEXT = TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT , MSTO = DR_JONES , MSTYPE = O , MSVBY = ==== More resultset ====== 1075494177366|1152|0|statement|SELECT A0.USEX,A0.UREF#,A0.UBRMM,A0.UBRCY,A0.UBRDD FROM PHPUNIT A0 WHERE A0.UREF# IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )|SELECT A0.USEX,A0.UREF#,A0.UBRMM,A0.UBRCY,A0.UBRDD FROM PHPUNIT A0 WHERE A0.UREF# IN ( '2185' , '2184' , '2187' , '2186' , '2189' , '2188' , '2191' , '2190' , '2177' , '2176' , '2179' , '2178' , '2181' , '2180' , '2183' , '2182' , '2201' , '2200' , '2203' , '2202' , '2205' , '2204' , '2193' , '2192' , '2195' , '2194' , '2197' , '2196' , '2199' , '2025' , '2198' , '2024' , '2027' , '2026' , '2029' , '2028' , '2031' , '2030' , '2017' , '2016' , '2019' , '2018' , '2021' , '2020' , '2023' , '2022' , '2041' , '2040' , '2043' , '2042' , '2045' , '2044' , '2047' , '2046' , '2033' , '2032' , '2035' , '2034' , '2037' , '2036' , '2039' , '2038' , '2008' , '2011' , '2010' , '2013' , '2012' , '2015' , '2014' , '2003' , '2005' , '2004' , '2007' , '2006' , '2089' , '2088' , '2091' , '2090' , '2093' , '2092' , '2095' , '2094' , '2081' , '2080' , '2083' , '2082' , '2085' , '2084' , '2087' , '2086' , '2105' , '2104' , '2107' , '2106' , '2109' , '2108' , '2111' , '2110' , '2097' , '2096' , '2099' , '2098' , '2101' , '2100' , '2103' , '2102' , '2057' , '2056' , '2059' , '2058' , '2061' , '2060' , '2063' , '2062' , '2049' , '2048' , '2051' , '2050' , '2053' , '2052' , '2055' , '2054' , '2073' , '2072' , '2075' , '2074' , '2077' , '2076' , '2079' , '2078' , '2065' , '2064' , '2067' , '2066' , '2069' , '2068' , '2071' , '2070' , '2153' , '2152' , '2155' , '2154' , '2157' , '2156' , '2159' , '2158' , '2145' , '2144' , '2147' , '2146' , '2149' , '2148' , '2151' , '2150' , '2169' , '2168' , '2171' , '2170' , '2173' , '2172' , '2175' , '2174' , '2161' , '2160' , '2163' , '2162' , '2165' , '2164' , '2167' , '2166' , '2121' , '2120' , '2123' , '2122' , '2124' , '2127' , '2126' , '2113' , '2112' , '2115' , '2114' , '2117' , '2116' , '2119' , '2137' , '2136' , '2139' , '2138' , '2141' , '2140' , '2143' , '2142' , '2129' , '2128' , '2131' , '2130' , '2133' , '2132' , '2135' , '2134' ) 1075494177376|-1||resultset|SELECT A0.USEX,A0.UREF#,A0.UBRMM,A0.UBRCY,A0.UBRDD FROM PHPUNIT A0 WHERE A0.UREF# IN ( '2185' , '2184' , '2187' , '2186' , '2189' , '2188' , '2191' , '2190' , '2177' , '2176' , '2179' , '2178' , '2181' , '2180' , '2183' , '2182' , '2201' , '2200' , '2203' , '2202' , '2205' , '2204' , '2193' , '2192' , '2195' , '2194' , '2197' , '2196' , '2199' , '2025' , '2198' , '2024' , '2027' , '2026' , '2029' , '2028' , '2031' , '2030' , '2017' , '2016' , '2019' , '2018' , '2021' , '2020' , '2023' , '2022' , '2041' , '2040' , '2043' , '2042' , '2045' , '2044' , '2047' , '2046' , '2033' , '2032' , '2035' , '2034' , '2037' , '2036' , '2039' , '2038' , '2008' , '2011' , '2010' , '2013' , '2012' , '2015' , '2014' , '2003' , '2005' , '2004' , '2007' , '2006' , '2089' , '2088' , '2091' , '2090' , '2093' , '2092' , '2095' , '2094' , '2081' , '2080' , '2083' , '2082' , '2085' , '2084' , '2087' , '2086' , '2105' , '2104' , '2107' , '2106' , '2109' , '2108' , '2111' , '2110' , '2097' , '2096' , '2099' , '2098' , '2101' , '2100' , '2103' , '2102' , '2057' , '2056' , '2059' , '2058' , '2061' , '2060' , '2063' , '2062' , '2049' , '2048' , '2051' , '2050' , '2053' , '2052' , '2055' , '2054' , '2073' , '2072' , '2075' , '2074' , '2077' , '2076' , '2079' , '2078' , '2065' , '2064' , '2067' , '2066' , '2069' , '2068' , '2071' , '2070' , '2153' , '2152' , '2155' , '2154' , '2157' , '2156' , '2159' , '2158' , '2145' , '2144' , '2147' , '2146' , '2149' , '2148' , '2151' , '2150' , '2169' , '2168' , '2171' , '2170' , '2173' , '2172' , '2175' , '2174' , '2161' , '2160' , '2163' , '2162' , '2165' , '2164' , '2167' , '2166' , '2121' , '2120' , '2123' , '2122' , '2124' , '2127' , '2126' , '2113' , '2112' , '2115' , '2114' , '2117' , '2116' , '2119' , '2137' , '2136' , '2139' , '2138' , '2141' , '2140' , '2143' , '2142' , '2129' , '2128' , '2131' , '2130' , '2133' , '2132' , '2135' , '2134' )|UREF# = 2003, USEX = M 1075494177376|-1||resultset|SELECT A0.USEX,A0.UREF#,A0.UBRMM,A0.UBRCY,A0.UBRDD FROM PHPUNIT A0 WHERE A0.UREF# IN ( '2185' , '2184' , '2187' , '2186' , '2189' , '2188' , '2191' , '2190' , '2177' , '2176' , '2179' , '2178' , '2181' , '2180' , '2183' , '2182' , '2201' , '2200' , '2203' , '2202' , '2205' , '2204' , '2193' , '2192' , '2195' , '2194' , '2197' , '2196' , '2199' , '2025' , '2198' , '2024' , '2027' , '2026' , '2029' , '2028' , '2031' , '2030' , '2017' , '2016' , '2019' , '2018' , '2021' , '2020' , '2023' , '2022' , '2041' , '2040' , '2043' , '2042' , '2045' , '2044' , '2047' , '2046' , '2033' , '2032' , '2035' , '2034' , '2037' , '2036' , '2039' , '2038' , '2008' , '2011' , '2010' , '2013' , '2012' , '2015' , '2014' , '2003' , '2005' , '2004' , '2007' , '2006' , '2089' , '2088' , '2091' , '2090' , '2093' , '2092' , '2095' , '2094' , '2081' , '2080' , '2083' , '2082' , '2085' , '2084' , '2087' , '2086' , '2105' , '2104' , '2107' , '2106' , '2109' , '2108' , '2111' , '2110' , '2097' , '2096' , '2099' , '2098' , '2101' , '2100' , '2103' , '2102' , '2057' , '2056' , '2059' , '2058' , '2061' , '2060' , '2063' , '2062' , '2049' , '2048' , '2051' , '2050' , '2053' , '2052' , '2055' , '2054' , '2073' , '2072' , '2075' , '2074' , '2077' , '2076' , '2079' , '2078' , '2065' , '2064' , '2067' , '2066' , '2069' , '2068' , '2071' , '2070' , '2153' , '2152' , '2155' , '2154' , '2157' , '2156' , '2159' , '2158' , '2145' , '2144' , '2147' , '2146' , '2149' , '2148' , '2151' , '2150' , '2169' , '2168' , '2171' , '2170' , '2173' , '2172' , '2175' , '2174' , '2161' , '2160' , '2163' , '2162' , '2165' , '2164' , '2167' , '2166' , '2121' , '2120' , '2123' , '2122' , '2124' , '2127' , '2126' , '2113' , '2112' , '2115' , '2114' , '2117' , '2116' , '2119' , '2137' , '2136' , '2139' , '2138' , '2141' , '2140' , '2143' , '2142' , '2129' , '2128' , '2131' , '2130' , '2133' , '2132' , '2135' , '2134' )|UREF# = 2004, USEX = F ==== More resultset ====== ----- Original Message ----- From: "Jakob Braeuchi" <[EMAIL PROTECTED]> To: "OJB Users List" <[EMAIL PROTECTED]> Sent: Saturday, January 31, 2004 9:08 AM Subject: Re: getCollectionByQuery() hung up in prefetchRelationship() > hi, > > ojb uses IN-statements to minimize the number of queries executed. > > the following sample uses the ProductGroup 1:n Articles model shipped with ojb. > > i executed a query returning about 50 Articles associated with ProductGroups > 1,2,3,4 or 5. ojb now retrieves these ProducGroups in a single SQL instead of > using about 50 SQLs: > > SELECT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM Kategorien A0 WHERE > A0.Kategorie_Nr IN ( '2' , '4' , '5' , '3' , '1' ) > > in OJB.properties there's a limit for the number of values in the IN-Clause, > default is afaik 200. if this limit is exeeded ojb has to execute another SELECT. > > please provide the sql-trace. > > hth > jakob > > > Jay Xu wrote: > > Unfortuantely, this is also what I observed. If you use P6Spy, you will see > > that instead of generating JOIN in SQL statement, it creates seperate SQL > > statements which cause 1+n SQL statement execution (in 1:1 mapping). This > > works, but quite inefficient. I followed turtorial about JOIN, but no luck. > > Really hope some experts in the group can help. > > > > ----- Original Message ----- > > From: "Jason Woodard" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Friday, January 30, 2004 10:36 AM > > Subject: getCollectionByQuery() hung up in prefetchRelationship() > > > > > > > >>Hi, > >> > >>I am trying to use OJB to manage experiment data, and have started > >>with a naive approach where each experiment is a nested collection > >>eventually ending with variable/value pairs. Storing data works well, > >>and retrieving it using getCollectionByQuery() works eventually, but > >>is taking much longer than it should. > >> > >>When retrieving an experiment object, OJB invokes BasePrefetcher's > >>prefetchRelationship(), which makes a bunch of JDBC calls via > >>getCollectionByQuery(), which seems to return all the data OJB needs > >>to reconstruct the object. This takes about 10 seconds for my test > >>experiment (1000 runs, 25 observations each). prefetchRelationship() > >>then invokes associateBatched(), and stays there for another 20 > >>minutes with no further debug output, mainly creating Identity > >>objects. > >> > >>I'm wondering whether I've set up my schema in a way that's grossly > >>inefficient for OJB. (I've appended part of the repository.xml file > >>below.) I've thought about adding a reference from Value back to > >>Level, or using explicit PKs instead of anonymous ones, but haven't > >>tried either yet. > >> > >>FWIW, I'm using OJB 1.0.rc5 with the PB interface against a MySQL > >>database (3.0.9-stable driver). > >> > >>many thanks for any ideas, > >> > >> -j > >> > >>Jason Woodard > >>[EMAIL PROTECTED] > >> > >> > >> > >><!-- Definitions for sim.harness.store.Level --> > >><class-descriptor > >> class="sim.harness.store.Level" > >> table="LEVEL" > >> > > >> <field-descriptor > >> name="id" > >> column="ID" > >> jdbc-type="INTEGER" > >> primarykey="true" > >> autoincrement="true" > >> access="anonymous" > >> /> > >> <!-- foreign key for parent collection --> > >> <field-descriptor > >> name="seriesId" > >> column="SERIES" > >> jdbc-type="INTEGER" > >> access="anonymous" > >> /> > >> <field-descriptor > >> name="variableId" > >> column="VARIABLE" > >> jdbc-type="INTEGER" > >> access="anonymous" > >> /> > >> <reference-descriptor > >> name="variable" > >> class-ref="sim.harness.store.Variable" > >> auto-update="true" > >> auto-delete="true" > >> > > >> <foreignkey field-ref="variableId"/> > >> </reference-descriptor> > >> <field-descriptor > >> name="valueId" > >> column="VALUE" > >> jdbc-type="INTEGER" > >> access="anonymous" > >> /> > >> <reference-descriptor > >> name="value" > >> class-ref="sim.harness.store.Value" > >> auto-update="true" > >> auto-delete="true" > >> > > >> <foreignkey field-ref="valueId"/> > >> </reference-descriptor> > >></class-descriptor> > >> > >><!-- Definitions for sim.harness.store.Variable --> > >><class-descriptor > >> class="sim.harness.store.Variable" > >> table="VARIABLE" > >> > > >> <field-descriptor > >> name="id" > >> column="ID" > >> jdbc-type="INTEGER" > >> primarykey="true" > >> autoincrement="true" > >> access="anonymous" > >> /> > >> <field-descriptor > >> name="name" > >> column="NAME" > >> jdbc-type="VARCHAR" > >> /> > >></class-descriptor> > >> > >><!-- Definitions for sim.harness.store.Value --> > >><class-descriptor > >> class="sim.harness.store.Value" > >> > > >> <extent-class class-ref="sim.harness.store.AbstractValue"/> > >></class-descriptor> > >> > >><!-- Definitions for sim.harness.store.AbstractValue --> > >><class-descriptor > >> class="sim.harness.store.AbstractValue" > >> > > >> <extent-class class-ref="sim.harness.store.IntValue"/> > >> <extent-class class-ref="sim.harness.store.DoubleValue"/> > >> <extent-class class-ref="sim.harness.store.BooleanValue"/> > >> <extent-class class-ref="sim.harness.store.StringValue"/> > >></class-descriptor> > >> > >><!-- Definitions for sim.harness.store.DoubleValue --> > >><class-descriptor > >> class="sim.harness.store.DoubleValue" > >> table="DBL_VALUE" > >> > > >> <field-descriptor > >> name="id" > >> column="ID" > >> jdbc-type="INTEGER" > >> primarykey="true" > >> autoincrement="true" > >> access="anonymous" > >> /> > >> <field-descriptor > >> name="value" > >> column="VALUE" > >> jdbc-type="DOUBLE" > >> /> > >></class-descriptor> > >> > >><!-- etc. for other Value types --> > >> > >> > >>--------------------------------------------------------------------- > >>To unsubscribe, e-mail: [EMAIL PROTECTED] > >>For additional commands, e-mail: [EMAIL PROTECTED] > >> > > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
