jeudi 15 octobre 2015

Replicated database is slow

I am using SymmetricDS software to replicate database across platforms, i receive database structure and data without any problems, but when it comes to queries and insertions it gets very slow. It takes around 300-400ms per query/insertion, which makes a pretty noticable lag in my application.

What is interesting that when i built exact same database manually, queries take around 10-15ms (same code and database).

So far i tried and failed with:

  • Stopping SymmetricDS instance
  • Optimisation advices found on SO

In my suspicion it has to do something with SymmetricDS tables and triggers.

Let me give you an example:

This is my query table:

enter image description here

and this is my query code:

public static List<EItemInvoiceDto> getItemInvoice(Context context, EParamDto eparamDto, Integer lokacija) {
    DatabaseHelper databaseHelper = new DatabaseHelper(context);
    SQLiteDatabase database = null;
    Cursor rs = null;
    long startTime=System.currentTimeMillis();
    List<EItemInvoiceDto> itemsInvoice = new ArrayList<EItemInvoiceDto>();
    try {
        database = databaseHelper.getReadableDatabase();
        String sql = "SELECT em3.leto, em3.gd, em3.strmst, zaporedna, datdok, casdok, idlokacije, blago, kolicina, osnova, cena, ddv, popust,"
                   + " ncena, rabat, marza, narocilo, naroc, naroc_kol, opomba, "
                   + " id, ean, naziv, dobavitelj, klasifikator, parent, enmere, aktiven, fixcena, ddv1, ddv2, v, narockuh, lokacija, opis "
                   + " FROM emaloprod3 em3, eblago eb "
                   + " WHERE em3.blago = eb.id "
                   + " AND em3.leto " + WiDbFormat.format(eparamDto.getLeto(), true)
                   + " AND em3.gd " + WiDbFormat.format(eparamDto.getGd(), true)
                   + " AND em3.strmst " + WiDbFormat.format(eparamDto.getStrmst(), true)
                   + " AND em3.idlokacije " + WiDbFormat.format(lokacija, true)
                   + " ORDER BY em3.zaporedna ASC ";
        Log.d(TAG, sql);
        rs = database.rawQuery(sql, null);
        if (rs.moveToFirst() == true) {
            do {
                EItemInvoiceDto eItemInvoiceDto = new EItemInvoiceDto();
                eItemInvoiceDto.setLeto(rs.getString(rs.getColumnIndex("leto")));
                eItemInvoiceDto.setGd(rs.getString(rs.getColumnIndex("gd")));
                eItemInvoiceDto.setStrmst(rs.getString(rs.getColumnIndex("strmst")));
                eItemInvoiceDto.setZaporedna(rs.getInt(rs.getColumnIndex("zaporedna")));
                eItemInvoiceDto.setDatdok(Date.valueOf(rs.getString(rs.getColumnIndex("datdok"))));
                eItemInvoiceDto.setCasdok(Time.valueOf(rs.getString(rs.getColumnIndex("casdok"))));
                eItemInvoiceDto.setIdlokacije(rs.getInt(rs.getColumnIndex("idlokacije")));
                eItemInvoiceDto.setBlago(rs.getInt(rs.getColumnIndex("blago")));
                eItemInvoiceDto.setKolicina(new BigDecimal(rs.getString(rs.getColumnIndex("kolicina"))));
                eItemInvoiceDto.setOsnova(new BigDecimal(rs.getString(rs.getColumnIndex("osnova"))));
                eItemInvoiceDto.setCena(new BigDecimal(rs.getString(rs.getColumnIndex("cena"))));
                eItemInvoiceDto.setDdv(new BigDecimal(rs.getString(rs.getColumnIndex("ddv"))));
                eItemInvoiceDto.setPopust(new BigDecimal(rs.getString(rs.getColumnIndex("popust"))));
                eItemInvoiceDto.setNcena(new BigDecimal(rs.getString(rs.getColumnIndex("ncena"))));
                eItemInvoiceDto.setRabat(new BigDecimal(rs.getString(rs.getColumnIndex("rabat"))));
                eItemInvoiceDto.setMarza(new BigDecimal(rs.getString(rs.getColumnIndex("marza"))));
                eItemInvoiceDto.setNarocilo(rs.getInt(rs.getColumnIndex("narocilo")));
                eItemInvoiceDto.setNaroc(WiDbFormat.convertIntToBoolean(rs.getInt(rs.getColumnIndex("naroc"))));
                eItemInvoiceDto.setNaroc_kol(new BigDecimal(rs.getString(rs.getColumnIndex("naroc_kol"))));
                eItemInvoiceDto.setOpomba(rs.getString(rs.getColumnIndex("opomba")));
                eItemInvoiceDto.setId(rs.getInt(rs.getColumnIndex("id")));
                eItemInvoiceDto.setEan(rs.getString(rs.getColumnIndex("ean")));
                eItemInvoiceDto.setNaziv(rs.getString(rs.getColumnIndex("naziv")));
                eItemInvoiceDto.setDobavitelj(rs.getInt(rs.getColumnIndex("dobavitelj")));
                eItemInvoiceDto.setKlasifikator(rs.getString(rs.getColumnIndex("klasifikator")));
                eItemInvoiceDto.setParent(rs.getString(rs.getColumnIndex("parent")));
                eItemInvoiceDto.setEnmere(rs.getString(rs.getColumnIndex("enmere")));
                eItemInvoiceDto.setAktiven(WiDbFormat.convertIntToBoolean(rs.getInt(rs.getColumnIndex("aktiven"))));
                eItemInvoiceDto.setFixcena(WiDbFormat.convertIntToBoolean(rs.getInt(rs.getColumnIndex("fixcena"))));
                eItemInvoiceDto.setDdv1(new BigDecimal(rs.getString(rs.getColumnIndex("ddv1"))));
                eItemInvoiceDto.setDdv2(new BigDecimal(rs.getString(rs.getColumnIndex("ddv2"))));
                eItemInvoiceDto.setV(rs.getInt(rs.getColumnIndex("v")));
                eItemInvoiceDto.setNarockuh(WiDbFormat.convertIntToBoolean(rs.getInt(rs.getColumnIndex("narockuh"))));
                eItemInvoiceDto.setLokacija(rs.getString(rs.getColumnIndex("lokacija")));
                eItemInvoiceDto.setOpis(rs.getString(rs.getColumnIndex("opis")));
                // Calculate saleValue of one item.
                eItemInvoiceDto.setVrednost(WiDbFormat.calculateSumValuePosition(eItemInvoiceDto));
                itemsInvoice.add(eItemInvoiceDto);
            } while (rs.moveToNext() == true);
        }
    } catch (Exception e) {
        Log.e(TAG, e.getMessage(), e);
        e.printStackTrace();
    } finally {
        DBHelper.closeAllConnections(rs, null, database);
        Log.v("test", "pridobivanja artikla  " + (System.currentTimeMillis() - startTime));
    }

    return itemsInvoice;
}

(I used this code for replicated database(300-400ms) and manually built one(10-15ms))

Did anyone experience same thing with SymmetricDS? How could i fix this issue, at the moment it is in unusable state.

Aucun commentaire:

Enregistrer un commentaire