SQL Server Performance Forum – Threads Archive
Reading from MS SQL database
Hi,<br /><br />I have an ETL application in C# that processes 10-20 million recordsin a single run. I have set it up so that it processes in chunks of few thousand records at a time.<br /><br />In th process of optimizing the read from operational database I found that each read is taking about 800 milliseconds. I ran the same stored proc (used for reading from application) from query analyzer and it takes only about 200 milliseconds. <br /><br />This was little surprising so I checked the execution plan for both but they are exactly same and doing exactly same number of reads too (captured data from profiler). I have repeated this several times and made sure there was nothing cached but same results <img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ /><br /><br /><br />This is how I create connection in application<br /><br />cStr = "Initial Catalog=myDB; Data Source=myServer; Integrated Security=SSPI; Connect Timeout=300";<br />SqlConnection cn = new SqlConnection (cStr);<br />cn.Open();<br /><br />This is how I execute stored proc<br /><br />SqlCommand cmd = new SqlCommand(sqlString, connection);<br />cmd.CommandType = CommandType.StoredProcedure;<br />//set params<br />SqlDataReader readAllCursor = cmd.ExecuteReader();<br /><br /><br />Any ideas why reading from C# application so slow compared to from query analyzer?<br /><br />
More information.. execution plan shows Clustered index seek (100%) and it returns about 3000 rows.
For the slow queries PROFILER would help to assess the information.
BTW what are the setting on SQL defined such as memory, remote login & query timeout values? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>