Sproc that joins 3 tables painful slow
-
There are many records for each field two of the tables that I am trying to join. I need somehting from all three to work however. Is there another way to write this or split it up to run faster than the two days it takes now?
CREATE PROCEDURE SP_CLARIFY_PER_REVIEWER
@START VARCHAR (8),
@END VARCHAR (8),
@REVIEWER VARCHAR (30)
AS
SELECT
FDIS_CLAIM.CLAIMSET_ID,
FDIS_CLAIM.CLAIM_ID,
FDIS_CLAIMSET.EXAMINER_ID
FDIS_CLAIMSET_LOG.LOG_MESSAGE
FROM
FDIS_CLAIM,
FDIS_CLAIMSET,
FDIS_CLAIMSET_LOG
WHERE
FDIS_CLAIM.CLAIMSET_ID >=@START
AND FDIS_CLAIM.CLAIMSET_ID <=@END
AND FDIS_CLAIM.CLAIMSET_ID=FDIS_CLAIMSET_LOG.CLAIMSET_ID
AND FDIS_CLAIM.CLAIMSET_ID=FDIS_CLAIMSET.CLAIMSET_ID
AND PATINDEX('%'+@REVIEWER+'%',FDIS_CLAIMSET.EXAMINER_ID) > 0
GO
Thanks For your continued Help.
Thanks For your continued Help. -
Check the estimated execution plan and see if you need new indexes.
-
The reason this query is taking a long time to execute is probably not the fact that you join three tables, it's the last where clause:
PATINDEX('%'+@REVIEWER+'%',FDIS_CLAIMSET.EXAMINER_ID) > 0
No matter what indexes you have this forces a table scan of the FDIS_CLAIMSET table, and if that table is large it will take time. Why do you need to do this? You are running it on a column named EXAMINER_ID, does this mean that this column contains several examiner_ids in a multi-value fashion?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Chris, wouldn't a clustered index on 'FDIS_CLAIMSET.CLAIMSET_ID' column (depending on the selectivity) would contribute hugely even if he uses a function?
A clustered index on 'FDIS_CLAIM.CLAIMSET_ID' would probably not be so bad either.
kreskins: How are the statistics? And where are the bottlenecks (i.e where would you gain most in optimizing your query)?
-
Yes, they could very well help, but it would still not solve the problem of searching for a string inside a lagrger string. The problem is not running a function as such (although it is not a SARG and therefore not really efficient). The problem is the usage of '%'+@variable+'%'. This means that SQL Server must go through every row and check if the string in the variable exists somewhere in the probably much larger column. No index can help it, since it can exist anywhere in the string.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
But with HIGH selectivity of the clustered index, you would narrow down that problem hugely. Since we don't know the uniqueness of the 'FDIS_CLAIMSET.CLAIMSET_ID' column, it could very well become just a scan of one or two rows... Chris, you're right, no index could actually be used on those but maybe that will not be the bottleneck.
-
Ah, of course, yes you're right. I'll be quiet now so that kreskins can sjow us the statistics.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply