Solved

Speed up cross join sql query

Posted on 2015-01-21
6
520 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 33

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 33

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 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