SQL Query linked to Access Performance Tuning: ASYNC_NETWORK_IO

Hi Experts,

I have an Access application (linked to SQL database) that contains a form with 4 combo boxes, each one has is own record source as follows.

#1
SELECT Patients.ID, [LastName] & " " & [FirstName] AS Name, Patients.Address, Patients.HomePhone FROM Patients ORDER BY [LastName] & " " & [FirstName];

Open in new window

#2
SELECT Patients.ID, [FirstName] & " " & [LastName] AS Name, Patients.Address, Patients.HomePhone FROM Patients ORDER BY [FirstName] & " " & [LastName];

Open in new window

#3
SELECT Patients.ID, Patients.Address, [FirstName] + ' ' + [LastName] AS Name, Patients.HomePhone
FROM Patients
ORDER BY Patients.Address;

Open in new window

#4
SELECT PatientsPhoneUnionQry.ID, PatientsPhoneUnionQry.HomePhone, PatientsPhoneUnionQry.Expr1 FROM PatientsPhoneUnionQry ORDER BY PatientsPhoneUnionQry.HomePhone;

Open in new window


Now when looking at the SSMS Activity Monitor I realized that query #3 is the most expensive one, and is also the one always causing ASYNC_NETWORK_IO, I am wondering why is it that way and what can I do to improve it?

FYI- Currently the table only has a PK clustered index on the ID column, I guess additional indexes would be required, just want to know which one's do you advise.
LVL 5
bfuchsAsked:
Who is Participating?
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.

PortletPaulfreelancerCommented:
You could incur the wrath of another expert here who particularly likes to dispel the myth that clustered indexes should be on the identity field. But, I don't know if that is the issue here.

I would guess that a non-clustered index on Patients.Address ASC would assist that order by clause.

Have you looked at any execution plans?
0
bfuchsAuthor Commented:
@PortletPaul,
Attached are the estimated execution plans.
Query1.sqlplan
Query2.sqlplan
Query3.sqlplan
0
PortletPaulfreelancerCommented:
:) thank you, just wondered if you had looked
I looked at query3's it didn't show a great deal, I would try an non-clustered index on Patients.Address ASC  
(to suit that ORDER BY)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Since you apply no restriction to the query, there isn't much you can do. An additional index might fast used or not, as the table needs to be read completely anyway.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't do much but create a clustered index on Patients.Address and then remove the ORDER BY clause since the records would be physically ordered by that column.
Of course since you can't have 2 clustered indexes in the same table you need to recreate the PK as non-clustered index.
0
PortletPaulfreelancerCommented:
But I would not do that, change the clustered index, to an Address field just for this one query. Could have major impacts to other parts of  the application.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You're right PortletPaul.  It's looks that the author is only testing his queries and I just wanted to give him a view on how that query could be improved.
I personally avoid the use of the ORDER BY but isn't always possible.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
How big is the Address column, and is this sort really necessary?  32% of total query time is an expensive sort.

>But I would not do that, change the clustered index, to an Address field just for this one query. Could have major impacts to other parts of  the application.
Completely agree with Paul.  Other processes could be severely impacted here.

What's the availability needs of this address data:  real-time, daily, ?
If daily, perhaps a workaround would be to run a job every day to insert all address data into a separate table, with clustering/indexing optimized for these queries to search on address.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also, a typical tactic I see in Access is to not bound the combo box to a record source right away, then wait for the first three characters to be typed.  Once that happens, bound the combo box to the query, with a WHERE ADDRESS LIKE ' & Me.your_address_field.Value = '%'.  That way the filter greatly reduces the query processing and number of rows that travel between SQL and Access.
0
bfuchsAuthor Commented:
Hi Experts,

@Jim,
'Interesting SQL issue' in the SQL zone does not say anything helpful as to what the question is
Let me explain what I meant first when posting this, when comparing #3 to the other sorting statements that are performing well, I found it interesting that its more optimized to sort by a computed column (LastName + ' ' + FirstName) than by an original column, although none of them are indexed.

