Help to optimize inner join

Question

Post reply

Help to optimize inner join

Anders Hansen

Ten Centuries

Points: 1211
More actions
September 23, 2009 at 1:19 am

#141908

Hi all
I have a query of the following form.
SELECT a.Col1, b.Col2
FROM aTable a
INNER JOIN vTable v ON v.AssetID = A.AssetID AND v.AssetSiteID = A.AssetSiteID
INNER JOIN bTable b ON
(b.AssetID = a.AssetID AND b.AssetSiteID = a.AssetSiteID) OR
(b.VersionID = v.VersionID AND b.VersionSiteID = v.VersionSiteID)
When i watch my execution plan i end up having an index scan for the bTable.
My question now is, can i create an index that gives me an index seek for this query, or can i rewrite my query so i end up with index seeks instead?
The point is that my bTable has two sets of values, either of the values can be null, and then the other set should be used.
Examples of bTable could be
AssetId AssetSiteId VersionId VersionSiteID Value
1 4 NULL NULL dfgjeerg
NULL NULL 2342 4 ergioerjgoi
I hope some of you guys can help me out here.
Best regards
/Anders

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I don't know which indexes get scanned. I suggest you read Gail Shaw's article on how to post performance problems:

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Anyway I guess you get the scans from the OR in the JOIN clause. Without the execution plan it's only a guess.

Regards

Gianluca

-- Gianluca Sartori

Anders Hansen Ten Centuries Points: 1211 More actions · Answer 2

Well sorry for not posting the execution plan, or explaining properly.

But you are exactly right.

I have an index covering bTable AssetId, AssetSiteId, VersionId and VersionSiteID and with the appropiate output columns.

And the join with the or clause takes 45% of my query cost. Probably due to the index scan instead of seek.

My question was more how i can write an index, or rewrite my query so i can get the same result but with better performance. Can i use two indexes, one for asset and one for version?

I will take a look at the link you supplied and see if it solves anything.

/Anders

steveb. SSC-Forever Points: 46733 More actions · Answer 3

Are you able to post your create index statements?

without looking at the execution plan it is hard to tell, but from the query one suggestion would be to have seperate indexes with AssetID and VersionID as the leading edge.

Also how many rows do your tables contain? with smaller tables it can be impossible to avoid a scan.

Anders Hansen Ten Centuries Points: 1211 More actions · Answer 4

I will try to generate a more complete post, but have ran into another task that i need to look at now.

I will get back with a more thorough explanation, execution plans, etc.

My first attempt to actually boil down the complete statement (which is a lot larger), gave me a diifferent execution plan than expected - so need to look a bit deeper into it myself as well.

Will get back hopefully later today.

/Anders

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 5

If it is the OR that is causing the scan, you may get a better plan by using a UNION to get rows from either side of the OR. Make sure you have indexes to cover both set of possible join columns.

SELECT a.Col1, b.Col2

FROM aTable a

INNER JOIN vTable v ON v.AssetID = A.AssetID AND v.AssetSiteID = A.AssetSiteID

INNER JOIN bTable b ON

(b.VersionID = v.VersionID AND b.VersionSiteID = v.VersionSiteID)

UNION

SELECT a.Col1, b.Col2

FROM aTable a

INNER JOIN vTable v ON v.AssetID = A.AssetID AND v.AssetSiteID = A.AssetSiteID

INNER JOIN bTable b ON

(b.AssetID = a.AssetID AND b.AssetSiteID = a.AssetSiteID)

Anders Hansen Ten Centuries Points: 1211 More actions · Answer 6

Hi all

I am sorry to leave this post open.

I have resigned from my current job, and never got back to solving this problem due to reprioritisation.

Thanks for using your time on helping me, and once again sorry for leaving the question unsolved.

/Anders