Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

SQL Sub-query Very Slow

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
dk04
Asked:
dk04
  • 3
  • 3
1 Solution
 
David ToddSenior DBACommented:
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
 
dk04Author Commented:
The sub select runs fine on its own. What would be the best way to alias this table? I will check the indexes.
0
 
David ToddSenior DBACommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
John_VidmarCommented:
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
 
dk04Author Commented:
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
 
David ToddSenior DBACommented:
Hi John,

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

Regards
  David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now