FYI- Address column is varchar(150)

perhaps a workaround would be to run a job every day to insert all address data into a separate table
I have done this with large tables, however this is not that large, wonder if its necessary to do that.
wait for the first three characters to be typed.  Once that happens, bound the combo box to the query
This is something they constantly use, I'm concerned it would by annoying for users having to wait in middle their typing in order to get the drop down filled.

@PortletPaul,
I looked at that just didn't see a diff between them as all were showing 33% for sorting, therefore I thought your experts would know better how to analyze it.

(Perhaps I can run the script that Scott had suggested in previous post and you can tell me what is suggested according to how table is being queried by users in general?)
See below
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28612852.html

@Vitor Montalvão, Qlemo,
I use it as a look-up for users to bring up a record, in order to find as you type I need it sorted, and with no restrictions (unless I deploy the tactic Jim is suggesting).

Thanks,
Ben
0
PortletPaulfreelancerCommented:
I'm not familiar with using that script by Scott Pletcher but it would be useful to see if missing indexes are suggested.

Scott is a way better judge on indexes than I am by the way.
0
Anthony PerkinsCommented:
Now when looking at the SSMS Activity Monitor I realized that query #3 is the most expensive one, and is also the one always causing ASYNC_NETWORK_IO,
Simply put this wait state means that SQL Server is waiting on the client to finish processing the data,  This could be a network problem, but more than likely it is just simply waiting on MS Access.  So if that is really the case no amount of SQL Server optimization is going to help you here.
0
bfuchsAuthor Commented:
@PortletPaul,
I am attaching the results of the script, lets see if someone here can help with this.

@Anthony Perkins,
Regardless of what you suspect the cause of the problem, either network related or its nature of Access, I am still wondering why is this different than the other queries thatdon't have this issue, and this is working well by the others I guess it must be a way to make it work by #3 as well..

