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:
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