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

x
?
Solved

SQL Sub-query Very Slow

Posted on 2014-02-17
7
Medium Priority
?
504 Views
Last Modified: 2014-02-19
I'm trying to narrow down the results with the sub-query but its very slow. I'm sure there is a better way to structure this.

SELECT rdd.StoreID, rdd.TransactionDate, rdd.tckVal, rdd.itmCode, rdd.itmName, rdd.itmID, rdd.enable, rdd.orderquantity, rdd.unidadcasepack, rdd.stockminredemption, 
                rdd.stockmin, rdd.preciounidad, rdd.preciocosto, rdd.precioreal, rdd.InitialInventory, rdd.purchased, rdd.returned, rdd.redeemed, rdd.adjusted, rdd.trIn, rdd.trOut, 
                rdd.finalInventory, rdd.tckRedeemed, rdd.vInitialInventory, rdd.vPurchased, rdd.vReturned, rdd.vRedeemed, rdd.vAdjusted, rdd.vTrIn, rdd.vTrOut, rdd.vPriceChange, 
                rdd.vFinalInventory, r.descripcionrubro, r.category, st.StoreName, P.Name


FROM            RedemptionDataDetailByDay AS rdd 
		INNER JOIN Rubro AS r ON rdd.rubro = r.idrubro AND rdd.StoreID = r.StoreID 
		INNER JOIN Providers AS P ON rdd.ProviderID = P.ID AND rdd.StoreID = P.StoreID 
                INNER JOIN FiscalDatesByDay AS fd ON rdd.TransactionDate = fd.TransactionDate 
                INNER JOIN Stores AS st ON rdd.StoreID = st.StoreID

      
WHERE			itmCode IN (SELECT itmCode FROM RedemptionDataDetailByDay WHERE (TransactionDate BETWEEN '2-10-14' AND '2-16-14')  AND (adjusted <> 0))
				AND (rdd.TransactionDate BETWEEN '1-10-14' AND '1-16-14')
				AND rdd.StoreID = 1 
				

Open in new window

0
Comment
Question by:dk04
[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
  • 3
7 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 39866572
Hi,

Does the sub-select on its own run okay?

SELECT itmCode FROM RedemptionDataDetailByDay WHERE (TransactionDate BETWEEN '2-10-14' AND '2-16-14')  AND (adjusted <> 0)

Strongly suggest aliasing this table.

Is there an index on TransactionDate - and indexes and statistics maintained?

The not equals on adjusted means that the query is non-sargable, and so this means a potential table scan instead of range index scans.

HTH
  David
0
 

Author Comment

by:dk04
ID: 39867314
The sub select runs fine on its own. What would be the best way to alias this table? I will check the indexes.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39868242
Hi,

I often add I to the alias for inner as in rddi instead of the rdd as used in the outer query

Try this for the where clause
WHERE			
	rdd.itmCode IN (
		SELECT rddi.itmCode 
		FROM dbo.RedemptionDataDetailByDay rddi
		WHERE 
			(rddi.TransactionDate BETWEEN '2014-02-10' AND '2014-02-16')  
			AND (rddi.adjusted <> 0)
		)
	AND (rdd.TransactionDate BETWEEN '2014-01-10' AND '2014-01-16')
	AND rdd.StoreID = 1 

Open in new window


Can you post the execution plan for this query?

Regards
  David
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39870198
There is no correlated sub-query, so it only runs once.  The RedemptionDataDetailByDay table/view sounds like it may have large data-volume, the sub-query may produce many duplicate records which may be an issue (maybe adding distinct to the sub-query would help).
0
 

Author Comment

by:dk04
ID: 39870499
David,

I tried the alias and it didn't seem to help. I eventually have to cancel the query because it just keeps spinning. I ended up creating a stored procedure and temp table and I have the information now. My next issue is passing multi-value parameters from SSRS to the stored  procedure. I will post that on a different forum. Thanks for your help.

John,

Thanks for the reply as well.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39871015
Hi John,

Can you post a link to your next question ...

Regards
  David
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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