Is there an easy way to do get a row number in each row in a select
statement?
Something like:
select rownumber,description price from lineorder order by row number
Thanks,
TomTshad,
Yes.
See:
How to dynamically number rows in a SELECT statement.
http://support.microsoft.com/defaul...kb;en-us;186133
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||Why can't the presentation tier do this? It's the only place that HAS to
loop through each row, one by one. Now you're forcing the database to do it
too, and performance can only suffer for it.
http://www.aspfaq.com/2427
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eV$hgGZ2FHA.636@.TK2MSFTNGP10.phx.gbl...
> Tshad,
> Yes.
> See:
> How to dynamically number rows in a SELECT statement.
> http://support.microsoft.com/defaul...kb;en-us;186133
I tried that:
Select rank=count(*),
Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
r.ItemName end as Description,
Price, PurchaseQty, TotalPrice = Price * PurchaseQty
from PurchaseDetail pd
join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
where CompanyID = 153973
group by Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2
then r.ItemName end,Price,PurchaseQty,Price * PurchaseQty
order by 1
But in my 8 rows returned I got (1,1,1,1,1,1,2,2)'
Is the Joins causing me a problem?
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OUJZsHZ2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Why can't the presentation tier do this? It's the only place that HAS to
> loop through each row, one by one. Now you're forcing the database to do
> it too, and performance can only suffer for it.
Actually, it can.
But that is an extra step,as I am binding it to a datagrid.
Tom
> http://www.aspfaq.com/2427
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
My philosophy is that rules of database normalization apply only to physical
tables and not to query results.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||> My philosophy is that rules of database normalization apply only to
> physical tables and not to query results.
FWIW, my objection to doing this in the database has nothing to do with
normalization at all, but with the extra work required (whether using a
subquery, or copying all the data to a separate table first). A simple
counter at the presentation layer is the very least impact on performance,
since it has to loop through all rows and display them one by one anyway.
A|||This assumes that the result is being consumed in such a way that the
developer can add the additional computed column. It may be exported from
DTS to a table or file, executed only in Query Analyzer and pasted into
email, or bound to a datagrid.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utST6ik2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> FWIW, my objection to doing this in the database has nothing to do with
> normalization at all, but with the extra work required (whether using a
> subquery, or copying all the data to a separate table first). A simple
> counter at the presentation layer is the very least impact on performance,
> since it has to loop through all rows and display them one by one anyway.
> A
>|||> This assumes that the result is being consumed in such a way that the
> developer can add the additional computed column. It may be exported from
> DTS to a table or file, executed only in Query Analyzer and pasted into
> email, or bound to a datagrid.
Yep, that's why I asked, "why can't the presentation tier do this?" and did
not say "ONLY the presentation tier can do this!"|||But do you think that only the presentation tier *should* do this?
;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhisP5k2FHA.1572@.TK2MSFTNGP10.phx.gbl...
> Yep, that's why I asked, "why can't the presentation tier do this?" and
> did not say "ONLY the presentation tier can do this!"
>
Showing posts with label statements. Show all posts
Showing posts with label statements. Show all posts
Wednesday, March 21, 2012
Row lock - Hold Lock
If I issue a Rowlock and holdlock statements in my select statement, would it
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.
First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.
|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>
|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>
|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.
First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.
|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>
|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>
|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
Row lock - Hold Lock
If I issue a Rowlock and holdlock statements in my select statement, would it
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> > If I issue a Rowlock and holdlock statements in my select statement, would
> > it
> > still escalate to pagelock or tablelock or it would keep the rowlock until
> > the transaction is done ?.
> >
> > Thanks.
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>> First off you can not limit the lock to a row by specifying the hint.
>> That
>> only tells it to start there but it is still free to escalate up to a
>> table
>> if the conditions are right. Locks never escalate from row to page, they
>> always go straight to table if they escalate at all. Adding HOLDLOCK to
>> a
>> select does little or nothing to the way the locks are done. By default
>> SQL
>> Server will lock the row as it is reading it and you don't need a hint to
>> do
>> that. It releases it when it is done reading the row. HOLDLOCK is
>> usually
>> used to hold the locks until the end of a transaction that was started
>> with
>> a BEGIN TRAN and has multiple statements in it. What is the intended
>> purpose
>> of the hint and why are you worried about it escalating? If you have
>> proper
>> indexes and a proper WHERE clause it should never escalate unless you are
>> trying to touch a major portion of the total rows.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> > If I issue a Rowlock and holdlock statements in my select statement,
>> > would
>> > it
>> > still escalate to pagelock or tablelock or it would keep the rowlock
>> > until
>> > the transaction is done ?.
>> >
>> > Thanks.
>>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> > Thanks for the info.......
> >
> > We have a table with 15 columns. The first column is 'username' that has a
> > clustered index. 12 of the rest of the columns are
> > deleted/inserted/updated
> > by individual users. If the users are running the same process at the same
> > time, each user inserts ~30000 rows into the table after deleting the rows
> > that are belong to them.
> >
> > Out of the profiler, I have seen a few lock escalations. What is the best
> > way to index these columns ?
> >
> > username column is like below;
> >
> > username
> >
> > user1
> > user1
> > user1
> > user1
> > user1
> > user1
> > user2
> > user2
> > user2
> > user2
> > user2
> > user3
> > user3
> > user3
> > user3
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> First off you can not limit the lock to a row by specifying the hint.
> >> That
> >> only tells it to start there but it is still free to escalate up to a
> >> table
> >> if the conditions are right. Locks never escalate from row to page, they
> >> always go straight to table if they escalate at all. Adding HOLDLOCK to
> >> a
> >> select does little or nothing to the way the locks are done. By default
> >> SQL
> >> Server will lock the row as it is reading it and you don't need a hint to
> >> do
> >> that. It releases it when it is done reading the row. HOLDLOCK is
> >> usually
> >> used to hold the locks until the end of a transaction that was started
> >> with
> >> a BEGIN TRAN and has multiple statements in it. What is the intended
> >> purpose
> >> of the hint and why are you worried about it escalating? If you have
> >> proper
> >> indexes and a proper WHERE clause it should never escalate unless you are
> >> trying to touch a major portion of the total rows.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> >> > If I issue a Rowlock and holdlock statements in my select statement,
> >> > would
> >> > it
> >> > still escalate to pagelock or tablelock or it would keep the rowlock
> >> > until
> >> > the transaction is done ?.
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>> That doesn't make a lot of sense. You can't Delete or Insert a column.
>> I
>> assume you mean the users Insert, Update or Delete the rows. How many
>> rows
>> in the whole table? If you attempt to Delete 30K rows in a relatively
>> small
>> table then SQL Server will most likely try to take out a table level
>> lock.
>> Does the DELETE include the clustered column in the WHERE clause? You
>> can
>> stop escalation to the table level by always having at least one shared
>> lock
>> in the table. But if you delete the rows in smaller batches you won't
>> escalate as long as they are not all wrapped in a single transaction.
>>
>> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and
>> is
>> efficient.
>> WHILE 1 = 1
>> BEGIN
>> DELETE FROM YourTable WHERE UserName = xxx
>> IF @.@.ROWCOUNT = 0
>> BREAK
>> END
>> SET ROWCOUNT 0
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>> > Thanks for the info.......
>> >
>> > We have a table with 15 columns. The first column is 'username' that
>> > has a
>> > clustered index. 12 of the rest of the columns are
>> > deleted/inserted/updated
>> > by individual users. If the users are running the same process at the
>> > same
>> > time, each user inserts ~30000 rows into the table after deleting the
>> > rows
>> > that are belong to them.
>> >
>> > Out of the profiler, I have seen a few lock escalations. What is the
>> > best
>> > way to index these columns ?
>> >
>> > username column is like below;
>> >
>> > username
>> >
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user2
>> > user2
>> > user2
>> > user2
>> > user2
>> > user3
>> > user3
>> > user3
>> > user3
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> First off you can not limit the lock to a row by specifying the hint.
>> >> That
>> >> only tells it to start there but it is still free to escalate up to a
>> >> table
>> >> if the conditions are right. Locks never escalate from row to page,
>> >> they
>> >> always go straight to table if they escalate at all. Adding HOLDLOCK
>> >> to
>> >> a
>> >> select does little or nothing to the way the locks are done. By
>> >> default
>> >> SQL
>> >> Server will lock the row as it is reading it and you don't need a hint
>> >> to
>> >> do
>> >> that. It releases it when it is done reading the row. HOLDLOCK is
>> >> usually
>> >> used to hold the locks until the end of a transaction that was started
>> >> with
>> >> a BEGIN TRAN and has multiple statements in it. What is the intended
>> >> purpose
>> >> of the hint and why are you worried about it escalating? If you have
>> >> proper
>> >> indexes and a proper WHERE clause it should never escalate unless you
>> >> are
>> >> trying to touch a major portion of the total rows.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> >> > If I issue a Rowlock and holdlock statements in my select statement,
>> >> > would
>> >> > it
>> >> > still escalate to pagelock or tablelock or it would keep the rowlock
>> >> > until
>> >> > the transaction is done ?.
>> >> >
>> >> > Thanks.
>> >>
>> >>
>> >>
>>sql
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> > If I issue a Rowlock and holdlock statements in my select statement, would
> > it
> > still escalate to pagelock or tablelock or it would keep the rowlock until
> > the transaction is done ?.
> >
> > Thanks.
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>> First off you can not limit the lock to a row by specifying the hint.
>> That
>> only tells it to start there but it is still free to escalate up to a
>> table
>> if the conditions are right. Locks never escalate from row to page, they
>> always go straight to table if they escalate at all. Adding HOLDLOCK to
>> a
>> select does little or nothing to the way the locks are done. By default
>> SQL
>> Server will lock the row as it is reading it and you don't need a hint to
>> do
>> that. It releases it when it is done reading the row. HOLDLOCK is
>> usually
>> used to hold the locks until the end of a transaction that was started
>> with
>> a BEGIN TRAN and has multiple statements in it. What is the intended
>> purpose
>> of the hint and why are you worried about it escalating? If you have
>> proper
>> indexes and a proper WHERE clause it should never escalate unless you are
>> trying to touch a major portion of the total rows.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> > If I issue a Rowlock and holdlock statements in my select statement,
>> > would
>> > it
>> > still escalate to pagelock or tablelock or it would keep the rowlock
>> > until
>> > the transaction is done ?.
>> >
>> > Thanks.
>>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> > Thanks for the info.......
> >
> > We have a table with 15 columns. The first column is 'username' that has a
> > clustered index. 12 of the rest of the columns are
> > deleted/inserted/updated
> > by individual users. If the users are running the same process at the same
> > time, each user inserts ~30000 rows into the table after deleting the rows
> > that are belong to them.
> >
> > Out of the profiler, I have seen a few lock escalations. What is the best
> > way to index these columns ?
> >
> > username column is like below;
> >
> > username
> >
> > user1
> > user1
> > user1
> > user1
> > user1
> > user1
> > user2
> > user2
> > user2
> > user2
> > user2
> > user3
> > user3
> > user3
> > user3
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> First off you can not limit the lock to a row by specifying the hint.
> >> That
> >> only tells it to start there but it is still free to escalate up to a
> >> table
> >> if the conditions are right. Locks never escalate from row to page, they
> >> always go straight to table if they escalate at all. Adding HOLDLOCK to
> >> a
> >> select does little or nothing to the way the locks are done. By default
> >> SQL
> >> Server will lock the row as it is reading it and you don't need a hint to
> >> do
> >> that. It releases it when it is done reading the row. HOLDLOCK is
> >> usually
> >> used to hold the locks until the end of a transaction that was started
> >> with
> >> a BEGIN TRAN and has multiple statements in it. What is the intended
> >> purpose
> >> of the hint and why are you worried about it escalating? If you have
> >> proper
> >> indexes and a proper WHERE clause it should never escalate unless you are
> >> trying to touch a major portion of the total rows.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> >> > If I issue a Rowlock and holdlock statements in my select statement,
> >> > would
> >> > it
> >> > still escalate to pagelock or tablelock or it would keep the rowlock
> >> > until
> >> > the transaction is done ?.
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>> That doesn't make a lot of sense. You can't Delete or Insert a column.
>> I
>> assume you mean the users Insert, Update or Delete the rows. How many
>> rows
>> in the whole table? If you attempt to Delete 30K rows in a relatively
>> small
>> table then SQL Server will most likely try to take out a table level
>> lock.
>> Does the DELETE include the clustered column in the WHERE clause? You
>> can
>> stop escalation to the table level by always having at least one shared
>> lock
>> in the table. But if you delete the rows in smaller batches you won't
>> escalate as long as they are not all wrapped in a single transaction.
>>
>> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and
>> is
>> efficient.
>> WHILE 1 = 1
>> BEGIN
>> DELETE FROM YourTable WHERE UserName = xxx
>> IF @.@.ROWCOUNT = 0
>> BREAK
>> END
>> SET ROWCOUNT 0
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>> > Thanks for the info.......
>> >
>> > We have a table with 15 columns. The first column is 'username' that
>> > has a
>> > clustered index. 12 of the rest of the columns are
>> > deleted/inserted/updated
>> > by individual users. If the users are running the same process at the
>> > same
>> > time, each user inserts ~30000 rows into the table after deleting the
>> > rows
>> > that are belong to them.
>> >
>> > Out of the profiler, I have seen a few lock escalations. What is the
>> > best
>> > way to index these columns ?
>> >
>> > username column is like below;
>> >
>> > username
>> >
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user2
>> > user2
>> > user2
>> > user2
>> > user2
>> > user3
>> > user3
>> > user3
>> > user3
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> First off you can not limit the lock to a row by specifying the hint.
>> >> That
>> >> only tells it to start there but it is still free to escalate up to a
>> >> table
>> >> if the conditions are right. Locks never escalate from row to page,
>> >> they
>> >> always go straight to table if they escalate at all. Adding HOLDLOCK
>> >> to
>> >> a
>> >> select does little or nothing to the way the locks are done. By
>> >> default
>> >> SQL
>> >> Server will lock the row as it is reading it and you don't need a hint
>> >> to
>> >> do
>> >> that. It releases it when it is done reading the row. HOLDLOCK is
>> >> usually
>> >> used to hold the locks until the end of a transaction that was started
>> >> with
>> >> a BEGIN TRAN and has multiple statements in it. What is the intended
>> >> purpose
>> >> of the hint and why are you worried about it escalating? If you have
>> >> proper
>> >> indexes and a proper WHERE clause it should never escalate unless you
>> >> are
>> >> trying to touch a major portion of the total rows.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> >> > If I issue a Rowlock and holdlock statements in my select statement,
>> >> > would
>> >> > it
>> >> > still escalate to pagelock or tablelock or it would keep the rowlock
>> >> > until
>> >> > the transaction is done ?.
>> >> >
>> >> > Thanks.
>> >>
>> >>
>> >>
>>sql
Row lock - Hold Lock
If I issue a Rowlock and holdlock statements in my select statement, would i
t
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a tabl
e
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default S
QL
> Server will lock the row as it is reading it and you don't need a hint to
do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started wit
h
> a BEGIN TRAN and has multiple statements in it. What is the intended purpo
se
> of the hint and why are you worried about it escalating? If you have prop
er
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many row
s
> in the whole table? If you attempt to Delete 30K rows in a relatively sma
ll
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lo
ck
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>
t
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a tabl
e
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default S
QL
> Server will lock the row as it is reading it and you don't need a hint to
do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started wit
h
> a BEGIN TRAN and has multiple statements in it. What is the intended purpo
se
> of the hint and why are you worried about it escalating? If you have prop
er
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many row
s
> in the whole table? If you attempt to Delete 30K rows in a relatively sma
ll
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lo
ck
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>
Subscribe to:
Posts (Atom)