Оптимизация БД. Сложный запрос. Внимание! Много текста!
Добавлено: 14 май 2014, 09:11
Добрый день форумчане!
Помогите оптимизировать один из разделов моего приложения.
Есть таблицы valuta(_id, name) - список валют,schet(_id,name) - список счетов, category(_id,name,...) - список категорий и подкатегорий, rashodi(_id, id_schet, id_cat, id_podCat,id_val,summa,date,...) - список операций расходы и доходы, obmen(_id, id_schet,id_val1,summa1,id_val2,summa2,...) - список операций обмена валют , moving(_id,id_schet1,id_schet2,id_val,summa) - список операций перемещений между счетами , history(_id,id_key,id_oper,date,...) - список история, все операции из предыдущих трех таблиц...
Задача - создать раздел "история" в приложении, надо выводить в список данные в хронологичном порядке за разные периоды...
У меня получился такой вот запрос:
[syntax=mysql]public Cursor getHistory(){
sql = "SELECT H._id as _id,R.summa as summa, H.id_key as id_key,H.date as dateH,R.date as date," +
" V.name as valName,C.name as catName,C2.name as podCatName,S.name as schetName," +
"'' as valName2,'' as summa2,'' as schetName2 FROM history H "
+ " INNER JOIN rashodi R ON R._id=H.id_oper "
+ " INNER JOIN valuta V ON V._id=R.id_val "
+ " INNER JOIN schet S ON S._id=R.id_schet "
+ " LEFT JOIN category C ON C._id=R.id_cat "
+ " LEFT JOIN category C2 ON C2._id=R.id_podCat "
+ " WHERE H.id_key>0 AND H.id_key<3 "
+ " union "+
" SELECT H._id as _id,O.summa1 as summa,H.id_key as id_key, " +
" H.date as dateH, O.date as date," +
" V.name as valName,'' as catName,'' as podCatName," +
" S.name as schetName,V2.name as valName2, O.summa2 as summa2, '' as schetName2 FROM history H "
+ " INNER JOIN obmen O ON O._id=H.id_oper "
+ " INNER JOIN valuta V ON V._id=O.id_val1 "
+ " INNER JOIN valuta V2 ON V2._id=O.id_val2 "
+ " INNER JOIN schet S ON S._id=O.id_schet "
+ " WHERE H.id_key==4"
+ " union "+
" SELECT H._id as _id,M.summa as summa,H.id_key as id_key, " +
" H.date as dateH, M.date as date," +
" V.name as valName,'' as catName,'' as podCatName," +
" S.name as schetName,'' as valName2, '' as summa2, S2.name as schetName2 FROM history H "
+ " INNER JOIN moving M ON M._id=H.id_oper "
+ " INNER JOIN valuta V ON V._id=M.id_val "
+ " INNER JOIN schet S ON S._id=M.id_schet1 "
+ " INNER JOIN schet S2 ON S2._id=M.id_schet2 "
+ " WHERE H.id_key==3"
;
Cursor c=mDB.rawQuery(sql, null);
Log.i("asd","сейчас будет лог");
logCursor(c);
return c;
}[/syntax]
Сначала запрос в таблицу history с id_key=1,2 - это расходы и доходы, к нему привязываются данные из других таблиц. Затем id_key=4 и id_key=3...
Все это передается адаптеру:
[syntax=java5]public class ListHistoryAdapter extends BaseAdapter {
// Declare Variables
Context context;
Cursor cursor;
LayoutInflater inflater;
View itemView;
TextView tv;
Calendar c;
int color;
Resources res = getResources();
String mCat, mOpis, mPodCat;
SimpleDateFormat ff;
public ListHistoryAdapter(Context context, Cursor cursor) {
this.context = context;
this.cursor = cursor;
inflater = (LayoutInflater) context
.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
ff = new SimpleDateFormat("dd.MM.yyyy HH:mm", Locale.getDefault());
}
@Override
public int getCount() {
return cursor.getCount();
}
@Override
public Cursor getItem(int position) {
cursor.moveToPosition(position);
return cursor;
}
@Override
public long getItemId(int position) {
cursor.moveToPosition(position);
return cursor.getLong(cursor.getColumnIndex("_id"));
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
cursor.moveToPosition(position);
int id_key=cursor.getInt(cursor.getColumnIndex("id_key"));
if ((id_key>0)&&(id_key<3))
{
itemView = inflater.inflate(R.layout.list_history, parent, false);
tv = (TextView) itemView.findViewById(R.id.tvDate);
c = Calendar.getInstance();
c.setTimeInMillis(cursor.getLong(cursor.getColumnIndex("date")));
tv.setText(ff.format(c.getTime()));
tv = (TextView) itemView.findViewById(R.id.schetName);
tv.setText(cursor.getString(cursor.getColumnIndex("schetName")));
color = res.getColor(res.getIdentifier(mColors[1], "color",
getActivity().getPackageName()));
tv.setTextColor(color);
tv = (TextView) itemView.findViewById(R.id.tvSubCat);
mCat = cursor.getString(cursor.getColumnIndex("catName"));
mPodCat = cursor.getString(cursor.getColumnIndex("podCatName"));
if (mPodCat == null) {
tv.setText(mCat);
} else {
tv.setText(mCat + " / " + mPodCat);
}
tv = (TextView) itemView.findViewById(R.id.tvSumma);
tv.setText(my.formatSumma(cursor.getFloat(cursor
.getColumnIndex("summa"))));
tv = (TextView) itemView.findViewById(R.id.tvValuta);
tv.setText(cursor.getString(cursor.getColumnIndex("valName")));
DrawSquare img = (DrawSquare) itemView.findViewById(R.id.img);
if (cursor.getInt(cursor.getColumnIndex("id_key")) == 2) {
color = res.getColor(res.getIdentifier("Green2", "color",
getActivity().getPackageName()));
img.text = "+";
} else {
color = res.getColor(res.getIdentifier("Red2", "color",
getActivity().getPackageName()));
img.text = "-";
}
img.color = color;
}
else
if (id_key==4)
{
itemView = inflater.inflate(R.layout.list_history_obmen, parent, false);
tv = (TextView) itemView.findViewById(R.id.tvDate);
c = Calendar.getInstance();
c.setTimeInMillis(cursor.getLong(cursor.getColumnIndex("date")));
tv.setText(ff.format(c.getTime()));
tv = (TextView) itemView.findViewById(R.id.schetName);
tv.setText(cursor.getString(cursor.getColumnIndex("schetName")));
color = res.getColor(res.getIdentifier(mColors[1], "color",
getActivity().getPackageName()));
tv.setTextColor(color);
tv = (TextView) itemView.findViewById(R.id.tvSubCat);
mCat = cursor.getString(cursor.getColumnIndex("catName"));
mPodCat = cursor.getString(cursor.getColumnIndex("podCatName"));
if (mPodCat == null) {
tv.setText(mCat);
} else {
tv.setText(mCat + " / " + mPodCat);
}
tv = (TextView) itemView.findViewById(R.id.tvSumma);
tv.setText(my.formatSumma(cursor.getFloat(cursor
.getColumnIndex("summa"))));
tv = (TextView) itemView.findViewById(R.id.tvSumma2);
tv.setText(my.formatSumma(cursor.getFloat(cursor
.getColumnIndex("summa2"))));
tv = (TextView) itemView.findViewById(R.id.tvValuta);
tv.setText(cursor.getString(cursor.getColumnIndex("valName")));
tv = (TextView) itemView.findViewById(R.id.tvValuta2);
tv.setText(cursor.getString(cursor.getColumnIndex("valName2")));
DrawSquare img = (DrawSquare) itemView.findViewById(R.id.img);
color = res.getColor(res.getIdentifier("Violet2", "color",
getActivity().getPackageName()));
img.text = "-";
img.color = color;
}
else
if (id_key==3)
{
itemView = inflater.inflate(R.layout.list_history_moving, parent, false);
tv = (TextView) itemView.findViewById(R.id.tvDate);
c = Calendar.getInstance();
c.setTimeInMillis(cursor.getLong(cursor.getColumnIndex("date")));
tv.setText(ff.format(c.getTime()));
tv = (TextView) itemView.findViewById(R.id.schetName);
tv.setText(cursor.getString(cursor.getColumnIndex("schetName")));
color = res.getColor(res.getIdentifier(mColors[1], "color",
getActivity().getPackageName()));
tv.setTextColor(color);
tv = (TextView) itemView.findViewById(R.id.schetName2);
tv.setText(cursor.getString(cursor.getColumnIndex("schetName2")));
color = res.getColor(res.getIdentifier(mColors[4], "color",
getActivity().getPackageName()));
tv.setTextColor(color);
tv = (TextView) itemView.findViewById(R.id.tvSumma);
tv.setText(my.formatSumma(cursor.getFloat(cursor
.getColumnIndex("summa"))));
tv = (TextView) itemView.findViewById(R.id.tvValuta);
tv.setText(cursor.getString(cursor.getColumnIndex("valName")));
DrawSquare img = (DrawSquare) itemView.findViewById(R.id.img);
color = res.getColor(res.getIdentifier("Blue2", "color",
getActivity().getPackageName()));
img.text = "-";
img.color = color;
}
else
Log.e("asd","ошибка в адаптере - неверное id_key="+id_key);
return itemView;
}
}[/syntax]
в итоге все работает, но если в базе 2 тысячи записей, то приложение висит примерно 1 секунду... это много... ведь 2 тысячи записей будет у каждого пользователя через год использования...
Уверен что вы сможете найти кучу ошибок, и дать советы для оптимизации.
Спасибо!
Помогите оптимизировать один из разделов моего приложения.
Есть таблицы valuta(_id, name) - список валют,schet(_id,name) - список счетов, category(_id,name,...) - список категорий и подкатегорий, rashodi(_id, id_schet, id_cat, id_podCat,id_val,summa,date,...) - список операций расходы и доходы, obmen(_id, id_schet,id_val1,summa1,id_val2,summa2,...) - список операций обмена валют , moving(_id,id_schet1,id_schet2,id_val,summa) - список операций перемещений между счетами , history(_id,id_key,id_oper,date,...) - список история, все операции из предыдущих трех таблиц...
Задача - создать раздел "история" в приложении, надо выводить в список данные в хронологичном порядке за разные периоды...
У меня получился такой вот запрос:
[syntax=mysql]public Cursor getHistory(){
sql = "SELECT H._id as _id,R.summa as summa, H.id_key as id_key,H.date as dateH,R.date as date," +
" V.name as valName,C.name as catName,C2.name as podCatName,S.name as schetName," +
"'' as valName2,'' as summa2,'' as schetName2 FROM history H "
+ " INNER JOIN rashodi R ON R._id=H.id_oper "
+ " INNER JOIN valuta V ON V._id=R.id_val "
+ " INNER JOIN schet S ON S._id=R.id_schet "
+ " LEFT JOIN category C ON C._id=R.id_cat "
+ " LEFT JOIN category C2 ON C2._id=R.id_podCat "
+ " WHERE H.id_key>0 AND H.id_key<3 "
+ " union "+
" SELECT H._id as _id,O.summa1 as summa,H.id_key as id_key, " +
" H.date as dateH, O.date as date," +
" V.name as valName,'' as catName,'' as podCatName," +
" S.name as schetName,V2.name as valName2, O.summa2 as summa2, '' as schetName2 FROM history H "
+ " INNER JOIN obmen O ON O._id=H.id_oper "
+ " INNER JOIN valuta V ON V._id=O.id_val1 "
+ " INNER JOIN valuta V2 ON V2._id=O.id_val2 "
+ " INNER JOIN schet S ON S._id=O.id_schet "
+ " WHERE H.id_key==4"
+ " union "+
" SELECT H._id as _id,M.summa as summa,H.id_key as id_key, " +
" H.date as dateH, M.date as date," +
" V.name as valName,'' as catName,'' as podCatName," +
" S.name as schetName,'' as valName2, '' as summa2, S2.name as schetName2 FROM history H "
+ " INNER JOIN moving M ON M._id=H.id_oper "
+ " INNER JOIN valuta V ON V._id=M.id_val "
+ " INNER JOIN schet S ON S._id=M.id_schet1 "
+ " INNER JOIN schet S2 ON S2._id=M.id_schet2 "
+ " WHERE H.id_key==3"
;
Cursor c=mDB.rawQuery(sql, null);
Log.i("asd","сейчас будет лог");
logCursor(c);
return c;
}[/syntax]
Сначала запрос в таблицу history с id_key=1,2 - это расходы и доходы, к нему привязываются данные из других таблиц. Затем id_key=4 и id_key=3...
Все это передается адаптеру:
[syntax=java5]public class ListHistoryAdapter extends BaseAdapter {
// Declare Variables
Context context;
Cursor cursor;
LayoutInflater inflater;
View itemView;
TextView tv;
Calendar c;
int color;
Resources res = getResources();
String mCat, mOpis, mPodCat;
SimpleDateFormat ff;
public ListHistoryAdapter(Context context, Cursor cursor) {
this.context = context;
this.cursor = cursor;
inflater = (LayoutInflater) context
.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
ff = new SimpleDateFormat("dd.MM.yyyy HH:mm", Locale.getDefault());
}
@Override
public int getCount() {
return cursor.getCount();
}
@Override
public Cursor getItem(int position) {
cursor.moveToPosition(position);
return cursor;
}
@Override
public long getItemId(int position) {
cursor.moveToPosition(position);
return cursor.getLong(cursor.getColumnIndex("_id"));
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
cursor.moveToPosition(position);
int id_key=cursor.getInt(cursor.getColumnIndex("id_key"));
if ((id_key>0)&&(id_key<3))
{
itemView = inflater.inflate(R.layout.list_history, parent, false);
tv = (TextView) itemView.findViewById(R.id.tvDate);
c = Calendar.getInstance();
c.setTimeInMillis(cursor.getLong(cursor.getColumnIndex("date")));
tv.setText(ff.format(c.getTime()));
tv = (TextView) itemView.findViewById(R.id.schetName);
tv.setText(cursor.getString(cursor.getColumnIndex("schetName")));
color = res.getColor(res.getIdentifier(mColors[1], "color",
getActivity().getPackageName()));
tv.setTextColor(color);
tv = (TextView) itemView.findViewById(R.id.tvSubCat);
mCat = cursor.getString(cursor.getColumnIndex("catName"));
mPodCat = cursor.getString(cursor.getColumnIndex("podCatName"));
if (mPodCat == null) {
tv.setText(mCat);
} else {
tv.setText(mCat + " / " + mPodCat);
}
tv = (TextView) itemView.findViewById(R.id.tvSumma);
tv.setText(my.formatSumma(cursor.getFloat(cursor
.getColumnIndex("summa"))));
tv = (TextView) itemView.findViewById(R.id.tvValuta);
tv.setText(cursor.getString(cursor.getColumnIndex("valName")));
DrawSquare img = (DrawSquare) itemView.findViewById(R.id.img);
if (cursor.getInt(cursor.getColumnIndex("id_key")) == 2) {
color = res.getColor(res.getIdentifier("Green2", "color",
getActivity().getPackageName()));
img.text = "+";
} else {
color = res.getColor(res.getIdentifier("Red2", "color",
getActivity().getPackageName()));
img.text = "-";
}
img.color = color;
}
else
if (id_key==4)
{
itemView = inflater.inflate(R.layout.list_history_obmen, parent, false);
tv = (TextView) itemView.findViewById(R.id.tvDate);
c = Calendar.getInstance();
c.setTimeInMillis(cursor.getLong(cursor.getColumnIndex("date")));
tv.setText(ff.format(c.getTime()));
tv = (TextView) itemView.findViewById(R.id.schetName);
tv.setText(cursor.getString(cursor.getColumnIndex("schetName")));
color = res.getColor(res.getIdentifier(mColors[1], "color",
getActivity().getPackageName()));
tv.setTextColor(color);
tv = (TextView) itemView.findViewById(R.id.tvSubCat);
mCat = cursor.getString(cursor.getColumnIndex("catName"));
mPodCat = cursor.getString(cursor.getColumnIndex("podCatName"));
if (mPodCat == null) {
tv.setText(mCat);
} else {
tv.setText(mCat + " / " + mPodCat);
}
tv = (TextView) itemView.findViewById(R.id.tvSumma);
tv.setText(my.formatSumma(cursor.getFloat(cursor
.getColumnIndex("summa"))));
tv = (TextView) itemView.findViewById(R.id.tvSumma2);
tv.setText(my.formatSumma(cursor.getFloat(cursor
.getColumnIndex("summa2"))));
tv = (TextView) itemView.findViewById(R.id.tvValuta);
tv.setText(cursor.getString(cursor.getColumnIndex("valName")));
tv = (TextView) itemView.findViewById(R.id.tvValuta2);
tv.setText(cursor.getString(cursor.getColumnIndex("valName2")));
DrawSquare img = (DrawSquare) itemView.findViewById(R.id.img);
color = res.getColor(res.getIdentifier("Violet2", "color",
getActivity().getPackageName()));
img.text = "-";
img.color = color;
}
else
if (id_key==3)
{
itemView = inflater.inflate(R.layout.list_history_moving, parent, false);
tv = (TextView) itemView.findViewById(R.id.tvDate);
c = Calendar.getInstance();
c.setTimeInMillis(cursor.getLong(cursor.getColumnIndex("date")));
tv.setText(ff.format(c.getTime()));
tv = (TextView) itemView.findViewById(R.id.schetName);
tv.setText(cursor.getString(cursor.getColumnIndex("schetName")));
color = res.getColor(res.getIdentifier(mColors[1], "color",
getActivity().getPackageName()));
tv.setTextColor(color);
tv = (TextView) itemView.findViewById(R.id.schetName2);
tv.setText(cursor.getString(cursor.getColumnIndex("schetName2")));
color = res.getColor(res.getIdentifier(mColors[4], "color",
getActivity().getPackageName()));
tv.setTextColor(color);
tv = (TextView) itemView.findViewById(R.id.tvSumma);
tv.setText(my.formatSumma(cursor.getFloat(cursor
.getColumnIndex("summa"))));
tv = (TextView) itemView.findViewById(R.id.tvValuta);
tv.setText(cursor.getString(cursor.getColumnIndex("valName")));
DrawSquare img = (DrawSquare) itemView.findViewById(R.id.img);
color = res.getColor(res.getIdentifier("Blue2", "color",
getActivity().getPackageName()));
img.text = "-";
img.color = color;
}
else
Log.e("asd","ошибка в адаптере - неверное id_key="+id_key);
return itemView;
}
}[/syntax]
в итоге все работает, но если в базе 2 тысячи записей, то приложение висит примерно 1 секунду... это много... ведь 2 тысячи записей будет у каждого пользователя через год использования...
Уверен что вы сможете найти кучу ошибок, и дать советы для оптимизации.
Спасибо!