Solved

SQL Sub-query Very Slow

Posted on 2014-02-17
7
474 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 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
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
 

Author Comment

by:dk04
ID: 39871617
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using this function 4 54
Many to one in one row 2 48
the way to learn Microsoft BI 13 70
T-SQL: Need to trim a single leading space 7 62
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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