Opened 9 years ago

Closed 8 years ago

Last modified 8 years ago

#10023 closed Bug Report - General (fixed)

Improve performance of time search

Reported by: dekarl@… Owned by: Karl Egly
Priority: minor Milestone: 0.26.1
Component: MythTV - General Version: Master Head
Severity: medium Keywords:
Cc: Ticket locked: no

Description

The SQL query for time search takes 2 minutes over here. By slightly refactoring the query that can be brought down to 2 seconds. Just factor out the description column into a superquery with the actual query being a subquery.

The new query looks like: 

select program.description, sub.* from program, (
SELECT DISTINCT program.chanid,
... here is the old query but without the description column ...
) as sub where program.chanid=sub.chanid and program.starttime=sub.starttime;

The reason for the difference is each description taking 48kb of memory no matter how much of that is actually used leading to the whole joining and sorting being done on the hard disk because it doesn't fit into the memory buffer. (at least that's what I suspect)

Attachments (4)

0001-Ignore-channels-without-xmltvid-when-guessing-if-the.patch​ (2.7 KB) - added by dekarl@… 9 years ago.
example of how it could work in the code
0002-Speed-up-time-search-by-factoring-out-the-descriptio.patch​ (1.5 KB) - added by dekarl@… 9 years ago.
bummer, here's the real file. Got to commit before format-patch
speeduptimequery.patch​ (2.9 KB) - added by Justin Hornsby 9 years ago.
The new query was fine but results were being incorrectly assigned - this patch should fix that
0007-Speed-up-and-reduce-temporary-space-of-time-search-a.patch​ (3.3 KB) - added by dekarl@… 8 years ago.
updated patch to master/0.26 post UTC

Download all attachments as: .zip

Change History (10)

Changed 9 years ago by dekarl@…

Attachment: 0001-Ignore-channels-without-xmltvid-when-guessing-if-the.patch​ added

example of how it could work in the code

Changed 9 years ago by dekarl@…

Attachment: 0002-Speed-up-time-search-by-factoring-out-the-descriptio.patch​ added

bummer, here's the real file. Got to commit before format-patch

comment:1 Changed 9 years ago by beirdo

Owner: set to sphery
Status: new → assigned

Changed 9 years ago by Justin Hornsby

Attachment: speeduptimequery.patch​ added

The new query was fine but results were being incorrectly assigned - this patch should fix that

Changed 8 years ago by dekarl@…

Attachment: 0007-Speed-up-and-reduce-temporary-space-of-time-search-a.patch​ added

updated patch to master/0.26 post UTC

comment:2 Changed 8 years ago by dekarl@…

Find attached an updated and retested patch against latest master.

comment:3 Changed 8 years ago by Karl Dietz <dekarl@…>

Resolution: → fixed
Status: assigned → closed

In c0c47344879d3467cfea62938a33e70278252b36/mythtv:

Speed up and reduce temporary space of time search a lot.

By factoring out the description into a super query the performance can
be greatly improved (e.g. from 2 minutes to 2 seconds)

Thanks to sphery for inspiration by [d9fb21853f] and justinh for testing
and fixing my braino with the column order.

Fixes #10023

comment:4 Changed 8 years ago by Karl Egly

Milestone: unknown → 0.27
Owner: changed from sphery to Karl Egly

comment:5 Changed 8 years ago by Karl Dietz <dekarl@…>

In 3d7a20ce1008b1a84127f17dea7c75e05cacb4aa/mythtv:

Speed up and reduce temporary space of time search a lot.

By factoring out the description into a super query the performance can
be greatly improved (e.g. from 2 minutes to 2 seconds)

Thanks to sphery for inspiration by [d9fb21853f] and justinh for testing
and fixing my braino with the column order.

Fixes #10023
(cherry picked from commit c0c47344879d3467cfea62938a33e70278252b36)

Conflicts:

mythtv/libs/libmyth/programinfo.cpp

comment:6 Changed 8 years ago by Karl Egly

Milestone: 0.27 → 0.26.1
Note: See TracTickets for help on using tickets.