row-fetch question

Hi everyone,
I ran Profiler to verify why users where complaining about the bad
performance of our web application. I saw thousands of continuous
cursoropens, fetch and close. I googled it and came across an article that
tackled about cursors. It mentioned about row-fetch. He mentioned that if
row-fetch is 1, it is very inefficient. I would like to know how I can get
the current value of row-fetch and how to modify it.
And also, am I correct that server-side api cursors are cpu-intensive?
Any help will be greatly appreciated.
Neil
The FetchBufferSize property can be set to increase the rows to fetchin a
single operation. The default is 100 unless the data source uses binary
large object (BLOB) storage, in that case the FetchBufferSize is ignored and
1 is used instead. So it really depends on the data you are querying.
Question. Is your web app calling a stored proc that has a cursor, calling a
tree of sub cursors, or your web app calling a recordset, then making
queries from the results set, etc?
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:efJy9nEqEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hi everyone,
> I ran Profiler to verify why users where complaining about the bad
> performance of our web application. I saw thousands of continuous
> cursoropens, fetch and close. I googled it and came across an article that
> tackled about cursors. It mentioned about row-fetch. He mentioned that if
> row-fetch is 1, it is very inefficient. I would like to know how I can get
> the current value of row-fetch and how to modify it.
> And also, am I correct that server-side api cursors are cpu-intensive?
> Any help will be greatly appreciated.
> Neil
>
|||Hi Dwinter,
I will have to dig in to the web-app because it is a third party
application.
I can ask the vendor about this.
What if it was calling a stored proc that has a cursor, calling a tree of
sub cursors, or your web app calling a recordset?
You can also read my reply to the other post today. This question is also
related to that. It's because, I am trying to troubleshoot an application
performance issue.
Thanks for you help.
Neil
"DWinter" <dwinter@.attbi.com> wrote in message
news:uo6fIzEqEHA.3708@.TK2MSFTNGP10.phx.gbl...
> The FetchBufferSize property can be set to increase the rows to fetchin a
> single operation. The default is 100 unless the data source uses binary
> large object (BLOB) storage, in that case the FetchBufferSize is ignored
> and
> 1 is used instead. So it really depends on the data you are querying.
> Question. Is your web app calling a stored proc that has a cursor, calling
> a
> tree of sub cursors, or your web app calling a recordset, then making
> queries from the results set, etc?
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:efJy9nEqEHA.3592@.TK2MSFTNGP14.phx.gbl...
>

row-fetch question

Hi everyone,
I ran Profiler to verify why users where complaining about the bad
performance of our web application. I saw thousands of continuous
cursoropens, fetch and close. I googled it and came across an article that
tackled about cursors. It mentioned about row-fetch. He mentioned that if
row-fetch is 1, it is very inefficient. I would like to know how I can get
the current value of row-fetch and how to modify it.
And also, am I correct that server-side api cursors are cpu-intensive?
Any help will be greatly appreciated.
NeilThe FetchBufferSize property can be set to increase the rows to fetchin a
single operation. The default is 100 unless the data source uses binary
large object (BLOB) storage, in that case the FetchBufferSize is ignored and
1 is used instead. So it really depends on the data you are querying.
Question. Is your web app calling a stored proc that has a cursor, calling a
tree of sub cursors, or your web app calling a recordset, then making
queries from the results set, etc?
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:efJy9nEqEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hi everyone,
> I ran Profiler to verify why users where complaining about the bad
> performance of our web application. I saw thousands of continuous
> cursoropens, fetch and close. I googled it and came across an article that
> tackled about cursors. It mentioned about row-fetch. He mentioned that if
> row-fetch is 1, it is very inefficient. I would like to know how I can get
> the current value of row-fetch and how to modify it.
> And also, am I correct that server-side api cursors are cpu-intensive?
> Any help will be greatly appreciated.
> Neil
>|||Hi Dwinter,
I will have to dig in to the web-app because it is a third party
application.
I can ask the vendor about this.
What if it was calling a stored proc that has a cursor, calling a tree of
sub cursors, or your web app calling a recordset?
You can also read my reply to the other post today. This question is also
related to that. It's because, I am trying to troubleshoot an application
performance issue.
Thanks for you help.
Neil
"DWinter" <dwinter@.attbi.com> wrote in message
news:uo6fIzEqEHA.3708@.TK2MSFTNGP10.phx.gbl...
> The FetchBufferSize property can be set to increase the rows to fetchin a
> single operation. The default is 100 unless the data source uses binary
> large object (BLOB) storage, in that case the FetchBufferSize is ignored
> and
> 1 is used instead. So it really depends on the data you are querying.
> Question. Is your web app calling a stored proc that has a cursor, calling
> a
> tree of sub cursors, or your web app calling a recordset, then making
> queries from the results set, etc?
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:efJy9nEqEHA.3592@.TK2MSFTNGP14.phx.gbl...
>> Hi everyone,
>> I ran Profiler to verify why users where complaining about the bad
>> performance of our web application. I saw thousands of continuous
>> cursoropens, fetch and close. I googled it and came across an article
>> that
>> tackled about cursors. It mentioned about row-fetch. He mentioned that if
>> row-fetch is 1, it is very inefficient. I would like to know how I can
>> get
>> the current value of row-fetch and how to modify it.
>> And also, am I correct that server-side api cursors are cpu-intensive?
>> Any help will be greatly appreciated.
>> Neil
>>
>

