Application massively slower than QA script

[Jakkels]

We have a query (a few actually) which runs for about 30 secs via Siebel 6 and the same query takes on 1 or 2 secs in Query Analyser, consistently. Naturally this performance problem is causing issues. We're wondering if the App isn't using the same execution plan, or using the indexes or....
It might be worth noting that the query returns 1 or no rows.
(I can add the query and more detail if anyone really, really wants )

The devlopers have created a VB app which mimmics to app running the query and we've put it through proflier, results..
Duration Reads CPU
Siebel Query 28300 3661280 23984

It seems a high number of reads there, and the result from QA is SO much faster.
Any ideas welcome, thanks.

 

[ClayG]

You might think you are duplicating the query in QA ..
Use Profiler to capture the sourcecode of the query as it comes from the application.

At a wild guess, could the application be passing Unicode strings to compare to ansi strings ?

 

[ArtieChoke]

I've seen similar results where the query runs very quickly in QA and takes forever in VB w/ ADO. We found out it was an order by clause that didn't have indexes on all the fields. I suspect ADO/ODBC/OLE doesn't necessarily do things the same way as the server does...

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Jakkels]

Thanks for the responses chaps. Will have a look at capturing the coding in profiler

 

[SQLSister]

You should consider using stored procedures instead of putting the code inthe application. Generally they process faster and SQL server can reuse the execution plan saving processing power.

Also consider if your code in general is efficient even in query anaylzer 1-2 seconds is a long time unless you have a very large database. Are you using cursors? Id so then you need to replace them to improve performance. Also are you asking fro more data than you need? That too can cause extra load onthe server and network slowing things down. I recently found a part of our code which was running slow and by removing the loop and limiting the records, I changed the time to process the stored procedure from 45 seconds to less than a millisecond.