Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Sub-query Very Slow

Posted on 2014-02-17
7
Medium Priority
?
520 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore 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 Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

579 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