Form suddenly got slow and causing query timeout.

Hi Experts,

I have an Access ADP App that linked to SQL 2005 BE.

There is a continuous form based on a view that always worked fine, however suddenly it started performing slow on updates and causing query timeout for users very frequent.

Following is the SQL of the view.
SELECT     TOP (99.9999) PERCENT dbo.NCI_Calls.ID, dbo.NCI_Calls.CallID, dbo.NCI_Calls.DateEntered, dbo.NCI_Calls.Initial, dbo.NCI_Calls.Day, dbo.NCI_Calls.Commission, 
                      dbo.NCI_Calls.CallTime, dbo.NCI_Calls.FacilityID, dbo.NCI_Calls.Coordinator, dbo.NCI_Calls.CallersName, dbo.NCI_Calls.Request, dbo.NCI_Calls.FollowUp1, 
                      dbo.NCI_Calls.FollowUp1Time, dbo.NCI_Calls.FollowUp2, dbo.NCI_Calls.FollowUp2Time, dbo.NCI_Calls.NCNotes, dbo.NCI_Calls.OfficeNotes, 
                      dbo.NCI_Calls.Reviewed, dbo.NCI_Calls.ReviewedBy, dbo.NCI_Calls.ReviewedTime, dbo.NCI_Calls.Imported, dbo.NCI_Calls.DateImported, 
                      dbo.NCI_Calls.FollowUpEmps, dbo.NCI_Calls.RequestedDate, dbo.NCI_Calls.ts, dbo.NCI_Calls.CommissionInitial, ISNULL(B.FollowUpsCnt, 0) AS FollowUpsCnt, 
                      dbo.NCI_Calls.HomeCareInitial, dbo.Facilitiestbl.Name AS FacName, dbo.NCI_Calls.Notes, dbo.NCI_Calls.ConditionalFormatColor, dbo.Facilitiestbl.County, 
                      dbo.NCI_Calls.FollowUpEmpsTime, dbo.NCI_Calls.CallType, dbo.NCI_Calls.Prolucent, dbo.NCI_Calls.ProlucentFollowUp, dbo.NCI_Calls.IncomeProducing, 
                      dbo.NCI_Calls.Completed, dbo.NCI_Calls.PatientID, dbo.NCI_Calls.ProlucentFollowUpTime
FROM         dbo.NCI_Calls LEFT OUTER JOIN
                      dbo.Facilitiestbl ON dbo.NCI_Calls.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
                          (SELECT     CallID, COUNT(*) AS FollowUpsCnt
                            FROM          dbo.NCI_CallsFollowUps
                            GROUP BY CallID) AS B ON dbo.NCI_Calls.CallID = B.CallID
ORDER BY dbo.NCI_Calls.Day, dbo.NCI_Calls.CallTime

Open in new window

Any idea what is causing it and how can I resolve this issue?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
what version of SQL Server is being used here?
bfuchsAuthor Commented:
sql express 2005
PortletPaulEE Topic AdvisorCommented:
2005, thanks.

I was only going to suggest a small change but its not relevant for 2005 versions of mssql
count(*) OVER(partition by dbo.NCI_Calls.CallID) AS FollowUpsCnt

The ordering is likely to be expensive (to performance). There isn't a lot of performance tuning potential that I can see in that query.

Have you looked at an execution plan for that query?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bfuchsAuthor Commented:
Ok, just looked at the estimated execution plan and it showed 99% for the sorting.

Since the sorting is based on NCI_Calls.Day, NCI_Calls.CallTime and Day column id already indexed, what do you suggest, add another index for day+call time or just one for call time?

BTW, if this is the cause of the issue, why did it just started happening, could be that the amount of data recently added contributed to the this?
PortletPaulEE Topic AdvisorCommented:
If it happened "all of a sudden" and there has been a recent increase in rows, then the existing indexes may need maintenance anyway.

An index of (dbo.NCI_Calls.Day & dbo.NCI_Calls.CallTime) might be worth trying if you can replicate this in a dev environment; I wouldn't want to do this as a test in a prod environment.
bfuchsAuthor Commented:
Actually I just analyzed the data and didn't see any significant increase lately that should justify this.

Could this possible be due to more users queering at the same time?

What type of maintenance do you suggest to perform in regular bases? (I was told that SQL automatically re-index & shrinks the data every time a full backup is performed and we have scheduled daily backups).

Re adding an index,  is it a problem if I add something and then change/remove later in production?

(Excuse me for asking such simple questions, as I am coming from Access database where all these are accomplished by a simple compact & repair utility..)
PortletPaulEE Topic AdvisorCommented:
I'm not a DBA, but:

