SQL Server. Insert into table whiles querying
-
Hi. I have a stored procedure that queries a table that takes about 35 seconds to return the results, meanwhile, from other stored procedures, i cannot insert new rows into that table, how is this possible to do? (even if that mean that on the large results the new records will not appear)? Thanks.
Is your SP speed acceptable? Or should it really execute much faster when done properly? Did you look into your indexing scheme? :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Depends on what you are trying to achieve. From what I hear your select statement is locking most of the rows in the table, which could cause a full table lock (read level). That would mean your insert would fail as it cannot get an exclusive lock on the table. The other way around is also possible as you have experienced. If the insert starts first the select will fail. You could potentially fix this by hinting to the SQL Server to use no locking on the select (add with nolock after the from part from the select), but this will cause you to get dirty data and uncommited data.
Hi, Thanks for your answer, i have tried the (WITH NOLOCK) on the select query but it still takes time to insert the new rows when running the select. it will wait until the select finishes.
-
Is your SP speed acceptable? Or should it really execute much faster when done properly? Did you look into your indexing scheme? :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
The SP are fine and tabled are well indexed, just the search results are very complicated calculations running on millions of records , and the problem is that when running the select for client to see in application, no inserts are allowed.
-
The SP are fine and tabled are well indexed, just the search results are very complicated calculations running on millions of records , and the problem is that when running the select for client to see in application, no inserts are allowed.
I guess Gerben's answer applies then. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Hi. I have a stored procedure that queries a table that takes about 35 seconds to return the results, meanwhile, from other stored procedures, i cannot insert new rows into that table, how is this possible to do? (even if that mean that on the large results the new records will not appear)? Thanks.
I would certainly look into ways of speeding up the query. Perhaps copy the relavent data to another table and do the calculations there.
-
Hi. I have a stored procedure that queries a table that takes about 35 seconds to return the results, meanwhile, from other stored procedures, i cannot insert new rows into that table, how is this possible to do? (even if that mean that on the large results the new records will not appear)? Thanks.
Revisit you code..... Are you using cursors? Also, it may be better to get the raw data into an intermediary table, then select from that table and perform computations, then insert into the destination table. Of course you should always clear the intermediate table before you start putting data into it.
-
Hi, Thanks for your answer, i have tried the (WITH NOLOCK) on the select query but it still takes time to insert the new rows when running the select. it will wait until the select finishes.
Like I stated the nolock is a hint given to the database, and the database server might choose to ignore it. From what I know, for at least for MS SQL, is that the nolock should solve the issue. But you could try to combine it with a ROWLOCK or PAGELOCK instruction for the insert statement, see locking hints for more on this. Keep in mind that large sets of indexes could also cause the locking you are experiencing, as an insert will update the index and could trigger a re-index.
-
Hi. I have a stored procedure that queries a table that takes about 35 seconds to return the results, meanwhile, from other stored procedures, i cannot insert new rows into that table, how is this possible to do? (even if that mean that on the large results the new records will not appear)? Thanks.
Your SELECT query is locking the whole table that is forcing the database engine to dishonor exclusive lock requests by INSERT statements. This could happen if the query is fetching most of the rows in the tables. 1. Revisit your SELECT query and make sure your select only those rows/columns required. 2. Index your table(s).
-
I would certainly look into ways of speeding up the query. Perhaps copy the relavent data to another table and do the calculations there.
I agree that you might want to build a "reporting" table that is refreshed nightly, hourly, whatever and have the users query against that table. Sometimes the data doesn't need to be up to the minute ... just make sure the end user knows that the data may be somewhat stale. I've used this method for a few reports and the first user who initiates the report takes the hit and creates the reporting view for the day. everyone request for the report goes against that dataset; each day a new dataset is created only if someone requests it. Each time a dataset is requested, all previous datasets are deleted. Just a thought. :rose: Good luck
-
Hi. I have a stored procedure that queries a table that takes about 35 seconds to return the results, meanwhile, from other stored procedures, i cannot insert new rows into that table, how is this possible to do? (even if that mean that on the large results the new records will not appear)? Thanks.
Eli Nurman wrote:
i cannot insert new rows into that table, how is this possible to do
How are you trying to update (insert) the table? By default SSIS for example trys to lock the table before doing any data changes, that behavior can be changed.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.