Duplicate Title Magic Node that excludes Different album

[Bex]

psyxonova, great work!

I've been thinking about something like this for a while. But my idea was to create Access Queries as substitutes for the SQLfilters used within Magic Nodes so it'll be faster.
I also have some ideas on different ways to find duplicates wich involves some advanced query techniques. I'll give it a go and let you know the result.

The result of the test is that it's not faster. Atleast not if you just let the Access query replace the SQLFilter code.
However, if you instead use the Access query to make a new table, wich you index, and then let the new table replace the SQLFilter code it really speeds things up!
In my case from 30 min to 3 sek!!!

But todo it this way you must run the queries within Access everytime you edit or add tracks to your library. Otherwise the changes will not be reflected.

Or is it a way to execute predefined action queries in Access from MM, or perhaps even a Macro?
If there is a way, will the execute time be as fast as doing it within Access?


If anyone is interested in how todo this (manually in Access) let me know and i'll try to explain.

[psyXonova]

Well, i don't know with what query you did the test but if you try to do it with my query you end up winning some (2-3) valuable seconds (my library is over 50.000 songs, my query ended up with 950 results and all that in less than 2 seconds).
There is no reason to create a new table and index it, since if you create a query that is based on indexed collumns then the query is allready indexed (in a way).
The problem was with magic nodes because you couldn't disable the child nodes and so if you clicked the node to expand by accident it was trying to group the results by artist which basically means that was using the query as a subquery for n times (where n is the number of artists in the artist table) ... or at least thats what i think it does. This is why i ended up adding the node manually to avoid the child nodes.

Besides all that i do believe that creating and running the query from Access is still a better choice since it allows you for better design and review of the query (including the ability for nested, high speed queries) as well as letting you add as many columns as you want (while always considering speed) and choosing only the appropiate for each case with scripting.
Don't forgret that there is a big limitation if you run SQL query from VBScript since it allows you to choose only one output collumn which basically forces you to write almost the same code as many time as the number of collumns you want to use.
I am pretty sure right now that perhaps nobody understands me because of the way i wrote all this and for this i am sorry.
Bex, i really like your idea of executing action queries from within MM. I wil ltry it on Monday using my work PC.
It would be nice if you could post some of your queries and see how we can make them run faster. I am pretty sure that if we can replicate your queries by creating nested access querys and use them (properly) as basis for magic nodes we can really make magic nodes .
Anyway don't forget that i just started learnig VBScript (but i am experienced enough concerning SQL) and it will take some time before i do it right!!

[Bex]

My goal was to make Magic Nodes with advanced SQLFilters fast! Not that your solution is bad, but it cant be incorporated with the power of Magic Nodes.
And what I found out was that the Magic Node wasnt any faster based on an Access query, but it was a bit faster if the node where based on a table. And it was really fast if the node was based on an indexed table.
It was a huge difference, from 30 min to 3 sek!! (I have 40.000+ tracks.)

Your script however was already so fast so the execute time saved was only like 3 sec which you already stated.

But one very interesting thing I noticed was that the execute time of the original Duplicate Titles node was on my collection 20 sec but only 3 sec with a Magic Node based on a indexed table!! (The creation of the table not included which automated would be under two sec.) (I have 7628 files in that node.)

But all this is of little interest if you cant execute predefined action queries (or preferably Access macros) from scripts and/or from MM itself. Not to mention all the users that dont have Access...

Tomorrow I'll post some Access queries which accomplish the stuff I mentioned plus some more nice stuff!

Lastly I want to say that I'm also experienced regarding SQL but I'm better with Access queries, but I know nothing about VBScript...

[mdave13]

Psyxonova,
Since I started this topic I thought I would Thank you an Bex for your work.
I was able to add your query to my database using a friends computer who had Access.
It worked very well, processing my 17,000+ songs in 2-3 seconds and gave exactly the results I was looking for.
Again, thanks to you and Bex.
Dave

[karltinsly]

I'd like to add my thanks too -- this really helps find all those dupes! Do I understand correctly that I need to rerun the query in access and then restart MM if I want to see changes made on this node, such as removing a bunch of these dupes?

[Bex]

I'd like to add my thanks too -- this really helps find all those dupes! Do I understand correctly that I need to rerun the query in access and then restart MM if I want to see changes made on this node, such as removing a bunch of these dupes?

No, the query is executed every time you click on the node!

[karltinsly]

Super! Thanks again to both of you, bex and psyxonova!

