Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Speed up cross join sql query

Posted on 2015-01-21
6
Medium Priority
?
704 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 35

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 35

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

721 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