| Author |
Topic |
|
GreatInca
Posting Yak Master
102 Posts |
 Posted - 2003-05-13 : 14:21:29
|
| since DataReaders / Firehose cursors only stream results, Do DataReaders cause a query to hold its shared locks and keep the connection busy while cycling slowly though a large result set (about 200,000 rows, sending an email for each one)? The query is accessing a table that cannot have a shared lock held on it for an extended time (it would break job searches) and I would not like to keep a connection open and tied up that long either.If so (I think it is) I am having trouble using DataRow.Item("ColumnName") (Looping Rows collection in a dataset). Doesn't seem to be supported even though MSDN says its there. ItemArray is there though.SearchAgents.cs(85): 'System.Data.DataRow' does not contain a definition for 'Item' |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-13 : 15:31:53
|
| No, I don't beilieve locks are held:[msdn]Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader[/msdn]For the datarow (This looks like VB.Net) you would do:MyDataRow("columnName")orMyDataRow(columnIndex)-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2003-05-14 : 19:40:00
|
| I figured out the dataset issue. Can't use .Item[] . Wihtout the .Item The output type was object which had to be casted to the type of the receiving local variable (I'm using C#).I'm still unsure about locks -- I don't think 200,000 rows of information will fit in a computer's network buffer all at once, which means SQL server has to wait several times for room, which might mean pausing the query and not giving up the locks until all the results are sent out and the query is finished (a single select statement not a transaction of several commands). At 1/4 to 2 seconds per row (Email), times 200,000 thats a long time. If it does all fit in the client's network buffer (at least 50-100MB of data), it would be really cool.Edited by - GreatInca on 05/14/2003 19:41:42 |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-05-14 : 20:10:57
|
| A DataReader "pulls" data from SQL Server on request ie via the Read() Method. It does place locks on the data.A DataTable or DataSet pulls the whole set in one go. You should NOT be using a DataReader for this action.DavidM"SQL-3 is an abomination.." |
|
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2003-05-15 : 14:11:51
|
| Cool I didn't switch over to a data reader. It runs the big sp that runs all the agents and puts them into tracking, and caches the results into a table (SearchID/ItemID pairs + keyword ranks) and returns a result set with Search-level information. This runs very quickly - even loading the data set.Then it loops with each searchagent and runs another SP that gets the result details with a datareader and builds the search results to put into the email. These queries are very quick when run through QA. It is very slow though SQl is having no prob. The app is running very very slow hogging the whole CPU only to format search results in the emails (takes longer than sending the email does - 2-4 sec vs. 1sec). Is there anything obvious that could be bogging it down?Edited by - GreatInca on 05/15/2003 14:13:41 |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-05-15 : 16:50:19
|
| What happens if you use a DataReader with a NOLOCK hint in the SELECT statement? A really fast nonlocking nonblocking SELECT??Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-05-15 : 19:21:55
|
| Michael,The problem is really more associated with Connection Pooling. When the DataReader is open no other connection from the pool can use it. Locking can be a problem (Shared Lock are issued by default on SELECTs), the NOLOCK hint can overcome this but you are using valuable SQL resources when they can be minimised by using the appropriate data container.Although both the Dataset and DataTable use the DataReader behind the scences, they are cycling very quickly through it and disposing of it quickly. You could use a DataReader, but you would have to populate a collection, close the DataReader, and then work from the collection.The techniques you employ for ADO.NET is not much different from classic ADO. Open the connection as late as a possible, retrieve the results as efficently as possible, close the connection ASAP.GreatInca, what worries is your use of the DataReader to format the email. If the DataReader is opened while you are formating, this is a bad thing!DavidM"SQL-3 is an abomination.." |
|
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2003-05-16 : 11:56:10
|
| I learned about the stringbuilder it is much faster now, about 5 times faster. About 40 concatinations per result, times 250 max results = up to 1000 concats per agent. It is now serving 15 search agents in about 5 seconds (10 have keywords) on the test server.Data reader can have up to 2,500 (250 jobs all in 10 unique cities) rows to format for 1 agent, more typical max of about 400. It does get closed immediately after the end of the agent loop, always before sending the email (1 email per jobseeker; jobseeker can have up to 10 agents). Email sending is now the slowest part (by far) as it is supposed to be.Edited by - GreatInca on 05/16/2003 12:00:50 |
|
|
|
|
DataReaders open for a long time & Locking