[psyXonova]

I am glad i helped,

Bex i am waiting for your queries. I my self am very interested too on creating cool magic NOdes

My scripts is just a workaround for the huge time Magin Nodes take to execute advanced SQL queries.
I hope we can make it...

[Bex]

I am glad i helped,

Bex i am waiting for your queries. I my self am very interested too on creating cool magic NOdes

My scripts is just a workaround for the huge time Magin Nodes take to execute advanced SQL queries.
I hope we can make it...

Haha, and I am waiting for you to check if it's possible to execute Access action queries (or preferable macros) from a scipt!

Well, I'm at work now but I'll post some cool queries here tonght

[Bex]

Ok here we go!

Here is my workaround to make slow Magic Nodes fast. (Magic Nodes with advanced SQLFilters.)

The idea is to create an indexed table in Access as a substitute for the SQLFilter used in the Magic Node.

Example:
Here is a very slow Magic Node wich displays all tracks with the same Artist, Title and Length.

Code: Select all

DupsArtistTitleLenght|SQL filter: IDArtist&SongTitle&Format(SongLength/1000/86400,'hh:nn:ss') IN (SELECT IDArtist&SongTitle&Format(SongLength/1000/86400,'hh:nn:ss') FROM Songs GROUP BY IDArtist&SongTitle&Format(SongLength/1000/86400,'hh:nn:ss') HAVING Count(Songs.ID)>1)\<Artist>

If you instead base the SQLFilter on an Acces Table it would look like this:

Code: Select all

DupsArtistTitleLenght|SQL filter: songs.id in (select id from TABLENAME)\<Artist>

So how do we create such a table in access? Well it's somehow complicated but i'll try to explain. (I assume you know how access works so I will not write any exact step by step instructions.)

1. First create a select query by pasting this code into the sql view of an Access query:

Code: Select all

SELECT Songs.IDArtist, Songs.SongTitle, Format([SongLength]/1000/86400,"hh:nn:ss") AS Length
FROM Songs
GROUP BY Songs.IDArtist, Songs.SongTitle, Format([SongLength]/1000/86400,"hh:nn:ss")
HAVING (((Count(Songs.ID))>1));

Save it as SamArtTitLen1

2. Create a "make table" query from this code:

Code: Select all

SELECT Songs.ID INTO SamArtTitLen
FROM Songs INNER JOIN SamArtTitLen1 ON (Songs.SongTitle = SamArtTitLen1.SongTitle) AND (Songs.IDArtist = SamArtTitLen1.IDArtist)
WHERE (((Format([SongLength]/1000/86400,"hh:nn:ss"))=[Length]))
GROUP BY Songs.ID;

Save it as SamArtTitLen3

3. Run the make table query (SamArtTitLen3). Close it and open up the newly created SamArtTitLen table in design view and add a key to the ID field.

4. Open the SamArtTitLen3 query in design view and change the query type to Append query.

5. Create a new delete query from this code:

Code: Select all

DELETE SamArtTitLen.ID
FROM SamArtTitLen;

Save it as SamArtTitLen2

Now you will have three queries:
- SamArtTitLen1, which is nested into SamArtTitLen3
- SamArtTitLen2, which deletes all data in table SamArtTitLen
- SamArtTitLen3, which adds new data to table SamArtTitLen
And one new table:
- SamArtTitLen, which is indexed on field ID
(The reason for having one delete and one add query is that we want to keep the key in the table SamArtTitLen which wouldnt be the case if we only used one make table query instead.)

Create a new Magic Node:

Code: Select all

DupsArtistTitleLenght|SQL filter: songs.id in (select id from SamArtTitLen)\<Artist>

This Magic Node is fast!

The downside is that you MUST run the SamArtTitLen2 and SamArtTitLen3 queries everytime you change or add tracks to MM.
This could however be simplified by creating an Access Macro:
- SetWarnings = no
- OpenQuery SamArtTitLen2
- OpenQuery SamArtTitLen3
Save it as MacroSamArtTitLen
The macro is executed in 3 sec!


This method could be used on some really advanced Magic Nodes which would have been impossible on large collections. Due to too long execute time.
Eg:
- Display all "Duplicate content" without those marked with OK in custom3
- Display all tracks with same Artist, title and (lenght+-3 sec)
- Display all tracks with partial match on artist and title
- Etc..

Let me know what you think of this and what you want. I'll try to help out if I can.

