Join based on LEFT(column,@var) works very slowly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join based on LEFT(column,@var) works very slowly

Author

Topic

oldfox
Starting Member

17 Posts

Posted - 2011-08-01 : 20:07:36

Hi all,
I am trying to join 2 tables
DECLARE @var int
SELECT @var=2

SELECT * FROM table1 a
join table2 b
ON a.column = LEFT(b.column,@var)

Query runs only few seconds when I use 2 instead of @var in the LEFT statement ( = LEFT(b.column,2) ), but when I use @var it takes 8 minutes.

Is there a way to make this query run faster?

wadie20000
Starting Member

1 Post

Posted - 2011-08-01 : 20:15:22

[url=http://edite-xhtml-page.blogspot.com/]édité une page xhtml et css[/url]
[url=http://programmation.x2h.info/]??? ???? ???? ???????[/url]

http://edite-xhtml-page.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 00:48:22

try like


SELECT * FROM table1 a
join table2 b 
ON a.column  LIKE b.column + '%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-02 : 02:03:08

Oldfox, this is because you have trashed the index on the column by using a non-indexed value deriving from your calculation over the column.
If you must insist in using the calculation, try adding a HASH JOIN query hint. It has a larger startup cost, but may dramatically increase the speed of your query.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources