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.
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
Microsoft AccessMicrosoft SQL ServerSQL
Last Comment
bfuchs
what version of SQL Server is being used here?
ASKER
Hi,
sql express 2005
sql express 2005
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?
(
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?
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?
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?
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.
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.
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..)
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for sharing all these,
For that alone was worth posting this question, regardless if I end up solving this issue!
For that alone was worth posting this question, regardless if I end up solving this issue!
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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?
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
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?
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].[N
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?
Command: ALTER INDEX [PK_NCI_Calls] ON [backup_homecare].[dbo].[N
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
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....
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....