Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 size of user tables in a database
 Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/06/2003 :  01:45:44  Show Profile  Visit nr's Homepage  Reply with Quote
What do you think a cursor is?
It still has to access the data in some way and if there was a more efficient way of doing it than using a clustered index than sql server would use it for accessing data.
The only advantage it may have would be in not having to lock records due to being single spid access - but it would have had to lock the records to get the data when allocatted to make use of that.
Maybe the cursor locks the data in memory for fast access so that other processes can't force it to page (not really possible as it could run out of memory) but this would be to the detriment of other processes.

If the column used to iterate thhrough the table isn't indexed then the cursor could generate an indexed set and save the table scans - but using a table variable would give a similar performance.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 11/06/2003 06:43:41
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 11/06/2003 :  05:50:27  Show Profile  Visit Stoad's Homepage  Reply with Quote
LOL, rob volk,

I give up... I need more faith... Quite so... :)
===============================
The anti-set-based mutiny is scotched.
==============================
Cursors are useful if you don't know sql.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/06/2003 :  08:57:40  Show Profile  Reply with Quote
quote:
Originally posted by nr

What do you think a cursor is?
It still has to access the data in some way and if there was a more efficient way of doing it than using a clustered index than sql server would use it for accessing data.
The only advantage it may have would be in not having to lock records due to being single spid access - but it would have had to lock the records to get the data when allocatted to make use of that.
Maybe the cursor locks the data in memory for fast access so that other processes can't force it to page (not really possible as it could run out of memory) but this would be to the detriment of other processes.

If the column used to iterate thhrough the table isn't indexed then the cursor could generate an indexed set and save the table scans - but using a table variable would give a similar performance.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.



OK.....but for this acadenic discussion (and since set based is the way to go)...when you say "it will run out of memory", how can that be...doesn't SQL Server build a temp table, fill the page with results, then "page" through the data? Then refresh the page when it needs more? Doesn't it read in chuncks (pages) instead of an index seek for every fetched row? In other words doesn't it seek and fill a page at a time for the fetch...while the fetch is waiting..

Compared to having to seek every time?

Because this only applies to larger volumes (and I can't think of why you would do it anyway), it is only speaking to how SQL Server gets at the data.

If a singleton select needs to reaccess the data every time, one for every row, isn't grabing a page at a time (for larger volumes) more efficient? Since it's it's one operation?

And I guess the bigger question is, how does one "see" what's going on.

Statistics?





Brett

8-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/06/2003 :  09:18:13  Show Profile  Reply with Quote
quote:
Originally posted by robvolk
we'll take (another) crack at it using set-based operations. Betcha we'll beat the cursor (again).



Also, not the question rob....

Non set based operations...

In DB2, a fetch will "fill" a page at a time, then read off that page (no data access until the data page is empty). Locks? sure!

Question was, when does the loop with singleton selects, lose to a cursor.

It's not a matter of which is better, it's when which is better.

Someone said...

SetBasedIsTheTruePath....

Yup...

Did MS just install cursors because they followed IBM (which they have for a number of things....damn I miss my OS/2)

There's got to be a desciption about how the process works (internally) in SQL Server...


Brett

8-)
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 11/06/2003 :  13:12:13  Show Profile  Visit robvolk's Homepage  Reply with Quote
There is (or was, in the 6.5 version) a discussion on some of the internal processes used by cursors in Inside SQL Server. Certain types of cursors do indeed use "temp" tables (they reside in tempdb and may not be true temp tables) but are processed differently from regular table processing.
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1632 Posts

Posted - 11/07/2003 :  07:57:19  Show Profile  Reply with Quote
In BOL "Singleton Select Statement", MS states the following...

"When you write code for a transaction that retrieves a single row of results, you can use a SELECT INTO statement. This is called a singleton SELECT statement.

When you write code for a transaction where the result set includes several rows of data, you must declare and use a cursor.
"


What's up with that ?? - "These are not the droids your looking for.."

Is the key word transaction ??

Edited by - ehorn on 11/07/2003 07:58:25
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 11/07/2003 :  08:11:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
That entry is specifically for Embedded SQL, not Transact-SQL. If you're writing a C program to access data using Embedded SQL, there are a number of differences in how you need to do things vs. other methods.
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1632 Posts

Posted - 11/07/2003 :  10:46:13  Show Profile  Reply with Quote
Thanks Robvolk. I guess I needed to read one more sentence in BOL or just look at the title.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/07/2003 :  11:13:22  Show Profile  Visit nr's Homepage  Reply with Quote
>> when you say "it will run out of memory", how can that be
I mean when there is not enough physical memory to hold all the data then sql server will have to use the disk and the performance will suddenly take a big dip.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/07/2003 :  11:20:04  Show Profile  Reply with Quote
And anyway, you have to do a lookup as in Nigels original example...you just loop through and do inserts...what's the comparisson there?


DECLARE @MaxRows int
SET @MaxRows = 10000

