Category Archives: SQL

Alles was unter dem Bereich SQL fällt. Bezogen auf Oracle Datenbanken.

Provisioning IIS-artifacts within an SSIS-package as part of a deployment process with C#

SSIS is not only suitable as “etl-processing-tool”. Its feature-set makes it a really good candidate for deployment processes, too.

In this multi-tenant scenario SSIS is used to provision a complete client-environment which also includes an application portal per tenant.
This portal is created using the following simplified script task inside SSIS.

Add a reference to Microsoft.Web.Administration which resides somewhere in your windows-folder. In my case its C:\Windows\SysWOW64\inetsrv\Microsoft.Web.Administration.dll.

using Microsoft.Web.Administration;

Add a script-task to your SSIS-package and create the privisioning logic …

public void Main()
{
  // These parameters are passed in from SSIS
  string clientAppCode = (string)Dts.Variables["Tenant::CustomShortCode"].Value;
  string svcUser = (string)Dts.Variables["Tenant::WinAccountUserFull"].Value;
  string svcPswd = (string)Dts.Variables["Tenant::WinAccountPassword"].Value;
  string webRoot = (string)Dts.Variables["Tenant::IISRootDir"].Value;
 
  // Build the app-name
  string appName = String.Format("{0}.customsoft.de", clientAppCode);
 
  // Process IIS articfacts
  using (ServerManager serverManager = new ServerManager())
  {
    if (serverManager.ApplicationPools[appName] != null)
    {
      // App pool already exists! Proceed with your error-handling ...
      Dts.TaskResult = (int)ScriptResults.Failure;
      return;
    }
 
    // Create a new application pool running .Net 4.* with the given user-identity
    ApplicationPool newPool = serverManager.ApplicationPools.Add(appName);
    newPool.ManagedRuntimeVersion = "v4.0";
    newPool.ProcessModel.UserName = svcUser;
    newPool.ProcessModel.Password = svcPswd;
    newPool.ProcessModel.IdentityType = ProcessModelIdentityType.SpecificUser;
 
    // Provision the app-pool
    serverManager.CommitChanges();
 
    // Create the (web-)site
    if (serverManager.Sites[appName] != null)
    {
      // Site already exists! Proceed with your error-handling ...
      Dts.TaskResult = (int)ScriptResults.Failure;
      return;
    }
 
    // Create the website with the specified bindings. Of course here is 443/SSL missing ;-)
    Site clientSite = serverManager.Sites.Add(appName, "http", String.Format("*:80:{0}.customsoft.de", clientAppCode), webRoot);
 
    // Add another binding for www.clientcode.customsoft.de
    clientSite.Bindings.Add("*:80:www." + clientAppCode + ".customsoft.de", "http"); 
 
    // Assign the newly created app-pool to the new site
    site.ApplicationDefaults.ApplicationPoolName = newPool.Name;
 
    serverManager.CommitChanges();
  }
 
  Dts.TaskResult = (int)ScriptResults.Success;
}

Of course there is a lot of room for improvements (proper error-handling, transactional rollback, …).

SQL Server FILESTREAM and master key

When dealing with RBS and SharePoint all guides provide the statement:

