SQLiteDatabase Plugin for MP2 (2 Viewers)

morpheus_xx · September 11, 2013

The current issue is, that it does not support automatic creation of database via EF, if it is not existent. I worked around this problem by generating the database myself for the first time. After this, the TVE35 plugin is able to work.

chefkoch · September 11, 2013

MJGraf said:
that's interesting - thanks! But maybe I didn't understand the rebase part, yet, sorry...
I creat the SQLite branch based on dev and push the SQLite code as it is now (SQLite disabled, SQLCE enabled).
Then I create a second branch SQLite_ENABLED based on the SQLite branch and just push one commit to the SQLite_ENABLED branch where I enable SQLite and disable SQLCE.

Now what I'm using for development for future fixes is apparently a local copy of SQLite_ENABLED. But when I get your suggestion right, the fix shouldn't be pushed to the remote SQLite_Enabled, but to the remote SQLite?!?

If so, I understand that I only have to rebase the one commit in SQLite_ENABLED to the latest commit of SQLite to keep it up to date. But to my understanding this doesn't help if I want to keep SQLite (and SQLite_ENABLED) up to date with DEV. I would have to rebase SQLite to the latest DEV from time to time to keep it up to date. But then I have probably broken SQLite_ENABLED because SQLite's history was rewritten?!? And I can't rebase SQLite_ENABLED to DEV because the code of the SQLitePlugin is neither contained in DEV nor in SQLite_ENABLED (the original source push was only to SQLite).

Running two branches in parallel for long time while both are being rebased on top of dev just makes it very complex and won't help.
The idea was to merge dev into SQLiteDatabase branch when needed instead of a rebase.

My concern is that the rebases in general will lead to troubles as soon as more and more devs are working MP2, but I am not sure if we are going to use merges. There are pros and cons for both.

So don't waste your time by trying to figure out my cryptic suggestions, as there your are doing things that are way more productive

MJGraf · September 18, 2013

Thanks for your suggestions, guys. I have in the meantime cleaned up the code made it "MP2-standard-compliant" (hopefully) and at the same time updated to the latest version of the system.data.sqlite library. This will then be v0.07, which I'll publish as soon as Alpha3 is out. As soon as we have the new dev branch including all the changes of Alpha3, I'll base my code on dev and push it to git. My "import-test" as described above went down from about 34 minutes to about 31 minutes - probably caused by some improvements in the system.data.sqlite code (website says something about the removal of unnecessary conversion of data types, which may affect the speed).

Besides that, it made me nervous that Lehmden mentioned he sees a material impact on performance of the whole MP2 system as soon as his database grows to 1 or 2GB and that it gets really bad when we are beyond 3 or 4GB. This must not be the case. In particular something so deep down in the system as the database must not be a bottleneck and we should easily be able to handle databases with 20 or even 50GB of data without suffering from (material) performance decrease.

The problem for me is that in the past development of this plugin I concentrated only on the import speed, i.e. the speed of adding media items to the database. But most of the time, we are probably not importing data, we are reading data from the database to display it in the MP2 Client. But I never tested and optimized the speed in that respect. The reason is probably because this is A LOT more cumbersome and takes A LOT more time. But I think now the time has come

I will use this thread as kind of a test protocol to make sure that we have all the data in one place, but if someone has any comments, please feel free to post them here.

First we have to define a set of performance tests to make sure that we can compare the results when we change the code:

Hardware
Let's start with the hardware I'm using so that others can conclude whether their systems should be faster or slower. I have an Intel i5 with 8GB ram, Win7 (x64). System drive is a Crucial C300 SSD, data drive is a WD 2TB disk drive. The MP2 Server binary files are as well as the media files located on the data drive, while the MP2 Server data directory (including the database file) is located on the SSD. The MP2 Client computer should be irrelevant for the pure database performance, but for the sake of completeness: It is installed on my Laptop (Vaio TT), which is connected via WLan.

Import Test
First let my specify the import test, which I have used previously in this thread, in a bit more detail. For the tests I intentionally only import music files. No pictures, videos, movies or series. The reason is simple, I don't have many of the latter ones, whereas I can use a music collection having a reasonable size and all music files are perfectly tagged. The test collection consists of 17.794 MP3 files and 2.332 flac files (in total 20.126), all converted "by hand" from my CD collection, carefully tagged with MusicBrainz Picard (including genres) and in particular, each file is tagged with a cover (resolution usually between 500x500 and 1000x1000). I know that this is storing redundant data, since I store every cover multiple times (once in each music file) but I want the files to be self-contained.

