• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Using composite fields in a JOIN

I'm using 2 fields for this JOIN and it runs a little slow :

UPDATE Nop_ProductVariant
SET OldPrice = recommended_price, ProductCost = cost, . . .
FROM Nop_ProductVariant  

INNER JOIN TopDawg ON  
 ((Nop_ProductVariant.ManufacturerPartNumber) + (CONVERT(nvarchar(50),Nop_ProductVariant.DownloadActivationType))) =
 (TopDawg.product_code + TopDawg.PID)

WHERE UnlimitedDownloads = 1


Is this ok or is there a better way of doing this?
Would taking there WHERE clause out help?      Thanks
0
MikeMCSD
Asked:
MikeMCSD
4 Solutions
 
dougaugCommented:
What indexes do you have on tables Nop_ProductVariant  and TopDawg?
0
 
APoPhySptCommented:
On SQL server management studio hit Display Estimated Execution Plan to see it sugests any index...

 Estimated execution plan button
But, by the looks of it, the Sums in the join clause don't seem a very good ideia...
0
 
Scott PletcherSenior DBACommented:
It's better to compare each column separately using AND rather than concatenate:

INNER JOIN TopDawg ON
    Nop_ProductVariant.ManufacturerPartNumber = TopDawg.product_code AND
    Nop_ProductVariant.DownloadActivationType = TopDawg.PID

Forcing concatenation will really slow SQL down a lot.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Scott PletcherSenior DBACommented:
One of the reasons is that concatenation will prevent the use of existing indexes, even if they would normally be used.  The style above, with direct column comparisons joined by an AND, will allow existing indexes to be used, if applicable.
0
 
lluddenCommented:
Any time you do a calculation like that in your WHERE clause, SQL performs at least one table scan.  If you you cannot join on the individual columns, then you should make a persisted computed column on the two fields, and index it.  That will allow for indexed lookups on your tables.
0
 
MikeMCSDAuthor Commented:
thanks all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now