
Signature
Andrew J. Kelly SQL MVP
Solid Quality Mentors
HI
I have webservice which delete , insert a update data from sql database.
sqlcon.Open();
sqlcom.CommandText = string.Format("delete from do_denni WITH (ROWLOCK)
where oscislo = {0} and convert(datetime,'{1}',104) <= datum and
DATEADD(dd,1,convert(datetime,'{2}',104)) <= datum ", oscislo,
datumod.ToString("dd.MM.yyyy"), datumdo.ToString("dd.MM.yyyy"));
sqlcom.ExecuteNonQuery();
in delete i delete rows for person with specific date range .... After
delete this I insert new rows . The webservice is called 50x in the same
time.....
thanx
> Can you be more specific? What do you mean 50x delete? SQL Server code
> just like nay other code is basically procedural in which the first
[quoted text clipped - 7 lines]
>>
>> thanx
Fred - 22 Jul 2008 06:59 GMT
Dans : news:ev2rht76IHA.1468@TK2MSFTNGP05.phx.gbl,
Marek Grolms écrivait :
> HI
> I have webservice which delete , insert a update data from sql
[quoted text clipped - 4 lines]
> oscislo, datumod.ToString("dd.MM.yyyy"),
> datumdo.ToString("dd.MM.yyyy")); sqlcom.ExecuteNonQuery();
First of all, you should correct your code to use SqlParameters instead
of String.Format parameters.
Have a look at SQL Injection in MSDN or on the web.

