|
|
|||||||||||||||||||||
|
More ASE Quiz questions: 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
June 2006 An insert trigger is created on a table as follows: create trigger MyTrigger on MyTable for insert as select * from insertedNow, when inserting a row, the trigger returns an empty result set, suggesting that no data was found in the inserted table. However, the row itself appears to have been inserted OK:
1> insert MyTable (a, b) values (12345, 'inserting 12345')
2> go
a b
----------- --------------------
(0 rows affected)
(1 row affected)
1> select * from MyTable where a = 12345
2> go
a b
----------- --------------------
12345 inserting 12345
(1 row affected)
How do you explain that while a row is inserted perfectly fine, the insert trigger does not see the inserted data, even though the trigger is fired as expected?
Answer: The explanation is that the trigger was created on a proxy table: inserts and retrieval (and other DML) on a proxy table work fine and from the outside there's nothing that suggests this isn't a normal table (as is indeed the purpose of a proxy table). Also, when doing DML, corresponding triggers fire when they exist, but their inserted and deleted tables will always be empty so they cannot perform any processing based on the inserted/updated/deleted rows. The reason why inserted and deleted are empty is that these tables are dynamically constructed from the transaction log records generated for the DML statement causing the trigger to fire. For a proxy table, the actual inserts, and the generation of log records, are taking place in the remote server where the 'real' table resides. Therefore, in the database where the proxy table resides, there simply are no transaction log records for any DML against the proxy table. Consequently, the trigger on the proxy table won't be able to do any sensible processing at all. Only a trigger on the 'real' table in the remote server would be able work as we'd expect from a trigger. Altogether, it's actually quite normal that a trigger on a proxy table behaves this way... perhaps the only surprising part is that a trigger on a proxy table is fired at all, or indeed, can be created at all. I guess this has been allowed because a proxy table is supposed to behave as much as a normal table... Should you want to try this yourself, here's some DDL to get you started: use master go -- assuming @@servername is defined! sp_addserver loopback, null, @@servername go use my_db go create table MyRealTable (a int, b varchar(20)) go create proxy_table MyTable at 'loopback.my_db..MyRealTable' go create trigger MyTrigger on MyTable for insert as select * from inserted go April 2006 A well-known way to determine the elapsed time for your queries is to run set statistics time on. This requires modifying the executed SQL script since set statistics time on is a SQL command that must be executed in order to have any effect. How can you determine the elapsed time for the queries in your session, without involving any SQL commands at all? Answer: There is little-known command flag for way for isql that does the job: by running isql -p, each executed batch gets timestamped by isql: % isql -Usa -P -SSYB150 -p 1> sp_helpdb 2> go [...output...] (return status = 0) Execution Time (ms.): 1000 Clock Time (ms.): 1000The time measured here is the end-to-end elapsed time as seen from the client application (isql). Note that the timing is based on the entire batch: isql starts timing when the batch is sent to the server, and stops the clock when the results have been received. If you'd use set statistics time on instead, the elapsed time for each statement inside an executed stored procedure would be printed as well (which may or may not be what you're interested in). This is because set statistics time on causes the ASE server to send timestamps for each statement to the client, while isql -p is done purely by the client itself without any functional involvement of the ASE server. When executing a batch multiple times (with 'go N'), 'Clock Time' will be cumulating the total elapsed time for all executions together, while 'Execution Time' is the time required for each individual batch (try 'go 100' yourself!) Unfortunately, isql appears to round the elapsed time to multiples of 1000 milliseconds. For very short statements, the elapsed time appears to be rounded down and is shown as 0. As always, sqsh comes to the rescue: sqsh -p measures the time much more precisely: % sqsh -Usa -P -SSYB150 -p [...] [101] SYB150.sa..master.1> sp_helpdb [101] SYB150.sa..master.2> go [...output...] Clock Time (sec.): Total = 0.132 Avg = 0.132 (7.58 xacts per sec.)Here, 'Total' is the cumulative amount of time for all batches when 'go N' is used, and 'Avg' the average time per batch. Given the more accurate timing, this looks like yet another reason to use sqsh instead of isql... March 2006 Some years ago, while in the middle of a troubleshooting operation involving a corrupt database, something happened that my heart skip a beat. I had just run sp_helpdb, and the result looked something like this:
1> sp_helpdb
2> go
name db_size owner dbid
created
status
------------------------ ------------- ---------------------- ------
------------------
----------------------------------------------------
PROD 2500.0 MB sa 9
Mar 18, 2004
abort tran on log full
REPORTING 4000.0 MB sa 11
Mar 22, 2004
select into/bulkcopy/pllsort, abort tran on log full
dbccdb 1200.0 MB sa 31516
Jun 29, 2004
trunc log on chkpt
(1 row affected)
(return status = 0)
1>
There were only 3 databases in my server! What had happened to my master database and other databases?? This corruption problem seemed to be getting out of hand...
Panicking, I ran sp_helpdb again, only to find those same three databases again. Getting down to basics, I did a select * from sysdatabases -- but still, it showed only those three databases. HELP!?!? Any clues? Answer: With hindsight, this is one of those classic mistake everyone has to make at least once... It appears I had run set rowcount 3 earlier in that session, and naturally, this was having an effect on all statements, include those in sp_helpdb, as well as the direct select from sysdatabases. Great was the relief when, after running set rowcount 0, all databases showed up normally again... January/February 2006 Let's assume you need to generate 100000 row inserts for testing the throughput of insert statements by ASE. In the spirit of the quiz question for May 2005, you're doing this as follows: 1> insert MyTable values (....) 2> go 100000Is this a good approach for performance testing? Answer: Obviously, this approach works fine from a functional perspective: 100000 inserts are generated as desired. While this is efficient from a lazy DBA's point of view -it requires only a minimal amount of typing- it is not the right approach for performance testing. Why not? When using the 'go N' subcommand of isql, the preceding command batch is sent to the ASE server N times. Each time, this requires network I/O (to send the query and return the result), and, inside ASE, parsing and compilation of the query. This overhead can be significant, and as a result, you may not be generating as much workload for the ASE server as you would perhaps have thought. To illustrate this, I ran two simple tests. First, I'm using 'go 100000' as above. Note that the isql session runs on the same host as the ASE server to minimise the actual network traffic: 1> select convert(varchar, getdate(), 109) 2> go ------------------------------ Feb 12 2006 10:27:53:596PM (1 row affected) 1> insert MyTable values (1) 2> go 100000 (1 row affected) 100000 xacts: 1> select convert(varchar, getdate(), 109) 2> go ------------------------------ Feb 12 2006 10:35:11:406PM (1 row affected)As you can see, inserting 100000 rows takes 07:18, which corresponds to ~230 inserts/second. That may seem fast enough, but let's try a different approach too. Here, I'm creating a stored procedure to perform the inserts in a loop: 1> create proc MyTable_insert @n int as 2> while @n > 0 3> begin 4> insert MyTable values (1) 5> select @n = @n - 1 6> end 7> go 1> select convert(varchar, getdate(), 109) 2> go ------------------------------ Feb 12 2006 10:37:10:216PM (1 row affected) 1> MyTable_insert 100000 2> go (return status = 0) 1> select convert(varchar, getdate(), 109) 2> go ------------------------------ Feb 12 2006 10:37:33:680PM (1 row affected)In this case, it takes only 23 seconds to insert 100000 rows, corresponding to ~4300 inserts/second. That works out as about 18 times faster than the 'go 100000' approach. Since this stored procedure invocation requires only a single client-server round trip (as opposed to 100000 such round-trips in the first test), this factor-18-difference is caused solely by the overhead mentioned before (network traffic, query parsing/compilation). Of course, the figures you find on your own server may be quite different, but the difference should still be very clear. Summarising, the conclusion should be that the overhead of the 'go 100000' approach (network traffic + query parsing, compilation, etc) is significant. For performance testing, this means that with 'go 100000', you're primarily testing how fast your isql client can send queries to the ASE server, rather than putting the maximum workload on ASE. NB: the above tests were executed in a regular database; when running these tests in tempdb, the difference will likely be bigger, since ASE uses various tempdb optimizations -like lazy commit writes- that do not apply to processing in normal databases. The net effect would be that ASE has to spend less time on inserting each row than in a regular database, and therefore the difference between these two tests would be even bigger. Because of these optimizations, running such performance tests in tempdb wouldn't be a fair when doing comparative tests for a real-life application. More ASE Quiz questions: 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001 |
|||||||||||||||||||||
|
