SQL Server Performance Forum – Threads Archive
sql locking and ms access
hello guysi have a question. every day i’m running a process to update about 3,000,000 records on table that has like 15,000,000 records. i’m using update with (rowlock) hint. i have some users using ms access conecting to the sql server via odbc. when i’m running the update, they’re also trying to run queries against the table that is been updated. but their queries are timing out. is there a way to fix this problem?? i know that if a run a query from sql analyzer with (nolock), it would work fine. but they can not use the hint with access, unless they use a pass trought query. but they won’t know how to use sql commands. so i was wonder if there is something that i need to change on the sql server or something to change on ms access thanks
How long take sql query?.
I asking that, because If you can’t update wiht nolock, there is no to much to do.
But if sql can optimize, may be this reduce timing out.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
well they way the update works is like this. i have a sql job that would
add the 3 million records first and then would do the update on those records that were inserted.
people can read the table, but nobody would add more records while the table is beeing updated. so it would be possible for me to use with (nolock) rather than with(rowlock)??
No at all!!!.
See execution plan in Query analyzer to find if query can optimize. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
ok,i’ll try that. thanks
Can you put the 3 millions record in a tmp table first, update, and then insert the results into the final table?
]]>