CODEWITHSUNDEEP
sql-server-2012
Don
Don

Reputation: 31

MS SQL Server 2012 query speed / optimization

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

Upvotes: 1

Views: 85

Answers (3)

Don
Don

Reputation: 31

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.

Upvotes: 0

John Odom
John Odom

Reputation: 1223

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.

Upvotes: 0

objectNotFound
objectNotFound

Reputation: 1783

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 = ##

Upvotes: 1

Related Questions