Row locking

Hi,

I am looking for a locking advice.

I am looking to implement a queue like mechanism where in multiple connections / users connect to the database to get a single row. I do it as follows.

I have two tables A & B. I have a stored procedure which joins the two tables and returns only one row whose status flag is N [N - not read before]. The returned row is the oldest whose status is N. The data returned is mainly from table A, and only one column from B. As we return the selected row its status should be update to T in table A.

As I said above this procedure is executed simultaneously by multiple users / connections.

How to use locking to make sure that each connection has a unique row and updates that row to set the status?

Will simply setting isolation level to repeatable read at the beginning of the procedure would do the magic?

Thanks

Saravanan K

If fetching & updating on single hit/scope then it is possible & acceptable with out any vulnerability. If these 2 operations are on separate hit/scope then it may cause deadlock/permanent lock on the SELECTED data.

You can get the flavor of sp_getapplock & sp_releaselock which is one of the good exercise for your requirement.

Sample,

Create proc LockedProcess(@.Data ..)

as

Begin

Exec sp_getapplock 'My Lock Name', 'Exclusive'

--Do Your Operation here

Exec sp_releaseapplock 'My Lock Name'

End

|||

Thanks a lot Manivannan.

The following is the outline of my proc.

CREATE PROC SVP_SELECTREPORTS

@.SVCTYPE VARCHAR(50)

AS

SET NOCOUNT ON

DECLARE @.ERRORCODE INT

DECLARE @.TEMPTABLE TABLE(ID INT,

FIELD1 VARCHAR(256),

FIELD2 CHAR(8),

FIELD3 VARCHAR(40),

FIELD4 VARCHAR(50),

FIELD5 CHAR(4),

FIELD6 TEXT,

FIELD7 TEXT,

FIELD8 DATETIME,

FIELD9 CHAR(1),

FIELD10 VARCHAR(40),

FIELD11 CHAR(1),

FIELD12 INT,

FIELD13 VARCHAR(40))

BEGIN

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--90 DAYS

BEGIN TRAN

INSERT INTO @.TEMPTABLE

SELECT TOP 1 UR.* FROM

(

SELECT

ID,

FIELD1,

FIELD2,

FIELD3,

FIELD4' = CASE WHEN ISNULL(LTRIM(RTRIM(FIELD4)),'') = '' THEN '' ELSE '(' + FIELD4 + ')' END,

FIELD5,

FIELD6,

FIELD7,

FIELD8,

FIELD9,

'FIELD10' = 'MYTYPE',

FIELD11,

FIELD12,

FIELD13 = FIELD10

FROM MYORG

WHERE FIELD10 = '1'

AND FIELD9 = 'N' AND FIELD11 = 'N'

UNION ALL

SELECT

A.ID,

A.FIELD1,

A.FIELD2,

A.FIELD3,

A.FIELD4,

A.FIELD5,

A.FIELD6,

A.FIELD7,

A.FIELD8,

A.FIELD9,

'FIELD10' = R.NM,

A.FIELD11,

A.FIELD12,

'FIELD13' = A.TYPE_NM

FROM MYRPT R, MYORG A

WHERE A.FIELD10 NOT IN ('1','3') AND

R.ID = SUBSTRING(A.FIELD10,3,LEN(A.FIELD10) - 2)

AND A.FIELD9 = 'N' AND A.FIELD11 = 'N'

) UR ORDER BY UR.ID ASC

SELECT @.ERRORCODE = @.@.ERROR