USE [WSS_Content]
IF NOT EXISTS 
(SELECT * FROM sys.symmetric_keys 
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE master KEY encryption BY password = N'Admin Key Password !2#4'

If you ever asked yourself if its possible to leave that statement… The answer is no!

Funny thing is that the installation of the rbs-client states out a successfull installation. But all mssqlrbs_* tables are missing for the specified content database. And running the PS commands for the content db:

$cdb = Get-SPContentDatabase WSS_Content
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed()

simply returns false.

After comparing a successfull installation and an unsuccessfull installation the logfile of the failed installation is missing the create statements for the tables. A simple

Remote Blob Storage on the database has failed with error message: Remote Blob Storage requires a master key to exist in this database

is written somewhere in that logfile. Very easy to overlook.

The only way to fix the “corrupt” content database and (re-)provision the missing tables is to create the master key via the “master-key sql-statement” followed by the following command:

msiexec /qn /i rbs.msi REMOTEBLOBENABLE=1 FILESTREAMPROVIDERENABLE=1 DBNAME=WSS_Content FILESTREAMSTORENAME=FilestreamProvider_1 ADDLOCAL=EnableRBS,FilestreamRunScript DBINSTANCE=CD

This will provision the missing tables and rbs is up and running …

Solving NULL values are recognized as text-values within dynamic queries in PostgresSQL

I’ve written a dynamic query which is finally executed via EXECUTE my_dynamic_query; The query contained a select list in which two columns should always be NULL.

-- ... stuff before
dynamic_select_cols := 'tbl1.foo, tbl1.bar, null AS theAlias1, null AS theAlias2, tbl2.foo2';
-- ... stuff after combining the dynamic queries
EXECUTE my_dynamic_query;

In my case the result of the query will be inserted in a temporary table in memory. The destination column for theAlias1 is of type INTEGER. When executing the query PostgreSQL gives me an error:

column "theAlias1" is of type integer but expression is of type text

So there is something wrong when the query is parsed, evaluated and executed.

Solving this was easy with the use of NULLIF. Zero equals zero so NULL is returned. Just as expected.

-- ... stuff before
dynamic_select_cols := 'tbl1.foo, tbl1.bar, NULLIF(0, 0) AS theAlias1, NULLIF(0, 0) AS theAlias2, tbl2.foo2';
-- ... stuff after combining the dynamic queries
EXECUTE my_dynamic_query;

WITH-Klausel

In der Praxis trifft man von Zeit zu Zeit auf aufgeblaehte SQL-Queries, die ausserordentlich kostenintensiven und langwierigen Ausfuehrungsplaenen folgen.
Zeigt eine weitergehende Analyse, dass die komplexe Abfrage eine oder mehrere Subqueries enthaelt, die mehrfach ausgefuehrt werden, dann besteht eine sehr
gute Chance durch Verwendung der sogenannten WITH-Klausel, die Performance der Abfrageausfuehrung erheblich zu steigern.

Im Kern wird die schnellere Ausfuehrung der Query dadurch realisiert, dass mittels der WITH-Klausel die Ergebnismenge der Subquery vorab materialisiert, mit einem Namen versehen und schliesslich in der Hauptquery abgegriffen wird.
Hier lohnt sich auch ein Vergleich und Gebrauch von "global temporary tables", die in Ihrer Funktions- und Wirkungsweise durchaus ähnlich sind:
Durch Entkopplung geeigneter Subqueries, die urspruenglich als Bestandteile einer komplexen Abfrage fungierten, wird dieses SQL-Statement vereinfacht.

WITH   SQ1 AS (SELECT...FROM...WHERE...),
       SQ2 AS (SELECT...FROM...WHERE...)
SELECT...
  FROM Q1, SQ1, SQ2 WHERE…

Interessanter Nebeneffekt:

Durch die Verwendung der WITH-Klausel wird anscheinend in einer Systemumgebung mit voreingestelltem CURSOR_SHARING=FORCE offensichtlich fuer die Query-Ausfuehrung diese systemweit gueltige Einstellung auf CURSOR_SHARING=EXACT zurueckgenommen (vergleichbar dem Einsatz des Hint /*+ cursor_sharing_exact */).

Zeigen die QEPs vorher noch eine allgemeine Ersetzung von Konstanten durch Parameter, so weisen die QEPs nachher die direkte Verwendung der Konstanten auf.

Somit orientiert sich der Optimizer auch grundsaetzlich an anderen Zugriffsstrategien.

Was in einer Umgebung mit problematischem Parseverhalten per Bindvariablen-Peeking einen "flüssigen" Anwendungsbetrieb ermöglichen kann, wird im Einzelfall einer komplexen Query unter Verwendung von Attributen mit stark ungleicher Werteverteilung zu relativ kostenintensiven QEPs führen.

Es empfiehlt sich dann für alle entsprechenden Konstanten/Parameter einen separaten und speziellen QEP zu erzwingen.

Vorraussetzung:

SQL-99 Standard bzw. Oracle9i Release 2

Links:

SQLStandards.html

to be continued….

MIN, MAX innerhalb eines SELECT-Statement

Manchmal benötigten man sowohl den kleinsten (min) als auch den größten (max) Wert einer indizierten Spalte gleichzeitig.


SELECT MIN(id) 
     , MAX(id) 
  FROM big_table

Bei diesem SELECT kommt zwar das richtige Ergebnis, dennoch kann die Antwortzeit sehr lange dauern. Der Grund liegt in einem FAST FULL INDEX SCAN.

Besser ist folgendes:

SELECT MIN(id) min_value 
  FROM big_table 
 UNION ALL 
SELECT MAX(id) max_value 
  FROM big_table

oder

SELECT (SELECT MIN(id) from big_table) min_value
      ,(SELECT MAX(id) from big_table) max_value 
  FROM dual

Bei beiden alternativen wird ein FULL INDEX SCAN durchgeführt und liefert das Ergebnis (fast) sofort zurück.
Den Umweg sollte man bei großen Tabellen und wo es auf Geschwindigkeit ankommt wählen.