Slow Query Performance in SqlDataReader vs. Query Analyzer / Management Studio

When working on a query for a web application, I noticed that the same query which executes within a second in Query Analyzer, would take nearly a minute when executed by a stored procedure called via SqlDataReader.ExecuteReader().

I was surprised because I had never seen such a huge difference in query execution times.

The query performs a LIKE operation on an indexed computed NVARCHAR column.

Fortunately, this SO question gave a hint at the SET ARITHABORT setting which seems to take a different value in Query Analyzer and .Net libraries.

The answer points to a deleted page which itself seems to be the answer to other questions and has thankfully been preserved in the Web Archive. (You need to Select All or disable CSS to read the text). The ARITHABORT setting is by itself a source of many questions on SO.

MSDN describes the aspect causing performance differences in my case as:

The setting of SET ARITHABORT is set at execute or run time and not at parse time.

SET ARITHABORT must be ON when creating or manipulating indexes on computed columns or indexed views.

After adding a SET ARITHABORT ON statement to the stored procedure, the query performed the same in both conditions.

For more information, see also the MSDN article SET Options That Affect Results.

Full version of Forms Tool Kit

A couple of years ago I developed a program called Forms Toolkit which was designed to be a more comfortable tool than Oracle Forms Developer for batch manipulation and batch search of .fmb source files.

Even though the application was never marketed in big style, the evaluation version seems to be available from several download sites, and has been downloaded from my site alone nearly 200 times during the last year.

The main features of Forms Toolkit include:

Searching, Replacing, and Setting Property Values

• Search:   Search for exact value or value range
• Replace:   Replace search results by fixed or calculated value
• Set:   Assign fixed value

Adding and Deleting Objects

• Add:   Copy and reference (subclass) objects
• Delete:   Delete matching objects

Filtering Objects

• Restrict the objects to be search and modified by defining filter criteria:
• Object type, object name
• Property values
  of the object or the parent object

More Functionality

• Define projects containing all Forms files of an application
• Save the definition of a search or replace operation to file
• Combine several single operations as a batch operation
• Object and property browser
• Extract quoted texts and display text properties
• Detailed protocol
• Files supported by Forms Open API: fmb, pll, mmb, olb

Forms Toolkit supports Oracle Forms Developer versions 6i, 9i, and 10g.

The most current version 1.18 is now available for download, and accepts license keys available from the online shop.

Missing Procedure Entry Point in TortoiseSVN

I tried to add version control on a machine with a quite dated installation of TortoiseSVN (1.5.5 Build 14361 of Oct 2008), but could not start any of the programs in the TortoiseSVN Programs menu due to the error message:

The procedure entry point ? _Xbad@tr1@std@@YAXW4error_type@regex_constants@12@@Z
could not be located in the dynamic link library MSVCP90.dll

I checked and found a library MSVCP90.dll in \windows\system32 (version 9.0.21022.8).

On a different PC, where I am using TortoiseSVN regularly, I found a DLL with the same name under \Windows\WinSxS\x86_Microsoft.VC90.CRT_1fc8b3b9a1e18e3b_9.0.30729.1_x-ww_6f74963e\, showing version number 9.0.30729.1.

Back on the original PC, the same DLL with the same version is found in the same directory. Surprise.

Well, my conclusion is that different versions of the MS C++ Runtime library install in different places, probably depending on the application installing the libraries.

My problem was solved by copying the most recent version of the 3 DLLs (msvcm90, msvcp90, msvcr90) from the WinSxS subdirectory to \Program Files\TortoiseSVN\bin. Works!

Source Control Management and Databases

Source controlling or versioning database schemas is a recurring topic on StackOverflow and on database-related blogs and web sites.

There does not seem to be a single correct solution, rather everybody tries to find their own way to deal with this topic.

The core of the problems seems to be the different types of data between file-based source code and database schemas. Whereas source control systems typically have to deal with source code in files, and every check-in relies on the ability to compile every revision in source control (well, in an ideal world), versioning databases is different in the aspects:

• store a version of the full database schema (for installation)
• store a change script / migration script to upgrade from one version to the next (for upgrades)
• store the values of certain tables (lookup tables, system and application constants)

Command-line tools

In recent years I developed a set of tools that I think may be helpful to the task of versioning databases:

SMOscript is a tool which scripts all database objects in an MS SQL Server database to file using the MS SMO library. Using the -f switch, one file per object is created in a directory. If that directory is under source control, the developer has all CREATE statements in the same revision tool as their application source code.

Oraddlscript is the equivalent of SMOscript for Oracle databases. It makes use of the built-in function DBMS_METADATA.GET_DDL, which provides the CREATE statements for any object inside an Oracle database.

Integrated solution

dbscript is a web application which tries to integrate the 3 aspects mention above:

• dbscript keeps in its database every schema ever uploaded or imported.
• dbscript compares any two database schema versions displaying the differences
• dbscript generates a migration script of selected objects as a result of this comparison
• dbscript compares a set of schema versions to display a timeline of version changes
• dbscript generates T-SQL value scripts and C# constant declarations based on database data

The generated files can be stored inside the dbscript database (always related to a schema version or a comparison between versions), and / or downloaded to a directory covered by source control.

Note: I am biased, since I use dbscript for the projects I develop since its first stable and reliable version. Actually, dbscript development itself uses dbscript, as can be seen in the SQL files shipped for installation and upgrades.

For more information on the products, see the company website. Free downloads are availabe here.

dbscript Documents

New documents are available containing product information on dbscript.

The documents can be viewed on the dbscript homepage.

Managing Update Notifications with dbscript

dbscript keeps track of all versions of a Project. This information can be used (and is being used) to provide update notifications to indicate newer versions of an application are available.

An application has a name and a current version number, and passes these two values to a URL which receives the list of more recent versions.

In dbscript, every project has a name, and an optional Update ID. The application name the application passes to the URL must match either of these values.

Similarly, every Project Version has a version identifier, an Update ID and an Update Message. By checking updates, only the versions’ Update ID is considered.

If there are any newer versions (determined by creation date of the version, or the explicitly assigned Version Date), the list of newer versions is returned.

dbscript also takes into account if you assigned project versions to Branches. Then the result is restricted to project versions in the same branch as the inquiring project version.

dbscript provides two URLs to handle update notification requests:

• checkupdates.aspx returns an HTML page which can be included in a web application using an iframe

• checkupdates_xml.ashx returns an XML document which can be parsed by the client application

(all dbscript-related terms are capitalized)

The latest version of dbscript is available for download here.

oraddlscript 0.16

Version 0.16 of oraddlscript includes two fixes:

• Database Links are now scripted
• Reduced memory consumption

Scripting database links required translating the OBJECT_TYPE “DATABASE LINK” into the GET_DDL() parameter value “DB_LINK”.

Increased memory consumption was caused by a DataReader that was not closed.

Thanks to Thomas for pointing out the problems.

The latest version of oraddlscript is available for download.