Slow query

This script takes 3 secs to process each row in the t1 table with has 50 rows. That’s a problem in larger t1 tables.
I’ve verified that all cols that are joined are indexed.

Any ideas for speeding up the process represented by this script:

UPDATE 
		serrapl AS t1 
	JOIN 
		emily.nicks AS t2 
	ON 
		upper(t1.fn) = upper(t2.name)
	JOIN
		vo.vo16c AS t3 
	ON	
		upper(t2.nickname) = upper(t3.fn)
	AND
		upper(t1.ln) = upper(t3.ln)
	AND
		upper(t1.full_addr) = upper(t3.key1)	
	SET
		t1.bday2 = t3.dob

which is ignored, since you run the values to join through upper().

3 Likes

I’ll check that out and report back. Thanks

Voila! Literally at least 157x times faster!

That’s a very important fact to learn.

Thanks again Dormilich.

I learned that while reading

157x times faster? You mean you got down to 1/157 of the previous time?

you can use a proper index for a caseinsensitive search

CREATE INDEX ON nicks ((upper(nickname));
1 Like

Yes! 157 seconds to process 50 rows down to 1 sec.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.