Delay in record appearing in table - why and how to prevent?

Question

Delay in record appearing in table - why and how to prevent?

WayneS

SSC Guru

Points: 95460
More actions
December 29, 2009 at 12:38 pm

#140241

I'm experiencing a weird issue where there is a 30-60 second delay in records being added to a table after the proc that does the insert has finished. I've attached the procedures that are being run for reference, but I don't see how this would pertain to what is happening.
dbo.Proc1: Drops table. Creates table (different schema). Inserts records into new table. Updates two other tables based on values in this table.
dbo.Proc2: selects data from the table into some temp tables, returns some of the data.
When these two procs are run concurrently, Proc2 returns an empty result set.
When the second proc is run in a loop with a waitfor delay '00:00:10' preceeding it, and repeating 6 times (for one minute), the expected result set will occur between the 4th and 6th running of the proc.
Note that the updates going on in the first proc are occurring based on the data being inserted into the table.
I have tried:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before running any of the procedures.
SET IMPLICIT TRANSACTIONS ON/OFF (both tried) before running any of the procedures.
So, what else is there that would cause a delay in a record being posted to a table where other procs can see it?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

The script errors out because there is no ccim_users schema. I removed that and the procedures compiled, but won't run because the tables are missing. Can you provide table layouts and data? How many records are in each table? I also noticed that there is no transaction being created as well as no error handling

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Are there any triggers?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Jack Corbett (12/30/2009)
Are there any triggers?

Spot on... another problem may be an explicitly declared transaction where all this stuff get's stacked up in the log and then get's "released" when the final COMMIT is executed. As Jack suggests, triggers on the table would increase this problem but those aren't the only things... Computed columns with PERSIST, Indexed Views, and Indexes (especially the clustered index) with very low cardinality for the first column in the index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)