I ran a query in SQL Query Analyzer and the query took less then a
second to return the results.

I run the same query from within a VB Application, and the query takes
close to 10 seconds. This is unacceptable. I am wondering why the
response is so slowed running from within VB App. I have pasted my
connection strings and query below to help you possibly troubleshoot
why this query is taking so long from vb, but not sql query analyzer.

BTW, both of these queries are being run remotely from the same client
computer.. So the speed difference should have nothing to do with
that..

**** VB CODE *****

Dim wrkODBC As DAO.Workspace
Dim conPubs As DAO.Connection

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "",
dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("Connection1", , ,
"ODBC;DATABASE=databasename;UID=sa;PWD=pass;DSN=server")

sqlstring = "SELECT distinct AnalysisMatrix.SpecificMethod,
AnalysisAnalyte.Analyte, BatchAnalysis.Batch, BatchAnalysis.Analysis "
sqlstring = sqlstring & "FROM (BatchAnalysis LEFT JOIN AnalysisMatrix
ON BatchAnalysis.Analysis = AnalysisMatrix.Analysis) LEFT JOIN
AnalysisAnalyte ON AnalysisMatrix.Analysis = AnalysisAnalyte.Analysis
"
sqlstring = sqlstring & "WHERE (((AnalysisMatrix.SpecificMethod)='EPA
624'))"

Set rstGetWorkOrders = conPubs.OpenRecordset(sqlstring, dbOpenDynamic)

**** END ******

Thanks, Jim....

Jim,
just a hint that may be helpful. The difference in speed is due to the fact
that when you execute the query from VB your request is going through 2 more
layers - Jet and ODBC - before it gets serviced by SQL Server. Query
Analyzer is a direct connection to SQL Server without those additional
layers, that's why it is much faster. One thing you could try is connecting
to your SQL server via MS Access and experimenting with the query. If you
can optimize the query to run faster in Access, you will get the same
response time in your VB app.

--
***************************************************************
Gagik A.
Have more than 1 VBP to compile? - Try Visual Make
http://www.visualmake.com
***************************************************************