1st SELECT after INSERT

Question

1st SELECT after INSERT

Viewing 5 posts - 16 through 19 (of 19 total)

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

Peldin Fernandes SSC Enthusiast Points: 186 More actions · Answer 1

Hi dballard

i too encountered a similar problem but not xactly the same

it wa sthat when u insert the records and then say select the transaction remain and the query goes in to a deadkock after some time try begin trans and end trans after & before inserting the records resp.

----------------

Peldin Fernandes

Regards,
Peldin Fernandes

dballard SSC Journeyman Points: 80 More actions · Answer 2

Hi Calvin,

Thanks for the code. I'll take a look.

Dave

stax68 SSChampion Points: 11711 More actions · Answer 3

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

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

dballard SSC Journeyman Points: 80 More actions · Answer 4

Hi Tim,

No actually. I got busy yesterday, and didn't have a chance to look. I think you may be correct about the statement cache being the issue. My SELECTSs show as being 'Adhoc' and of course are not in the cache until the 1st retrieval. The retrieval is a set of about 25 SELECTs, so for MSSQL to compile these will take some time.

Do you happen to know of any MSSQL7 tracing or profile options that would show the time MSSQL spends compiling and generating the query plan? If this is correct, then it looks like for me that statement caching can have a large impact on my application performance.

I'll take a look at your Using Stored Procedures and Caching Mechanisms reference.

Thanks for your help,

Dave

stax68 SSChampion Points: 11711 More actions · Answer 5

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 the only disk activity is (lazy) writes. Then if the problem still occurs you know it's not disk activity.

The only relevant traces I can think of is the Profiler 'Misc.' category, 'Execution Plan' event class. If you exclusive use of a test server, you could also look at the 'SQL Statistics' counters in PerfMon.

Some possible causes of poor performance would be non-0 values for:

SQL Compilations/sec

Failed Auto-Params/sec

Unsafe Auto-Params/sec.

The BOL article on Using Stored Procedures and Caching Mechanisms is only in BOL 2000 - but applies also to SQL 7.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant