Will a view be useful here?
-
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
-
I doubt it.
A view is something that you use for abstraction and/or security.
ie to hide and 'package' complex queries and/or use as an accessable object rather than the base table.
What does your original select look like? Is it the same as in the example view, or is it different?
Are you sure that you're experiencing deadlocks, and not just 'normal blocking'?
What is the exact problem? Time? How long is too long? How long is 'normal'?
/Kenneth
-
Hai Kenneth,
Thanks
I link this Journal table with large queries to get the journal name.
Now i think to substitute the linking of select queries with journal table to linking of select queries to that ViewJournals
Yes, not just blocking, i get deadlocks. The node1 query being an update to Journal table, and say node 2 query being a select query linking journal table.
The victim happens to be either node 1 or node 2.
Any help reg. the use of view appreciated
Rohini
-
View will not change anything.
As I understand, you only need to use ID and name of the journal in these complex select queries. Since these will change very rarely (as opposed to LastNo), risk of problems caused by dirty reads is very low - I suggest you use WITH(NOLOCK) hint for this table.
Example:
SELECT a.col1, a.col2, j.jrlname
FROM sometable a
JOIN journals j WITH(NOLOCK) ON j.JrlID=a.journal
Look up WITH(NOLOCK) in BOL, it is explained pretty well there. Basically it means that for this table no locks are created, and if the table is already locked by some other process (like the update), such lock is simply ignored.
-
Sometimes dirty reads can prove to be helpful, though one needs to be aware that it's not entirely without risks.
There is a (perhaps remote, but nevertheless) risk, that a dirty read may either miss out on some data, or read the same data more than once. The circumstances around this involves page splits done by another process than the read along with a scan in fileorder, that happens during the read. So, it may be 'low chance', or not an issue anyway, but it's something to take into account when pondering if dirty reads are the right thing for you.
/Kenneth
-
My advice:
1. Limit the results you need (do not bring the entire table down [if you can])
2. Make sure the select (reading query) uses appropriate indexes ( same for updates)
3. If you still see deadlocks instead of (NOLOCK) Try (READPAST) first!
4. You are *not* guarrantee that your hints are going to be respected by the optimizer. Hints are nothing but well ... "hints". The optimizer *most of the time* respects your wishes but not 100% of the time
Therefore you need to incorporate retry logic on your clients.5. if the update statement is happening on multiple columns try to update only the columns you need.
6. If you are using SQL 2005 and the overhead on that server interms of DML is not too big try changing that database to "read-commited snapshot isolation"
7. Last and not Least ... GOOD LUCK!
Cheers,
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply