Thread: Problem with ORs in JOIN statement

Hi,

I have a problem with the SQL statement detailed below. The query returns the results I need, but takes an insane amount of time to execute. I now have so many records in the db that the page generally won't load.

SELECT dscan.guid, dscan.drive, dscan.folder, dscan.filename, source.guid
FROM source RIGHT JOIN dscan
ON ((source.guid & '_dtr' = dscan.guid OR source.guid & '_dto' = dscan.guid OR source.guid = dscan.guid)
AND dscan.guid LIKE '%" & Replace(strSearch_guid, "'", "''") & "%'
AND dscan.filename NOT LIKE '.[_]%'
AND dscan.drive = 'Z:')
WHERE source.guid Is Null
ORDER BY dscan.drive, dscan.guid

The problem is in this part of the JOIN:

(source.guid & '_dtr' = dscan.guid OR source.guid & '_dto' = dscan.guid OR source.guid = dscan.guid)

If I only have one of those conditions, the page loads instantly, but with all 3 it bombs.

Anyone know why this is happening and how I can fix it?

________________

FYI for anyone wondering what I'm doing, I'm comparing a list of filenames (dscan.guid) against database records (source.guid) to identify misnamed or misplaced files.

The files sometimes have exactly the same name as the database record or sometimes have meaningful appendages (_dtr or _dto).

Because they are meaningful, those appendages are used in other database functions so I can't just get rid of them. I could create another column in the dscan table and separate those appendages from the rest of the filename which would then allow me to just use:

source.guid = dscan.guid

in the query. But I'm curious as to why it's slow to execute as is, and prefer not to have to add a whole column just to get this one query to work.

I don't have an index on the dscan table - it's subject to frequent updates.

But I added one just to see if it made a difference and it hasn't helped with this query. The page still won't load.

Hey ngreen,

I'm definitely the novice here so you've probably thought of this..but if you're trying to compare to see misnamed records, there's a couple ways I go about it that are a bit simpler:


SELECT ID, Column
FROM TableA
Where Column not in(Select Column from TableB)

Probably too simple for what you're trying to accomplish.

Another way I get this is with an intersect or an except query

SELECT ID, Column
From TableA

INTERSECT (or Except depending on if you want to view the rows that match
vs those that don't match)

SELECT ID, Column
From TableB

Again, I"m sure this is way too simple for what you're doing but these help me tremendously with all the bad data entry that I have to catch on a regular basis

Originally Posted by Wolffy

Is this something that has to be run in real-time or is this something that you only do on occasion? At some point, the complexity of the query and size of the data just makes it too much to run interactively (for example, I have a query that takes about 2.5 hours to run, but I only do it once a month).

Consider breaking out the work and make this a stored procedure that you can run when necessary to create a reporting table.

That's an option I hadn't considered. I avoid additional tables like this wherever possible. If it comes to this, I think I'd rather add the extra column to the dscan table to allow me to run a straight source.guid=dscan.guid comparison.

Originally Posted by Flam

Hey ngreen,

I'm definitely the novice here so you've probably thought of this..but if you're trying to compare to see misnamed records, there's a couple ways I go about it that are a bit simpler:


SELECT ID, Column
FROM TableA
Where Column not in(Select Column from TableB)

Probably too simple for what you're trying to accomplish.

That method works, but is even worse when it comes to execution time. I tried that method briefly with some of the simpler comparisons I run and found it to be very slow.

Originally Posted by Flam

Another way I get this is with an intersect or an except query

SELECT ID, Column
From TableA

INTERSECT (or Except depending on if you want to view the rows that match
vs those that don't match)

SELECT ID, Column
From TableB

Again, I"m sure this is way too simple for what you're doing but these help me tremendously with all the bad data entry that I have to catch on a regular basis

I'd never heard of this one (I'm a complete novice too) so I'll give it a try next week and report back on how it goes. But I'm not sure if it can handle what I need, eg. it would need to be:

SELECT guid
From dscan

EXCEPT

SELECT (guid OR guid & '_dto' OR guid & '_dtr')
From TableB