Optimize function that uses cursors

Hello,
I have a question regarding ways to optimize a function that currently uses
cursors to get some data from an SQL Database table.
I'm looking for others' opinions as we have discussed this internally in our
team but have reached to no viable conclusion yet. We are mostly interested
in ways to use SELECT INTO, UNION, or other constructs that we may have
missed instead of the cursors (but to be able to run them within a function:
SELECT INTO statement isn't allowed within a user defined function).
The function can be made recursive as there are no much recursions (we
expect no more than 10 levels of parent groups), but we have implemented it
using a WHILE look instead of recursion also for performance reasons.
Thank you in advance for your time. Any comments/suggestions will be highly
appreciated.
Basically we have a table called GroupItems with schema and data like below
(each item may have none, one, or more parent groups; the groups are also
considered items):
IDItem, IDGroup
--
3, 1
4, 2
5, 3
5, 4
6, 2
We also have defined a recursive user function that retreives all the parent
groups and ancestor groups (i.e. the parents of the parents and so on) for a
given ID (including that ID itself). The function returns a table data type
and uses a cursor to select next level of parents and their ancestors (using
a recursive call in the select of the cursor), and in the cursor look it use
s
insert into statements to add the data to the table.
For the sample data above, and using @.idItem = 5 as the parameter, the
function would return:
ID
--
1
2
3
4
5
As a picture is like a thousand words, the code for the function is like thi
s:
CREATE function dbo.GetParentItems(@.idItem int)
returns @.t table([ID] int primary key)
begin
-- insert current ID
insert into @.t ([ID]) values (@.idItem)
declare @.cnt int
select @.cnt = count(*) from @.t
declare @.more bit
set @.more = 1
-- the @.more variable will tell us when to stop
while @.more = 1
begin
-- get the direct parents of the current
items from the current @.t variable, except those parents which are already
added to the result; we store the new IDs in a temporary @.v variable
declare @.v table ([ID] int primary key)
declare @.id int
delete from @.v
declare c cursor for
select [GroupItems].[IDGroup]
from [GroupItems]
where
[GroupItems].[IDItem] in (select [ID] from @.t) and
[GroupItems].[IDGroup] not in (select [ID] from @.t)
open c
fetch next from c into @.id
while @.@.fetch_status = 0
begin
insert into @.v ([ID]) values (@.id)
fetch next from c into @.id
end
close c
deallocate c
-- now add the new IDs from @.v to the result
@.t
declare cv cursor for
select [ID] from @.v
open cv
fetch next from cv into @.id
while @.@.fetch_status = 0
begin
insert into @.t ([ID]) values (@.id)
fetch next from cv into @.id
end
close cv
deallocate cv
declare @.cntCur int
select @.cntCur = count(*) from @.t
-- if we have added no new IDs, the counts
will be the same and we stop looping
if @.cnt = @.cntCur
set @.more = 0
set @.cnt = @.cntCur
end
return
end
Thank you very much again.
Sorin DolhaSorin
Look at this example written by Itzik Ben-Gan
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
"Sorin Dolha" <SorinDolha@.discussions.microsoft.com> wrote in message
news:A70E5266-647F-4D49-A162-D608C0A5ABB3@.microsoft.com...
> Hello,
> I have a question regarding ways to optimize a function that currently
uses
> cursors to get some data from an SQL Database table.
> I'm looking for others' opinions as we have discussed this internally in
our
> team but have reached to no viable conclusion yet. We are mostly
interested
> in ways to use SELECT INTO, UNION, or other constructs that we may have
> missed instead of the cursors (but to be able to run them within a
function:
> SELECT INTO statement isn't allowed within a user defined function).
> The function can be made recursive as there are no much recursions (we
> expect no more than 10 levels of parent groups), but we have implemented
it
> using a WHILE look instead of recursion also for performance reasons.
> Thank you in advance for your time. Any comments/suggestions will be
highly
> appreciated.
> Basically we have a table called GroupItems with schema and data like
below
> (each item may have none, one, or more parent groups; the groups are also
> considered items):
> IDItem, IDGroup
> --
> 3, 1
> 4, 2
> 5, 3
> 5, 4
> 6, 2
> We also have defined a recursive user function that retreives all the
parent
> groups and ancestor groups (i.e. the parents of the parents and so on) for
a
> given ID (including that ID itself). The function returns a table data
type
> and uses a cursor to select next level of parents and their ancestors
(using
> a recursive call in the select of the cursor), and in the cursor look it
uses
> insert into statements to add the data to the table.
> For the sample data above, and using @.idItem = 5 as the parameter, the
> function would return:
> ID
> --
> 1
> 2
> 3
> 4
> 5
> As a picture is like a thousand words, the code for the function is like
this:
> CREATE function dbo.GetParentItems(@.idItem int)
> returns @.t table([ID] int primary key)
> begin
> -- insert current ID
> insert into @.t ([ID]) values (@.idItem)
> declare @.cnt int
> select @.cnt = count(*) from @.t
> declare @.more bit
> set @.more = 1
> -- the @.more variable will tell us when to stop
> while @.more = 1
> begin
> -- get the direct parents of the current
> items from the current @.t variable, except those parents which are already
> added to the result; we store the new IDs in a temporary @.v variable
> declare @.v table ([ID] int primary key)
> declare @.id int
> delete from @.v
> declare c cursor for
> select [GroupItems].[IDGroup]
> from [GroupItems]
> where
> [GroupItems].[IDItem] in (select [ID] from @.t) and
> [GroupItems].[IDGroup] not in (select [ID] from @.t)
> open c
> fetch next from c into @.id
> while @.@.fetch_status = 0
> begin
> insert into @.v ([ID]) values (@.id)
> fetch next from c into @.id
> end
> close c
> deallocate c
> -- now add the new IDs from @.v to the
result
> @.t
> declare cv cursor for
> select [ID] from @.v
> open cv
> fetch next from cv into @.id
> while @.@.fetch_status = 0
> begin
> insert into @.t ([ID]) values (@.id)
> fetch next from cv into @.id
> end
> close cv
> deallocate cv
> declare @.cntCur int
> select @.cntCur = count(*) from @.t
> -- if we have added no new IDs, the counts
> will be the same and we stop looping
> if @.cnt = @.cntCur
> set @.more = 0
> set @.cnt = @.cntCur
> end
> return
> end
> Thank you very much again.
> --
> Sorin Dolha|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Look up nested sets model for trees. Get a copy of TREES & HIERARCHIES
IN SQL. There is no need for recursion or any procedural code from the
vague narrative you posted instead of specs.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Look up nested sets model for trees. Get a copy of TREES & HIERARCHIES
IN SQL. There is no need for recursion or any procedural code from the
vague narrative you posted instead of specs.

Optimization Required for User Defined Function

Hi,

I've an UDF which inside has two query joined by union and it 's similar to this

select * from Table1 ... (several conditions)
union
select * from Table2 ... (several conditions) (this could takes long time to run)

Since i can't write dynamic sql into UDF , i can't avoid to insert Table2 into the query but to improve permormance I've seen how costant can help me.
For Example if I change my UDF in

select * from Table1 ... (several conditions)
union
select * from Table2 Where 1=2 AND (several conditions)

Optimazer is able to skip completely the second execution, so i need to transform 1=2 into a dynamic condition for example test a field table existence.
select * from Table2 Where Exist (select * from Table3 where Field1=1)

That is why i try to write a single UDF can adapt itself to several situations using second condition only where is necessary and not always.
The problem is the dynamic condition for simple could be, wasn't recognize as costant.

For Example
select top 1 * from MyTable where (select 1)=2
select top 1 * from MyTable where 1=2

If you see the execution plan of these 2 queries you could see that the first takes more than 80% of execution time and in the second less than 20%.
Moreover the second plan use a costant scan unlike the first doesn't it.

Do anyone know a way to tell to optimizer to use a simple condition as constant ? This improve drastically my UDF performance.... :( :(

Thanks.1) why is it essential to make it a function and not procedure or view.
2) how do u find

..you could see that the first takes more than 80% of execution time and in the second less than 20%...

if u r referring to execution-plan these % values relative to the batch and does not represent an absolute value. and practically both are taking 0 sec in my machine.
3) if u use "select" in a "where" it is evaluated once for each row of the outer query hence is inefficient.

Optimal cpu utilization

Hi all,

What is the maximum CPU utilization for SQL SERVER to function properly. our server's CPU utilization is touching an averrage of 70-80 % is it ok?

What can be the result of CPU utilization touching 80+ constantly?

This can be a very long answer, and much of it depends on what you are trying to achieve.

In general, you want to leverage the resources you have, the fact that you are using 80% of CPU tells me you still have 20% more to work w/ and implies you can still continue to increase the workload.

Key is to understand the usage over a period of time. If you see that over a period of high activity, you see that CPU is at 80% then you are fine. However if you are expecting workloads to increase then you will need to understand CPU usage in regards to your workloads.

And since CPU is shared by all apps, if this machine is not dedicated SQL machine, then you need to also look at CPU usage by other apps.

Thank You

Ajay

|||Actually, 75% - 80% is generally regarded as the maximum efficient usage of a CPU. After 80%, the CPU starts to become bogged down with scheduling threads, and other workload management tasks. This server is in trouble. Run a trace, and check for queries that are using more than 1,000 reads. Some of these may be tunable with indexes, or possibly re-writing them to use existing indexes.|||

This blog post is relevant: http://blogs.msdn.com/slavao/archive/2006/09/28/776437.aspx

Thanks, Ron D.

Operation not allowed in this context

This error message is showing up when I execute the following code (called in a function):

Set rs = New ADODB.Recordset
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic
With rs
For j = 0 To .RecordCount - 1
rs(0).Value = j + 1
If j < .RecordCount - 1 Then .MoveNext
Next j
.Close
End With
Set rs = Nothing

Any idea what causes this to fire?what line is giving the error?

Set rs = New ADODB.Recordset
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic
For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
rs.Close
Set rs = Nothing|||...rs.Close.|||I have no idea. But try this one:

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic

For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
rs.Close
Set rs = Nothing|||...what I have noticed is that if I comment out the rs.Close it runs just fine. That's not to say that it is ok to not close out your recordsets but, since I am new to ADO (always worked in DAO before) I am a little uncertain as to when that is/isn't necessary.

Any ideas on that?|||I suggest that you close your connection to the database and set the recordset to nothig:

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & _
" AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic

For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
conn.Close 'Close the connection
Set conn = Nothing
Set rs = Nothing|||open due to the frequent requests for data in my app. But I usually close the recordsets immediately. Comment please!|||Originally posted by Bobba Buoy
open due to the frequent requests for data in my app. But I usually close the recordsets immediately. Comment please!

What should I comment?|||You suggested that closing the connection was probably a good idea. I am noting that I usually leave the connection open during the life of the application since it is usually pretty active. I was wondering how bad of an idea you thought that was.|||Originally posted by Bobba Buoy
You suggested that closing the connection was probably a good idea. I am noting that I usually leave the connection open during the life of the application since it is usually pretty active. I was wondering how bad of an idea you thought that was.

If your application is used by many users at same time, it's a good idea to close the database connection. One open connection takes several kilobytes in memory.

OPENXML Question

How do I return the number of rows inserted/updated using OPENXML?
I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
Generic update example trying to return the # of rows updated:
declare @.i int
exec sp_xml_preparedocument @.i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'
update test
set test.xmlData = ox.xmlData
from OpenXml(@.i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
RETURN @.@.ROWCOUNT --Returns a 0
exec sp_xml_removedocument @.i
Thanks,It will return the rowcount.
Can you check if the data was really updated.
I think its the data problem.
Or try using print @.@.rowcount as see.
"Robert" wrote:

> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>|||Check my procedure here:
It works for me (rowcount stuff that is)
if exists (select * from sysobjects
where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)
drop procedure uspTitleUpdate
GO
CREATE PROCEDURE dbo.uspTitleUpdate (
@.xml_doc TEXT ,
@.numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @.hdoc INT -- handle to XML doc
DECLARE @.errorTracker int -- used to "remember" the @.@.ERROR
DECLARE @.updateRowCount int
DECLARE @.insertRowCount int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @.titleupdate TABLE (
identityid int IDENTITY (1,1) ,
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate datetime ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @.hdoc(with is the holder for
@.xml_doc), and put it IN a variableTable
INSERT @.titleupdate
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate ,
alreadyExists
)
SELECT
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
dbo.udf_convert_xml_date_to_datetime (pubdate) ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@.hdoc, '/TitlesDS/Titles', 2) WITH (
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate varchar(32) ,
alreadyExists bit
)
--select * from @.titleupdate
--lets differeniate between existing (update) and new ones (insert)
Update @.titleupdate
SET
alreadyExists = 1
FROM
@.titleupdate tu , titles
WHERE
--this where clause is a little weird, usually you'll must match
--primary key (int or global identifiers)
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
SET NOCOUNT OFF
Update
titles
set
title = tu.title ,
type = tu.type ,
pub_id = tu.pub_id ,
price = tu.price ,
advance = tu.advance ,
royalty = tu.royalty ,
ytd_sales = tu.ytd_sales ,
notes = tu.notes ,
pubdate = tu.pubdate
FROM
@.titleupdate tu , titles
WHERE
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
AND
tu.alreadyExists <> 0
Select @.updateRowCount = @.@.ROWCOUNT
INSERT INTO titles
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
)
Select
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
FROM
@.titleupdate
WHERE
alreadyExists = 0
Select @.insertRowCount = @.@.ROWCOUNT
select @.numberRowsAffected = @.insertRowCount + @.updateRowCount
--select * from titles
SET NOCOUNT OFF
GO
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:3C2C4124-DEE9-4A0E-82CE-FE91CCFDC5AF@.microsoft.com...
> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>