DECLARE @n INT,@data CHAR(400)
SET @n = 0
WHILE @n <= @MaxRows
BEGIN
	INSERT INTO OutData 
	SELECT n,data FROM TestData WHERE n = @n

	SET @n = @n+1
END
GO

DECLARE @MaxRows int
SET @MaxRows = 10000
DECLARE @n INT,@data CHAR(400)
SET @n = 0
WHILE @n <= @MaxRows
BEGIN
INSERT INTO OutData
SELECT n,data FROM TestData WHERE n = @n
SET @n = @n+1
END
GO
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1632 Posts

Posted - 11/07/2003 :  11:26:10  Show Profile  Reply with Quote
My test was to try to compare speed singleton and cursor
I assumed the function (insert) to be constant between the two tests.


Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/07/2003 :  12:17:36  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Ehorn --
your test and results are not what Nigel did. You just went through and selected N based on the next number.

In Nigel's example, for each name, he had to SELECT the MIN() that is greater than the one that he is currently using.

you would need to rewrite your test like this for the Singleton SELECT part like this:


SET NOCOUNT ON

DECLARE @MaxRows int
SET @MaxRows = 10000

DECLARE @n INT,@data CHAR(400)
SET @n = 0
WHILE @n <= @MaxRows
BEGIN
        SET @N = (select min(n) from numbers Where n > @N)
	INSERT INTO OutData
	SELECT n,data FROM TestData WHERE n = @N
END
GO


or something like that .... you were not comparing apples to apples.

of course, if we know EXACTLY what row to select because the table is just a sequence of numbers we can get the row we need w/o using a cursor, but to loop through the rows when there IS no sequence -- I'd have to see which method is faster.

- Jeff

Edited by - jsmith8858 on 11/07/2003 12:33:57
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1632 Posts

Posted - 11/07/2003 :  12:46:13  Show Profile  Reply with Quote
I must have misread the the original question as being a more general when does a cursor outperform a singleton select, not necessarily just applied to Nigels method proposed in this post. My logic is that the measurment is not on what function is being performed it is on the time/resources to execute a singleton vs a cursor. In this case, I believe the test was apples to apples in that both tests performed the same functional operation, an insert and the variable being a cursor vs singleton.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/07/2003 :  13:41:02  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
no !!!!! Regardless of Nigel's specific solution, do you see:

The cursor is looping through rows in a table to find the "next" row.

The singleton select was just adding 1 to a counter to find the "next" row.

How is that a fair comparison? Again, run your test again without assuming that the singleton select can just "add 1" to get the next value it needs. Force it to do what the cursor is doing: accessing the TABLE to get the next row.

In other words, instead of using sequential numbers create a table of strings and run the same test and see what happens.

- Jeff

Edited by - jsmith8858 on 11/07/2003 13:45:20
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1632 Posts

Posted - 11/07/2003 :  13:55:31  Show Profile  Reply with Quote
jsmith8858, Thank you for explaining the difference and flaw in my test. I understand now.
Hopefully someone can post a scientific answer to the question as this is what Brett is after.

Edited by - ehorn on 11/07/2003 13:56:33
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/07/2003 :  14:04:56  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Can you run it again with a change similiar to what I showed? I'd be curious to see the results.

- Jeff
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1632 Posts

Posted - 11/07/2003 :  14:21:34  Show Profile  Reply with Quote
I would be happy to re-run the test with the changes you prescribed, but I believe Brett wanted to see it at higher volumes (250,000), to determine if there would be a crossover point. Possibly someone with better hardware can provide these results.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/07/2003 :  15:07:13  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
the point is: there may not BE a cross over point. the cursor might be faster from the get-go, even with the smaller set. or, they might be almost exactly the same. It is worth checking for even a small sample of data.

- Jeff
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1632 Posts

Posted - 11/07/2003 :  15:51:06  Show Profile  Reply with Quote

My DBA was kind enough to let me run this test at work
these are the results

Rows		Cursor		Singleton

10,000 		0:04 min	0:04 min
100,000  	0:15 min	0:13 min
200,000  	1:22 min	1:15 min
500,000		3:37 min	3:17 min
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 11/07/2003 :  18:24:21  Show Profile  Visit Stoad's Homepage  Reply with Quote
DDL:
create table books (price int) -- Note: no index so far

Singleton:
declare @p int
set @p=0
select @p=min(price) from books where price>@p
while @p is not null
begin
insert into #t select @p
select @p=min(price) from books where price>@p
end

Cursor:
declare @p int
declare abc cursor for select price from books
open abc
fetch next from abc into @p
while @@fetch_status=0
begin
insert into #t select @p
fetch next from abc into @p
end
close abc deallocate abc

RESULTS:
When no index in books: Singleton >> 22 sec Cursor >> 2 sec.

After creating clustered index on price: both are 2 sec.

And this is very strangely.
Even with Index I would expect Cursor to be Winner.

Moreover, I noticed a tend: more rows - more faster is singleton relatively to cursor...
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000