Solved

Speed up cross join sql query

Posted on 2015-01-21
6
560 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 34

Accepted Solution

by:
ste5an earned 500 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 34

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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