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.

At one place we need to query a table to determine whether any records exists that are in a subset of the same table.

The columns that are queried are varchar columns containing file-paths, and we need to determine whether there are any duplicates for a set of rows.

Say, this is the table:

--------------------------------
| Id  | Path              | ....
--------------------------------
|   0 | file1.txt       |
|   1 | file2.txt       |
|   2 | file3.txt       |
|   3 | file1.txt       |
|   4 | file4.txt       |
--------------------------------

only that in reality there are millions of paths.

Now, at one point we have already collected the data from a number of rows for the table (say, Id 0 and Id 2 => file1.txt and file3.txt) and we now need to find out whether there are any duplicate entries in the table. (The data is just in an string array on the client side.)

Due to the way the application is built at the moment, there will be a loop in the application, essentially executing

for all f in (file1.txt, file3.txt)
    select count(*) from Table where Path = f;

Given that the current production DB has about 14M records and the typical query size is about 100-2000 different paths the performance of this is horrible. (Yes, it's actually running above query 2000 times We have thought about re-engineering this, but we're a bit stumped at how to lay this out properly.

Given 2000 strings, I very much doubt it would be a good idea to run

select count(*) from Table where Path in (str1, ... str1999);

What are alternatives, generally speaking? This has to run both against Oracle(10+) and SQLServer(2008+).

Edit:

The problem here is that I have n "records" outside of the DB and need to query the DB for each of those records.

As long as n is sufficiently small, we can just dynamically build an OR'ed query and be done with it. Although, of course dynamically built queries do have there own set of problems. (So the "dumb" solution of just running the same query n times may have its merits after all.)

The comments suggest to put the n records into a (temporary) table, but then the question becomes how to efficiently put the n records into that table (or table-valued parameter) in the first place?

share|improve this question
    
Do you have the paths in a table? Seems the logical and easy way to go, assuming I'm reading your question correctly (it's not really that clear) –  Phil Dec 5 '13 at 14:02
    
@Phil - well currently no. There is just this one table that is first queried for a few hundred records and later the data from this few hundred records (that is still cached by the client) needs to be matched against the whole table. Would it make sense to have something like a "scratchpad" table inside the the db? Is this common? –  Martin Dec 5 '13 at 14:52
    
Yeah, it's common. –  Phil Dec 5 '13 at 14:52
1  
In SQL Server you are better off using a table-valued parameter than a comma-separated list of values. However good luck getting an optimal solution that is coded exactly the same way for both platforms. –  Aaron Bertrand Dec 5 '13 at 15:27
1  
In Oracle you might be better off inserting the filenames into a table and then using that in IN clause or as a normal join, rather than large OR conditions. –  Raj Dec 10 '13 at 20:35
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.