Speed of execution - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Speed of execution

Author

Topic

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-03-18 : 13:15:20

Hello experts! I need Your advice!

I have small table(<30rows) but query count to this table are about 10 per second all time! Is it better to use indexes in this case or maybe(for speed) it is not neccessary use indexes at all for that table? Because this table updates from another process very often.

Thanx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-18 : 13:31:55

no indexwes wont help you. you do mean less than 30 rows, right?

your count is probably being blocked.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-18 : 13:34:50

Sounds like the table has a lot of contention and your read (count query) is being blocked by some long running writes to the table. You can confirm the blocking by running (from a query analyzer window) "exec SP_WHO2 active". A column is returned called BlkBy which will contain the SPID of the process blocking.

then you can run: "dbcc inputbuffer(<spid>)" to see what command is blocking.

Are you using access or something to directly access this table? That could cause long running locks.

Be One with the Optimizer
TG

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-03-18 : 15:39:46

OK. But how can I speed up UPDATE query?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-18 : 16:01:04

well that depends completly on your update statement and concurrency

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-03-18 : 16:14:40

UPDATE ClearingFarm SET ClearingFarm.[Date] = CONVERT(DATETIME, '2007-12-26 23:17:06', 102) , ClearingFarm.Ask = 1.4493, ClearingFarm.Bid = 1.4491, Modified={ fn NOW() }, IsAllowed=0 WHERE (ClearingFarm.symbol='EURUSD') AND (ClearingFarm.Broker_id=10)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-18 : 16:20:34

emm is this sql server or access?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-03-18 : 16:26:36

mssql 2000

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-18 : 16:31:21

use getdate() instead of { fn NOW() }.

do this
begin tran
your update
exec sp_lock -- look at the locks being take
rollback

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-03-18 : 16:34:04

Thanx! Is it all that I can do to speed up?

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-03-18 : 16:37:26

After I ran exec sp_lock, I get a table of data! How can I analyze this data? All rows ar with mark GRANT

Thanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources