|
|
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)
HXTT ACCESS
|
lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-13 01:40:09 |
Hi there,
I still have serious performance problems when using lockType=ACCESS.
First query is slow (around 6 seconds)
Second query is fast (less than 400 ms)
Third query is slow again if occurs after delayClose.
I tried with the default delayClose and with a specific delayClose, but it's happening in any case.
Curious thing is that even with a delayClose value of 100 (seconds), within this delay the HXTT driver will correctly "see" changes to the database that have been submitted with an Access client. So I guess even when it's fast, it's still reading from the DB and not from the cache.
Thanks for your help,
Patrick
|
Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-05-13 02:06:59 |
>Curious thing is that even with a delayClose value of 100 (seconds), within this delay the HXTT driver will correctly "see" changes to the database that have been submitted with an Access client.
refreshInterval: To specify a a refresh interval setting in seconds which determines how long it takes for data changed by one user to be displayed in the session of a second user. It's only used for MS Access compatible lock mode(lockType=ACCESS).
>So I guess even when it's fast, it's still reading from the DB and not from the cache.
It will check whether need to reload the total database according to refreshInterval value.
>First query is slow (around 6 seconds)
What's your sql sample? First load need to load database information besides table information.
>Second query is fast (less than 400 ms)
A bigger delayedClose value will quicken your following querys, because it needn't to reload database inforamtion, but fouc on specific table.
|
Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-13 04:59:07 |
Ok thanks for your quick answer.
In our case, we're using hibernate in a tomcat server, with a connection pool. Would it make sense to use a very big delayedClose? In that case, could you tell me how I could close the connection myself? I would need it when the server is shut down.
thanks
Patrick
|
Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-05-13 05:40:48 |
|
delayedClose means only it can utilize some cached information. For shut down, you needn't care too much since it's write at once.
|
Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-13 05:44:03 |
JVM doesn't quit until delayedClose when I try to stop Tomcat.
I need a method to force HXTT driver to quit; I would call it from my own ServletContextListener.contextDestroyed
thanks
|
Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-13 05:44:49 |
I tried this one:
com.hxtt.sql.access.AccessDriver.releaseAll();
But it doesn't work.
|
Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-05-13 06:27:36 |
|
Then you can use a smaller delayedClose, for instance 30000 (30s). If it's idle more than 30 seconds, that database will be closed.
|
Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-13 06:55:42 |
That means I'll have to wait 30 seconds to stop Tomcat. It's quite annoying on the integration and test servers.
How does it work when HXTT driver is used inside a pool connection, where a max idle time can be configured? If the connection pool tries to close an idle connection, but the delayedClose is still running, what will happen?
I guess it's all about this question: why do we need a delayedClose parameter? I would like my program (or the connection pool) to control when to open and to close connections.
It would be great if there was a way to specify delayedClose=never, and to have an api to explicitly close the connection. And that should be integrated in any java/jdbc/connection pool standard.
Thanks
Patrick
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-05-13 21:47:11 |
>That means I'll have to wait 30 seconds to stop Tomcat. It's quite annoying on the integration and test servers.
You needn't. HXTT Access is using daemon thread for delayedClose process, and the Java Virtual Machine exits when the only threads running are all daemon threads.
So if your Tomcat can't shutdown at once through "catalina stop", you should check whether other application is using non-daemon thread.
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-05-13 23:42:08 |
ps -ef|grep tomcat to get the process id.
$JAVA_HOME/bin/jstack pid to fint what thread is not daemon.
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-14 08:08:03 |
Hi again,
Following your suggestion I spent a whole day playing around with different delayedClose values and debugging with VisualVM. Here are my observations, I hope they'll help you.
Kind regards,
Patrick
Observations
1. Simple SELECT query
Creates two Threads (NOT deamons) almost simultaneously. T1 stops after ~6s., T2 stops after ~6+delayedClosed s. First request is fast.
If I keep querying (meaning: T2 never gets older than delayedClose before receiving a new request), T2 keeps alive for another ~6+delayedClose s. T1 dies whatever I do after ~6 s.
If I wait more than delayedClose s. (after the start of T2) but less than ~6+delayedClose s. (which means T2 is still alive), T3 is created but stops after ~6s. T2 is still alive for another ~6+delayedClose s.
2. Complex SELECT LEFT OUTER JOIN query
Creates T1, and then after x s. creates T2 (it looks like x is the time it takes for the query to return the resultset for the first time). T1 stops after ~6+x s., T2 after ~6+delayedClose. First request (x) is very slow (around 6 seconds)
If I keep querying, T2 keeps alive for another ~6+delayedClose s. T1 dies whatever I do after ~6+x s. The difference here is that the results are much faster (around 1 s.).
If I wait more than delayedClose s. (after the start of T2) but less than ~6+delayedClose s. (which means T2 is still alive), the scenario is a little different than above: a new pair of T3 and T4 are created and behave exactly like T1 and T2. Request is again very slow.
3. All Thread are NOT deamons and not doing much
Here is a Thread dump when doing the complexe query, where T1 is "Thread-38" and T2 is "Thread-39"
"Thread-39" prio=2 tid=0x04575400 nid=0xdfc waiting on condition [0x0674f000]
java.lang.Thread.State: TIMED_WAITING (sleeping)
at java.lang.Thread.sleep(Native Method)
at com.hxtt.global.am.run(Unknown Source)
at java.lang.Thread.run(Thread.java:722)
Locked ownable synchronizers:
- None
"Thread-38" daemon prio=2 tid=0x017cac00 nid=0xe74 waiting on condition [0x069ff000]
java.lang.Thread.State: TIMED_WAITING (sleeping)
at java.lang.Thread.sleep(Native Method)
at com.hxtt.global.am.run(Unknown Source)
at java.lang.Thread.run(Thread.java:722)
Locked ownable synchronizers:
- None
4. Tomcat is unable to shutdown before all non-deamon Threads are dead
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-14 08:08:30 |
Here is the simple SELECT query:
SELECT VALEUR0_."VAL-ISI" AS VAL1_85_, VALEUR0_."VAL-BEC" AS VAL44_85_,
VALEUR0_."VAL-BAI" AS VAL45_85_, VALEUR0_."VAL-CGV" AS
VAL46_85_, VALEUR0_."VAL-MCON" AS VAL47_85_, VALEUR0_."VAL-MNO"
AS VAL48_85_, VALEUR0_."VAL-MRBA" AS VAL49_85_,
VALEUR0_."VAL-DOM" AS VAL50_85_, VALEUR0_."VAL-POR" AS
VAL51_85_, VALEUR0_."VAL-PCO" AS VAL52_85_, VALEUR0_."VAL-VEN"
AS VAL53_85_, VALEUR0_."VAL-ABR" AS VAL2_85_,
VALEUR0_."VAL-ADD" AS VAL3_85_, VALEUR0_."VAL-CCC" AS VAL4_85_,
VALEUR0_."VAL-CCON" AS VAL5_85_, VALEUR0_."VAL-CHFX" AS
VAL6_85_, VALEUR0_."VAL-COM" AS VAL7_85_, VALEUR0_."VAL-CRBA"
AS VAL8_85_, VALEUR0_."VAL-CRBT" AS VAL9_85_,
VALEUR0_."VAL-DAEX" AS VAL10_85_, VALEUR0_."VAL-DCOD" AS
VAL11_85_, VALEUR0_."VAL-DCOF" AS VAL12_85_,
VALEUR0_."VAL-DCREA" AS VAL13_85_, VALEUR0_."VAL-DDISD" AS
VAL14_85_, VALEUR0_."VAL-DDISF" AS VAL15_85_,
VALEUR0_."VAL-DEMI" AS VAL16_85_, VALEUR0_."VAL-DISD" AS
VAL17_85_, VALEUR0_."VAL-DISF" AS VAL18_85_,
VALEUR0_."VAL-DMOD" AS VAL19_85_, VALEUR0_."VAL-DRBA" AS
VAL20_85_, VALEUR0_."VAL-DREC" AS VAL21_85_, VALEUR0_."VAL-ECH"
AS VAL22_85_, VALEUR0_."VAL-ECHU" AS VAL23_85_,
VALEUR0_."VAL-EMETTEUR" AS VAL24_85_, VALEUR0_."VAL-FLAT" AS
VAL25_85_, VALEUR0_."VAL-FLRD" AS VAL26_85_,
VALEUR0_."VAL-FLRF" AS VAL27_85_, VALEUR0_."VAL-LIP" AS
VAL28_85_, VALEUR0_."VAL-LIVR" AS VAL29_85_,
VALEUR0_."VAL-MONTE" AS VAL30_85_, VALEUR0_."VAL-NBCOUP" AS
VAL31_85_, VALEUR0_."VAL-NBJB" AS VAL32_85_, VALEUR0_."VAL-NCH"
AS VAL33_85_, VALEUR0_."VAL-NOM1" AS VAL34_85_,
VALEUR0_."VAL-NOM2" AS VAL35_85_, VALEUR0_."VAL-NVAL" AS
VAL36_85_, VALEUR0_."VAL-REM" AS VAL37_85_, VALEUR0_."VAL-SKF"
AS VAL38_85_, VALEUR0_."VAL-SOLDE" AS VAL39_85_,
VALEUR0_."VAL-SPEC" AS VAL40_85_, VALEUR0_."VAL-TIN" AS
VAL41_85_, VALEUR0_."VAL-UCO" AS VAL42_85_, VALEUR0_."VAL-USER"
AS VAL43_85_
FROM [tb-valeur] valeur0_ order by valeur0_."val-isi" ASC limit 5
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-14 08:09:02 |
Here is the complexe SELECT LEFT OUTER JOIN query:
SELECT VALEUR0_."VAL-ISI" AS VAL1_85_8_, VALEUR0_."VAL-BEC" AS
VAL44_85_8_, VALEUR0_."VAL-BAI" AS VAL45_85_8_,
VALEUR0_."VAL-CGV" AS VAL46_85_8_, VALEUR0_."VAL-MCON" AS
VAL47_85_8_, VALEUR0_."VAL-MNO" AS VAL48_85_8_,
VALEUR0_."VAL-MRBA" AS VAL49_85_8_, VALEUR0_."VAL-DOM" AS
VAL50_85_8_, VALEUR0_."VAL-POR" AS VAL51_85_8_,
VALEUR0_."VAL-PCO" AS VAL52_85_8_, VALEUR0_."VAL-VEN" AS
VAL53_85_8_, VALEUR0_."VAL-ABR" AS VAL2_85_8_,
VALEUR0_."VAL-ADD" AS VAL3_85_8_, VALEUR0_."VAL-CCC" AS
VAL4_85_8_, VALEUR0_."VAL-CCON" AS VAL5_85_8_,
VALEUR0_."VAL-CHFX" AS VAL6_85_8_, VALEUR0_."VAL-COM" AS
VAL7_85_8_, VALEUR0_."VAL-CRBA" AS VAL8_85_8_,
VALEUR0_."VAL-CRBT" AS VAL9_85_8_, VALEUR0_."VAL-DAEX" AS
VAL10_85_8_, VALEUR0_."VAL-DCOD" AS VAL11_85_8_,
VALEUR0_."VAL-DCOF" AS VAL12_85_8_, VALEUR0_."VAL-DCREA" AS
VAL13_85_8_, VALEUR0_."VAL-DDISD" AS VAL14_85_8_,
VALEUR0_."VAL-DDISF" AS VAL15_85_8_, VALEUR0_."VAL-DEMI" AS
VAL16_85_8_, VALEUR0_."VAL-DISD" AS VAL17_85_8_,
VALEUR0_."VAL-DISF" AS VAL18_85_8_, VALEUR0_."VAL-DMOD" AS
VAL19_85_8_, VALEUR0_."VAL-DRBA" AS VAL20_85_8_,
VALEUR0_."VAL-DREC" AS VAL21_85_8_, VALEUR0_."VAL-ECH" AS
VAL22_85_8_, VALEUR0_."VAL-ECHU" AS VAL23_85_8_,
VALEUR0_."VAL-EMETTEUR" AS VAL24_85_8_, VALEUR0_."VAL-FLAT" AS
VAL25_85_8_, VALEUR0_."VAL-FLRD" AS VAL26_85_8_,
VALEUR0_."VAL-FLRF" AS VAL27_85_8_, VALEUR0_."VAL-LIP" AS
VAL28_85_8_, VALEUR0_."VAL-LIVR" AS VAL29_85_8_,
VALEUR0_."VAL-MONTE" AS VAL30_85_8_, VALEUR0_."VAL-NBCOUP" AS
VAL31_85_8_, VALEUR0_."VAL-NBJB" AS VAL32_85_8_,
VALEUR0_."VAL-NCH" AS VAL33_85_8_, VALEUR0_."VAL-NOM1" AS
VAL34_85_8_, VALEUR0_."VAL-NOM2" AS VAL35_85_8_,
VALEUR0_."VAL-NVAL" AS VAL36_85_8_, VALEUR0_."VAL-REM" AS
VAL37_85_8_, VALEUR0_."VAL-SKF" AS VAL38_85_8_,
VALEUR0_."VAL-SOLDE" AS VAL39_85_8_, VALEUR0_."VAL-SPEC" AS
VAL40_85_8_, VALEUR0_."VAL-TIN" AS VAL41_85_8_,
VALEUR0_."VAL-UCO" AS VAL42_85_8_, VALEUR0_."VAL-USER" AS
VAL43_85_8_, BRANCHE1_."BEC-NUM" AS BEC1_12_0_,
BRANCHE1_."BEC-LIB" AS BEC2_12_0_, GVAL2_."CGV-NUM" AS
CGV1_39_1_, GVAL2_."CGV-C54" AS CGV2_39_1_, GVAL2_."CGV-C55" AS
CGV3_39_1_, GVAL2_."CGV-CATPLAC" AS CGV4_39_1_,
GVAL2_."CGV-COA" AS CGV5_39_1_, GVAL2_."CGV-LIB" AS CGV6_39_1_,
MONNAIE3_."MON-ISO" AS MON1_51_2_, MONNAIE3_."MON-ARR" AS
MON2_51_2_, MONNAIE3_."MON-LIB" AS MON3_51_2_,
MONNAIE3_."MON-NO" AS MON4_51_2_, MONNAIE3_."MON-UNI" AS
MON5_51_2_, MONNAIE4_."MON-ISO" AS MON1_51_3_,
MONNAIE4_."MON-ARR" AS MON2_51_3_, MONNAIE4_."MON-LIB" AS
MON3_51_3_, MONNAIE4_."MON-NO" AS MON4_51_3_,
MONNAIE4_."MON-UNI" AS MON5_51_3_, PAYS5_."NODOM" AS
NODOM1_60_4_, PAYS5_."NOMPAYS" AS NOMPAYS2_60_4_, PAYS5_."PAYS"
AS PAYS3_60_4_, PAYS6_."NODOM" AS NODOM1_60_5_,
PAYS6_."NOMPAYS" AS NOMPAYS2_60_5_, PAYS6_."PAYS" AS
PAYS3_60_5_, PLACECOT7_."PCO-NUM" AS PCO1_64_6_,
PLACECOT7_."PCO-COD" AS PCO2_64_6_, PLACECOT7_."PCO-ECH" AS
PCO3_64_6_, PLACECOT7_."PCO-LIB" AS PCO4_64_6_,
PLACECOT7_."PCO-LIB1" AS PCO5_64_6_, PLACECOT7_."PCO-LIEU" AS
PCO6_64_6_, STOCK8_."STO-ADR" AS STO1_74_7_,
STOCK8_."STO-ALIASCPT" AS STO2_74_7_, STOCK8_."STO-CCRED" AS
STO3_74_7_, STOCK8_."STO-CDEB" AS STO4_74_7_,
STOCK8_."STO-GCPT" AS STO5_74_7_, STOCK8_."STO-GEN" AS
STO6_74_7_, STOCK8_."STO-GSO" AS STO7_74_7_,
STOCK8_."STO-LANGUE" AS STO8_74_7_, STOCK8_."STO-LISTE" AS
STO9_74_7_, STOCK8_."STO-RESULT" AS STO10_74_7_,
STOCK8_."STO-TRI" AS STO11_74_7_, STOCK8_."STO-VENTIL" AS
STO12_74_7_
FROM [TB-VALEUR] VALEUR0_ LEFT OUTER JOIN [TB-BRANCHE] BRANCHE1_ ON
VALEUR0_."VAL-BEC"=BRANCHE1_."BEC-NUM" LEFT OUTER JOIN
[TB-GVAL] GVAL2_ ON VALEUR0_."VAL-CGV"=GVAL2_."CGV-NUM" LEFT
OUTER JOIN [TB-MONNAIE] MONNAIE3_ ON
VALEUR0_."VAL-MNO"=MONNAIE3_."MON-ISO" LEFT OUTER JOIN
[TB-MONNAIE] MONNAIE4_ ON
VALEUR0_."VAL-MRBA"=MONNAIE4_."MON-ISO" LEFT OUTER JOIN
[TB-PAYS] PAYS5_ ON VALEUR0_."VAL-DOM"=PAYS5_."NODOM" LEFT
OUTER JOIN [TB-PAYS] PAYS6_ ON
VALEUR0_."VAL-POR"=PAYS6_."NODOM" LEFT OUTER JOIN [TB-PLACECOT]
PLACECOT7_ ON VALEUR0_."VAL-PCO"=PLACECOT7_."PCO-NUM" LEFT
OUTER JOIN [TB-STOCK] STOCK8_ ON
VALEUR0_."VAL-VEN"=STOCK8_."STO-ADR"
WHERE valeur0_."val-isi"='??0000275125'
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-14 08:11:36 |
|
Oh and I forgot another important observation: even if I start with the simple query and send the complex query before delayedClose, the request will be slow.
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-05-14 20:08:30 |
>Creates two Threads (NOT deamons) almost simultaneously.
Checked. That non-daemon thread is used for pooled sql. Changed it to daemon thread. Please download it.
>SELECT LEFT OUTER JOIN query:
>the request will be slow.
LEFT OUTER JOIN is slower than inner join usually.
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-15 00:01:54 |
Ok Thanks. I used HXTT Access JDBC4.1 Driver (Version 5.1.141) Compiled: May 15, 2013 and there are only deamon threads now.
I'm able to set a high delayedClose, and Tomcat is still able to shut down.
Is there any way you can optimize what's happening with the "complexe" query? It is so much faster after the first request...
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-05-15 00:14:58 |
>Is there any way you can optimize what's happening with the "complexe" query?
Automatic temporary index is disabled when delayedClose<=60s. For join sql, it will build temporary index when there's no index. After that, other sqls can utilize temporary index util that table be closed. But that feature hasn't be opened for lockType=ACCESS compatible mode.
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-15 00:33:48 |
Ok thanks for your support. Let me know if you plan on implementing this for lockType=ACCESS compatible mode.
Kind regards,
Patrick
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-05-15 00:39:55 |
|
You can zip and email us your VALEUR0_ dataabase sample, if it contains only test data.
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-15 02:11:50 |
|
Sorry I can't do that
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-05-15 02:32:54 |
This TB-VALEUR table has more than 13'000 entries, with a single PK ("val-isi"). Concerning the LEFT OUTER JOINs ins the complex query:
- All left fields are non-indexed (but one)
- All outer fields are PK's but one (but it's indexed)
Any suggestion ? thanks
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-05-15 02:41:49 |
|
Thanks. We will simulate and test it.
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
Patrick |
2013-06-12 09:11:39 |
Solved: I change the way I'm using JPA so that it generates simpler SQLs queries that don't have so many LEFT OUTER JOINs.
It's much more faster now.
Thanks for your help,
Patrick
|
Re:Re:Re:Re:Re:Re:Re:Re:lockType=ACCESS still slow when outside of delayedClose |
HXTT Support |
2013-06-12 19:58:44 |
According to my test, your LEF JOIN sql can get the result in seconds if you have indexed correctly the relative column:
*create table maintable(c1 varchar(16) , c2 varchar(8), c3 varchar(8), c4 varchar(8),i1 int, i2 int, i3 int,i4 int);
*create index c1 on maintable (c1); /*valeur0_."val-isi"*/
*create table infoc2 (id varchar(8) primary key, info varchar(32));create table infoc3 (id varchar(8) primary key, info varchar(32));create table infoc4 (id varchar(8) primary key, info varchar(32));
*create table infoi1 (id int primary key, info varchar(32));create table infoi2 (id int primary key, info varchar(32));create table infoi3 (id int primary key, info varchar(32));create table infoi4 (id int primary key, info varchar(32));
*insert into infoc2 (id) values(reccount());insert into infoc2 (id) values(reccount());insert into infoc2 (id) values(reccount());insert into infoc2 (id) values(reccount());insert into infoc2 (id) values(reccount());insert into infoc2 (id) values(reccount());insert into infoc2 (id) values(reccount());insert into infoc2 (id) values(reccount());insert into infoc2 (id) values(reccount());insert into infoc2 (id) values(reccount());
*update infoc2 set info='info'+id;
*insert into infoc3 select * from infoc2;/* 27 rows*/
*insert into infoc4 select * from infoc2
*insert into infoi1 select * from infoc2;
*insert into infoi2 select * from infoi1;
*insert into infoi3 select * from infoi1;
*insert into infoi4 select * from infoi1;
*insert into maintable (c2) values(reccount());insert into maintable (c2) values(reccount());insert into maintable (c2) values(reccount());insert into maintable (c2) values(reccount());insert into maintable (c2) values(reccount());insert into maintable (c2) values(reccount());insert into maintable (c2) values(reccount());insert into maintable (c2) values(reccount());insert into maintable (c2) values(reccount());
*insert into maintable select * from maintable;insert into maintable select * from maintable;insert into maintable select * from maintable;insert into maintable select * from maintable;insert into maintable select * from maintable;insert into maintable select * from maintable;insert into maintable select * from maintable;insert into maintable select * from maintable;insert into maintable select * from maintable;insert into maintable select * from maintable;insert into maintable select * from maintable;
*select top 1 reccount() from maintable;/* 18432 row */
*update maintable set c2=recno()%50,c3=recno()%25,c4=recno()%4,i1=recno()%33,i2=recno()%11,i3=recno()%100,i4=recno()%73;
*update maintable set c1='??0000275'+(recno()% 333) where recno()%2=0
select maintable.*,infoc2.info,infoc3.info,infoc4.info,infoi1.info,infoi2.info,infoi3.info,infoi4.info from maintable left join infoc2 on maintable.c2=infoc2.id left join infoc3 on maintable.c3=infoc3.id left join infoc4 on maintable.c4=infoc4.id left join infoi1 on maintable.i1=infoi1.id left join infoi2 on maintable.i2=infoi2.id left join infoi3 on maintable.i3=infoi3.id left join infoi4 on maintable.i4=infoi4.id where maintable.c1='??0000275125';
select maintable.*,infoc2.info,infoc3.info,infoc4.info,infoi1.info,infoi2.info,infoi3.info,infoi4.info from maintable left join infoc2 on maintable.c2=infoc2.id left join infoc3 on maintable.c3=infoc3.id left join infoc4 on maintable.c4=infoc4.id left join infoi1 on maintable.i1=infoi1.id left join infoi2 on maintable.i2=infoi2.id left join infoi3 on maintable.i3=infoi3.id left join infoi4 on maintable.i4=infoi4.id where maintable.c1='??0000275125';
c1 VARCHAR 32 32 0 false 12
c2 VARCHAR 16 16 0 false 12
c3 VARCHAR 16 16 0 false 12
c4 VARCHAR 16 16 0 false 12
i1 INTEGER 11 10 0 false 4
i2 INTEGER 11 10 0 false 4
i3 INTEGER 11 10 0 false 4
i4 INTEGER 11 10 0 false 4
infoc2.info VARCHAR 64 64 0 false 12
infoc3.info VARCHAR 64 64 0 false 12
infoc4.info VARCHAR 64 64 0 false 12
infoi1.info VARCHAR 64 64 0 false 12
infoi2.info VARCHAR 64 64 0 false 12
infoi3.info VARCHAR 64 64 0 false 12
infoi4.info VARCHAR 64 64 0 false 12
??0000275125 8 8 2 29 7 58 20 info8 info8 info2 null info7 null info20
??0000275125 24 24 null 2 2 24 29 info24 info24 null info2 info2 info24 null
??0000275125 40 15 2 8 8 90 38 null info15 info2 info8 info8 null null
??0000275125 6 6 null 14 3 56 47 info6 info6 null info14 info3 null null
??0000275125 22 22 2 20 9 22 56 info22 info22 info2 info20 info9 info22 null
??0000275125 38 13 null 26 4 88 65 null info13 null info26 info4 null null
??0000275125 4 4 2 32 10 54 1 info4 info4 info2 null info10 null info1
??0000275125 20 20 null 5 5 20 10 info20 info20 null info5 info5 info20 info10
??0000275125 36 11 2 11 null 86 19 null info11 info2 info11 null null info19
??0000275125 2 2 null 17 6 52 28 info2 info2 null info17 info6 null null
??0000275125 18 18 2 23 1 18 37 info18 info18 info2 info23 info1 info18 null
??0000275125 34 9 null 29 7 84 46 null info9 null null info7 null null
??0000275125 0 null 2 2 2 50 55 info0 null info2 info2 info2 null null
??0000275125 16 16 null 8 8 16 64 info16 info16 null info8 info8 info16 null
??0000275125 32 7 2 14 3 82 null null info7 info2 info14 info3 null null
??0000275125 48 23 null 20 9 48 9 null info23 null info20 info9 null info9
??0000275125 14 14 2 26 4 14 18 info14 info14 info2 info26 info4 info14 info18
??0000275125 30 5 null 32 10 80 27 null info5 null null info10 null null
??0000275125 46 21 2 5 5 46 36 null info21 info2 info5 info5 null null
??0000275125 12 12 null 11 null 12 45 info12 info12 null info11 null info12 null
??0000275125 28 3 2 17 6 78 54 null info3 info2 info17 info6 null null
??0000275125 44 19 null 23 1 44 63 null info19 null info23 info1 null null
??0000275125 10 10 2 29 7 10 72 info10 info10 info2 null info7 info10 null
??0000275125 26 1 null 2 2 76 8 info26 info1 null info2 info2 null info8
??0000275125 42 17 2 8 8 42 17 null info17 info2 info8 info8 null info17
??0000275125 8 8 null 14 3 8 26 info8 info8 null info14 info3 info8 info26
??0000275125 24 24 2 20 9 74 35 info24 info24 info2 info20 info9 null null
The total row number of resultset: 27
BUILD SUCCESSFUL (total time: 4 seconds)
|
|
|
Main
webmaster@hxtt.com