Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alecl
Starting Member
13 Posts |
 Posted - 2002-03-29 : 12:10:17
|
| I know cursors are bad for performance, but I've heard someone mention that a cursor was just a recordset. If I return a recordset from a stored procedure, that's fine though. It's only if I start traversing using a cursor inside the stored procedure that it's a performance issue, right? |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-29 : 14:01:33
|
quote:
I've heard someone mention that a cursor was just a recordset.
This is true, though the semantics are confusing. In everday parlance the term "cursor" refers to a server-side cursor, the kind created when you traverse a cursor in an sp. A recordset is a client-side cursor.As my handle says, cursors are evil!!setBasedIsTheTruepath<O> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-29 : 14:58:06
|
quote:
I know cursors are bad for performance, but I've heard someone mention that a cursor was just a recordset
Technically that's not accurate, as setbasedisthetruepath has mentioned. A recordset is a client-side concept. It has no bearing on the server/database end. You can use either a SELECT statement or a cursor to retrieve rows in SQL Server, but when it gets delivered to ADO (or whatever data access layer you're using), it is still a recordset.quote:
If I return a recordset from a stored procedure, that's fine though. It's only if I start traversing using a cursor inside the stored procedure that it's a performance issue, right?
No, you can have plenty of performance slowdowns by using MoveNext in an ADO recordset. The problem isn't so much in MoveNext itself, it's in the loop that's used to move from one row to the next in a recordset. For example, if you're getting data from SQL Server, and you want to display it on a web page (in a table), this is slow code:response.write "<table>"Do While Not rs.EOF response.write "<tr><td>" response.write rs("Name") response.write "</td><td>" response.write rs("Address") response.write "</td><td>" response.write rs("Phone") response.write "</td></tr>" rs.MoveNext()Loopresponse.write "</table>"Why is this slow? Each time you use response.write, you incur some overhead in accessing the response object. Each time you use the rs object, you also incur some object overhead. This overhead is multiplied for each iteration through the loop. Even if you put the string together in one line, like this:response.write "<tr><td>" & rs("Name") & "</td><td>" & rs("Address") & "</td><td>" & rs("Phone") & "</td></tr>"You still have 4 object references (instead of 11). The & operator is also a performance hog, because VB COPIES the original string, appends the 2nd string to the copy, then replaces the original string with the copy! Multiply this by 1,000 and you have some significant overhead that is essentially wasted processing power.If you are using ADO and you need to output the results, use the GetRows or GetString methods. These will convert a recordset into an array, or a formatted string. They are FAST! In the case of GetRows, once the data is in the array, you can access it using regular array notation. This is faster than MoveNext because there is no object overhead. You can access any array element instantly, without having to position a cursor on it first.GetString is, IMHO, even better, especially if you are doing simple output into a string. The code from above can be done using GetString like this:data=rs.GetString(,, "</td><td>", "</td></tr><tr><td>", "")response.write "<table><tr><td>" & data & "</td><tr></table>"Bit shorter, isn't it????  The best part is that this code has only 2 object references, and only 2 "&" operations. THIS IS FOR THE ENTIRE RECORDSET, REGARDLESS OF THE NUMBER OF ROWS! This is about as minimal as you can get in processing a recordset.If you want more information on GetRows and GetString, search these ASP sites:www.learnasp.comwww.asp101.comwww.4guysfromrolla.comwww.15seconds.comYou can also find information in the ADO documentation. You can get it from the MDAC SDK documentation (in the ADO260.CHM file):http://www.microsoft.com/data/download.htm |
|
|
|
jon
Starting Member
3 Posts |
Posted - 2002-04-01 : 20:11:54
|
| another advantage is that after u store the contents of the recordset object into a VB safe-array (in JScript: qRS = new VBArray(oRS.GetRows())), u can close and release the connection and recordset objects.a disadvantage with the above is that the database could change and the data becomes stale, so it all depends on the probability of that non-concurrency .also if the data spans more than one page, u have 2 use some way to pass the data between pages |
|
|
|
|
|
|
|
|
Cursors and Recordsets