Query MANY times slower in VB6 app than in SQL Server Management Studio
Posted on 2011-10-14
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.
E.g. When run in SSMS
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