Odbc Import Converts Decimal Wrong

[linroc]

Hi,

I am importing a number of sybase tables into a mysql db.

It seems to work fine – but for some reason it converts some numbers into the type 'decimal' – and doing so it throws away the digits after the seperator (,).

It seems as if it converts the numbers into integers ??

Is there a way to avoid this and still use the odbc import wizard??

Anothe issue is speed – is there a way to speed up the impport process – currently i am able to import approx. 1200 records/minute – thats a long time when fetching ~500.000 records. If i use MS Access the import process is 10-20 times faster.

Hope someone has a few hints for me – I am turning grey

/cartmann

[peterlaursen]

1)

Read about numerical types here:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

A decimal is defined with two parameters – if only one is used then MySQL assumes theat the last one is = (zero). A decimal(6) >> decimal(6,0), that is 123456,79 is rounded to 123457. With a decimal(6,1) 123456,79 is rounded to 123456,8. But it would be more normal to use a real or double type. Decimals and floats are for special purposes- real and doubles are the 'common' non-integer types to use with MySQL. You know how to map types with the import proces? Howver for 'precison math' with monetary data in particular, you may stick to DECIMAL. You give in the parameters in the 'length' column. See attached.

2)

using decimals also could to some extend explain the speed issue (they are known to be slow since they are stored as strings before mySQL 5.03). But I simply think this is primarly a matter of tuning MySQL. There are dozens of parameters to tune with, depending on table/engine type and more. How did you decide the buffer sizes in your my.ini? You might get MySQL Administrator and play a little with the various buffers. Also try using the 'my-huge.ini' or the 'my-large.ini'template

Also is it a debug MySQL server that you are running? More info about the server is needed. Where is it running? How do you connect?

3)

To speed up things consider if you can use the FILTERING and SQL_WHERE options to do an incremental import (not to import data that have been imported allready). Refer to:

http://www.webyog.com/faq/14_79_en.html

http://www.databasejournal.com/features/my…p/10897_3550146

[peterlaursen]

hmmmm ..

there is a FAQ to write here on DECIMALs and SQLyog..

… done http://webyog.com/faq/29_90_en.html <_<

[linroc]

Hi,

thanks for the reply 🙂

Regarding the decimal issue – i would prefer to use doubles, but using the import wizard it chooses to use decimal. If importing with access the choice is number – which i believe is similar to the decimal – but here scale and precision are correctly.

Is there somewhere to setup the precision and scale without doing it for each field to import – I mean something like a general preference ?? Or even worse turn all fields into text fields :huh: I know big mistake – but all my work with this is ending up in text files as a customer requirement.

The speed issue – it seems it is something I need to dig into. My mysql installation is a default 4.1 windows installment – so there is probably lots of improvement possibillities.

/cartmann

[peterlaursen]

Quote:

i would prefer to use doubles, but using the import wizard it chooses to use decimal.

well, then override the proposal of that silly ODBC-driver! Simply press the 'map' button in the Migration Wizard for the column(s) and change the name and type as you like (as long as it is legal with the data).

In this respect that you can override the driver, the SQLyog Migration Tool is unique!

[linroc]

I should probably one time create the sql (based on the corrected wizard sql) and save it to use in the future.

Well it seems i have my data for this time and the weekend is closing in 😀 . I must try the optimization next time (which is next month i think).

Thanks for your replies – as always very helpfull.

/cartmann

peterlaursen wrote on Feb 3 2006, 03:25 PM:

well, then override the proposal of that silly ODBC-driver!  Simply press the 'map' button in the Migration Wizard for the column(s) and change the name and type as you like (as long as it is legal with the data). 

In this respect that you can override the driver, the SQLyog Migration Tool is unique!

[post=”8650″]<{POST_SNAPBACK}>[/post]

[peterlaursen]

YEP .. that is one of the basic concepts that you can save and re-use the jobfile.

[Author]

Posts