Enjoy!

PS If it is possible to run macros or action queries in Access from a script then this method is really something to explore. Anyone knows?

[trooper95]

Nice work Bex!
I have very limited knowledge of MS Access and it only took me 30 minutes to get this to work. I havn't set up the macro portion yet. That's next.
Node works great. My only problem is that it returns 16,000 songs out of a 54,000 song library. Something seems odd.
Anyway, is there a way to do this so that it matches the same song AND same album? You know, if you have the same album in 3 places on your hard drives.

[Bex]

Nice work Bex!
I have very limited knowledge of MS Access and it only took me 30 minutes to get this to work. I havn't set up the macro portion yet. That's next.
Node works great. My only problem is that it returns 16,000 songs out of a 54,000 song library. Something seems odd.
Anyway, is there a way to do this so that it matches the same song AND same album? You know, if you have the same album in 3 places on your hard drives.

Thanks!
16 000 out of 54 000? That does indeed sounds odd. But are they duplicates or not?

Here is the code for same title and albumID. Follow the procedure from above:

Code: Select all

SELECT Songs.IDAlbum, Songs.SongTitle
FROM Songs
GROUP BY Songs.IDAlbum, Songs.SongTitle
HAVING (((Count(Songs.ID))>1));

Save as SameTitleAlbumID1

Code: Select all

SELECT Songs.ID INTO SameTitleAlbumID
FROM Songs INNER JOIN SameTitleAlbumID1 ON (Songs.SongTitle = SameTitleAlbumID1.SongTitle) AND (Songs.IDArtist = SameTitleAlbumID1.IDAlbum)
GROUP BY Songs.ID;

Save as SameTitleAlbumID3

- Run the make table query (SameTitleAlbumID3). Close it and open up the newly created SameTitleAlbumID table in design view and add a key to the ID field.

- Open the SameTitleAlbumID3 query in design view and change the query type to Append query.

Code: Select all

DELETE SameTitleAlbumID.ID
FROM SameTitleAlbumID;

Save as SameTitleAlbumID2

The Magic Node:

Code: Select all

SameTitleAlbumID|SQL filter: songs.id in (select id from SameTitleAlbumID)\<Artist>

[Bex]

And here you the code for same title and albumName. This Code is actually to prefer since it also includes albums wich has same name but different id.
Follow the procedure from above:

Code: Select all

SELECT Albums.Album, Songs.SongTitle
FROM Songs INNER JOIN Albums ON Songs.IDAlbum = Albums.ID
GROUP BY Albums.Album, Songs.SongTitle
HAVING (((Count(Songs.ID))>1));

Save as SameTitleAlbumName1

Code: Select all

SELECT Songs.ID INTO SameTitleAlbumName
FROM (Songs INNER JOIN Albums ON Songs.IDAlbum = Albums.ID) INNER JOIN SameTitleAlbumName1 ON (Songs.SongTitle = SameTitleAlbumName1.SongTitle) AND (Albums.Album = SameTitleAlbumName1.Album)
GROUP BY Songs.ID;

Save as SameTitleAlbumName3

- Run the make table query (SameTitleAlbumName3). Close it and open up the newly created SameTitleAlbumName table in design view and add a key to the ID field.

- Open the SameTitleAlbumName3 query in design view and change the query type to Append query.

Code: Select all

DELETE SameTitleAlbumName.ID
FROM SameTitleAlbumName;

Save as SameTitleAlbumName2

The Magic Node:

Code: Select all

SameTitleAlbumName|SQL filter: songs.id in (select id from SameTitleAlbumName)\<Artist>

[psyXonova]

Bex, Good news here,

I think i found a workaround for executing Action Queries from within a script.
I still haven't found a way to create a make table query though but i dont think is needed.
From what i see all your queries require just one table with one indexed column, the songID.
What i am thinking is to create a script that will prepare the data for MagicNodes everytime you want to check for duplicates or any other advanced query.
That way we will have only ONE generic MagicNode that will always check in our table for SongID.
All the advanced queries will be saved inside the MediaMonkey.ini with the help of that script. So, eg if you want to check for Duplicate Title and Album you will run the script and select the specific option. From now on, it is on the script to delete all previous data from our table, query the database and insert the new data in it and afterwards give focus to the Generic MagiicNode that will display results.
I am almost done with the query but i will be very busy the next week so expect news from me after some days.

[Bex]

psyxonova, thats cool!

Looking forward to your solution!