New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[SQLite] Оптимизация запросов определния наличия в шаре файла и факта скачки #1077

Closed
pavel-pimenov opened this Issue Aug 24, 2015 · 2 comments

Comments

Projects
None yet
1 participant
@pavel-pimenov
Owner

pavel-pimenov commented Aug 24, 2015

From Pavel.Pimenov@gmail.com on May 21, 2013 14:06:59

Сейчас выборка идет последовательностью из 2-х SQL запросов
через вызов интерфейсных функций is_download_tth и is_known_file

  1. В окне поиска
    else if (CFlylinkDBManager::getInstance()->is_download_tth(sr->getTTH()))
    columns[COLUMN_LOCAL_PATH] = TSTRING(I_DOWNLOADED_THIS_FILE);
    else if (CFlylinkDBManager::getInstance()->is_known_file(sr->getTTH()))
    columns[COLUMN_LOCAL_PATH] = TSTRING(THIS_FILE_WAS_IN_MY_SHARE);

  2. В окне просмотра шары

                    if (CFlylinkDBManager::getInstance()->is_download_tth(f->getTTH()))
                        f->setFlag(DirectoryListing::FLAG_DOWNLOAD);
                    else if (CFlylinkDBManager::getInstance()->is_known_file(f->getTTH()))
                        f->setFlag(DirectoryListing::FLAG_OLD_TTH);
    

В результате в базу летит запрос вида

select count(*) from fly_tth where tth=x'27f929fe7ca5fe04e2a9931c29570234fc84bb437073f408'

если ничего не нашли, то идет второй запрос

select id from fly_hash where tth=x'27f929fe7ca5fe04e2a9931c29570234fc84bb437073f408'

TODO - переделать на посылку одного SQL запроса вида

select 1 from fly_tth where tth=x'27f929fe7ca5fe04e2a9931c29570234fc84bb437073f408'
union all
select 2 from fly_hash where tth=x'27f929fe7ca5fe04e2a9931c29570234fc84bb437073f408'

с анализом курсора и возвратом 2-х флажков.
изменение позволит сократить кол-вл транзакций к базе
и более оптимально получать результат в большинстве случаев

Original issue: http://code.google.com/p/flylinkdc/issues/detail?id=1040

@pavel-pimenov

This comment has been minimized.

Owner

pavel-pimenov commented Aug 24, 2015

From Pavel.Pimenov@gmail.com on May 21, 2013 18:39:00

This issue was closed by revision r14015 .

Status: Fixed

@pavel-pimenov

This comment has been minimized.

Owner

pavel-pimenov commented Aug 24, 2015

From a.rain...@gmail.com on May 21, 2013 20:18:52

Дополнил, и проверил r14024 .

Status: Verified
Cc: a.rain...@gmail.com

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment