Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Speed up cross join sql query

Posted on 2015-01-21
6
Medium Priority
?
763 Views
Last Modified: 2015-01-21
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
Comment
Question by:taz8020
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40561739
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
 
LVL 3

Author Comment

by:taz8020
ID: 40561812
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
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40561830
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 3

Author Comment

by:taz8020
ID: 40562026
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
 
LVL 36

Expert Comment

by:ste5an
ID: 40562059
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
 
LVL 3

Author Closing Comment

by:taz8020
ID: 40562521
Thank you
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question