IF (@.ERRORCODE <> 0)

GOTO ERROR

UPDATE MYORG SET FIELD9 = 'T' WHERE

ID = (SELECT ID FROM @.TEMPTABLE)

SELECT @.ERRORCODE = @.@.ERROR

IF (@.ERRORCODE <> 0)

GOTO ERROR

SELECT * FROM @.TEMPTABLE

END

IF THERE IS A PROBLEM ROLLBACK

ERROR:

IF (@.ERRORCODE <> 0)

BEGIN

ROLLBACK TRAN

PRINT 'UNEXPECTED ERROR OCCURED'

RETURN -100

END

--IF THERE ARE NO ERRORS AND THE EXECUTION REACHES THIS LINE COMMIT THE TRANSACTION

COMMIT TRAN

END

Do you think the locking is sufficient enough so that all users gets one unique row?

|||

Personally, I don't like it. I do recognize, however, that serialization is sometimes a necessary evil.

You are artificially creating a single point of 'blocking' behavior. This could cause major problems as the usage increases.

Normal 'best practice' is to remove and decrease points of blocking.

But if you must continue down the path of serialization, this thread, especially the code example, may help you.

You will want to minimize the code (including switching) that must execute while the AppLock is set.

In the second SELECT (of the UNION statement) there is what appears to be a cartesian JOIN -that may not be efficient.

It seems that the @.TempTable can be eliminated with a single UPDATE statetment with a WHERE clause wherein the [ID] is validated against the entire [SELECT TOP 1 UR.* FROM...] structure.

|||

Thanks Arnie .

If I eliminate the @.TempTable and update in a single statement, how do I return the

specific row to the calling application? [I am using SQL – 2000].

I am working on your other suggestions.

|||

If you were using SQL 2005, it would be easy.

Then you could just refer to Books Online about using the OUTPUT capability of an UPDATE statement.

Refer to Books Online, Topic: UPDATE

Scroll down to sub-topic: E. Using UPDATE with the OUTPUT clause.

However, since you are using SQL 2000, you will have to do it a bit differently.

The following code example will allow you to see a potential method that doesn't require a Temp table:

Code Snippet

USE Northwind
GO

BEGIN TRAN

DECLARE
@.LastName varchar(20),
@.FirstName varchar(20)

SELECT City
FROM Employees
WHERE EmployeeID = 5

UPDATE Employees
SET
City = 'MyTown',
@.LastName = LastName,
@.FirstName = FirstName
WHERE EmployeeID = 5

SELECT
@.LastName,
@.FirstName

SELECT City
FROM Employees
WHERE EmployeeID = 5

ROLLBACK TRAN

City
London

-- --
Buchanan Steven

City
MyTown

Row level Locking

To All:
I like to know if Row-level Locking is set by default. For example ... if
thousands of users regularly update a table, is it gauranteed that Sql
Server does a row level lock or something needs to be defined?
Thanks,
TunjiLocking models are dynamic in SQL Server. No there is no guarantee a row
lock will be chosen. SQL will do what it thinks is best according to a large
body of information that is documented in BOL amonth other places.
Do you have a specific problem? Or just trying to understand the locking
model better?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Tunji" <Tunj@.hotmail.com> wrote in message
news:efYBulfAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> To All:
> I like to know if Row-level Locking is set by default. For example ... if
> thousands of users regularly update a table, is it gauranteed that Sql
> Server does a row level lock or something needs to be defined?
> Thanks,
> Tunji
>|||Brain,
Yes, i just like to know the effect and result of having thousands of users
updating a particular table before putting it into test and production.
Most importantly how locks are handled in this kind of situation.
Thanks for your contribution to the Swl Server world and great Articles in
Sql Server Magazine.
Tunji
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23b%23wsvfAEHA.3316@.TK2MSFTNGP11.phx.gbl...
> Locking models are dynamic in SQL Server. No there is no guarantee a row
> lock will be chosen. SQL will do what it thinks is best according to a
large
> body of information that is documented in BOL amonth other places.
> Do you have a specific problem? Or just trying to understand the locking
> model better?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Tunji" <Tunj@.hotmail.com> wrote in message
> news:efYBulfAEHA.2212@.TK2MSFTNGP10.phx.gbl...
if
>

Row level Locking

