Solved

SQL Sub-query Very Slow

Posted on 2014-02-17
7
460 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 500 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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
 

Author Comment

by:dk04
ID: 39871617
0

Featured Post

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mssql SQL query 14 46
BULK LOGGED - log full 9 25
Error in query 3 52
SQL Server - Check Constraint or NOT NULL? 11 69
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now