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

Speed up cross join sql query

Hi I have a query that worked well when I did not have many products but now I have 50,000 products it takes ages to run. There are 3 tables, I use 2 tables on cross join to give me one table that I then update to the products table.

UPDATE Products 
SET 
ThumbNailImageUrl = REPLACE(dbo.GenerateFromProductName.ThumbnailImageURL, '{ProductRef}', dbo.GenerateFromImages.ImageRef + dbo.GenerateFromProductName.ImageEndRef), 
MainImageURL = REPLACE(dbo.GenerateFromProductName.[250pxImageURL], '{ProductRef}', dbo.GenerateFromImages.ImageRef + dbo.GenerateFromProductName.ImageEndRef),  
LargeImageUrl = REPLACE(dbo.GenerateFromProductName.[1000pxImageURL], '{ProductRef}', dbo.GenerateFromImages.ImageRef + dbo.GenerateFromProductName.ImageEndRef), 
XLargeImageUrl = REPLACE(dbo.GenerateFromProductName.[2000pxImageURL], '{ProductRef}', dbo.GenerateFromImages.ImageRef + dbo.GenerateFromProductName.ImageEndRef), 
ImageExtra1 = REPLACE(dbo.GenerateFromProductName.ExtraImage1, '{ProductRef}', dbo.GenerateFromImages.ImageRef + dbo.GenerateFromProductName.ImageEndRef),  
ImageExtra2 = REPLACE(dbo.GenerateFromProductName.ExtraImage2, '{ProductRef}', dbo.GenerateFromImages.ImageRef + dbo.GenerateFromProductName.ImageEndRef),  
ImageExtra3 = REPLACE(dbo.GenerateFromProductName.ExtraImage3, '{ProductRef}', dbo.GenerateFromImages.ImageRef + dbo.GenerateFromProductName.ImageEndRef),  
Dimensions = GenerateFromProductName.ProductDimensions, 
Price = GenerateFromProductName.Price, 
ItemWeight = GenerateFromProductName.ItemWeightInGrams, 
DisplayOrder = GenerateFromProductName.DisplayOrder, 
QuantityInStock = GenerateFromProductName.QuantityInStock, 
PriceUpDated = GenerateFromProductName.LastUpdated, 
LastUpDate = CASE WHEN GenerateFromImages.LastUpdated > GenerateFromProductName.LastUpdated THEN GenerateFromImages.LastUpdated ELSE GenerateFromProductName.LastUpdated END
FROM            Products CROSS JOIN
                         GenerateFromImages CROSS JOIN
                         GenerateFromProductName
WHERE        (Products.ProductRef = GenerateFromImages.ImageRef + GenerateFromProductName.ImageEndRef) and (CASE WHEN GenerateFromImages.LastUpdated > GenerateFromProductName.LastUpdated THEN GenerateFromImages.LastUpdated ELSE GenerateFromProductName.LastUpdated END > dbo.Products.LastUpDate)

Open in new window

0
taz8020
Asked:
taz8020
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
UPDATE with CROSS JOIN ?? that cannot give reliable results...

see this article to write update with joins correctly:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

very likely you want to do a regular INNER or eventually a LEFT join, but not a CROSS JOIN
0
 
taz8020Author Commented:
would I be better creating a temp table from the 2 tables with a cross join and then using the temp table with an inner join to do the update. Or would you use a select statement of the cross join in the from part of the update?
0
 
ste5anSenior DeveloperCommented:
It's in the first a design problem:

SELECT *
FROM    Products
        CROSS JOIN GenerateFromImages
        CROSS JOIN GenerateFromProductName
WHERE   ( Products.ProductRef = GenerateFromImages.ImageRef + GenerateFromProductName.ImageEndRef )

Open in new window


What a weird kind of relation are those additive reference numbers?

You need appropriate indices, then a rewrite of your date condition should work:

SELECT  *
FROM    Products
        CROSS JOIN GenerateFromImages
        CROSS JOIN GenerateFromProductName
WHERE   ( Products.ProductRef = GenerateFromImages.ImageRef + GenerateFromProductName.ImageEndRef )
        AND GenerateFromImages.LastUpdated > Products.LastUpDate
        AND GenerateFromProductName.LastUpdated > dbo.Products.LastUpDat

Open in new window


But the core problem is the amount of date generated by the cross join. Here you may use a temporary table derived from

SELECT  I.ImageRef + P.ImageEndRef ,
        I.ImageRef ,
        P.ImageEndRef ,
        CASE WHEN I.LastUpdated > P.LastUpdated THEN I.LastUpdated
             ELSE P.LastUpdated
        END LastUpdated
FROM    GenerateFromImages I
        CROSS JOIN GenerateFromProductName P;

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
taz8020Author Commented:
great thanks just one more question I just noticed I am nearly at my limit (1020mb) on internal sql express what is the cheapest version to I go for when I hit it?
0
 
ste5anSenior DeveloperCommented:
Database size limit is 10GB, so there is space left. The memory limits only mean that you execution time will suffer.

When it's about pricing, then it depends on your use-case. Often an hosted web edition (windows server + sql server web edition) is the cheapest.

When you don't need special features, then you may find Standard sufficent.

For available editions see Editions and Components of SQL Server 2014.
0
 
taz8020Author Commented:
Thank you
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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