Labeling and SQL Server 2008

Discussion created by Pimplebutt on Oct 4, 2011
My environment

  • Visual Studio 2008

  • Sql Server 2008

  • ArcEngine 9.3.1

  • VB.NET

Parcel Layer
About 650,000 parcels

Sql Attribute Table being joined to.
Approx 125 columns
Approx 650,000 rows

Please see the attached text file as well.
It shows examples of the SQL statements captured by the SQL Profiler Trace.

I join the parcel layer to an attribute table on Sql Server 2008.

Basically, what I'm running into is enormous performance problems with certain labeling where clauses.

ArcEngine, when it internally queries sql server, in some cases for some bizarre reason, decides NOT to use the where clause that I specified and instead queries for every single column and row in the whole attribute table. This results in a wait time of about 10 minutes before the labels appear.

I can find no rhyme or reason as to why this behavior occurs, and perhaps its a bug within ArcEngine.
It does not appear to have anything to do with the datatypes of the label expression, nor the datatypes of the fields in the specified where clauses.

In many other cases, where ArcEngine works properly, it correctly uses my where clause when querying the attribute table, and the labels appear within seconds. Not only does it use the where clause, but it only queries for the parcels that are in the visible extent. In my attached file, in most of my tests, you'll notice each test has only 3 lines of SELECT statements, because ArcEngine was working properly and only queried for the visible parcels.

In the attached file, I have included details about what the map scale was, the scale range in place when labeling was applied, the expression, and where clause, and the SQL statements caught by the SQL Profiler trace.

In cases where it queried every row and column, I didn't pollute the attached file with that mess... just showed a few lines of the trace along with noting that that is what happened.

If you can unlock the secret to this rediculousness, you are WAAYYY underpaid.