Signature
Fred
foleide@free.fr
Uri Dimant - 22 Jul 2008 07:33 GMT
Marek
What is an output? Do you have any indexes define on the tables?
ELECT resource_type, resource_description,
resource_associated_entity_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id > 0
> HI
> I have webservice which delete , insert a update data from sql database.
[quoted text clipped - 22 lines]
>>>
>>> thanx
Marek - 22 Jul 2008 08:43 GMT
> Marek
> What is an output? Do you have any indexes define on the tables?
[quoted text clipped - 39 lines]
>
> - Show quoted text -
processor works on 100% permanently
KEY
(7901ef738398)
72057594043498496 U GRANT
KEY
(780101dc368a)
72057594043498496 U GRANT
KEY
(7201653c8be5)
72057594043498496 U GRANT
KEY
(750157cc55d2)
72057594043498496 U GRANT
KEY
(7401b963e0c0)
72057594043498496 U GRANT
KEY
(6f018b933ef7)
72057594043498496 U GRANT
KEY
(7401e2d74e5a)
72057594043432960 X GRANT
KEY
(76013e88257f)
72057594043432960 X GRANT
KEY
(8401e635f030)
72057594043432960 X GRANT
PAGE
1:76444
72057594042974208 IU GRANT
KEY
(8101089a4522)
72057594043432960 X GRANT
KEY
(86013a6a9b15)
72057594043432960 X GRANT
KEY
(8701d4c52e07)
72057594043432960 X GRANT
KEY
(8d01b0259368)
72057594043432960 X GRANT
KEY
(8a0182d54d5f)
72057594043432960 X GRANT
KEY
(8b016c7af84d)
72057594043432960 X GRANT
KEY
(90015e8a267a)
72057594043432960 X GRANT
KEY
(7e0149e210bb)
72057594043432960 X GRANT
KEY
(7f01a74da5a9)
72057594043432960 X GRANT
KEY
(7c0195bd7b9e)
72057594043432960 X GRANT
KEY
(9501c05a3efd)
72057594043432960 X GRANT
PAGE
1:76457
72057594042974208 IU GRANT
PAGE
1:76458
72057594042974208 IU GRANT
KEY
(93011c0555d8)
72057594043432960 X GRANT
KEY
(9201f2aae0ca)
72057594043432960 X GRANT
KEY
(5401a1d81883)
72057594043498496 X GRANT
KEY
(5a017d8773a6)
72057594043498496 X GRANT
KEY
(7401323de3cc)
72057594043498496 U GRANT
KEY
(4c01d1a7b516)
72057594043498496 X GRANT
KEY
(7101dc9256de)
72057594043498496 U GRANT
KEY
(52010df8de33)
72057594043498496 X GRANT
KEY
(7601ee6288e9)
72057594043498496 U GRANT
KEY
(770100cd3dfb)
72057594043498496 U GRANT
KEY
(48016918635c)
72057594043498496 X GRANT
KEY
(7d01642d8094)
72057594043498496 U GRANT
KEY
(7a0156dd5ea3)
72057594043498496 U GRANT
OBJECT
1826105546 Sch-S GRANT
OBJECT
1826105546 Sch-S GRANT
OBJECT
1826105546 Sch-S GRANT
OBJECT
1826105546 Sch-S GRANT
OBJECT
1826105546 Sch-S GRANT
KEY
(4601b5470879)
72057594043498496 X GRANT
KEY
(7b01b872ebb1)
72057594043498496 U GRANT
KEY
(0201be3304cb)
72057594043498496 X GRANT
KEY
(0401626c6fee)
72057594043498496 X GRANT
KEY
(4301cf89040d)
72057594043498496 X GRANT
KEY
(f000a95b320a)
72057594043498496 X GRANT
KEY
(f6007504592f)
72057594043498496 X GRANT
KEY
(fc0011e4e440)
72057594043498496 X GRANT
KEY
(fa00cdbb8f65)
72057594043498496 X GRANT
KEY
(ee00057bf4ba)
72057594043498496 X GRANT
KEY
(8f013bed9ac2)
72057594043432960 X GRANT
KEY
(8e01d5422fd0)
72057594043432960 X GRANT
KEY
(8901e7b2f1e7)
72057594043432960 X GRANT
KEY
(8c01091d44f5)
72057594043432960 X GRANT
KEY
(830183524c88)
72057594043432960 X GRANT
KEY
(82016dfdf99a)
72057594043432960 X GRANT
KEY
(8801b1a292bf)
72057594043432960 X GRANT
KEY
(85015f0d27ad)
72057594043432960 X GRANT
KEY
(9601a53d8245)
72057594043432960 X GRANT
KEY
(94017962e960)
72057594043432960 X GRANT
KEY
(910197cd5c72)
72057594043432960 X GRANT
KEY
(7d012c85ac03)
72057594043432960 X GRANT
KEY
(7b01f0dac726)
72057594043432960 X GRANT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(5c014ef0ac46)
72057594043498496 X GRANT
KEY
(8001c22a1911)
72057594043432960 X GRANT
KEY
(56012a101129)
72057594043498496 X GRANT
KEY
(700115d52cd6)
72057594043498496 U GRANT
KEY
(700115d52cd6)
72057594043498496 U WAIT
KEY
(5801f64f7a0c)
72057594043498496 X GRANT
KEY
(4a01e2d06af6)
72057594043498496 X GRANT
KEY
(790133bae21b)
72057594043498496 U GRANT
KEY
(44013e8f01d3)
72057594043498496 X GRANT
KEY
(7c01dd155709)
72057594043498496 U GRANT
KEY
(50018630d799)
72057594043498496 X GRANT
KEY
(7301575a5f74)
72057594043498496 U GRANT
KEY
(4e015a6fbcbc)
72057594043498496 X GRANT
KEY
(7201b9f5ea66)
72057594043498496 U GRANT
KEY
(780165aa8143)
72057594043498496 U GRANT
KEY
(75018b053451)
72057594043498496 U GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IX GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:2727
72057594042646528 IU GRANT
KEY
(7001eef4824f)
72057594043498496 U GRANT
KEY
(7101005b375d)
72057594043498496 U GRANT
KEY
(760132abe96a)
72057594043498496 U GRANT
KEY
(7301dc045c78)
72057594043498496 U GRANT
KEY
(770164bb8a32)
72057594043498496 U GRANT
KEY
(7a018a143f20)
72057594043498496 U GRANT
KEY
(6e0142d444ff)
72057594043498496 U GRANT
KEY
(7201691f47f0)
72057594043432960 X GRANT
KEY
(7201691f47f0)
72057594043432960 U WAIT
KEY
(7201691f47f0)
72057594043432960 U WAIT
KEY
(7801b5402cd5)
72057594043432960 X GRANT
KEY
(fe009a2cedea)
72057594043498496 X GRANT
KEY
(f800467386cf)
72057594043498496 X GRANT
KEY
(f20022933ba0)
72057594043498496 X GRANT
KEY
(f400fecc5085)
72057594043498496 X GRANT
KEY
(ea0052ec9635)
72057594043498496 X GRANT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
PAGE
1:17319
72057594043498496 IU GRANT
PAGE
1:17319
72057594043498496 IU GRANT
KEY
(ec008eb3fd10)
72057594043498496 X GRANT
PAGE
5:11012
72057594043432960 IU GRANT
PAGE
5:11012
72057594043432960 IX GRANT
PAGE
5:11012
72057594043432960 IU GRANT
KEY
(5005584715a6)
72057594042974208 U GRANT
KEY
(4e056fa9b681)
72057594042974208 U GRANT
PAGE
5:11202
72057594043432960 IU GRANT
PAGE
5:11202
72057594043432960 IX GRANT
KEY
(000135fb0d61)
72057594043498496 X GRANT
KEY
(d9003beca8c0)
72057594043301888 X GRANT
PAGE
1:17566
72057594043498496 IU GRANT
PAGE
1:17565
72057594043498496 IU GRANT
KEY
(be00a6228144)
72057594043432960 X GRANT
KEY
(bf00488d3456)
72057594043432960 X GRANT
PAGE
1:17598
72057594043498496 IU GRANT
KEY
(c10094d25f73)
72057594043432960 X GRANT
KEY
(8605c4bf6123)
72057594042908672 U GRANT
KEY
(73010c78fb48)
72057594043432960 X GRANT
PAGE
5:11315
72057594043432960 IU GRANT
PAGE
5:11315
72057594043432960 IU GRANT
PAGE
5:11315
72057594043432960 IU GRANT
PAGE
5:11315
72057594043432960 IU GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
KEY
(57014f77ad91)
72057594043498496 X GRANT
KEY
(59019328c6b4)
72057594043498496 X GRANT
KEY
(5d012b9710fe)
72057594043498496 X GRANT
KEY
(5101e3576b21)
72057594043498496 X GRANT
KEY
(4f013f080004)
72057594043498496 X GRANT
KEY
(4b0187b7d64e)
72057594043498496 X GRANT
KEY
(45015be8bd6b)
72057594043498496 X GRANT
KEY
(0101509cb1d9)
72057594043498496 X GRANT
KEY
(f30047f48718)
72057594043498496 X GRANT
OBJECT
2146106686 IX GRANT
OBJECT
2146106686 IS GRANT
OBJECT
2146106686 IS GRANT
OBJECT
2146106686 IX GRANT
OBJECT
2146106686 IX GRANT
OBJECT
2146106686 IX GRANT
OBJECT
2146106686 IS GRANT
OBJECT
2146106686 IS GRANT
OBJECT
2146106686 IS GRANT
KEY
(f5009babec3d)
72057594043498496 X GRANT
KEY
(760142969880)
72057594043498496 U GRANT
KEY
(ff00ff4b5152)
72057594043498496 X GRANT
KEY
(f90023143a77)
72057594043498496 X GRANT
KEY
(c9006166b5da)
72057594043432960 U GRANT
KEY
(eb00378b2a8d)
72057594043498496 X GRANT
KEY
(c40031ab9aa1)
72057594043432960 U GRANT
KEY
(c100df042fb3)
72057594043432960 U GRANT
KEY
(ed00ebd441a8)
72057594043498496 X GRANT
KEY
(ca00554b27ce)
72057594043432960 U GRANT
KEY
(cb00bbe492dc)
72057594043432960 U GRANT
KEY
(c50067bbf9f9)
72057594043432960 U GRANT
KEY
(c80089144ceb)
72057594043432960 U GRANT
KEY
(7801ed41780b)
72057594043498496 U GRANT
KEY
(790103eecd19)
72057594043498496 U GRANT
KEY
(7b01dfb1a63c)
72057594043498496 U GRANT
OBJECT
2114106572 IX GRANT
PAGE
5:11889
72057594043432960 IU GRANT
KEY
(5501c4bfa43b)
72057594043498496 X GRANT
KEY
(5b0118e0cf1e)
72057594043498496 X GRANT
KEY
(49010c7fdfe4)
72057594043498496 X GRANT
KEY
(4701d020b4c1)
72057594043498496 X GRANT
KEY
(4d01b4c009ae)
72057594043498496 X GRANT
KEY
(5301689f628b)
72057594043498496 X GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IX GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16102
72057594043498496 IX GRANT
KEY
(bd00c3453dfc)
72057594043432960 X GRANT
KEY
(bd00c3453dfc)
72057594043432960 U WAIT
KEY
(c0002dea88ee)
72057594043432960 X GRANT
KEY
(c200f1b5e3cb)
72057594043432960 X GRANT
KEY
(c3001f1a56d9)
72057594043432960 X GRANT
KEY
(750187b0f2e2)
72057594043432960 X GRANT
KEY
(77015bef99c7)
72057594043432960 X GRANT
KEY
(fd00748358f8)
72057594043498496 X GRANT
KEY
(fb00a8dc33dd)
72057594043498496 X GRANT
KEY
(f100cc3c8eb2)
72057594043498496 X GRANT
KEY
(750127f12438)
72057594043498496 U GRANT
KEY
(7401c95e912a)
72057594043498496 U GRANT
KEY
(f7001063e597)
72057594043498496 X GRANT
KEY
(c900302c9b76)
72057594043432960 U GRANT
KEY
(cc00de832e64)
72057594043432960 U GRANT
KEY
(c60002dc4541)
72057594043432960 U GRANT
KEY
(c700ec73f053)
72057594043432960 U GRANT
KEY
(ca0004010962)
72057594043432960 U GRANT
KEY
(c000663cf82e)
72057594043432960 U GRANT
KEY
(c000663cf82e)
72057594043432960 U WAIT
KEY
(c000663cf82e)
72057594043432960 U WAIT
KEY
(c000663cf82e)
72057594043432960 U WAIT
KEY
(cb00eaaebc70)
72057594043432960 U GRANT
KEY
(ef00601c4802)
72057594043498496 X GRANT
KEY
(c30054cc2619)
72057594043432960 U GRANT
KEY
(c200ba63930b)
72057594043432960 U GRANT
KEY
(77018826c4b3)
72057594043498496 U GRANT
KEY
(7a01668971a1)
72057594043498496 U GRANT
PAGE
5:8052
72057594042908672 IU GRANT
PAGE
5:8042
72057594042908672 S GRANT
PAGE
5:8043
72057594042908672 S GRANT
PAGE
1:79279
72057594042974208 S GRANT
PAGE
1:79280
72057594042974208 IU GRANT
KEY
(0301db54b873)
72057594043498496 X GRANT
KEY
(0501070bd356)
72057594043498496 X GRANT
KEY
(4201aaeeb8b5)
72057594043498496 X GRANT
PAGE
1:79317
72057594042974208 S GRANT
PAGE
1:79317
72057594042974208 S GRANT
PAGE
1:79318
72057594042974208 IU GRANT
Marek - 22 Jul 2008 10:09 GMT
why
delete from do_kumulace WITH (ROWLOCK) where oscislo = 501212 and
convert(datetime,'01.03.2007',104) <= do and
convert(datetime,'31.03.2007',104) >= od
blocking this command ?
delete from do_intervaly WITH (ROWLOCK) where oscislo = 501070 and
convert(datetime,'17.09.2007',104) <= datum and
convert(datetime,'23.09.2007',104) >= datum
?
dave ballantyne - 22 Jul 2008 10:16 GMT
> why
> delete from do_kumulace WITH (ROWLOCK) where oscislo = 501212 and
[quoted text clipped - 6 lines]
>
> ?
Whats the relationship between do_kumulace and do_intervaly is one a
view on another ? Does one have a cascading delete to the other ?
Does
Select * from do_kumulace where oscislo = 501212 and
convert(datetime,'01.03.2007',104) <= do and
convert(datetime,'31.03.2007',104) >= od
and
Select * from do_intervaly where oscislo = 501070 and
convert(datetime,'17.09.2007',104) <= datum and
convert(datetime,'23.09.2007',104) >= datum
execute with a decent query plan ?
Im assuming that do ,od and datnum are datetimes , in that case you dont
need the convert.
Has the app left a transaction open ?
Dave
Marek - 22 Jul 2008 11:21 GMT
I am not use transaction in the application ? I use sqlconnection and
sqlcomand . Relation between tables is not ....
Uri Dimant - 22 Jul 2008 10:35 GMT
Marek
There are many objects (72057594043498496,72057594043432960 ) can show us by
using OBJECT_NAME... to what table/index does it relate?
Again, do you have index on do? or oscislo ? Clustered Index might be a good
candidate 'datum' column
> why
> delete from do_kumulace WITH (ROWLOCK) where oscislo = 501212 and
[quoted text clipped - 6 lines]
>
> ?
Marek - 22 Jul 2008 11:23 GMT
> Marek
> There are many objects (72057594043498496,72057594043432960 ) can show us by
[quoted text clipped - 15 lines]
>
> - Show quoted text -
how I get information about object 72057594043498496 ?
Uri Dimant - 22 Jul 2008 13:06 GMT
Marek
OBJECT_NAME functions takes a parameter objectid
SELECT OBJECT_NAME (72057594043498496)
On Jul 22, 11:35 am, "Uri Dimant" <u...@iscar.co.il> wrote:
> Marek
> There are many objects (72057594043498496,72057594043432960 ) can show us
[quoted text clipped - 21 lines]
>
> - Show quoted text -
how I get information about object 72057594043498496 ?
Marek - 22 Jul 2008 14:49 GMT
> Marek
> OBJECT_NAME functions takes a parameter objectid
[quoted text clipped - 31 lines]
>
> - Show quoted text -
I want try this , but in this table not object id ....
SELECT resource_type,
resource_description,resource_associated_entity_id, request_mode,
request_status
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id >
0
Marek - 22 Jul 2008 14:53 GMT
> Marek
> OBJECT_NAME functions takes a parameter objectid
[quoted text clipped - 31 lines]
>
> - Show quoted text -
Arithmetic overflow error converting expression to data type int.