Query Performance Differences - Need Direction RRS feed

  • Question

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



    Friday, November 2, 2007 4:12 PM

All replies

  • Hi,

    I have a question before looking for ideas. Is it the same database u used to test performance, the same data?

    For example I had a similar problems years ago, and the problem was that wa had an image column and users played to store huge images in the database.


    Friday, November 2, 2007 4:29 PM
  • Thanks for the response.


    The tests were all run in the same production database and each user's query will return an identical set of data as it is filtered only by the accounting company code.  Each machine in question was pulling this data using the same company code filter.  The columns in the select list are all integer, varchar, and datetime data types.  There are no image ,BLOB, or any type of binary fields in the SQL table either.



    - PMR

    Friday, November 2, 2007 5:08 PM