|
|
|||||||||||||||||||||
|
This page contains some quiz-type ASE questions to test your
ASE knowledge; a little diversion for those moments when
there's nothing better to do. Please note that these questions are not intended as test questions for ASE certification exams: they'll rather be about strange, obscure, bizarre or little-known ASE features. Also, these might well be solutions for which we haven't found a problem yet... (but it might help your certification exam preparations anyway...). I'll try to add a new question every month. February 2010 In 2010, it's 21 years ago I got involved in what turned out to be my first project involving ASE (then named Sybase SQL Server). After so many years, I liked to think I knew what was worth knowing about that very first thing you encounter when entering into the world of ASE: the isql utility. Turns out, I was wrong. While doing some performance benchmarking recently, I ended up staring at some strange-looking results that just couldn't be right. In essence, this was a comparison between a stored proc and isql both doing the same 10,000 simple selects (this was not what was I was testing, but where I ended up after digging into the results). The stored proc looked like this: create proc p as declare @cnt int set @cnt = 0 while @cnt < 10000 begin select * from my_tab set @cnt = @cnt + 1 end goThe stored procedure was then kicked off with a straightforward isql script: exec p goThe isql-based test just took the following simple script as input: select * from my_tab go 10000(I've written about the 'go 10000' approach before; see the May 2005 quiz question for more). Now, the strange thing was that, even though in both cases the exact same query was executed 10,000 times, the test with the stored procedure was about 4 times slower than than the test with the 'go 10000' script. That made no sense, since the stored proc test should be faster, or at least equal, since there's less processing overall. Here's why: the 'go 10000' test sends the query to the server 10,000 times, while the stored proc test send the 'exec p' to the server only once. Otherwise, both tests perform the same select query 10,000 times, and produce the same 10,000 result sets. So logically, the 'go 10000' test should be slower than the stored proc test since it has to do more client-server roundtrips. But what I was seeing is that it was actually 4 times faster than the stored proc test. The statement cache was enabled (so there was no optimizer overhead anywhere), and I had verified that the query plans and I/O counts were identical. The table had 100 rows and 20 columns, and the data was static. There were no other users in the server. What was going on? Go here for the answer. January 2010 You have a SQL script file containing tables, data, stored procedures etc., which are an essential part of the applications running on your ASE system. Periodically, as changes to your applications are implemented, the contents of this script changes too, and it needs to be re-run (with 'isql') against the ASE database for the application. When running this script, you're assuming a database named 'PROD' to exist, since this is the place where the tables, stored procedures (etc) are located. However, you need to be 100% sure that this database indeed exists, and also that it can be accessed (it could inacessible due to having been marked 'suspect' for example, or it could have been put in single-user mode or in dbo-use-only mode). So, if for some reason, there is no database named 'PROD', or it cannot be used, the script should not run, but raise an error message indeed. This is less trivial to implement than it sounds. The question: How can you conditionally abort execution of your SQL script somewhere in the middle of the script when database 'PROD' cannot be accessed? Go here for the answer. More ASE Quiz questions: 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001 |
|||||||||||||||||||||
|
