I had the honor meeting Tom Brown, here in Israel. Systematics (the representative of Esri in Israel) bring him to three days. On Wednesday Tom gave a lecture about SDE- new features and Performance and on Thursday he had a one-on-one meetings. I hope Tom will publish his presentations so I could link it from here.

Here are some tips I've learned from those two days:

Register all your tables
You might register all your tables with ArcSDE using sdetable -o register (even the non-geo-layers), this will add a row to the GDB_OBJECTCLASSES. The main reason is optimizations, for each time the ArcObject api will try to access your tables\layers he won't need to search for it all over the tables-dictionary.

Do NOT overload feature datasetes just for managing it better. You probably ask why? This is because when editing and browsing the layer it will initialize all the classes underneath the dataset. use this only for Topology and Geometric Networks.

Profiling your SDE (enabling sdeintercept)

you can enable it as a client or maybe as the server, I'll probably use it in my ArcGIS Server, so  for me I need to install the SDE client libraries, after installing you will need to set the sdeintercept variable, you can put this line in a batch file and run it whenever you need it:

   1: set SDEINTERCEPT=crwtf 

   2: set SDEINTERCEPTLOC=D:\tmp\sde_server

Intercepting the operations to SDE will create a huge file so it will be a good practice to enable it for a specific operation, watch the file to understand what happen clear it and start over again for another operation. I didn't try this yet, but I will publish more on it whenever I found more time.

Move edits to base option (Versioning-workflow)
We all know the Versioned and the non-Versioned pros and cons, ESRI give us new option, now we can work like a versioned option, but still our data will be save and accessed as non-versioned (with one DEFAULT table of data). you will be able to query the main table using simple SQL and without the need for Compress it from time to time. this model called the Move edits to base option (aka: Hybrid model)

Geodatabase and Raster's archiving
At my project we handle a huge processes of loading large Rasters to the SDE, this operation can take some time but the main problem is that your database is archiving each bit to help it recover failures. Archiving operations cause the redo-log to grow and blow, which cause us to turn off archiving. Tom also showed a command for turning off the archiving. At Thursday he thought loud of another idea, that, maybe, in the next versions the loading of Rasters will be using direct write which will bypass the redo logs.

Spatial Types
Oh boy, this is a syntactic sugar, from the 9.2 versions (oracle) you can write such bunch of code

   1: SELECT sa.name "Sensitive Areas", hs.name "Hazardous Sites" 

   2: FROM   sensitive_areas sa, 

   3:        hazardous_sites hs 

   4: WHERE  

   5:        ST_Overlaps (sa.zone, ST_Buffer (hs.location,.01)) = 1;

   1: INSERT INTO hazardous_sites 

   2: VALUES (1, 102, 'W. H. Kleenare Chemical Repository', db2gse.ST_PointFromText('point (52 24)',1)

You can extend your reading here.

Two tips for the next versions:
TDDer prespective:
Testing spatial queries is hard but important, as you can see the query above there are many scenarios:

• point inside an area
• point outside the area
• point on the area border

Here I would be more than happy to have a test table to see the results, and to give a better readability for the next programmer who would like to run my query.

Keep it simple:
As you can see the insert statement I need to know the next number for insertion it is also connected to another table in the database. I don't want my programmers to bother about this index, as I see this it can be solved by adding an automatic triggers while registering the layer as Geodatabase.

As you already know my team works on ESRI products, which are off-the-shelf products, we have daily battles of understanding and solving performance issues. the main challenge is to understand where in our branched architecture the problem.
I already wrote about this ESRI processes and the "Process Explorer" I also called this Debugging the unknown.

I always said that we all good programmers, we all know to write (more or less) readable code, but most of us lack the understanding of how things are working at the background. The difference between a good programmer to an expert is that the experts truly know what is going on behind the scenes.

Last week Liran, talented boy from our Unix team, shows me this magic: Cacti.
Cacti is an RRDTool which can help us gather important details about the OS functionality and its background operations.
I am still not sure what we wrote their in his scripts, but I saw that it was short&easy to write new scripts.
The real cool thing is that creates an intranet site, which makes this easier to connect, it runs in front of our UNIX\Linux servers. I know how to run some cool scripts in UNIX, I also wrote some scripts using shell\bash\kshell, but this is better, you can get everything using graphs, and do not need to remember even you DB server name.
We are using Cacti to monitor our SDE server (Linux) and our DB Server(Oracle 10.2 on AIX5.3).

Two days later, we return our investment, when we had two crashes because of Virtual memory, you can easily see that in those graphs, which saves the history in MySQL DB.

Again, It won't solve all your problems, but you must understand that without such tools you will spend hours on hours when your DB fall again and again.
With such tool you can also predict errors when you are doing you exams at the development phase.

I would like to hear about more tools you know and use.

I had a strange problem trying to use the SQL Server 2005 Express, (this is the built in DB that come with the VS2005 installation).

The Problem & MS solution:
I need to install on my laptop a local DB, but I don't want to install the full version of SQL Server 2005. Microsoft gave us a great tool which you already installed in your VS2005 installation the: SQL Server 2005 Express.

There are still things to fix:
There is only one problem with this cool tool, you cannot connect to this DB, create new one or even do something useful with this tool.

So you are trying the first idea on your mind, in VS server explorer -> add new DB, choosing you compute name as the Server Name(in combo box, it is already there so it must work, isn't it?).

Only one server-name for you man!

Giving meaningful, descriptive name to your DB (e.g. DB1).

New DB name is now available

But you can't, it won't recognize the SQL Server 2005 Express.

My Solution:
In the Server name you will have to write: .\SQLExpress.
Everything else that I've tried failed, such as: MyCompuerName, SQLExpress, local.

Try it yourself.

Every one who write queries in Oracle knows that for inserting comma (') into a varchar field you need to double it, which will look like this:

insert into shops(shop_name, description)
values ('Adam''s shop', 'selling bugs for free');

But I've found out this one too (and also found out that there are many of you that don't know this trick)
try to write the query with this special character - &  (And)
Oracle will think that this one is a parameter asking you to insert value.
so I said why not double it too.

update shops
set      shop_name='Lorel&&Hardy'
where  code = 69;

Do not try those tricks at home.