Re: Lock types (Was: <No Subject Supplied>)

In <21fkdvINNi2i@emory.mathcs.emory.edu> naomi@qip.anasazi.com writes: >> >I tried to to select the count of the number of rows in a table this >> >afternoon and I received a message from ISQL that the table was locked. I >> >ran 'tbstat -k' and received the following: >> >> Alternatively, if you don't actually need Committed Read isolation, just >> SET ISOLATION TO DIRTY READ, and it will work fine.>> >> graeme >> -- >The problem I have with setting ISOLATION is that it affects the entire >database, and sometimes I only want a particular table. A silly thought No it doesn't. It only affects the sqlturbo/sqlexec which you execute it against. AND you can turn Commited Read (or whatever) back on again after your SELECT so that nothing else is affected. >though. To get the number of rows in a table, sometimes I : > SELECT nrows > FROM systables > WHERE tabname = "table_name"; >Its much faster than count(*). Not nowadays it's not. In 5.00 it's just as fast, maybe faster (optimiser cost = 1 in both cases). I can't remember at what release they fixed the bug that it used to do a scan, but I think it was gone by 4.10. What is more SELECT nrows gives you a potentially inaccurate result as it returns the # of rows when you last UPDATED STATISTICS, not the # of rows *now*. graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.