SqlConnection timeout but Access 2003 doesn't

[Guest]

The client had an Access 2003 form querying a SQL 2000 database. They wanted
the front-end redone in .NET. I obliged, assuming it would be simple. I used
a datagrid, and the "sql" set of ADO.NET components
(sqlConnection,sqlAdapter,sqlCommand).

When I run the query from the .NET form, it always times out - Meanwhile the
access form queries just fine (and quick too - about 2 seconds).
I tried extending the timeout on the select command but that doesnt work,
and why should I have to extend it to 9-10 seconds when the access query runs
in 2 seconds?
(Thanks in advance)

Before I end up going back to vb6 and regular ADO, does anyone have any
suggestions?

 

[Mary Chipman]

Yes, create a Profiler trace on both the Access and the .NET apps so
you can figure out why it's timing out in one and not the other.

--Mary

 

[Guest]

I dont know if I can use SQL profiler because it is a production server.
Would profiling it put a strain on the server?

Let me also add that the same query works with an OleDB data components
(OleDBDataAdapter,OleDBCommand,OleDBConnection) - so far anyway.

 

[Mary Chipman]

Yes, it will impact performance on the production server -- I'm not
suggesting you leave it there permanently. But you need to do this so
you can examine the statements that are being passed between client
and server so that you can see what the problem is. If you can
recreate the situation on a test server, that would be best, but
absent that, you don't have a lot of choices. Pick an off-peak time to
set up a test and hopefully you'll be able to see where the problem
lies.

--Mary

 

[Guest]

Thanks Mary. This is the workaround I'm going with now: I'm using Ole
components instead. I know that MSDN docs say that the SQL ADO.NET components
are optimized for communicating with SQL 7 but I'm beginning to believe this
isnt exactly true.
Remember I said the original front end was done in Access and worked mostly
fine. Well a few more things I noticed, first was that Access used Ole and
that at the same time the SQL components were timing out, the Access
components (and the Ole components) took a little longer to run the query but
they never timed out. Also Enterprise manager (all running the same query)
took a long time to query at that moment also.

I assume it is something with the SQL installation (maybe being very busy at
the time) but they are all running the same select statement.

 

[Mary Chipman]

It depends on how you are executing the query from Access. If you're
running it from a linked table in the FE, it's using ODBC. From code,
ADO/OLEDB. It also depends on the SQL statements getting passed to the
server, the indexes, etc. That's why I suggested Profiler to
troubleshoot. Your quick fix may help with this one particular query,
but you could be opening the door to other problems down the road if
you don't have an understanding of what's going on under the hood with
the different providers. Among the possible causes could be fetching
too much data, poor indexing, calling server-side functions, outer
joins or inefficient SQL syntax, and so on.

--Mary