quote:

---

But I wonder how many people have thought about making a single Phone Numbers table with maybe a field

tbl_From

to know which table...

code please?

I would favour using a lookup table even for these small domains. Although you are right that check constraints would would involve fewer joins (including the SQL Server internal joinlike...

Dave, just had a thought - if it's practical to do on a test environment, you could use DBCC pintable, which forces the whole table to be RAM resident, so...

So you've eliminated the (apparently strong) possibility that the initial delay is due to building the query plan??????

I wrote 'procedure cache' - meant 'plan cache'.

It sounds like it is the query plan. If your app is sending simple textual SQL statements with constants as SARGs, then there won't be a match in the procedure...

ADO spawns additional connections! May not be the cause of your problem, but is interesting anyway...

http://support.microsoft.com/default.aspx?scid=kb;EN-US;194979

I (and Guarddata, it appears) still don't believe that the code as you describe it would open a new connection. Something else is going on in the code.

You might...

Using nolock means that transactions are no longer honoured. You can read data which has not been (and might never be) committed. Equally you might be unable to see data...

Yes - agree with GuardData - closing the rocordset ought to release any locks it is holding - in some configurations of the lock- and cursor- type parameters, the rst...

I suspect that FK caused the problem because the new connection is waiting for the other connection to commit and release its locks so that the new conn. can check...

Don't understand what's happening here, but here's a few randomly selected musings:

Have you tried updating the table from Query analyser rather than the app?

How about dropping the trigger and then...

Is there any reason why you wouldn't remove the mistake of creating a new connection? That would solve your problem.

A couple of subsidiary points:

Using @@identity is more efficient than select...

alzdba - how do you see the trans. iso. level via Profiler? That would be very convenient...