The test itself is easy: I delete the data directory of client and server (including the database), start the server and the client, add a global share with the local file system resource provider pointing to the root directory of the music test collection and terminate the client. From the server log file I can see when the import has started and when it was finished. As mentioned above, this currently (with the yet to be published v0.07) takes about 31 minutes. The size of the database file after the import is 1.773.888 KB in the windows explorer.
I ran the test multiple times exactly like that and the results were somewhere between 30 minutes and 33 minutes - so there is a deviation of about +/- 5% caused by things like SSD garbage collection, etc.

This is where we stand with this test. As you can read in this thread, I wasn't able to speed this up anymore, no matter what additional settings I chose for the SQLite database. But we have to keep this test as (1) we have to make sure that the import doesn't slow down while we are optimizing the database reads and (2) I will use exactly the database resulting from this import for the read tests below.

Read Tests
As mentioned above, what we need now are some tests regarding reading from the database. I haven't defined them all, yet, because I'm still stuck with doing the first test runs on the first test - which are already very interesting, but more about that later....
The idea was to have four read tests - two synthetic tests and two real live tests:

The first (synthetic) test should be a complex search query resulting in a lot of media items ("Read Test 1")
The second (synthetic) test should be a simple query to return exactly one media item based on a search for the media item ID ("Read Test 2")
The third test will be entering the audio section of MP2 with the music collection as described above ("Read Test 3")
The fourth test will be clicking on the first album displayed, which contains 11 music files in my test collection ("Read Test 4")

What I will measure with these test is exclusively the time the respective SQL queries consume. I'll take these figures from the SQLDebug.log. I will NOT measure the time until anything is displayed, because we have to concentrate on one thing to improve and this is only the database itself here. If we realize that the database is not the bottleneck, we can do other tests for other parts of MP2.
If you have comments on the tests I chose, please let me know. So far I have only done some first tests runs for Read Test 1 - so nearly anything can still be changed. I will describe the Read Tests in detail later below together with the respective first test results.

Startup Test
Since Lehmden mentioned that a big database also slows down the startup of the MP2 client, I will later also have a look at the queries executed at startup and treat this as a further "Startup Test".

Connection Test
As pointed out by Lehmde below, a big database slows down the time until the MP2 Client connects to the MP2 Server. I will have a look at the queries executed when a client connects as further "Connection Test".

So far so good - have to get some food now and will continue later to report my first findings...

Michael

MJGraf · September 18, 2013

Read Test 1

What I'm doing technically is the following:

Code:

var necessaryMias = new List<Guid> { MediaAspect.ASPECT_ID };
var optionalMias = new List<Guid>(ServiceRegistration.Get<IMediaItemAspectTypeRegistration>().LocallyKnownMediaItemAspectTypes.Keys);
MediaItemQuery miq = ServiceRegistration.Get<IMediaLibrary>().BuildSimpleTextSearchQuery(searchString, necessaryMias, optionalMias, null, false, true);
var result = ServiceRegistration.Get<IMediaLibrary>().Search(miq, false);

searchString is in this case "simon".

A bit less technically explained: I'm searching for all media items, which have "*simon*" in any text field of any media item aspect and I want to have these media items returned with any media item aspect they have (i.e. in particular including the Thumbnail Aspect with the covers). This query returns exactly 235 media items in my test collection.

What happens with this search on the database level. The SQLDebug.Log shows that this search executes in total 12 SQL queries. For documentation purposes, I include the 12 SQL queries below. They are relatively complex, include a lot of left outer joins and where "like" clauses. But every SQL query has one "from" table and I am going to refer to the different SQL queries based on the name of this "from" table. So here are the SQL queries:

SQL1 (Genres)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_GENRES T0
INNER JOIN V_GENRES T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL2 (AudioLanguages)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_AUDIOLANGUAGES T0
INNER JOIN V_AUDIOLANGUAGES T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL3 (Actors)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_ACTORS T0
INNER JOIN V_ACTORS T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL4 (Directors)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_DIRECTORS T0
INNER JOIN V_DIRECTORS T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL5 (Writers)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_WRITERS T0
INNER JOIN V_WRITERS T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL6 (Artists)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_ARTISTS T0
INNER JOIN V_ARTISTS T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

