?
Solved

Query MANY times slower in VB6 app than in SQL Server Management Studio

Posted on 2011-10-14
17
Medium Priority
?
2,241 Views
Last Modified: 2012-05-12
Hello,
   We have a an existing view that when run in SSMS takes about 1 second, but running it via our VB6 application takes about 120 seconds.  Same PC & Server. It's a large query with many joins.

Database is SQL 2005.
App is vb6 using SQLOLEDB with ADO.

SQL Profiler shows VAST difference in CPU & 'reads' when run in SSMS vs when run via our app.  The below are the results of doing a select count(*) from the view with a simple where clause.

E.g. When run in the APP.
CPU:18437
READS:2124287
DURATION:82683

E.g. When run in SSMS
CPU:47
READS:8121
DURATION:50

VB App connection string has been simplified to username, password, initial catalog and server.

No other parts of the system appear top perform worse in the app vs SSMS.  There is one difference with this query though - it uses the row_number() function (10times).

The query has started performing badly since adding some joins to other tables using the row_number() function.  This lets us get 10 fields per primary row e.g. value1, value2, value3->10 for the top 10 transactions for the given row.

e.g. ID, inspection_result_01, inspection_result_02,inspection_result_03 -> inspection_result_10.

ID comes from one table, the inspection results come from another table : inspection results, listing the 1st 10 of these transactions.

I'd have though that the SQL Server (2005 on Win2k3) should perform the query in the exact same way no matter what the source.  Am I wrong about this?

Does anyone have any idea why this performs differently depending on the source?
What baffles me is that the 'reads' when profiled are so much different.  It seems it's processed differently depending on the source which seems bizarre to me.

Thanks in advance - L



0
Comment
Question by:LRX
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36967213
does it change if you put the whole query into a stored procecure?
0
 

Author Comment

by:LRX
ID: 36967318
Hi angellll - not sure yet - something I can try though.
0
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 1600 total points
ID: 36968122
Hello, what if your try with the SQL Native Client 9.0, of course if it is available on your VB application environment:
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;

Open in new window


Reference:
http://connectionstrings.com/sql-server-2005#p111
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36968247
Also you can try with the SQL Native Client 10.0 provider:
Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;

Open in new window


If you don't have it installed you can download the installer from this link:
http://download.microsoft.com/download/0/E/6/0E67502A-22B4-4C47-92D3-0D223F117190/sqlncli.msi
0
 
LVL 21

Expert Comment

by:mastoo
ID: 36969659
You can have different default connection properties.  The easiest way I know to see them is to start Sql Profiler while both connections are active.  It will show you a line for ExistingConnection's and you can verify that whether all the properties match.

The other possibility would be one of them is using a bad cached plan.  I'll suggest running "DBCC FREEPROCCACHE" to test this hypothesis, but read the BOL about this first and understand the possible effects.
0
 
LVL 11

Assisted Solution

by:kbirecki
kbirecki earned 400 total points
ID: 36971960
Yes, the same query can absolutely perform differently depending on where you call it from.  If you run it directly in SSMS, that is A) communicating with the SQL server directly, and B) it is independent from the workstation where SSMS is running.  SSMS is going to doa  good job of streaming your results back and starting to show you the results as soon as it can while still waiting for the test of the results to come streaming to the client.  Your app is dependent on whatever connection method you are using.  As yv989c suggests, the SQL native client is going to be the best, but we don't know yet how you are connecting from your VB6 app.  It might be waiting for *all* of the results before doing anything, and if you have a large resultset, that can be slow.  The connection method would be helpful to know, but that's not the first place I'd look, especially if the rest of your app runs well.

First, I'd suggest running the Execution Plan on your query and see where the query has the worst performance.  Those joins can be disastrous to performance if not properly optimized.  And even if your query runs well in SSMS, that doesn't mean they run well over the wire to a workstation even with the native SQL client as the connection.

I'm not a super expert on this, but this is something I have dealt with.  There are really two directions I target improving queries:

