Оптимизация БД. Сложный запрос. Внимание! Много текста!

SQLite, Preferences, файлы, SD, Content Provider, XML, JSON
Ответить
парень
Сообщения: 223
Зарегистрирован: 30 мар 2013, 22:52

Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение парень » 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 тысячи записей будет у каждого пользователя через год использования...

Уверен что вы сможете найти кучу ошибок, и дать советы для оптимизации.

Спасибо!
Мой первенец: MyMoney. Менеджер расходов

Бьем рекорды русских топов :)

Могу ответить на любые вопросы по маркетингу и развитию.

Аватара пользователя
petrovichtim
Сообщения: 77
Зарегистрирован: 11 фев 2014, 22:53
Откуда: Москва
Контактная информация:

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение petrovichtim » 14 май 2014, 10:34

На вскидку сделай индексы на поля по которым соединяешь таблицы

Аватара пользователя
Foenix
Сообщения: 4201
Зарегистрирован: 20 окт 2012, 12:01

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение Foenix » 14 май 2014, 11:46

скорее всего тормозят твои union, и что-то много слишком inner join (почему их все так любят не пойму, сколько работаю ни разу не использовала :-D)
но такой запрос тормозить не должен, я выбираю запросы покруче и у меня это моментально отображается на 90 тыс записях.
если хочешь - пришли мне тестовую базу и запрос не в таком виде, а в обычном - я посмотрю через менеджер что у тебя там. Тут глаза сломать можно же. Надо было в лог выдать запрос и скопировать (хотя бы так). Таблицы тоже в строчку написал - глаза сломаешь что к чему.
R.id.team

NullPointerException - что делать???
viewtopic.php?f=33&t=3899&p=28952#p28952
Где моя ошибка?
viewtopic.php?f=60&t=3198

Аватара пользователя
Foenix
Сообщения: 4201
Зарегистрирован: 20 окт 2012, 12:01

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение Foenix » 14 май 2014, 11:47

и кстати, что значит "приложение висит", это как это оно у тебя умудрятеся висеть? ты ж асинхронно запросы делаешь?? или нет??
R.id.team

NullPointerException - что делать???
viewtopic.php?f=33&t=3899&p=28952#p28952
Где моя ошибка?
viewtopic.php?f=60&t=3198

Аватара пользователя
Foenix
Сообщения: 4201
Зарегистрирован: 20 окт 2012, 12:01

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение Foenix » 14 май 2014, 11:49

и переделай свой адаптер срочно под holder-pattern или как его там. Тебе его тут просто доктор прописал. Тормозить может еще и от этого.
R.id.team

NullPointerException - что делать???
viewtopic.php?f=33&t=3899&p=28952#p28952
Где моя ошибка?
viewtopic.php?f=60&t=3198

Аватара пользователя
adarash
Сообщения: 333
Зарегистрирован: 17 июл 2013, 09:59

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение adarash » 14 май 2014, 12:03

ViewHolder его еще вроде называют

Аватара пользователя
Foenix
Сообщения: 4201
Зарегистрирован: 20 окт 2012, 12:01

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение Foenix » 14 май 2014, 13:34

дада, точно
R.id.team

NullPointerException - что делать???
viewtopic.php?f=33&t=3899&p=28952#p28952
Где моя ошибка?
viewtopic.php?f=60&t=3198

парень
Сообщения: 223
Зарегистрирован: 30 мар 2013, 22:52

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение парень » 14 май 2014, 17:27

Foenix писал(а):и кстати, что значит "приложение висит", это как это оно у тебя умудрятеся висеть? ты ж асинхронно запросы делаешь?? или нет??
:P :oops: нет ))) не умею...
Мой первенец: MyMoney. Менеджер расходов

Бьем рекорды русских топов :)

Могу ответить на любые вопросы по маркетингу и развитию.

парень
Сообщения: 223
Зарегистрирован: 30 мар 2013, 22:52

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение парень » 14 май 2014, 21:55

спасибо за наводки. после нескольких тестов выяснилось, что задержка происходит в методе getView... Выходит ViewHolder... не могу найти примера, где в ViewHolder помещаются несколько разных шаблонов одного списка... Можете ткнуть примером?

И еще... читаю урок 136, там адаптер SimpleCursorAdapter... мне он не подходит... в BaseAdapter swapcursor'a нету... эта тема мне пока сложно дается...

Скажите могу ли я использовать что-то другое? например cursorAdapter??? главная задача, что в зависимости от id_key (один из столбцов полученного курсора) должен быть разный шаблон(макет,итем, слой)...

Спасибо
Мой первенец: MyMoney. Менеджер расходов

Бьем рекорды русских топов :)

Могу ответить на любые вопросы по маркетингу и развитию.

Аватара пользователя
Foenix
Сообщения: 4201
Зарегистрирован: 20 окт 2012, 12:01

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение Foenix » 14 май 2014, 22:14

кошмар, я тебя не узнаю
R.id.team

NullPointerException - что делать???
viewtopic.php?f=33&t=3899&p=28952#p28952
Где моя ошибка?
viewtopic.php?f=60&t=3198