To All:
I like to know if Row-level Locking is set by default. For example ... if
thousands of users regularly update a table, is it gauranteed that Sql
Server does a row level lock or something needs to be defined?
Thanks,
TunjiLocking models are dynamic in SQL Server. No there is no guarantee a row
lock will be chosen. SQL will do what it thinks is best according to a large
body of information that is documented in BOL amonth other places.
Do you have a specific problem? Or just trying to understand the locking
model better?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Tunji" <Tunj@.hotmail.com> wrote in message
news:efYBulfAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> To All:
> I like to know if Row-level Locking is set by default. For example ... if
> thousands of users regularly update a table, is it gauranteed that Sql
> Server does a row level lock or something needs to be defined?
> Thanks,
> Tunji
>|||Brain,
Yes, i just like to know the effect and result of having thousands of users
updating a particular table before putting it into test and production.
Most importantly how locks are handled in this kind of situation.
Thanks for your contribution to the Swl Server world and great Articles in
Sql Server Magazine.
Tunji
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23b%23wsvfAEHA.3316@.TK2MSFTNGP11.phx.gbl...
> Locking models are dynamic in SQL Server. No there is no guarantee a row
> lock will be chosen. SQL will do what it thinks is best according to a
large
> body of information that is documented in BOL amonth other places.
> Do you have a specific problem? Or just trying to understand the locking
> model better?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Tunji" <Tunj@.hotmail.com> wrote in message
> news:efYBulfAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> > To All:
> >
> > I like to know if Row-level Locking is set by default. For example ...
if
> > thousands of users regularly update a table, is it gauranteed that Sql
> > Server does a row level lock or something needs to be defined?
> >
> > Thanks,
> >
> > Tunji
> >
> >
>

Row level filtering (does not need to be secure)

I'm trying to design a system where I can filter (not secure) a users results, the user may or may not pass in a user ID. We typically use middle tier connection pooling with a single identity, so I believe labelling is not suitable.

I think the ideal solution would be...

User to establishes a connection through our application, a user id will be established as part of the connection. A view is created describing what the user is able to access. Preferably the user should not be aware of the view. The user or our application executes a number of select queries.

Note that there may be many users with different filters required connecting at any time.

Direct user updates of the table do not need to be supported.

This topic comes up on the forums from time to time in the context of row level security.

The following whitepaper may also be useful to you:

http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

It uses labeling but I think your solution will require something like that because you are pushing an id for the user down to SQL Server from the middle tier.

HTH,

-Steven Gott

S/DET

SQL Server

Row based security

Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:

> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>

Row based security

I have a database system that contains 1 category of data.
There are plans in the future to add other categories of data as well.
Some users of the database can have access to 1 or more of the categories of
data, and other users can only have access to 1 category, and non of the
other categories of data.
I have a solution in place for row level security using stored procedures
and lookup tables to define what data can be viewed by what users.
In the Oracle world, row based security is handled, but in SQL Server there
is no mechanism in place within the security model to accommodate for it.
What are some approaches in SQL Server that others have used to accommodate
for Row level security issues?
Simon WorthCheck this link for an approach on how do implement row level security in SQ
L
Server:
http://vyaskn.tripod.com/ row_level...as
es.htm
-Sue
"Simon Worth" wrote:

> I have a database system that contains 1 category of data.
> There are plans in the future to add other categories of data as well.
> Some users of the database can have access to 1 or more of the categories
of
> data, and other users can only have access to 1 category, and non of the
> other categories of data.
> I have a solution in place for row level security using stored procedures
> and lookup tables to define what data can be viewed by what users.
> In the Oracle world, row based security is handled, but in SQL Server ther
e
> is no mechanism in place within the security model to accommodate for it.
> What are some approaches in SQL Server that others have used to accommodat
e
> for Row level security issues?
> --
> Simon Worth
>
>|||Thanks Sue,
I've read that article before, and that's actually were I developed some
ideas from.
I was just wondering what others have done in the past, or are planning on
implementing in the future.
Just curiosity I guess. Plus, once in awhile you get an answer that really
makes sense that you hadn't thought of before.
Simon Worth
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0D4434BE-321E-4A38-87BF-2D08A238D35F@.microsoft.com...
> Check this link for an approach on how do implement row level security in
SQL[vbcol=seagreen]
> Server:
> http://vyaskn.tripod.com/ row_level...as
es.htm
> -Sue
> "Simon Worth" wrote:
>
categories of[vbcol=seagreen]
procedures[vbcol=seagreen]
there[vbcol=seagreen]
it.[vbcol=seagreen]
accommodate[vbcol=seagreen]

Row based security

Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:

> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>