updation of 1200 records

Try to do the update not within one sql-statement but within a loop. So you have the update of one record into a transaction, and not 1500 rows.
Write something like this :
DECLARE @id int
SELECT @id=-999999999
WHILE EXISTS(SELECT * FROM TABA WHERE ID>@m) BEGIN
  BEGIN TRAN
  SELECT @id=MIN(ID) FROM TABA WHERE ID>@ID
  UPDATE TABA SET ....
  COMMIT TRAN
END

Could you explain me how will this approach help me reduce time of updation..

Since the Applications are different i have no control of  AppA and Appb.Moreover the tables TabA and TabB are two different table having no link atall

Regards
khacharn

Maybe your server is not powerfull enough or update is done in inefficient way
(for example it does table scan of TableA and B because there are no usefull indexes)
Post here your server configuration,table size, statements used to update tables - it will give us something to work with.

following...
additionally, the sql statements for updating the tables (rough structure is ok, full detail is not necessary) are important too (of both applications, please)
Cheers

Hi all
1 ) TableA has almost 150 columns..no indexes..
2) TableB has almost 730 columns(Don't panic..MS-SQL 7 allows 1024 !)..again no indexes..

Sample update statement for AppA
-----------------------------------------------------------
update_sql_curr_price = "update bse_intraday set p" & time_stamp_dummy & " = " & CurrentClosePrice & " where scripcode = " & scripCode & ""
                   con.Execute update_sql_curr_price
-----------------------------------------------------------


Sample update statement for AppB
-----------------------------------------------------------
update_sql_curr_price = "update bse_intraday set p" & time_stamp_dummy & " = " & CurrentClosePrice & " where scripcode = " & scripCode & ""
                   con.Execute update_sql_curr_price
-----------------------------------------------------------

Believe me the update statements are really very simple...can i make a primary key as the index for the table..will it help

I would again reteirate the BASIC PROBLEM:
************************************************
AppA is Constanbtly updating TableA..almost 1500 rows every 3-4 minutes

ALSO
AppB is contantly updating TableB almost every minute.

Now whenever both these transactions are tanking place at the same time i abserver thqat the SPEED of both decreases considerably..I feel as if both the Database transactions are trying to stop eachother to complete its operation..

I want a solution so that both the Updation transaction donot slow down..

************************************************

Regards
khacharn

Dear nigelrivett
Could you be more specific as to how do i do it..maybe some more code and explanation..
needless to say it would be great of you to do that..
Regards
khacharn

create table tblRes (id int, desc varchar(100))
go
insert tblRes select 1,'Update resource'


The updates should be done via stored procs and pass parameters into them.
As you seem to be using VB then the transaction and locking can be controlled the client
No idea if this will all work - depends how you have set up the connection.
Execute the commands one by one and see if the server is holding transactions and locks correctly.

con.execute "begin tran"
con.execute "select * from tblRes (tablockx,holdlock) where 1 = 0"
update_sql_curr_price = "update bse_intraday set p" & time_stamp_dummy & " = " & CurrentClosePrice & " where scripcode = " & scripCode & ""
                   con.Execute update_sql_curr_price
con.execute "commit tran"

I strongly suggest to add indexes to your tables, each of your  your apps does table scan ,which takes a lot of resources.

>> no indexes
>> again no indexes..
horror scenario regarding the performance.
i agree with spiridonov to add indexes, i think the scriptcode column would be a good candidate.
Add the index on both tables, compare performance then and report the results...
Cheers

Khacharn,

you should index the tables. It'll make your updates faster.

Make an index on field "scripcode" of both tables.

Next use the Nigelrivett solution.
It's a good idea to make the updates inside a transaction (you define a transaction using the keywords BEGIN TRAN and COMMIT TRAN).

I only don't understood in the Nigelrivett solution the use of the resource table (tblRes).

Good Luck

Just a thought here.

Would not adding indexes slow down the process.  As without indexes the record is just to be added to the table.

However, when we have an index (primary index) would not the index have to be rebuilt which would in effect slow down the inserts.  Secondly there will be a check to determine if the primary index already exists.

The index would surely help in Selecting the records.

Sure an index slows down inserts but if you have to find the record it will do a few page reads (depending on size of index) as opposed to reading every record in the table. Consider a table of 2000 pages - with a good index you will access maybe 3 or 4 pages - without all 2000.

as the statements are only update (at least in this case, index will only fasten the process...

Ooops.

I missed  that one out.  For an update I agree that the index would speed up things.

You must add an index (primary key should also work fine) on scripcode if the table has any significant size.

Updating through a loop as a previous poster mentioned will not help - since you will get table scans with each execution of the update. It will probably take significantly more time.

Most likely what's happening is that when you execute both AppA and AppB together, though they're working on two different tables, there may be contention for memory resources causing swapping of pages to/from disk. You might also try increasing the amount of memory you give SQL Server.

However, the first choice is to add the index (or primary key) on scripcode.

khacharn,

A number of these questions are quite old. Please return and reward the Experts who have given you their time and knowledge in responding to your question.

https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=11503298
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=11454878
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20176230
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20175433
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20174345
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20156027
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20155243
https://www.experts-exchange.com/jsp/qShow.jsp?ta=xml&qid=20011867

Thanks,

Netminder
Community Support Moderator
Experts Exchange

angelIII
>> as the statements are only update (at least in this case, index will only fasten the process...

Surprised that this came from you. Indexes can slow down updates as well as inserts.

If you take into accout that the table(s) have no indexes at all, i do not agree. The first index (typically primary key) WILL make updates faster.
Any other index of course needs to be updated if the fields in that index are updated, which can slow down the update again.

CHeers

If the page splits due to the update then the index also has to be updated.
If the table is small then the overhead from the index maintenance can be greater than the time saved in finding the record.

Sorry - having problems with a utility company today so looking for a good argument. May just go out and buy a toy instead - looking for an excuse to get a wireless network.

agreed, but an index on primary rarely gets updated (at least it shouldnt)...
CHeers

Doesn't matter whether or not the indexed value is updated - if the data page splits the index still has to be updated (unless there is also a different clustered index).

Force-accepted by

Netminder
Community Support Moderator
Experts Exchange

angelIII: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20266887