ADO Vs ADO.Net

Question

we have a sql query, when we are connecting from a front end to the SQL Server using ADO.Net it is faster, however, if we try to connect using ADO, it takes 100sec.

What could be the reason, the connection pool within the SQL Server remains the same right irrespective of the type of connection used to connect to the backend Server.

Also, how many connections are available from the SQL Server for a front End at a time, Do we have a control over it.

thanks

Nik

Answer 1

You give us close to nothing to go on, but I would start looking at the execution plans for the queries and see if they differ. I would also use PRofiler to see if the submitted SQL from the two apps differ greatly. And finally, I would look at the source code for the app. SQL Server do not limit how many connections a client app can use.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 2

thanks for the response, but we have a confirmation that the connection to the SQL is taking a long time, so how do we know about a process unless it is not connected to SQL using the profiler.

Answer 3

Are you saying that *connecting to* SQL Server takes longer time, or execution of the query takes longer time?

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 4

Connecting To SQL Server takes a long time. It is sql server 2005, 64 bit.

thanks for replying

Answer 5

I would have the networking expert trace that and try to see what differs from the "quick case".

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 6

IIRC, the set options for ADO are slightly different than ADO.NET.  This should be clear in the Execution Plans, and I'd start there as Tibor said.  Beyond that look at the net_packet_size for differences.  ADO.NET default is 8K where ADO was 4K (again, IIRC). 

select 
	c.session_id, 
	c.net_transport, 
	c.net_packet_size, 
	c.client_net_address,
	s.host_name,
	s.quoted_identifier,
	s.arithabort,
	s.ansi_null_dflt_on,
	s.ansi_defaults,
	s.ansi_warnings,
	s.ansi_padding,
	s.ansi_nulls,
	s.concat_null_yields_null
from sys.dm_exec_connections c
join sys.dm_exec_sessions s on c.session_id = s.session_id

If you have different servers also check the duplex settings on the NIC's for the application servers.  An incorrect duplex setting can cut performance significantly.  See Linchi Shea's post on this:

http://sqlblog.com/blogs/linchi_shea/archive/2008/09/16/performance-impact-mismatched-network-duplex-setting-can-sink-your-oltp-throughput.aspx

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 7

Thanks  lot.

Also, how do i verify if connection pooling is being used or the application waiting for a connection