Modify
Warning Please read the Ticket HowTo before creating or commenting on a ticket. Failure to do so may cause your ticket to be rejected or result in a slower response.

Opened 3 years ago

Closed 17 months ago

Last modified 16 months ago

#10023 closed Bug Report - General (fixed)

Improve performance of time search

Reported by: dekarl@… Owned by: dekarl
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@… 3 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@… 3 years ago.
bummer, here's the real file. Got to commit before format-patch
speeduptimequery.patch (2.9 KB) - added by justinh 2 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@… 22 months ago.
updated patch to master/0.26 post UTC

Download all attachments as: .zip

Change History (10)

Changed 3 years ago by dekarl@…

example of how it could work in the code

Changed 3 years ago by dekarl@…

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

comment:1 Changed 2 years ago by beirdo

  • Owner set to mdean
  • Status changed from new to assigned

Changed 2 years ago by justinh

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

Changed 22 months ago by dekarl@…

updated patch to master/0.26 post UTC

comment:2 Changed 22 months ago by dekarl@…

Find attached an updated and retested patch against latest master.

comment:3 Changed 17 months ago by Karl Dietz <dekarl@…>

  • Resolution set to fixed
  • Status changed from assigned to 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 17 months ago by dekarl

  • Milestone changed from unknown to 0.27
  • Owner changed from mdean to dekarl

comment:5 Changed 16 months 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 16 months ago by dekarl

  • Milestone changed from 0.27 to 0.26.1

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'new'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.