Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Brief on application:

This is audio fingerprinting application, being developed in Java with Microsoft SQL Server 2005 database.

I have one application to create fingerprints of original songs and put these fingerprints in database. To store fingerprint in database I have table:

CREATE TABLE [dbo].[fp_core](
    [hashkey] [bigint] NOT NULL,
    [note_id] [int] NOT NULL,
    [timeoffset] [int] NOT NULL
) ON [PRIMARY]

The application processes song and takes 100 sample per second, so around 15000 samples for complete song. These sample values are stored in database, 1 row for each sample as {HASHKEY, NOTE_ID, TIMEOFFSET}. For fingerprint of complete song, I may have around 15000 rows in fp_core table. I am planning to put fingerprints of 50000 songs in database, so around 750 million rows will be in fp_core table.

I have other application to process recordings and detect songs played in it. Process is, create set of HASHKEY from recording audio, same as for creating fingerprint of original song. Recording audio will generate around 20000-30000 HASHKEYs. Then application retrieves rows from fp_core table for all matching HASHKEYs generated by recording audio.

To retrieve data from fp_core table by processing recording, I am doing is, filling these all HASHKEYs of recording in one more table, table is:

CREATE TABLE [dbo].[fp_core_keys](
    [hashkey] [bigint] NOT NULL
) ON [PRIMARY]

then I am joining these two tables to retrieve all matching rows, the query is:

select fp.hashkey, fp.note_id, fp.timeoffset
from dbo.fp_core fp 
INNER JOIN dbo.fp_core_keys keys ON fp.hashkey = keys.hashkey

I have following indexes:

CREATE CLUSTERED INDEX [index_fp_core] ON [dbo].[fp_core] 
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_fp_core_keys] ON [dbo].[fp_core_keys] 
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Problem:

Retrieving data using above query is so slow, taking time around 40 seconds.

Right now, here is stats:

Query:

select count(hashkey) from fp_core
go
select count(distinct(hashkey)) from fp_core

Result:

57177764
13675633

Plan:

enter image description here

Can anybody help me?

share|improve this question
    
As I read it, when you want to find something, you load the sample into fp_core_keys. Why not just filter (WHERE) in your query? –  billinkc Sep 15 '13 at 17:31
    
Do I understand correctly that for every song, you'll have 15k samples and these are stored as 15k rows in the table, all with same noteID? –  ypercube Sep 15 '13 at 17:58
    
@ypercube yes correct –  UDPLover Sep 15 '13 at 17:59
    
@billinkc do you mean to use WHERE with IN clause? –  UDPLover Sep 15 '13 at 18:01
    
So, you want to compare the 20-30k hashkeys of a sample with the 15k hashkeys of a song and if there are no (or few) matches, to be discarded. If there are many matches, the sample is identified as to be this song (and do this against all groups of 15k until you identify the sample.) –  ypercube Sep 15 '13 at 18:05

1 Answer 1

If you are determined to retain your existing table structure, the best indexes will be something like:

create index ix1 on fp_core(hashkey, note_id, timeoffset);
create index ix1 on fp_core_keys(hashkey);

You mention that the hashkeys can be different for the same song, but it sounds like you have some mechanism for detecting similar songs - if possible, add an additional column called 'songid' to your fp_core table and then query on this single value using a WHERE clause.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.