[the_wizzkid]

Hi,

just had a similar thing – using 7.14 Enterprise on XP with MysqlServer 4.1.21 on Linux.

Copied from the above DB server to another with MysqlServer 5.1.31 on Linux about 800+ Tables. Most went fine, but Tables with lots of records (over 800 MB up to 1.5 G:cool: and long data rows wer copied only in part – about half of it. When repeating the copy on single tables, it went fine but the copy process was significant slower… is it because of the different Versions?

Can't tell exact which tables and if it was exactly half- i fixed it in the meanwhile. But i might need to do the operation in a few days again… let you know then.

[the_wizzkid]

Thank you for the Bug report! I'll check into it!

[the_wizzkid]

ups…Typo… xcuse me…

MySql Server Version ist 4.1.21.

[the_wizzkid]

Hi…

2nd Post: “I think you are comparing two queries that do not return the same number of rows! I also think that the time is spent in transfer of the result!”

Look at the query.. it is the same!

First post:

1)

Setup:

Yes, commandline is running on the MySQL machine.

and SQLyog (you are not telling you are using SQLyog, but I assume you do)

I did.. first line in my topic…

remote machine with 1 GBit conection, no tunneling.

MySQL server 4.1.2 SQLyog version: on top of my message… 7.12…

I think you didn't get the point… from my PC, using sqlyog 7.12, doing both Queries, one time with a variable and the other one with out – but it's basically the same query, same Sqlyog, same PC.

Just one ist with the value set directly, the other one with a variable (Same value as the direct one), and this takes 12 seconds longer:

Select with Variable:

set @docnum = '40803231';

Select * from IASSALITEM where client = '00' and company = '01' and doctype = 'GR' and docnum = @docnum ;

(0 row(s) affected)

Execution Time : 00:00:00:000

Transfer Time : 00:00:00:016

Total Time : 00:00:00:016

(34 row(s) returned)

Execution Time : 00:00:07:062

Transfer Time : 00:00:03:141

Total Time : 00:00:10:203

Result is 1 to 30 Rows.

This remains approx. the same, even executing this 5 times in a row – no Cache!!!

Select without Variable:

Select * from IASSALITEM where client = '00' and company = '01' and doctype = 'GR' and docnum = '40803231';

(34 row(s) returned)

Execution Time : 00:00:00:000

Transfer Time : 00:00:00:015

Total Time : 00:00:00:015

Just for your Info, the Speed on the Server is for sure faster, but I just tried this on the Server to see if the “variable mode” takes there also some more time, but it doesn't…

Here is another example, on a A Customer Table with adress data, each customer has 2 rows. Table has 160000 rows:

set @Customer = '012345';

Select customer from IASCUSTOMER where customer = @Customer;

(2 row(s) returned)

Execution Time : 00:00:00:219

Transfer Time : 00:00:00:000

Total Time : 00:00:00:219

Select customer from IASCUSTOMER where customer = '012345';

(2 row(s) returned)

Execution Time : 00:00:00:000

Transfer Time : 00:00:00:015

Total Time : 00:00:00:015

First Select takes more than 2nd one, even if executed more often, (seems to use no cache!) the other one goes faster.

But with bigger tables, the differents is much bigger!

4)

EXPLAIN SELECT .. are the same, as it ist the same query… just one has the value, the other one the variable.

5)

CREATE TABLE statement:

CREATE TABLE `IASSALITEM` (

`CLIENT` char(2) collate latin1_german1_ci NOT NULL default '',

`COMPANY` char(3) collate latin1_german1_ci NOT NULL default '',

`DOCTYPE` char(2) collate latin1_german1_ci NOT NULL default '',

`DOCNUM` varchar(8) collate latin1_german1_ci NOT NULL default '',

`ITEMNUM` int(11) NOT NULL default '0',

`ITEMTYPE` char(1) collate latin1_german1_ci default NULL,

`VALIDFROM` datetime default NULL,

`VALIDUNTIL` datetime default NULL,

`BUSAREA` char(3) collate latin1_german1_ci default NULL,

`REFDOCTYPE` char(2) collate latin1_german1_ci default NULL,

`REFDOCNUM` varchar(8) collate latin1_german1_ci default NULL,

`REFITEMNUM` int(11) default NULL,

`REFSUBITEM` int(11) default NULL,

`CREATEDBY` varchar(12) collate latin1_german1_ci default NULL,

`CREATEDAT` datetime default NULL,

`CHANGEDBY` varchar(12) collate latin1_german1_ci default NULL,

`CHANGEDAT` datetime default NULL,

`ISOFFCHAR` int(11) default NULL,

`ISORDCHAR` int(11) default NULL,

`ISDELCHAR` int(11) default NULL,

`ISINVCHAR` int(11) default NULL,

`OFFTYPE` int(11) default NULL,

`ORDTYPE` int(11) default NULL,

`DELTYPE` int(11) default NULL,

`INVTYPE` int(11) default NULL,

`OFFSTAT` int(11) default NULL,

`ORDSTAT` int(11) default NULL,

`DELSTAT` int(11) default NULL,

`INVSTAT` int(11) default NULL,

`PAYMSTAT` int(11) default NULL,

`RESSTAT` int(11) default NULL,

`ISSUEDSTAT` int(11) default NULL,

`ISDELETE` int(11) default NULL,

`ISTEXT` int(11) default NULL,

`ISPRINT` int(11) default NULL,

`ISEXPORT` int(11) default NULL,

`ISPACKITEM` int(11) default NULL,

`ISAVAILCHK` int(11) default NULL,

`ISREQSET` int(11) default NULL,

`ISGOODSISSUE` int(11) default NULL,

`ISINVOICEITEM` int(11) default NULL,

`ISDISCOUNT` int(11) default NULL,

`ISCASHDISC` int(11) default NULL,

`ISCUSTSPECIFIC` int(11) default NULL,

`MATERIAL` varchar(25) collate latin1_german1_ci default NULL,

`PLANT` char(3) collate latin1_german1_ci default NULL,

`WAREHOUSE` char(3) collate latin1_german1_ci default NULL,

`STOCKPLACE` varchar(10) collate latin1_german1_ci default NULL,

`SPECIALSTOCK` char(1) collate latin1_german1_ci default NULL,

`BATCHNUM` varchar(12) collate latin1_german1_ci default NULL,

`MTEXT` varchar(40) collate latin1_german1_ci default NULL,

`QUANTITY` decimal(21,8) default NULL,

`QUNIT` char(3) collate latin1_german1_ci default NULL,

`QUANTITYX` decimal(21,8) default NULL,

`QUNITX` char(3) collate latin1_german1_ci default NULL,

`SELQUNITX` int(11) default NULL,

`ISSUEDQTY` decimal(21,8) default NULL,

`RESERVEDQTY` decimal(21,8) default NULL,

`INVOICEDQTY` decimal(21,8) default NULL,

`AVAILQTY` decimal(21,8) default NULL,

`AVAILAMNT` decimal(21,2) default NULL,

`ORDEREDQTY` decimal(21,8) default NULL,

`SHIPDATE` datetime default NULL,

`DESDLVDATE` datetime default NULL,

`PRODDATE` datetime default NULL,

`TIMEKEY` int(11) default NULL,

`REQDATE` datetime default NULL,

`REQTYPE` char(2) collate latin1_german1_ci default NULL,

`REQNUM` int(11) default NULL,

`REQENDDATE` datetime default NULL,

`TRANTYPEGI` char(3) collate latin1_german1_ci default NULL,

`TRANTYPEGR` char(3) collate latin1_german1_ci default NULL,

`SPRICE` decimal(21,2) default NULL,

`PRICEFACTOR` decimal(21,10) default NULL,

`SUBTOTAL` decimal(21,2) default NULL,

`GRANDTOTAL` decimal(21,2) default NULL,

`GROSS` decimal(21,2) default NULL,

`VATKEY` char(2) collate latin1_german1_ci default NULL,

`VATRATE` decimal(5,2) default NULL,

`VATAMNT` decimal(21,2) default NULL,

`DISCKEY1` char(3) collate latin1_german1_ci default NULL,

`DISCKEY2` char(3) collate latin1_german1_ci default NULL,

`DISCKEY3` char(3) collate latin1_german1_ci default NULL,

`DISCKEY4` char(3) collate latin1_german1_ci default NULL,

`DISCKEY5` char(3) collate latin1_german1_ci default NULL,

`DISCTYPE1` int(11) default NULL,

`DISCTYPE2` int(11) default NULL,

`DISCTYPE3` int(11) default NULL,

`DISCTYPE4` int(11) default NULL,

`DISCTYPE5` int(11) default NULL,

`DISCRATE1` decimal(21,2) default NULL,

`DISCRATE2` decimal(21,2) default NULL,

`DISCRATE3` decimal(21,2) default NULL,

`DISCRATE4` decimal(21,2) default NULL,

`DISCRATE5` decimal(21,2) default NULL,

`DISCAMNT1` decimal(21,2) default NULL,

`DISCAMNT2` decimal(21,2) default NULL,

`DISCAMNT3` decimal(21,2) default NULL,

`DISCAMNT4` decimal(21,2) default NULL,

`DISCAMNT5` decimal(21,2) default NULL,

`TDISCAMNT` decimal(21,2) default NULL,

`DISCFROMHEAD` decimal(21,2) default NULL,

`PARPAYAMNT` decimal(21,2) default NULL,

`PARPAYVAT` decimal(21,2) default NULL,

`OPENINVAMNT` decimal(21,2) default NULL,

`CURRENCY` char(3) collate latin1_german1_ci default NULL,

`HCURRENCY` char(3) collate latin1_german1_ci default NULL,

`MCURRENCY` char(3) collate latin1_german1_ci default NULL,

`EXCHRATE` decimal(21,10) default NULL,

`PRICING` varchar(10) collate latin1_german1_ci default NULL,

`PRICELIST` char(2) collate latin1_german1_ci default NULL,

`SALDEPT` char(3) collate latin1_german1_ci default NULL,

`HIERARCHY` char(3) collate latin1_german1_ci default NULL,

`ACCOUNT` varchar(25) collate latin1_german1_ci default NULL,

`CUSTOMER` varchar(8) collate latin1_german1_ci default NULL,

`PACKTYPE` varchar(4) collate latin1_german1_ci default NULL,

`NUMOFPACKS` decimal(21,8) default NULL,

`GROSSWEIGHT` decimal(21,8) default NULL,

`GROSWEIGHTUNIT` char(3) collate latin1_german1_ci default NULL,

`NETWEIGHT` decimal(21,8) default NULL,

`NETWEIGHTUNIT` char(3) collate latin1_german1_ci default NULL,

`VOLUME` decimal(21,8) default NULL,

`VOLUMEUNIT` char(3) collate latin1_german1_ci default NULL,

`CUSTOMNUM` varchar(10) collate latin1_german1_ci default NULL,

`CUSTOMRATE` decimal(21,10) default NULL,

`EXPORTTEXT` varchar(40) collate latin1_german1_ci default NULL,

`EUROMAT` varchar(13) collate latin1_german1_ci default NULL,

`STATMATNUM` varchar(15) collate latin1_german1_ci default NULL,

`ORGCOUNTRY` char(3) collate latin1_german1_ci default NULL,

`REFCOMPANY` char(2) collate latin1_german1_ci default NULL,

`COMMISSIONER` varchar(8) collate latin1_german1_ci default NULL,

`COMMISRATE` decimal(5,2) default NULL,

`GROSWEIGHTPRC` decimal(21,2) default NULL,

`NETWEIGHTPRC` decimal(21,2) default NULL,

`VOLUMEPRC` decimal(21,2) default NULL,

`STEXT` varchar(40) collate latin1_german1_ci default NULL,

`LTEXT` text collate latin1_german1_ci,

`PROJECT` varchar(8) collate latin1_german1_ci default NULL,

`COSTCENTER` varchar(8) collate latin1_german1_ci default NULL,

`CCPLANT` char(3) collate latin1_german1_ci default NULL,

`PLANORDER` varchar(8) collate latin1_german1_ci default NULL,

`CUSTORDERTYPE` char(2) collate latin1_german1_ci default NULL,

`CUSTORDERNUM` varchar(8) collate latin1_german1_ci default NULL,

`CUSTORDERITEM` int(11) default NULL,

`ASSETTYPE` varchar(4) collate latin1_german1_ci default NULL,

`ASSETNUMBER` varchar(8) collate latin1_german1_ci default NULL,

`ASSETITEM` int(11) default NULL,

`INTORDERNUM` varchar(8) collate latin1_german1_ci default NULL,

`INTORDERITEM` int(11) default NULL,

`ISRESFORDEL` int(11) default NULL,

`COSTOBJECT` varchar(8) collate latin1_german1_ci default NULL,

`COSTITEM` int(11) default NULL,

`ISSTOP` int(11) default NULL,

`ISTOLERANCE` int(11) default NULL,

`LOWERTOL` decimal(5,2) default NULL,

`UPPERTOL` decimal(5,2) default NULL,

`SKUNIT` char(3) collate latin1_german1_ci default NULL,

`FACTOR` decimal(21,10) default NULL,

`SUBCHFROMHEAD` decimal(21,2) default NULL,

`ISCALCFROMGROSS` int(11) default NULL,

`NORESERV` int(11) default NULL,

`CONSPLACE` varchar(14) collate latin1_german1_ci default NULL,

`DLOADPLACE` varchar(5) collate latin1_german1_ci default NULL,

`KANBAN` varchar(22) collate latin1_german1_ci default NULL,

`CARRYFROM` datetime default NULL,

`CARRYUNTIL` datetime default NULL,

`ISCOMPLETE` int(11) default NULL,

`SETITEMNUM` int(11) NOT NULL default '0',

`REFSETITEM` int(11) default NULL,

`ITEMISS` int(11) default NULL,

`ISSET` int(11) default NULL,

`ISVARIANT` int(11) default NULL,

`VARIANTKEY` varchar(20) collate latin1_german1_ci default NULL,

`VOPTIONS` varchar(255) collate latin1_german1_ci default NULL,

`ISLEASE` int(11) default NULL,

`ISPRDORDER` int(11) default NULL,

`ISSIMULATION` int(11) default NULL,

`POTYPE` char(2) collate latin1_german1_ci default NULL,

`PRDORDER` varchar(8) collate latin1_german1_ci default NULL,

`ISPROTECT` int(11) default NULL,

`PROTPER` int(11) default NULL,

`RESERVINC` int(11) default NULL,

`LASTPROTDATE` datetime default NULL,

`TERMINAL` varchar(8) collate latin1_german1_ci default NULL,

`DEPARTMENT` varchar(8) collate latin1_german1_ci default NULL,

`WORKLEVEL` varchar(4) collate latin1_german1_ci default NULL,

`STATION` varchar(8) collate latin1_german1_ci default NULL,

`WARRBEGINDAT` datetime default NULL,

`WARRENDDAT` datetime default NULL,

`WARRANTPER` int(11) default NULL,

`CONTACTNUM` varchar(12) collate latin1_german1_ci default NULL,

`ROHGEWINN` decimal(21,2) default NULL,

UNIQUE KEY `IASSALITEM_U1` (`DOCNUM`,`ITEMNUM`,`SETITEMNUM`,`DOCTYPE`,`COMPANY`,`CLIENT`),

KEY `IASSALITEM_1` (`DOCNUM`,`DOCTYPE`,`COMPANY`,`CLIENT`),

KEY `IASSALITEM_10` (`CLIENT`,`COMPANY`,`DOCTYPE`,`DOCNUM`,`VALIDFROM`,`ISORDCHAR`,`ORDTYPE`,`ORDSTAT`,`ISTEXT`,`ISSTOP`),

KEY `IASSALITEM_2` (`MTEXT`,`MATERIAL`,`DOCNUM`,`DOCTYPE`,`COMPANY`,`CLIENT`),

KEY `IASSALITEM_4` (`CLIENT`,`VALIDFROM`,`MATERIAL`),

KEY `IASSALITEM_5` (`CLIENT`,`COMPANY`,`DOCTYPE`,`DOCNUM`,`ISTEXT`,`DELSTAT`,`ISDELCHAR`,`ISORDCHAR`,`ISSTOP`),

KEY `IASSALITEM_6` (`CLIENT`,`COMPANY`,`DOCTYPE`,`DOCNUM`,`MATERIAL`,`MTEXT`,`PLANT`,`WAREHOUSE`),

KEY `IASSSALITEM_R1` (`CLIENT`,`COMPANY`,`REFDOCTYPE`,`REFDOCNUM`,`SETITEMNUM`),

KEY `CLIENT` (`CLIENT`,`COMPANY`,`VALIDFROM`,`ISDELCHAR`,`MATERIAL`),

KEY `IASSALITEM_CUST_MAT` (`CLIENT`,`COMPANY`,`DOCTYPE`,`CUSTOMER`,`ISDELETE`),

KEY `IASSALITEM_3` (`CLIENT`,`COMPANY`,`CUSTOMER`,`DOCTYPE`,`DOCNUM`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci

[the_wizzkid]

I forgot… if this speed factor can't be resolved,

may be SQLyog could then write the export locally in a TEMP location and copy it to the destination…

[the_wizzkid]

Are you exporting to a networkshare? try to export locally, if it is faster.

See also topic:

Csv Export Slow To Network Location” in forum “SQLyog: Bugs / Feature Requests”.

We have to export some 100k lines, too, and it exports 22000 lines a minute locally… and 1900 a minute on a network share…

By the way, I'm programming, too, and GUI does influence performance in such a case… showing every 10 or 50 lines would be enough to show and it would speed up the export- even if it would be only 10% faster would be a nice thing to have…

[the_wizzkid]

Hi,

just to let you know… we have the same issue since about 2 years through all the different releases…

Our Networkdrive ist fast (1Gb connection) usually networkdrive is as fast as local hard disk. Only when exporting with SQLyog to Networkshare, it is real slow. 🙁 We have a Windows Domain and Networkshare is a Windows 2003 Server. But also a NAS on Linux basis is so slow…

We export everything locally and copy it then… its quite annoing, as exports hve to be done more than one time daily…

[the_wizzkid]

I'm missing the second function, too… would be great to have that. My DB has about 820 Tables and i have a test and Production DB, almost all but not all tables have to be copied from time to time.

Would be even better if selections with Shift & Click would be working to select multiple tables. 🙂

[the_wizzkid]

Good idea,

i very often have short queries with need of variation of them and would need this funktion, too!

Martin

[the_wizzkid]

Hi Mark,

Using 6.14 Enterprise on XP Prof. SP2, Radeon 9250 – Dual View Mode. I have the same behavior. Doesn't bother me, as i resize very seldom that window. Icon Flickering takes about half a second.

The_Wizzkid

[the_wizzkid]

Hi there,

I didn't see this in the last bug fixes… is this already fixed, or when will it be?

we're using this option very frequently and need it…

Martin

[the_wizzkid]

Here is one- its a Table called TMPSALCOMMIS with all german special Characters in it.

[the_wizzkid]

Hi there, thank you for your advice. I looked into the FAQ which I didn't look at before, but the problem remains the same…

if I start the GUI Client and do NOT use any SET NAMES command, just do my select, it comes out right in the result pane. I can copy the result into Excel, for example, and it is correct.

But when doing an Export of this data from the Resultpane with right klick and “Export table data…” into “CSV”, then it gets scrambled in the CSV.

Doing the same Export into “Excel XML”, I get the correct output…

This worked still in the 5.X version.

[the_wizzkid]

I look at the blob fields using the CTRL + L in the “Result” panel (does't work in the “Table data” panel) for Long text – is a actually a great feature.

By the way, blob's are not shown correctly in the “table data” panel, if the last Column is a Blob. I can see only a part of the blob. Preference Setting: Truncate column headers… : I can see 1/3rd of the blob field, but can't scroll enough to the right to see the “Blob”-Text on the button.

Without the truncate setting: scrolling normally to the right, I can't even see the blob column, and clicking on the > on the scrollbar, I shows partally up. –

Would be good to fix this, as I would miss the last column, if I don't know it's there… 🙁

[the_wizzkid]

Got it, I sould have used the other topic- sorry…

For right now, I'm saving each in a single File. Is not the best solution, but at least I find it when needed.

I through most of the Queries away at the end of day, but there are quite some which I need more often each week and I don't want to write them all over again each time… And that's the point – I'm writing a big one, taking some time of trying and than I got to do some other urgent query, then turn back to my big query. And after other 5 interuptions and another 50 lines of to-be-thrown-away-queries I forget about the query at the top- my 30 minutes of writing and getting tables and fields together are gone!

So, it might be bothering getting a Save? question at the end, but if it's only for those queries I saved already of opened, it would help a lot… by the way, my junior has the same problems…

[Author]

Posts