"I was told that SQL automatically re-index & shrinks the data every time a full backup is performed and we have scheduled daily backups"
doesn't sound right to me.

If this was the case then no utilities for index maintenance would exist, like the following:

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold winner in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.
Ola Hallengren

This conference video is a useful introduction as Ola explains his approach and answers questions: recommended video

also refer to

Alternatively others have recommended "Brent Ozar Unlimited" for a scripts on index maintenance (blitzindex).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks for sharing all these,

For that alone was worth posting this question, regardless if I end up solving this issue!
bfuchsAuthor Commented:
Hi PortletPaul,

I did create the index as you suggested and that eliminated the cost of sorting fully, see attached sqlplan.
However its still taking very long after each update, what else can I do to improve it?
(The name of file is 2008 as I did the plan using 2008 SSMS).
PortletPaulEE Topic AdvisorCommented:
I don't know how to specifically avoid that expensive eager spool (writing resultset to a temp table), and even if I could the overall speed of execution might be slower because the optimizer is choosing to do this to "optimize rewinds".
execution plan shows spooling
Try removing       TOP()      this can cause spooling
also try adding       WITH (NOLOCK)    to each table (if this is appropriate to your situation)
bfuchsAuthor Commented:
I use the top(99.9999) as I had experienced the sorting not be accurate if there are not included.

Re nolock option, I tried that and still performing slow,

Just thought of 3 other possibilities

1- remove the following part & have the front end deal with that.
                          (SELECT     CallID, COUNT(*) AS FollowUpsCnt
                            FROM          dbo.NCI_CallsFollowUps
                            GROUP BY CallID) AS B ON dbo.NCI_Calls.CallID = B.CallID

Open in new window

2- Since user mostly selects a small set of records (average 50), usually its based on date range + some other filter like Initial = 'AA', would it be faster to have a function/stored procedure accepting parameter to filter? (Will have to see if Access allows Edits on store proc or its read only, I think there are some difficulties with this).

3- Having a temp table that holds dates and always deleting and inserting dates within date range selected, then having SQL join that table.

What do you say for them?
bfuchsAuthor Commented:
Actually I ran the script of hallengren you suggested for a backup database and below is the results for the NCI_Calls table.

Command: ALTER INDEX [PK_NCI_Calls] ON [backup_homecare].[dbo].[NCI_Calls] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)
Comment: ObjectType: Table, IndexType: Clustered, ImageText: Yes, NewLOB: No, FileStream: No, AllowPageLocks: Yes, PageCount: 10174, Fragmentation: 37.2616
Outcome: Succeeded
Duration: 00:00:02
Date and time: 2015-03-10 22:47:09

Perhaps I should run that command on prod DB?
PortletPaulEE Topic AdvisorCommented:
Don't sort until AFTER filtering. i.e. why sort the entire table with joins if you are filtering by a date range anyway?

I seriously suggest you remove from both the TOP() and the ORDER BY.

you may want NOLOCK regardless of any performance impacts

c. (your 2.)
A Table-Valued Function (TVF) or similar returning ~50 rows would almost certainly be better BUT, will that be "editable" i.e. will that support the continuous form you are using (I don't know Access so I don't know the answer)

d. (your 1.)
Removing this would reduce required query effort. If however you move to a TVF or proc this might not be necessary

dates in a table, join to that table
If your table is adequately indexed for those date fields it is unlikely that putting a date range into a table will make a big difference. I'm assuming you would only need a single "from date" and a single "end date".

Please read "Beware of Between". (Best practice on date ranges is to use  a combination of >= and <)

Introducing Ola's procedures into a production environment requires planning and a maintenance window. I have never done this as I'm not a DBA and not a user of those scripts. If you believe that index in production needs maintenance then yes you could rebuild it but the time this takes may be longer (I don't know if it would be or not) and I'd expect to do that in a maintenance window.

nb: You are really asking me for advice on managing your prod environment I'm not that comfortable providing. Ola's scripts do come highly recommended by many DBA's both at EE and elsewhere. It is just that I am not a DBA and not personally acquainted with the scripts in detail.
bfuchsAuthor Commented:

Thanks for the detailed description, I just tested replacing the Top(99) with top(100) (it did that on its own when i removed the top99) and removing the the joining of that calculated table, and now its performing much better, I will probably stay with this solution, just will perform some more testing tom before finalize it.

bfuchsAuthor Commented:
Thanks PortletPaul,

The slowness got fully resolved.

I'm still having a Network_IO issue with that screen, but as long users are not complaining I am OK, when they start complain I will post another question....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.