MJGraf · September 18, 2013

SQL7 (Genres)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_GENRES_0 T0
INNER JOIN V_GENRES_0 T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL8 (Albumartist)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_ALBUMARTISTS T0
INNER JOIN V_ALBUMARTISTS T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL9 (Composers)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_COMPOSERS T0
INNER JOIN V_COMPOSERS T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL10 (Episode)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_EPISODE T0
INNER JOIN V_EPISODE T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL11 (DVDEpisode)

SELECT
T0.MEDIA_ITEM_ID A0,
T9.ATTRIBUTE_VALUE A1
FROM NM_DVDEPISODE T0
INNER JOIN V_DVDEPISODE T9 ON T0.ID = T9.ID
INNER JOIN M_MEDIAITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T18 ON T18.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T1 ON T18.ID = T1.ID
LEFT OUTER JOIN NM_ACTORS T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T2 ON T19.ID = T2.ID
LEFT OUTER JOIN NM_DIRECTORS T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T3 ON T20.ID = T3.ID
LEFT OUTER JOIN NM_WRITERS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T4 ON T21.ID = T4.ID
LEFT OUTER JOIN NM_ARTISTS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T5 ON T22.ID = T5.ID
LEFT OUTER JOIN NM_GENRES_0 T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T6 ON T23.ID = T6.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T7 ON T24.ID = T7.ID
LEFT OUTER JOIN NM_COMPOSERS T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T8 ON T25.ID = T8.ID
LEFT OUTER JOIN M_PROVIDERRESOURCE T11 ON T0.MEDIA_ITEM_ID = T11.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T12 ON T0.MEDIA_ITEM_ID = T12.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T13 ON T0.MEDIA_ITEM_ID = T13.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T14 ON T0.MEDIA_ITEM_ID = T14.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T15 ON T0.MEDIA_ITEM_ID = T15.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T16 ON T0.MEDIA_ITEM_ID = T16.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T17 ON T0.MEDIA_ITEM_ID = T17.MEDIA_ITEM_ID
WHERE
(
T10.TITLE LIKE @V0 ESCAPE @E1
OR T10.MIMETYPE LIKE @V2 ESCAPE @E3
OR T10.COMMENT LIKE @V4 ESCAPE @E5
OR T11.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T11.PATH LIKE @V8 ESCAPE @E9
OR T1.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T12.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T12.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T2.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T3.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T4.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T12.STORYPLOT LIKE @V22 ESCAPE @E23
OR T5.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T13.ALBUM LIKE @V26 ESCAPE @E27
OR T6.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T7.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T8.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T13.ENCODING LIKE @v34 ESCAPE @E35
OR T14.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T14.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T14.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T14.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T14.FLASHMODE LIKE @V44 ESCAPE @E45
OR T14.FNUMBER LIKE @V46 ESCAPE @E47
OR T14.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T14.METERINGMODE LIKE @V50 ESCAPE @E51
OR T14.CITY LIKE @V52 ESCAPE @E53
OR T14.STATE LIKE @V54 ESCAPE @E55
OR T14.COUNTRY LIKE @V56 ESCAPE @E57
OR T15.SERIESNAME LIKE @V58 ESCAPE @E59
OR T15.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T15.EPISODENAME LIKE @V62 ESCAPE @E63
OR T16.MOVIENAME LIKE @V64 ESCAPE @E65
OR T16.ORIGNAME LIKE @V66 ESCAPE @E67
OR T16.IMDBID LIKE @V68 ESCAPE @E69
OR T16.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T16.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T16.TAGLINE LIKE @V74 ESCAPE @E75
OR T17.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

SQL12 (Mediaitem)

SELECT
T0.MEDIA_ITEM_ID A72,
T0.MEDIA_ITEM_ID A73,
T1.MEDIA_ITEM_ID A74,
T2.MEDIA_ITEM_ID A75,
T19.MEDIA_ITEM_ID A76,
T3.MEDIA_ITEM_ID A77,
T4.MEDIA_ITEM_ID A78,
T5.MEDIA_ITEM_ID A79,
T6.MEDIA_ITEM_ID A80,
T7.MEDIA_ITEM_ID A81,
T8.MEDIA_ITEM_ID A82,
T9.MEDIA_ITEM_ID A83,
T10.MEDIA_ITEM_ID A84,
T0.TITLE A0,
T0.MIMETYPE A1,
T0.RECORDINGTIME A2,
T0.RATING A3,
T0.COMMENT A4,
T0.PLAYCOUNT A5,
T0.LASTPLAYED A6,
T1.SYSTEM_ID A7,
T1.PATH A8,
T1.PARENTDIRECTORY A9,
T2.LASTIMPORTDATE A10,
T2.DIRTY A11,
T2.DATEADDED A12,
T3.DURATION A13,
T3.AUDIOSTREAMCOUNT A14,
T3.AUDIOENCODING A15,
T3.AUDIOBITRATE A16,
T3.VIDEOENCODING A17,
T3.VIDEOBITRATE A18,
T3.WIDTH A19,
T3.HEIGHT A20,
T3.ASPECTRATIO A21,
T3.FPS A22,
T3.ISDVD A23,
T3.STORYPLOT A24,
T4.ALBUM A25,
T4.DURATION_0 A26,
T4.TRACK A27,
T4.NUMTRACKS A28,
T4.ENCODING A29,
T4.BITRATE A30,
T4.DISCID A31,
T4.NUMDISCS A32,
T5.WIDTH_0 A33,
T5.HEIGHT_0 A34,
T5.EQUIPMENTMAKE A35,
T5.EQUIPMENTMODEL A36,
T5.EXPOSUREBIAS A37,
T5.EXPOSURETIME A38,
T5.FLASHMODE A39,
T5.FNUMBER A40,
T5.ISOSPEEDRATING A41,
T5.ORIENTATION A42,
T5.METERINGMODE A43,
T5.LATITUDE A44,
T5.LONGITUDE A45,
T5.CITY A46,
T5.STATE A47,
T5.COUNTRY A48,
T6.SERIESNAME A49,
T6.SEASON A50,
T6.SERIESSEASONNAME A51,
T6.EPISODENAME A52,
T6.FIRSTAIRED A53,
T7.MOVIENAME A54,
T7.ORIGNAME A55,
T7.IMDBID A56,
T7.TMDBID A57,
T7.COLLECTIONNAME A58,
T7.COLLECTIONID A59,
T7.RUNTIME A60,
T7.CERTIFICATION A61,
T7.TAGLINE A62,
T7.POPULARITY A63,
T7.BUDGET A64,
T7.REVENUE A65,
T7.SCORE A66,
T8.THUMBNAIL A67,
T9.THUMBNAIL_0 A68,
T10.CHANNEL A69,
T10.STARTTIME A70,
T10.ENDTIME A71
FROM M_MEDIAITEM T0
LEFT OUTER JOIN M_PROVIDERRESOURCE T1 ON T1.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMPORTEDITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_DIRECTORY T19 ON T19.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_VIDEOITEM T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_AUDIOITEM T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_IMAGEITEM T5 ON T5.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_SERIESITEM T6 ON T6.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_MOVIEITEM T7 ON T7.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_THUMBNAILSMALL T8 ON T8.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_THUMBNAILLARGE T9 ON T9.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN M_RECORDINGITEM T10 ON T10.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN NM_GENRES T20 ON T20.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES T11 ON T20.ID = T11.ID
LEFT OUTER JOIN NM_ACTORS T21 ON T21.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ACTORS T12 ON T21.ID = T12.ID
LEFT OUTER JOIN NM_DIRECTORS T22 ON T22.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_DIRECTORS T13 ON T22.ID = T13.ID
LEFT OUTER JOIN NM_WRITERS T23 ON T23.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_WRITERS T14 ON T23.ID = T14.ID
LEFT OUTER JOIN NM_ARTISTS T24 ON T24.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ARTISTS T15 ON T24.ID = T15.ID
LEFT OUTER JOIN NM_GENRES_0 T25 ON T25.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_GENRES_0 T16 ON T25.ID = T16.ID
LEFT OUTER JOIN NM_ALBUMARTISTS T26 ON T26.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_ALBUMARTISTS T17 ON T26.ID = T17.ID
LEFT OUTER JOIN NM_COMPOSERS T27 ON T27.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID
LEFT OUTER JOIN V_COMPOSERS T18 ON T27.ID = T18.ID
WHERE
(
T0.TITLE LIKE @V0 ESCAPE @E1
OR T0.MIMETYPE LIKE @V2 ESCAPE @E3
OR T0.COMMENT LIKE @V4 ESCAPE @E5
OR T1.SYSTEM_ID LIKE @V6 ESCAPE @E7
OR T1.PATH LIKE @V8 ESCAPE @E9
OR T11.ATTRIBUTE_VALUE LIKE @V10 ESCAPE @E11
OR T3.AUDIOENCODING LIKE @V12 ESCAPE @E13
OR T3.VIDEOENCODING LIKE @V14 ESCAPE @E15
OR T12.ATTRIBUTE_VALUE LIKE @V16 ESCAPE @E17
OR T13.ATTRIBUTE_VALUE LIKE @V18 ESCAPE @E19
OR T14.ATTRIBUTE_VALUE LIKE @V20 ESCAPE @E21
OR T3.STORYPLOT LIKE @V22 ESCAPE @E23
OR T15.ATTRIBUTE_VALUE LIKE @V24 ESCAPE @E25
OR T4.ALBUM LIKE @V26 ESCAPE @E27
OR T16.ATTRIBUTE_VALUE LIKE @V28 ESCAPE @E29
OR T17.ATTRIBUTE_VALUE LIKE @V30 ESCAPE @E31
OR T18.ATTRIBUTE_VALUE LIKE @V32 ESCAPE @E33
OR T4.ENCODING LIKE @v34 ESCAPE @E35
OR T5.EQUIPMENTMAKE LIKE @V36 ESCAPE @E37
OR T5.EQUIPMENTMODEL LIKE @V38 ESCAPE @E39
OR T5.EXPOSUREBIAS LIKE @V40 ESCAPE @E41
OR T5.EXPOSURETIME LIKE @V42 ESCAPE @E43
OR T5.FLASHMODE LIKE @V44 ESCAPE @E45
OR T5.FNUMBER LIKE @V46 ESCAPE @E47
OR T5.ISOSPEEDRATING LIKE @V48 ESCAPE @E49
OR T5.METERINGMODE LIKE @V50 ESCAPE @E51
OR T5.CITY LIKE @V52 ESCAPE @E53
OR T5.STATE LIKE @V54 ESCAPE @E55
OR T5.COUNTRY LIKE @V56 ESCAPE @E57
OR T6.SERIESNAME LIKE @V58 ESCAPE @E59
OR T6.SERIESSEASONNAME LIKE @V60 ESCAPE @E61
OR T6.EPISODENAME LIKE @V62 ESCAPE @E63
OR T7.MOVIENAME LIKE @V64 ESCAPE @E65
OR T7.ORIGNAME LIKE @V66 ESCAPE @E67
OR T7.IMDBID LIKE @V68 ESCAPE @E69
OR T7.COLLECTIONNAME LIKE @V70 ESCAPE @E71
OR T7.CERTIFICATION LIKE @V72 ESCAPE @E73
OR T7.TAGLINE LIKE @V74 ESCAPE @E75
OR T10.CHANNEL LIKE @V76 ESCAPE @E77
)
-------------------------------------------------------
"V0" [String]: '%simon%'
"E1" [String]: '\'
"V2" [String]: '%simon%'
"E3" [String]: '\'
"V4" [String]: '%simon%'
"E5" [String]: '\'
"V6" [String]: '%simon%'
"E7" [String]: '\'
"V8" [String]: '%simon%'
"E9" [String]: '\'
"V10" [String]: '%simon%'
"E11" [String]: '\'
"V12" [String]: '%simon%'
"E13" [String]: '\'
"V14" [String]: '%simon%'
"E15" [String]: '\'
"V16" [String]: '%simon%'
"E17" [String]: '\'
"V18" [String]: '%simon%'
"E19" [String]: '\'
"V20" [String]: '%simon%'
"E21" [String]: '\'
"V22" [String]: '%simon%'
"E23" [String]: '\'
"V24" [String]: '%simon%'
"E25" [String]: '\'
"V26" [String]: '%simon%'
"E27" [String]: '\'
"V28" [String]: '%simon%'
"E29" [String]: '\'
"V30" [String]: '%simon%'
"E31" [String]: '\'
"V32" [String]: '%simon%'
"E33" [String]: '\'
"V34" [String]: '%simon%'
"E35" [String]: '\'
"V36" [String]: '%simon%'
"E37" [String]: '\'
"V38" [String]: '%simon%'
"E39" [String]: '\'
"V40" [String]: '%simon%'
"E41" [String]: '\'
"V42" [String]: '%simon%'
"E43" [String]: '\'
"V44" [String]: '%simon%'
"E45" [String]: '\'
"V46" [String]: '%simon%'
"E47" [String]: '\'
"V48" [String]: '%simon%'
"E49" [String]: '\'
"V50" [String]: '%simon%'
"E51" [String]: '\'
"V52" [String]: '%simon%'
"E53" [String]: '\'
"V54" [String]: '%simon%'
"E55" [String]: '\'
"V56" [String]: '%simon%'
"E57" [String]: '\'
"V58" [String]: '%simon%'
"E59" [String]: '\'
"V60" [String]: '%simon%'
"E61" [String]: '\'
"V62" [String]: '%simon%'
"E63" [String]: '\'
"V64" [String]: '%simon%'
"E65" [String]: '\'
"V66" [String]: '%simon%'
"E67" [String]: '\'
"V68" [String]: '%simon%'
"E69" [String]: '\'
"V70" [String]: '%simon%'
"E71" [String]: '\'
"V72" [String]: '%simon%'
"E73" [String]: '\'
"V74" [String]: '%simon%'
"E75" [String]: '\'
"V76" [String]: '%simon%'
"E77" [String]: '\'

MJGraf · September 18, 2013

Now the interesting part - the first results from Read Test 1.
What I did is as I said I did a completely fresh import of my test music collection restarted the MP2 Server and executed the search as described above:

Import / Restart MP2 Server / Read Test 1:
SQL1 (Genres): 1ms
SQL2 (AudioLanguages): 1ms
SQL3 (Actors): 2ms
SQL4 (Directors): 1ms
SQL5 (Writers): 1ms
SQL6 (Artists): 308ms
SQL7 (Genres): 25ms
SQL8 (Albumartist): 61ms
SQL9 (Composers): 116ms
SQL10 (Episode): 1ms
SQL11 (DVDEpisode): 1ms
SQL12 (Mediaitem): 94ms

As you can see, the important queries here are SQL6 (Artists), SQL7 (Genres), SQL8 (Albumartist), SQL9 (Composers) and SQL12 (Mediaitem). This is expected since these are dealing with music - the other queries deal with videos, movies, etc. which are not in my database. These relevant SQL queries take in total about 0.6 seconds, which is relatively long IMO, but it is not extreeeeemly long so that it renders MP2 unusable.
To verify my results, I restarted the server and executed the same search again.

Restart MP2 Server / Read Test 1:
SQL1 (Genres): 1ms
SQL2 (AudioLanguages): 1ms
SQL3 (Actors): 1ms
SQL4 (Directors): 1ms
SQL5 (Writers): 1ms
SQL6 (Artists): 482ms
SQL7 (Genres): 25ms
SQL8 (Albumartist): 64ms
SQL9 (Composers): 116ms
SQL10 (Episode): 1ms
SQL11 (DVDEpisode): 1ms
SQL12 (Mediaitem): 80ms

Now the relevant SQL queries took in total about 0.77 seconds, which I thought was just a measuring inaccuracy. So I restarted the MP2 Server and did the test again. And now the magic happened:

Restart MP2 Server / Read Test 1:
SQL1 (Genres): 1ms
SQL2 (AudioLanguages): 1ms
SQL3 (Actors): 1ms
SQL4 (Directors): 1ms
SQL5 (Writers): 0ms
SQL6 (Artists): 80ms
SQL7 (Genres): 19ms
SQL8 (Albumartist): 53ms
SQL9 (Composers): 93ms
SQL10 (Episode): 1ms
SQL11 (DVDEpisode): 1ms
SQL12 (Mediaitem): 55ms

All of a sudden, the search only took 0.3 seconds. I couldn't believe it and did the Read Test 1 two more times:

Restart MP2 Server / Read Test 1:
SQL1 (Genres): 1ms
SQL2 (AudioLanguages): 1ms
SQL3 (Actors): 0ms
SQL4 (Directors): 1ms
SQL5 (Writers): 1ms
SQL6 (Artists): 81ms
SQL7 (Genres): 20ms
SQL8 (Albumartist): 52ms
SQL9 (Composers): 93ms
SQL10 (Episode): 1ms
SQL11 (DVDEpisode): 1ms
SQL12 (Mediaitem): 56ms

Restart MP2 Server / Read Test 1:
SQL1 (Genres): 1ms
SQL2 (AudioLanguages): 1ms
SQL3 (Actors): 1ms
SQL4 (Directors): 1ms
SQL5 (Writers): 1ms
SQL6 (Artists): 80ms
SQL7 (Genres): 20ms
SQL8 (Albumartist): 53ms
SQL9 (Composers): 94ms
SQL10 (Episode): 1ms
SQL11 (DVDEpisode): 1ms
SQL12 (Mediaitem): 55ms

As you can see, now the measurements were more or less constant at about 0.3 seconds. The last test I did so far was just doing Read Test 1 again, but this time without restarting the MP2 Server - and again the result was more or less the same:

Read Test 1:
SQL1 (Genres): 1ms
SQL2 (AudioLanguages): 1ms
SQL3 (Actors): 1ms
SQL4 (Directors): 1ms
SQL5 (Writers): 1ms
SQL6 (Artists): 78ms
SQL7 (Genres): 20ms
SQL8 (Albumartist): 55ms
SQL9 (Composers): 93ms
SQL10 (Episode): 1ms
SQL11 (DVDEpisode): 1ms
SQL12 (Mediaitem): 64ms

And I already have a suspicion where this comes from - maybe a first hook to implement an optimization.
More about it tomorrow...

Michael

Lehmden · September 19, 2013

Hi

MJGraf said:
a big database also slows down the startup of the MP2 client,

This was not exactly what I've meant. The Start of the MP2 client is as fast as ever. Only the time until the client is connected to the Server increases.

MJGraf · September 19, 2013

Lehmden said:
This was not exactly what I've meant. The Start of the MP2 client is as fast as ever. Only the time until the client is connected to the Server increases.

Thanks Lehmden - will correct that.

In the meantime I found out that the result above has apparently noting to do with SQLite. What I did was just wait for one day, use the MP2 Server computer as regular MP1 htpc and run the same Read Test 1 again. The result shows, we are up to the same performance as above with the first test. So apparently this is caused by some kind of Windows caching mechanism...

Wait one day / Restart MP2 Server / Read Test 1:
SQL1 (Genres): 2ms
SQL2 (AudioLanguages): 1ms
SQL3 (Actors): 1ms
SQL4 (Directors): 1ms
SQL5 (Writers): 1ms
SQL6 (Artists): 374ms
SQL7 (Genres): 23ms
SQL8 (Albumartist): 64ms
SQL9 (Composers): 118ms
SQL10 (Episode): 1ms
SQL11 (DVDEpisode): 1ms
SQL12 (Mediaitem): 84ms

But at least it's reproducible. This time the cache kicks in already when I run the search for the second time:

Restart MP2 Server / Read Test 1:
SQL1 (Genres): 1ms
SQL2 (AudioLanguages): 1ms
SQL3 (Actors): 1ms
SQL4 (Directors): 0ms
SQL5 (Writers): 1ms
SQL6 (Artists): 82ms
SQL7 (Genres): 22ms
SQL8 (Albumartist): 55ms
SQL9 (Composers): 96ms
SQL10 (Episode): 1ms
SQL11 (DVDEpisode): 1ms
SQL12 (Mediaitem): 56ms

And then it stays like that:

Restart MP2 Server / Read Test 1:
SQL1 (Genres): 1ms
SQL2 (AudioLanguages): 1ms
SQL3 (Actors): 0ms
SQL4 (Directors): 1ms
SQL5 (Writers): 1ms
SQL6 (Artists): 83ms
SQL7 (Genres): 20ms
SQL8 (Albumartist): 51ms
SQL9 (Composers): 92ms
SQL10 (Episode): 1ms
SQL11 (DVDEpisode): 1ms
SQL12 (Mediaitem): 55ms

We have to keep that in mind for further tests - although I'm not sure, yet, which result we shall take from our tests. While the slower first time results reflect more the raw SQLite performance, the cache also kicks in when using MP2 in real time so these results should be closer to reality. For now I will always perform the test two or three times and record all results to make sure we have them both - with and without the help of windows.

But back to the original ideas:
The first one was whether this has anything to do with the "prepare" command. Since I'm executing exactly the same query multiple times, I thought that maybe SQLite somehow has stored this SQL query in a prepared state so that it can be executed faster when run the second or third time. But this cannot be the case since I restarted the MP2 server between all the tests and there is no way for SQLite to store any kind of prepare statement - unless it is stored in the database file itself.
While reading about this, I stumbled upon the "ANALYZE" SQL command for SQLite. What this command does is analyzing all the tables, their contents and the respective indices and storing the results in a permanent internal table of the SQLite database file. These results are then used by the query optimizer of SQLite to improve the performance. Bus as you can read on the linked page, ANALYZE is only run, when you execute it explicitly, which I didn't.
But hey - maybe we have to run that command after every import!

So stay tuned. Tests with ANALYZE will follow...
Michael

MJGraf · September 19, 2013

Hmmm, there seems to be a lot of room for improvement.
I just opened the SQLite database file with SQLiteSpy and noticed the following:
Every table which has a non-int primary key, has two identical indices. I haven't checked, but I would suppose this holds true for each and every table MP2 generates. As an example:

There is a table named MEDIA_ITEMS and two indices for this table:
MEDIA_ITEMS_PK_IDX (based on column MEDIA_ITEM_ID) and
sqlite_autoindex_MEDIA_ITEMS_1 (also based on column MEDIA_ITEM_ID)

As the name of the second index implies, the index was auto generated by SQLite. And as per this, this is expected behaviour:

Any PRIMARY KEY (other than an INTEGER PRIMARY KEY) and any UNIQUE constraint automatically generates an index. This cannot be suppressed.

I would guess, this holds true for any database system out there since the database has to check for uniqueness somehow. If so, we shouldn't generate indices for primary keys. If there are database systems out there that do not auto generate such an index, we should maybe extend the ISQLDatabase interface by something like

Code:

bool CreateIndexOnPrimaryKeys()

and only let MP2 Server create an index, if this returns true.

This basically means that we spend double the time necessary for creating indices right now. However, this should mainly influence inserts - and therefore the import. Not reading from the database. I'll see if I can recreate an empty database, delete one of each double indices manually and see how the import performs... If it's faster, I can maybe delete the unnecessary indices on startup. But this is of course a workaround. It would be better not to create them instead.

Michael

MJGraf · September 19, 2013

and according to this:

> Greg Obleshchuk wrote:
>>
>> So in these cases there is no benefit from creating an index on a column
>> that is INTEGER PRIMARY KEY?
>>
>
> Putting an index on an INTEGER PRIMARY KEY will make INSERT,
> DELETE, and UPDATE slower since the index must be maintained.
> But no SELECT will ever use the index. So adding an index
> to an INTEGER PRIMARY KEY is less than no benefit - it hurts.
>
> See ticket #292. If you say "UNIQUE PRIMARY KEY" (as some
> users want to do) SQLite will create two identical indices
> Only one index will ever be used - the other justs wastes
> CPU time and disk space. I'll get around to fixing that
> someday. Probably at the same time I should rig it so that
> attempts to create named indices on PRIMARY KEY are ignored
> too. Once that happens, you can create indices on your
> INTEGER PRIMARY KEY all you want - SQLite will ignore your
> attempts - and everything will work at maximum efficiency
> regardless of what you try to do.

the first problem with the "UNIQUE PRIMARY KEY" seems to be solved - but it still accepts named indices on PRIMARY KEYS unfortunately. Import Test is just running.

And for the files: I manually deleted the named indices on primary keys for tables starting with "M_". In the "NM_" tables, the auto generated key is on both columns, whereas our named index is only on one of them. So I left both indices. In the "V_" tables, the auto index is on the ID, our index is on (I think) the "attribute" column. So I left these as well.

SQLiteDatabase Plugin for MP2 (2 Viewers)

morpheus_xx

Retired Team Member

chefkoch

Retired Team Member

MJGraf

Retired Team Member

MJGraf

Retired Team Member

MJGraf

Retired Team Member

MJGraf

Retired Team Member

Lehmden

Retired Team Member

MJGraf

Retired Team Member

MJGraf

Retired Team Member

MJGraf

Retired Team Member

Users who are viewing this thread