Strange SQL Query Results

Epicor ERP 10
1

So we have an external BAQ that calls a view in another database. The view queries the Epicor database and performs the logic on how we process sales attributed to a sales rep quota.

The BAQ and even the view have been running fine for many months until the last couple of weeks. It started to take a really long time to finish but only under certain queries.

Here is the query used against the view (GRC_RepCreditTowardQuota). I have multiple where clauses listed with the time it takes to process each with how many rows are returned. What i can’t seem to understand nor know how to fix is why it is taking so much longer to query for dates less than 12 months hold. Querying for older data returns results faster than querying for newer data. ??? Even when the same results are returned.

SELECT [Company]
      ,[OrderNum]
      ,[OrderDate]
      ,[SalesRepCode]
      ,[SalesRepName]
      ,[RepGrpID]
      ,[RepGrp]
      ,[RepGroupEmail]
      ,[Total]
  FROM [Epicor10Reporting].[dbo].[GRC_RepCreditTowardQuota]  --6 sec, 60,415 rows
 --where RepGrpID = 'HS-MdSouth' and OrderDate >= DateAdd(month, -12, GETDATE())  --0 secs, 200 rows
 --where RepGrpID = 'HS-MdSouth' and OrderDate >= DateAdd(month, -11, GETDATE())  --63  secs, 177 rows
 --where RepGrpID = 'HS-MdSouth' and OrderDate >= DateAdd(month, -3, GETDATE())  --20  secs, 9 rows
 --where RepGrpID = 'HS-MdSouth' and OrderDate >= '03/30/2020'  --0 sec, 200 rows
 --where RepGrpID = 'HS-MdSouth' and OrderDate >= '2020/03/30'  --0 sec, 200 rows
 --where RepGrpID = 'HS-MdSouth' and OrderDate >= '2021/03/01'	--8 sec, 0 rows

Any suggestions on what we can try and do or what is going on? A month ago everyone one of these where clauses took about 0 secs.

2

Try Index Rebuilding,
Check the corresponding tables under this view and find out their Fragmentation. If such tables are fragmented more then or near by 30 %,Yes Index Rebiuilding is due.

1 Like
3

We have Index Rebuilding on a maintenance plan and it doesn’t seem to be changing the outcome. We are going to manually trigger some reindexing today to see if it changes anything.

1 Like
4

You may want to throw an OPTION(RECOMPILE) in there if you’re going to use the DateAdd.

5

I think the issue is with the GetDate() being a datetime.

Maybe try cast(GetDate() as date) ??

Or even better define a start and end variable at the beginning and assign them as your date range?

6

Running the native reindexing task from SSMS didn’t seem to change the outcome however, we used the scripts from Ola Hallengren (https://ola.hallengren.com/) to reindex the tables and it worked great. Now each variation of the where clause takes 0 secs.

2 Likes
7

Nice @jdewitt6029!

8

Don’t you consider my post as a solution ? :roll_eyes:

1 Like
9

Olga’s solution is great! I’ve been using and learning from it for ages. I found it while dealing with a similar situation a long time ago. Olga’s solution deals with tons of common bulk-rebuild problems by only working on what needs work. Full blown bulk rebuilds dump statistics and stored plans, and this solution does a good job of identifying how performance artifacts can be retained. The nightly maintenance routine runs much faster, and queries aren’t sluggish every morning.

10

That’s cool that you are also using it! What version are you on?

There are many times where I will be warned about indexing the database. I am not sure if it is only in the context of adding new indexes?

Does Olga’s script add indexes? Could this be potentially dangerous?

11

I’m working with a cloud customer these days, so I guess I’m currently between versions. My own roadblock was from a different ERP’s odd database choices. Fill factor was all over the place from 100% to as low as 30 (!!!). Mainly, some badly designed foreign keys and indexed views caused a full blown rebuild of one index to shuffle other indexes. Rebuild A, now B is 95% fragmented. Rebuild B, now A is fragmented… Ola’s generosity with this script saved me from pulling out what remains of my hair.

Ola is highly regarded in the SQL community and his work is considered trustworthy, but more importantly, that script is in plain text so it’s easy to verify. It’s painless to control-f for “create index” and find out if it creates indexes.

Figuring out the pros and cons of indexes and sql server optimization in general is a deeeeep rabbit hole. For technical stuff like this, sqlservercentral.com and Brent Ozar have both been great resources for me over the years. PASS.org as well, but alas, they are no more.

1 Like
12

One love, John, thank you for the info.