Will a View be useful?
-
Dear All,
Consider a table called Journals.It is a master table that contains the
Journal names with its id and LastNos.
The fields are
JrlID JrlName LastNo
----- ---------- ---------
Ca_P CASH PAYMENTS 00015
Ca_R CASH RECEIPTS 00250
GJV GENERAL JOURNAL 00650
etc.,
This means that In the JrlID for Cash Payments is Ca_P and there are 15 entries
made in Cash Payments.
So when every entry is made for a cash payment then LastNo is updated in Journal table
Where ever i need to show journal names in any reports i use this table
Where ever any entry is made , the LastNo is updated in this table.
So when updation takes place, the select qry seems to be very slow,and leads to deadlock
So i have a plan to create a View..say ViewJournals
Create View ViewJOurnals as Select JrlID,JrlName from Journals
And i am going to use ViewJournals whereever i need to show the Journal names.
Will this reduce the possiblity of slower selects/updates or in any case deadlocks.
Please help.
Regards,
Rohini
-
try using locking hints in your update statement so that it locks only for updates and allows select.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
A view will not help at all.
Are 'Cash payments' recorded in another detail table?
Does this table contain LastNo?
If so, it doesn't seem that you need to be storing LastNo in this table.
If you want to find the LastNo for a JrlID you would write a query linking your master table to the detail via the JrlID and finding the Max(JrlEntryNo).
If you cannot change this, you probably want to look into modifying the query with some hints has the last poster said. Here is a guide I was just looking at.
Anyway, read on if you dare, there is a good chance I'm wrong from here down!!
Someone correct me if I'm in error please. If you have a large number of connections inserting detail records, each time they finish, they need to modify the master record - this process, interacting with another which is locking the master could be the cause.
Say that a user is looking at the master records, doing a select on the table you showed above - it will result in a shared lock on the master table. It will not be updateable until the recordset is released.
Hope this helps!
-
sam you're right, unless you use dirty reads writers block readers, especially those bringing back multiple rows.
rohini you need to read up on transactions and isolation levels to understand how your database works.
A view will not help.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply