Query performance issue - Need direction

My company has developed a VB.NET 2003 application which interfaces with the SQL Server database of another software package. We are experiencing serious performance differences at one of our client sites on a particular query that is one of the frequent queries that runs in our software which I will try to outline below.



Our database and the accounting system database are in separate instances on the same SQL Server 2005 box. Our query is a simple SELECT statement with filtering which properly utilizes the built-in indexes for that table. In their case it returns roughly 13000 records (vendor listing). There is no report of slowness regarding other queries as none of them return the volume that this particular query does.



On one offending workstation: if we run this query, it takes less than a second to get results back in SQL Management Studio's query window. This is consistent with the accounting software which takes about 2 seconds to run the query and display the results in a grid (the accounting system is Viewpoint, a leading construction accounting system written in VB6). In our system, attempting to do the same thing as the accounting system, it is taking nearly 16 seconds to retrieve and display the results. This workstation is running on WIndows XP SP2. They are experiencing this same behavior on several workstations.



At the same client site, they have identified two Windows 2000 workstations which can run the query and display the results from our system in approx. 2 seconds - which is what we would expect and is also what we experience internally on our development platform, and 12 other clients who run our software and interface with the same accounting system - regardless of OS on the workstation.



Based on the results in the query window and the results on the 2000 workstations and the success of other clients who have as many records if not more than this particular client - i don't think we have an issue with the query or the code. The results are very consistent on each workstation as well so I don't think it's a network traffic or activity issue. My gut points me at .NET Framework version differences - since the things that would affect this process are framework items - SQL Server OLEDB drivers and objects, DataGrids, Datatables and DataViews,etc.



On one of the XP workstations I had them remove .NET framework 3.0 which had no effect on the problem, but they also have v. 1.0.3705, 1.0 Hotfix (KBB886906), Framework 1.1, 1.1. Hotfix (KBB886903), Framework 2.0 with 2 security updates (KB917823 and BK922770).



Can anyone point me in a direction on things that I can check that might be different between their XP and 2000 workstations which might affect this process? I am working on some caching logic which will reduce the number of times this query is run, but I would like to understand why there is a behavioral difference.



Any help is appreciated. Please let me know if there is any other information I can provide.



Thanks.
PaulJudging from the fact that some workstations are experiencing problems while others are not, I would concentrate on the workstations experiencing the problems, rather than the query or the database server. First thing I would check is what speed and duplex settings are on a machine that performs well, and one that does not perform well. I would wager these are not the same. The reason you only see it on this one query (or a set of queries) is that you are trying to bring back 13,000 rows of data in these queries (a more significant network load than the usual < 100 rows per operation).|||The problem turned out to be an issue with a "chimney offload" setting on one of their NIC cards on the server.

Once this setting was disabled, the slowness disappeared.

See article below:
http://support.microsoft.com/kb/942861/en-us

query performance for filtering and lookup in a web-app

Hi,

I am developing a web-app with ASP.NET 2.0.

I have main query for a grid which accepts a lot of filter parameter. If the filter are set to "all" I would like to retrive all records for this condition... it has also like 6 Left joins for looking up values (long description of a key)

I tryed this query with like 50 Records and it was very quick... then I saw the production this one got like 20.000 Records and of course bigger lookup tables, too. I used my query in Enterprise Manager it took 2 minuetes (which is way to long)... Now I need to come up with a solution which faster...

The left joins should be not the issues... without the filtering I come with the 20.000 records to 5 sek. That should be valueable...

For filtering I use this trick:

...

AND

(Table1.Name = @.Value1 OR @.Value1 is NULL OR @.Value1='')

AND

(Table1.LastName = @.Value2 OR @.Value2 is NULL OR @.Value2='')

AND

(Table1.ZIPCODE = @.Value3 OR @.Value3 is NULL OR @.Value3='')

...

I don't know wheather this is the right place to post but I hope someone has some performance tips for me...

Is results in the domain of 20k rows what you expect?

If it is, then you need also find out where the most part of the time is spent - is it on the server, or is it in the asp app?
(I could imagine it would take a while to deal with 20.000 rows in the grid?)

