asked on

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?

PortletPaul

what version of SQL Server is being used here?

bfuchs

ASKER

Hi,
sql express 2005

PortletPaul

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?

bfuchs

ASKER

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?

PortletPaul

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.

bfuchs

ASKER

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..)

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

Thanks for sharing all these,

For that alone was worth posting this question, regardless if I end up solving this issue!

bfuchs

ASKER

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).
2008.sqlplan

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

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.

LEFT OUTER JOIN
                          (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?

bfuchs

ASKER

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?

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

Hi,

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.

Thanks,
Ben

bfuchs

ASKER

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....