First, you have to identify where the poorest performance resides.  One way is to use the Execution Plan to identify and optimize your poorly performing joins, possibly with changes to indexes if you can, and/or redesign the tables or joins if necessary.  There's a trade off between continuing to add indexes to tables to help queries versus beginning to degrade performance of a table itself with regards to adds/updates/deletes if there are a lot of indexes and statistics to update.  (It's kind of an art.)  Another way to identify poorly performing parts of the query is to literally break it up into smaller parts, check those parts, and keep building it up until you run into the part(s) that exhibit the poorest performance.  Working in SSMS for all this is good to keep a common environment.  For reference, here are some of the SQL optimization links I've found helpful and saved - even if they reference a previous SQL, they are still helpful in practice.

http://www.simple-talk.com/sql/performance/graphical-execution-plans-for-simple-sql-queries/
http://www.guidanceshare.com/wiki/How_To_Optimize_SQL_Queries
http://msdn.microsoft.com/en-us/library/ms979196.aspx
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
http://technet.microsoft.com/en-us/magazine/2007.11.sqlquery.aspx

Secondly, anything that is not "set-based", like the row_number() function, is going to perform poorly.  This may be one of a number of aspects of your query that are slowing it down.  A lot of times you'll find there are different ways to achieve the same goal, but in a more optimized fashion, or different method altogether.  What are the row numbers for?  Display purposes?  Because they don't mean anything to SQL server.  If it's for display, and, for instance, the data is being displayed in a list box, since you's have to cycle through the records anyway, use a counter in your app to insert the row numbers and skip the row_number function in the query altogether.

I've found there is no magic bullet for all circumstances.  There are more things to consider that you'll only find once you dig into it.  For instance, how big is the result set?  Does the user need *all* of those records at a time?  Can you feed them in "pages".

Also, reducing the connection string to minimal elements is not necessarily helpful for performance.  Maybe you should set the connection string to read only, or Forward Only, so that the SQL server knows you're not expecting to do any updates or move backward in the recordset.  It performs better that way.

Also, (you're probably getting the idea this could go on and on, and it can) in your query, are you using the WITH NOLOCK clause?  This can dramatically improve performance with joins when you are not expecting to also update the query.

To summarize, plan to spend some time identifying manageable components of the query, post questions here on what you find and we'll be happy to help find ways to improve it piece by piece.  Posting the query itself will be the biggest help to start.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36971994
@kbirecki, nice your recommendations, only one main observation, be careful with the locking mode NOLOCK, this can give you undesirable results because your query will return uncommitted data.
0
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 36972326
If you have a query like
SELECT ...
VB can work with it in 2 ways:
(1) Row by row
(2) Bulk data set
Your time difference looks like (1). To change it to (2), which used by SSMS, you must declare DataSet and use the method "Fill" to populate result set. When DataSet is in memory on your Client Computer you can go through it row-by_row or assign it to some UI component like TableView (it will do all for you).
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 36973147
@yv989c, good point about not using nolock in certain circumstances, such as transactional environments.  For some reason, I was thinking this was not transactional, but for anyone interested, this thread discusses it pretty well.  For my situation, I'm not dealing with transactional and I have used it to dramatically improve performance, where I couldn't find other ways to improve a complex query.

And formand's comment is a good one.  It's along the same lines as what I was mentioning about using set's versus row-level iterative functions.  I've avoided this and dramatically improved certain operations in some circumstances by using temp tables (yes, I did!) in a stored procedure where data was gathered step-by-step into a final temp table, massaged a little and then the result was returned to the app.  It was far faster than building all of it into a complex query which called functions for every row.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 36974035
@LRX

Please post the VB code snippet(s) where you define and instantiate the DB connection and invoke and retrieve results from the SQL.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36974074
Also the VIEW would be good to see any know how many rows it retrieves.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36974078
Let's try that again as it does not make sense:
Also, please post the VIEW you are using, how you execute it from SSMS and VB and tell us how many rows it returns.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37282895
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 

Author Comment

by:LRX
ID: 37282896
Hello - So sorry to reply so late - I some of the comments woked - circumstances changed and I wasn't able to try any until days ago.

Thanks & all the best - L
0
 

Author Closing Comment

by:LRX
ID: 37282929
Thank you everyone.... sorry about the delay getting back to this.

yv989c: Changing to the Native Client increased performance across the board, especially with that Query.

kbirecki : Your reply helped a lot with a better understanding of how it all hangs together and how to approach some of the other issues relating to row_number & set based vs other ways.

All others, thank you for your input.  Sorry I wasn't able to post code, it just wasn't practical.

Thanks again. - LRX.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question