Also, look at the query plans.
Is there proper indicies in place to support the query?
Depending on how the parameters to the query are supplied, different combinations may get suboptimal plans, depending on what other combos have been used.

When developing, one should always try to test against 'production-like' data, in quality as well as volume.
It helps in order to find 'issues' as early as possible.

...I don't think I can be more specific at this time.

/Kenneth

|||

Thanks for your answer. I think my question was not to specific, too.

I don't think that I will always have the 20k records inside the db. But what I whould like to know is how performand a query is when you have a construct like this for filtering in the where clause.

AND

(Table1.Name = @.Value1 OR @.Value1 is NULL OR @.Value1='')

AND

(Table1.LastName = @.Value2 OR @.Value2 is NULL OR @.Value2='')

AND

(Table1.ZIPCODE = @.Value3 OR @.Value3 is NULL OR @.Value3='')

It is more a basic question how whould one have to set up query which needs like 7 filter options. I would like to tackle this with one query and the TableAdapters. Otherwise I have to check in Code which values are set and build a dynamic query... But asp.net security guides says... avoid dynamic queries... so I wanna basically know a bestpractise way which is also fast =) ...

Sorry for beeing not so precise...

Regards,

P.S.: I will look at indeces, too. This might be a point to optimize...

Query Notification

Hi,
I am using .net 2.0 and sql server 2005.
Now i want to notify user Application (C# Application) about the change in certain table.
For Example: if tbl1 be any table and when insert operation is carried out in that table.
Then after 100 or multiple of 100 record insertion in tbl1 i want the notification to be given to user Application (C# Application).
Is this possible through Query Notification.?
I even dont know if this is the right forum for this question..

Please Help

It is a common mistake to confuse Query Notifications with Notification Services.

Query never ends execution

Hello

I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete.

Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @.@.rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT.

TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows.

What could be wrong?What could be wrong?
I'll guess it is one of these issues:
A) Gerbils nibbling on your network cable.
B) Global warming affecting your server environment.
C) Bears. Big nasty ones.
D) Some problem with the code you did not bother posting.|||I vote for bears. they are always on the threatdown causing trouble.|||Here is the general structure of the problematic stored procedure (Consider that there are 2 remote tables and 2 local tables instead of just one, and that the operations are made for both of them in a similar fashion):

declare @.LastUpdate datetime
declare @.Workstation varchar(250)

set @.Workstation=host_name()

SET NOCOUNT ON

execute spGetLastUpdate @.LastUpdate output

insert into Synonym_MyRemoteTable1Temp
select
@.Workstation,
ID,
Value1,
Value2,
Value3
from myLocalTable1
where UpdateTimeStamp>@.LastUpdate

execute Synonym_spMyRemoteProcedure @.Workstation -- Explained below
/*
This remote procedure makes an update and an insert as follows:

update MyRemoteTable1
set
Value1=MyRemoteTable1Temp.Value1,
Value2=MyRemoteTable1Temp.Value2,
Value3=MyRemoteTable1Temp.Value3
from MyRemoteTable1
inner join MyRemoteTable1Temp on MyRemoteTable1Temp.ID=MyRemoteTable1.ID and WorkStation=@.WorkStation

insert into MyRemoteTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from MyRemoteTable1Temp
where
WorkStation=@.WorkStation and ID not in (select ID from MyRemoteTable1)
*/

update MyLocalTable1
set
Value1=T.Value1,
Value2=T.Value2,
Value3=T.Value3,
UpdateTimeStamp=GetDate()
from MyLocalTable1
inner join
(
select
ID,
Value1,
Value2,
Value3
from Synonym_MyRemoteTable1
where UpdateTimeStamp>@.LastUpdate and
WorkStation<>@.Workstation

) as T on T.ID=MyLocalTable1.ID

insert into MyLocalTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from Synonym_MyRemoteTable1
where
UpdateTimeStamp>@.LastUpdate and
Workstation<>@.WorkStation and
ID not in (select ID from MyLocalTable1)