MS SQL Server 2012 query speed / optimization

Question

First, I apologize if this has been answered elsewhere, but I was unable to find anything today. If it has been answered, the lack is me, not the search system.

I am having an issue where in a stored procedure works fairly quickly until I get to a specific point.
I have a database of POS sales data for a restaurant chain. Among the various tables we have, the ones I need for this query are:

Item (the definitions of the various items; each row includes the SALES category the item belongs to; see caveats below)
Category (the definitions of the various categories items can be in)
CategoryItem (the mapping between the above)
HstItem (the historical sales of the items)

Caveats: There are 2 types of categories each item can be in:

sales categories: each item can be in one sales category at a time.
reporting categories: each item can be in a arbitrary number of these categories simultaneously.

I am needing to get sales totals for 6 date ranges for a specific REPORTING category (week to date, wtd ly, period to date, ptd ly, year to date, ytd ly).
All of that said, for all of my code the query / procedure runs in a decent amount of time, until to the section for the reporting category.
My select statement currently includes the following WHERE clause:

where hstitem.itemid in (select fkitemid from categoryitem where categoryitemid = ##)

I am looking for a more efficient / faster way to execute this.

Any assistance is greatly appreciated. Thanks in advance.

EDIT:

The full original query is as follows:

insert into #GnG (GStoreID, CurWkGNG, CurWkGNGLY,CurYTDGNG,CurYTDGNGLY,CurrPTDGNG,CurrPTDGNGLY)
select 
    hgi.FKStoreId, 
    CurWkGnG = sum(case when hgi.DateOfBusiness between @SDate and @EDate then hgi.price else 0 end),
    CurWkGnGLY = sum(case when hgi.DateOfBusiness between @SDateLY and @EDateLY then hgi.price else 0 end),
    CurYTDGnG = 
        case
            when convert(varchar(10),opendate,126) between convert(varchar(10),@LYTDStart,126) and convert(varchar(10),@LYTDEnd,126) then sum(case when hgi.DateOfBusiness between DATEADD(day, (DATEPART(week, opendate) * 7 + DATEPART(weekday, opendate)) - (DATEPART(week, DATEADD(year, 1, opendate)) * 7 + DATEPART(weekday, DATEADD(year, 1, opendate))), DATEADD(year, 1, opendate)) and @CYTDEnd then hgi.price else 0 end)
            else sum(case when hgi.DateOfBusiness between @CYTDStart and @CYTDEnd then hgi.price else 0 end)
        end,
    CurYTDGnGLY = sum(case when hgi.DateOfBusiness between @LYTDStart and @LYTDEnd then hgi.price else 0 end),
    CurrPTDGnG = sum(case when hgi.DateOfBusiness between @CurrPtDStart and @CurrPtDEnd then hgi.price else 0 end),
    CurrPTDGnGLY = sum(case when hgi.DateOfBusiness between @CurrPtDLYStart and @CurrPtDlyEnd then hgi.price else 0 end)
from hstGndItem hgi
join #StoresIncluded si
on hgi.FKStoreID = si.StoreID
where hgi.fkitemid in 
    (select fkitemid from categoryitem where categoryitemid = 25)
group by hgi.fkstoreid, opendate, comping
order by hgi.fkstoreid

Thanks for putting the query in your question. Did any of the suggested answers helped you? You could use what is shown in [this question](http://stackoverflow.com/questions/11675077/measure-the-time-it-takes-to-execute-a-t-sql-query) for tracking your query times. — John Odom, Aug 17 '16 at 15:18
I apologize. It has been hectic here at the office. I did try converting the subquery in the where to a join, and there was no appreciable difference in how long this part of the stored procedure ran. the code in the above edit takes approx 8 min to run for my 102 live stores. — Don, Aug 17 '16 at 19:30
It's cool, what about the change where instead of an `IN` you use `WHERE EXISTS`? — John Odom, Aug 17 '16 at 21:14
The issue with using where exists is that it only matches the first instance. As the need is to get the full sales amount of all items in the category, that is unfortunately not feasible. — Don, Aug 18 '16 at 01:45

score 1 · Answer 1 · answered Aug 12 '16 at 18:56

1

Try converting the "IN" to a inner join like so :

FROM hstitem h inner join categoryitem c on c.fkitemid = h.itemid 
where c.categoryitemid = ##

answered Aug 12 '16 at 18:56

objectNotFound

1,683
2
18
25

score 0 · Answer 2 · answered Aug 12 '16 at 18:59

You can use WHERE EXISTS instead of IN to check if the ID exists in the table:

WHERE EXISTS
(
    SELECT ci.fkitemid
    FROM categoryitem ci
    WHERE ci.categoryitemid = ## AND ci.fkitemid = hstitem.itemid
)

The difference between the IN clause and using EXISTS is that the sub-query inside the WHERE EXISTS will exit prematurely after a match have been found while the IN clause would wait until the sub-query is finished.

score 0 · Accepted Answer · answered Sep 28 '16 at 16:43

I apologize for the lag in my response here. I found AN answer. Dont know if it is the right one or not, but it works for us. I removed the code to use a sub select from the where, and am now generating a new table to hold the values that should pull. The new code to populate the table runs each morning around 0600. I then am having the main code simply join to that table to pull the single answer, rather than perform math based on the sub-query.

Thank you all for the suggestions.

MS SQL Server 2012 query speed / optimization

3 Answers3