Thanks,
Ben
first.csv
second.csv
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I use it as a look-up for users to bring up a record, in order to find as you type I need it sorted
Can't you sort in the client side instead use SQL Server to bring the records sorted?
0
bfuchsAuthor Commented:
@Vitor,
I tried already using a pass-through query where the workload is done at the server, also tried creating a view and linking to it, so far nothing helped.
0
bfuchsAuthor Commented:
Simply put this wait state means that SQL Server is waiting on the client to finish processing the data,  This could be a network problem, but more than likely it is just simply waiting on MS Access
I thought of an idea, how about add some code to force the drop-down to go till the end of the list as soon as I open the form?
Is someone familiar with that code?
0
Anthony PerkinsCommented:
How about using a drop down that has a reasonable number of entries.  By reasonable, I mean less than 100.  Besides the performance problem loading thousands of rows into a drop down is (and don't take this the wrong way) pointless.
0
bfuchsAuthor Commented:
@Anthony,
I'm out of the office those days, when I return will give that a try and see what users has to say about it.
Thanks,
Ben
0
bfuchsAuthor Commented:
Hi Experts,

I added an index for the address column but the same network_io still appears on SSMS.

Do someone has an idea what could possible be the difference between sorting by last + First names and sorting by address field?

I think the answer to this will be a key of solving this problem.
0
bfuchsAuthor Commented:
For testing purpose I removed the sorting and saw that the IO issue remains there...see attached.

Besides I realized that the SQL command being sent to the server is being modified from what its programmed, as I select first the ID, Address, Name, while in SQL its showing ID, Name, Address...included in attachment.
Perhaps something is corrupted in my Access application:-(!
Untitled.png
0
bfuchsAuthor Commented:
Hi Experts,

I might have a solution to this problem.
Tried to change the sql for the following and now the network_io is gone.
SELECT DISTINCT Patients.ID, Patients.Address, Patients.LastName, Patients.FirstName FROM Patients ORDER BY Patients.Address;

Open in new window

0
PortletPaulfreelancerCommented:
Adding "DISTINCT" improved performance! (that indicates a reduction of data volume)

What is the result of the following query please?

select count(*) from (
       select Patients.ID from FROM Patients GROUP BY Patients.ID HAVING COUNT(*) > 1
       ) derived;
0
bfuchsAuthor Commented:
@PortletPaul,

Results is zero.

It does not mean it increased performance immediately, what it actually did was forced SQL to send all records at once, instead of keeping the request on hold for a long period of time, and this will in the long run improve performance in general.

Well, this is my guess perhaps you experts know better how Access interacts with SQL and suggest something better to tweak the system.
0
PortletPaulfreelancerCommented:
I have no answer to this,
but I find it rather hard to believe that adding distinct (when every row is already uniquely identified)  makes a difference.

Could be less traffic in the network but that is pure conjecture.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The data may also be already in cache.
0

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
Anthony PerkinsCommented:
The data may also be already in cache.
That is the only plausible explanation.
0
bfuchsAuthor Commented:
Hi Experts,

It turned to be that the distinct didn't always worked, see attached.
I guess will have to try what Jim suggested to restrict values according to what users type in first, and will see how my users accepts it.

However I will really appreciate if someone can figure out the main question originally posted, why is different  query #3 from the other queries, that all are selecting the same qty of records and only at #3 is it causing network_io?

@Vitor, Anthony,
The cache you're mentioning is an explanation to the distinct, correct?

Thanks,
Ben
Untitled.png
0
bfuchsAuthor Commented:
FYI- When I checked in SSMS for recent expensive queries, and I sort by duration DESC this query comes up on top of the list.

Also what I find it strange  is the fact that in execution plan it was showing 33% for sorting before, and that didn't change even though I added an index on the address field.
0
Anthony PerkinsCommented:
The cache you're mentioning is an explanation to the distinct, correct?
It would explain why it is sometimes fast.  The plan is cached so the second time you run a query it is a lot faster.

Also what I find it strange  is the fact that in execution plan it was showing 33% for sorting before, and that didn't change even though I added an index on the address field.
I would suggest you post the schema (CREATE TABLE)  as it stands now.  As well as the Actual Execution Plan (not the Estimated Execution Plan).
0
bfuchsAuthor Commented:
@Anthony,
It would explain why it is sometimes fast.  The plan is cached so the second time you run a query it is a lot faste
Actually this just strengthens my question , why is this query performing slow every time, if all users are constantly running it, as it gets to run as soon as they open the main screen of the app?

Attached the files.
Pat.sql
patAdd.sqlplan
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your table has more than 100 columns and only an index?

([FirstName] + [LastName] = [varchar](100) ) * 6241 rows = 624100 bytes
([Address] [varchar](150)) * 6241 rows = 624100 bytes = 936150 bytes
This can justify why is faster to sort by the name than the address.

Without a WHERE clause and no more indexes it will be hard to improve those queries. Only as I suggested before by created a clustered index on the ORDER BY column but as mentioned by others experts you need to be careful with the impact to other queries and specially on the application.
0
bfuchsAuthor Commented:
@Vitor,

Actually how about if I only select the first 100 characters of the address field, do you think that would help?

Also as mentioned, I created a non clustered index on the address field, not sure why that does not show in the create table script.

BTW, how do you know the record count of the table?
0
bfuchsAuthor Commented:
FYI- I just tried selecting the first 75 characters and got the same network issue, see attached.
Untitled.png
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Actually how about if I only select the first 100 characters of the address field, do you think that would help?
I think it will be worst since you'll apply a function on every rows for that column.

BTW, how do you know the record count of the table?
It's in the execution plan you posted.
0
Anthony PerkinsCommented:
I created a non clustered index on the address field, not sure why that does not show in the create table script.
Tools | SQL Server Object Explorer | Scripting | Script indexes (True)  
Restart SSMS
0
Anthony PerkinsCommented:
BTW, how do you know the record count of the table?
...
It's in the execution plan you posted.

If it is not roughly 6241 rows than that could also be a problem.

If there is an index on Address, as you can see from the query plan, it is not using it.

But I gather that you or your users are bound and determined to load thousands of rows into a drop down box.  If the answer is yes, then I am going to have to wish you luck, as that is a recipe for lack of usability.
0
bfuchsAuthor Commented:
@Anthony,

As you can see attached indeed there is an index on address field.
What could be the reason its not using it?
Is there a way I can force SQL to use that index?

Re limiting the qty of records, you're right however I am still concerned how are users going to accept the fact that in middle their typing it stops responding for a moment while the drop down is being populated.
Untitled.png
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try to include Patients.LastName and Patients.FirstName in the index and I think the engine may use it.
0
Anthony PerkinsCommented:
Try to include Patients.LastName and Patients.FirstName in the index and I think the engine may use it.
Rather than that use a covering index as in
CREATE NONCLUSTERED INDEX IX_PatientsAddress ON dbo.Patients (Address) INCLUDE ( ID, Firstname, LastName, HomePhone)

Open in new window

0
bfuchsAuthor Commented:
OK Guys,

We are getting somewhere-:)

I did what you suggested and sql plan looks OK now. (See attached)

However I still have this network_io issue with that query.

Actually I thought that Vitor/Anthony might be right with that assumption that data is already in cache for other queries, as most of the time, users pull up a record by name rather then by address, and since by actually using the drop down, it causes SQL to render all records to Access so this causes all data to be in cache, as opposed to Address that is used occasionally, and therefore most of the time all data keeps stranded on the network pipe..

The only thing that still wonders me is the reason why drop down #4 is not having this issue, in fact its not even showing in SSMS activity monitor anything for that query
?!!, although that gets open along with all the other drop downs.

Below is the SQL for #4
Query name is PatientsPhoneUnionQry
SELECT Patients.ID, Patients.HomePhone, "Home" AS Expr1
FROM Patients
WHERE (((Patients.HomePhone) Is Not Null))
union all
SELECT Patients.ID, Patients.WorkPhone, "Work" AS Expr1
FROM Patients
WHERE (((Patients.WorkPhone) Is Not Null))
union all
SELECT Patients.ID, Patients.EmergencyContactPhone, "Emerg 1" AS Expr1
FROM Patients
WHERE (((Patients.EmergencyContactPhone) Is Not Null))
UNION ALL SELECT Patients.ID, Patients.EmergencyContact2Phone, "Emerg 2" AS Expr1
FROM Patients
WHERE (((Patients.EmergencyContact2Phone) Is Not Null));

Open in new window

then the drop down's record source is
SELECT PatientsPhoneUnionQry.ID, PatientsPhoneUnionQry.HomePhone, PatientsPhoneUnionQry.Expr1 FROM PatientsPhoneUnionQry ORDER BY PatientsPhoneUnionQry.HomePhone;

Open in new window


Thanks,
Ben
patAdd.sqlplan
0
bfuchsAuthor Commented:
Hi Experts,

You will not believe but this is what I figure out,

That if I include where Patients.Address is not null, then its not showing up in activity monitor, although only 71 records do not have address filled in.

What maid me think of it was the fact that #4 also have this condition.

Will do some more testing tom when users are in.

P.S. I believe this works in conjunction with the new index you suggested, as I had already tried that previously and it didn't work.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Rather than that use a covering index as in
Anthony, that's what I wanted to say. Just used the word include because it is what is used in the command but yes, technically is a covered index.
0
bfuchsAuthor Commented:
Works great, problem is gone!!
Thanks to all participants.
This just proves not to give up, in the end the hassle pays off..
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.