парень
Сообщения: 223
Зарегистрирован: 30 мар 2013, 22:52

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение парень » 15 май 2014, 00:03

Возможно ответ настолько прост, что нет желания отвечать. Как бы то ни было, стараюсь задавать вопросы редко и тогда когда действительно что-то не понимаю.
Мой первенец: MyMoney. Менеджер расходов

Бьем рекорды русских топов :)

Могу ответить на любые вопросы по маркетингу и развитию.

Аватара пользователя
Foenix
Сообщения: 4201
Зарегистрирован: 20 окт 2012, 12:01

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение Foenix » 15 май 2014, 09:50

да дело не в этом, как это про holder ты не можешь найти пример? в гугле https://www.google.ru/search?client=ope ... el=suggest
первая ссылка Android ViewHolder Pattern Example

про курсоры я вообще ничего не поняла - зачем тебе менять адаптер

третье - пересмотри свой getview - убери оттуда все "постоянные" инициализации не зависящие от конкретной строки (точно так же как и holder)
типа
color = res.getColor(res.getIdentifier(mColors[1], "color", getActivity().getPackageName()));

запрос так и не прислал.
посмотри вебинар последний по отлову ошибок - там было про оптимизацию, строки и так далее много интересного.
R.id.team

NullPointerException - что делать???
viewtopic.php?f=33&t=3899&p=28952#p28952
Где моя ошибка?
viewtopic.php?f=60&t=3198

парень
Сообщения: 223
Зарегистрирован: 30 мар 2013, 22:52

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение парень » 15 май 2014, 18:31

Foenix писал(а):да дело не в этом, как это про holder ты не можешь найти пример? в гугле https://www.google.ru/search?client=ope ... el=suggest
первая ссылка Android ViewHolder Pattern Example

про курсоры я вообще ничего не поняла - зачем тебе менять адаптер

третье - пересмотри свой getview - убери оттуда все "постоянные" инициализации не зависящие от конкретной строки (точно так же как и holder)
типа
color = res.getColor(res.getIdentifier(mColors[1], "color", getActivity().getPackageName()));

запрос так и не прислал.
посмотри вебинар последний по отлову ошибок - там было про оптимизацию, строки и так далее много интересного.
Холдер то нашел, не могу найти пример под разные шаблоны. Я так понял - надо под каждый уникальный макет свой холдер делать. У меня их три. Так вот вчера возился всю ночь, пока не смог ни найти примера, ни методом тыка... Все вокруг да около кручусь... Пример бы существенно облегчил задачу.

Про курсор... в уроке 136, по завершению загрузки курсора из БД, мы с помощью метода swapcursor передаем его в адаптер... Так вот если у меня BaseAdapter, то эклипс ругается, типа неизвестен такой метод в моем адаптере. Если адаптер сделать наследуемый от SimpleCursorAdapter или CursorLoader то ругаться перестает, но надо тогда сам адаптер менять...

третье - спасибо, вместо mColors[1] например, будет cursor.getString... Иными словами они не будут постоянными.

Запрос - не актуально, т.к. нашел где тратится время...
Это адаптер, и почему то мой адаптер метод getView обрабатывал все видиые итемы 3 раза ! Поместил запрос и адаптер и присвоение его в другой класс и на другой layout, там один раз. Затем разобрался и теперь обрабатывается как надо, 1 раз.

Вебинар посмотрю. Спасибо.
Мой первенец: MyMoney. Менеджер расходов

Бьем рекорды русских топов :)

Могу ответить на любые вопросы по маркетингу и развитию.

парень
Сообщения: 223
Зарегистрирован: 30 мар 2013, 22:52

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение парень » 15 май 2014, 19:12

Правильно говорят: "Утро вечера, мудренее"...

Разобрался с холдером.

[syntax=java5]if ((itemView==null)||(itemView.getTag()==null)||(!itemView.getTag().getClass().equals(ViewHolder2.class)) )
[/syntax]

// если itemView==null - делаем новый холдер,
если itemView.getTag()==null (холдера нет) - делаем новый холдер
если (!itemView.getTag().getClass().equals(ViewHolder2.class)) (холдер отличный от нужного для этого шаблона) - делаем новый холдер.

Вроде все работает!

Осталось понять с загрузкой данных в фоне. Вопрос про BaseAdapter, swapCursor и урок 136...
Мой первенец: MyMoney. Менеджер расходов

Бьем рекорды русских топов :)

Могу ответить на любые вопросы по маркетингу и развитию.

парень
Сообщения: 223
Зарегистрирован: 30 мар 2013, 22:52

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение парень » 03 авг 2014, 09:13

Посмеялся над своими вопросами )))
Мой первенец: MyMoney. Менеджер расходов

Бьем рекорды русских топов :)

Могу ответить на любые вопросы по маркетингу и развитию.

парень
Сообщения: 223
Зарегистрирован: 30 мар 2013, 22:52

Re: Оптимизация БД. Сложный запрос. Внимание! Много текста!

Сообщение парень » 29 сен 2015, 21:12

блин. Снова просмеялся )
Мой первенец: MyMoney. Менеджер расходов

Бьем рекорды русских топов :)

Могу ответить на любые вопросы по маркетингу и развитию.

Ответить