Vítejte na blog.vyvojar.cz Přihlásit | Registrovat | Pomoc

Radim.NET

Radim Hampel : Microsoft business intelligence, performance management and data warehousing
Analysis Services partition slice

When you have more than one partition in your cube, you should consider using Slice property(highlight your partition and you will find Slice in its Properties window). Slice information is used at query time, when queried members are sequentially compared against all partition for possible inclusion. This helps to skip partitions which don't keep data for current query.

Slice can be either set by developer or (in case of MOLAP storage) it is recognized by Analysis Services automatically, during partition processing. So it is not necessary to explicitly set Slice partition, you can rely on AS engine, but you have to be aware of consequences. Partition slice is created automatically during processing of each partition for each attribute of related dimensions. For ROLAP partitions you have to specify Slice manually. But how is the automatic slice information retrieved?

Each attribute has internal surrogate identifier, called DataID, which is assigned during dimension processing, it is increasing integer that is assigned in processing order. When Full process of a dimension is executed, DataIDs are reassigned and that's the reason why related partitions/cubes must be re-processed also, because they would contain invalid referencing DataIDs. Because DataIDs are sequential, system can work with ranges of members.  After partition is processed, engine will store information about minimum and maximum DataID. This information is stored in \OLAP\Data\<DBID>\<CubeID>\<MeasureGroupID>\<PartitionID>\Info.<Version>.xml. More about slice storage location can be found in post How to find slice values that Analysis Services assigned to the partition dimensions? On my laptop I choose Adventure Work file "c:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db\Adventure Works DW.20.cub\Sales Summary.20.det\Total_Orders_2003.20.prt\info.21.xml" and here are two examples:

<MapDataIndex>
    <DimensionID>Order Date Key - Dim Time</DimensionID>
    <PropertyID>CalendarYear</PropertyID>
    <m_cbOffsetIndex>0</m_cbOffsetIndex>
    <m_MinIndex>4</m_MinIndex>
    <m_MaxIndex>4</m_MaxIndex>
</MapDataIndex>

<MapDataIndex>
    <DimensionID>Order Date Key - Dim Time</DimensionID>
    <PropertyID>CalendarQuarterDesc</PropertyID>
    <m_cbOffsetIndex>0</m_cbOffsetIndex>
    <m_MinIndex>8</m_MinIndex>
    <m_MaxIndex>11</m_MaxIndex>
</MapDataIndex>

These examples show that for partition "Total Orders 2003" there is just one member from CalendarYear (minIndex = maxIndex = 4) with DataID 4, same for CalendarQuarter - four members 8-11.

Based on this information you can rely on AS in case when the attribute members are processed in natural order. For example, Year attribute should be contain members 2000, 2001, 2002, 2003, ... Processing order is not the same as member order and even when you set OrderBy property to Key, DataIDs will be assigned in same sequence as they are supplied from data source. When years would come as "2003, 2002, 2001, 2004" and you would have 2 partitions, 1st for years 2001 and 2002 and 2nd for years 2003 and 2004 second partition would have minIndex=1 and maxIndex=4 ! This would result in unnecessary reads (Chris Webb created a feedback about it, you can vote here). So when your attributes might restrict number of related partitions, but processing order doesn't fit this schema, you should explicitly state partition slice property and don't rely on the engine.

You can use PartitionHealtCheck tool to check distribution of DataIDs in your partitions, it will show you when members are overlapping and potentially requiring more reads than necessary.

Other articles on partition slice:
Partition Slice Impact on Query Performance - SSAS2005
SSAS Partition Slicing
Viewing Partition Slice Information

Posted 16. dubna 2008 13:21 by radim | 0 Comments

Passing MDX parameters in Reporting Services reports

A common requirement in reporting applications is to drill down from parent report into details about selected part of the report. Reporting Services provides a handful of such navigation options, more specifically jump to report, jump to bookmark and jump to url. In my example I use jump to report option and from parent report (Top 10 products) I can drill down to sales details of selected product. Now, with relational data source I'd probably fetch ProductID and ProductName and I'd pass them to detail report as parameters. But how to do the same thing with MDX datasets?

Dimension(attribute) members have key, name and value. Name is always string value describing that particular member, while key is unique identifier of that member. In my example(from Adventure Works) product "Mountain-200 Black, 38" had unique name [Product].[Product].&[364]. Take a look at following report:

Top10products

This report shows products with highest sales during 2004. When user clicks on product he will get subreport with detailed sales in all months. But how to pass the product unique name to subreport? The answer is that Reporting services can use extra dimension properties (additional information returned in cellset), not only member's name, see this query:

DimensionPropertiesQuery

 The most important part is  DIMENSION PROPERTIES member_caption, member_unique_name, member_value that will instruct AS to return not only the name, but also additional properties. These properties could be used automatically in Reporting Services, they are called extended field information for Analysis Services. They won't appear in dataset, but they are retrieved at query time. While the name of member can be retrieved using Fields!FieldName.Value, extended information is accessible through Fields!FieldName("Property name"). There are multiple properties, but the one that I'm after here is UniqueName. So to pass the key to subreport, it is necessary to use Fields!Product("UniqueName"):

UniqueNameProperties

Full list of extended properties: Using Extended Field Properties for an Analysis Services Database

Posted 8. dubna 2008 15:24 by radim | 3 Comments

Jak zjistit, že některé z kroků jobu nebyly úspěšné?

Pro základní monitorování správného běhu Sql Server Agent jobů se nabízí možnost využití notifikací. Je možno zaslat email, "net send" zprávu, případně zapsat do EventLogu. Notifikace se spouští na tři základní události: job se správně ukončí, skončí chybou a reakce na prosté spuštění (bez rozlišení návratového kódu). Problém vyvstane tehdy, když je potřeba spouštět řadu nezávislých kroků, které se musí provést i v případě, kdy jeden z předchozích kroků havaroval. Příkladem může být záloha několika databází anebo procesení několika analytických kostek. Pro tyto případy jsou jednotlivé kroky nastaveny tak, že pokračují dalším krokem a to i v případě neúspěchu (On Success, On Failure -> Go to next step). Tímto ovšem se ztrácí příznak chyby a není možnost reagovat jeden chytný krok. Tedy buď reportovat chyby a job ukončit anebo pokračovat a chybu ignorovat.

V podobných případech používám krátký kód, který zjistí (podle názvu jobu) návratový kód jednotlivých kroků a pokud se vyskytl problém, ukončí s chybou celý job. Využívá se tabulka msdb..sysjobsteps a příznak last_run_outcome

 if (    

        select    count(*)
        from msdb..sysjobs j inner join msdb..sysjobsteps js on (j.job_id = js.job_id)
        where
                name        =    'Cube processing'
            and step_name    !=    'CHECK STEP ERROR'
            and last_run_outcome = 0
    ) > 0
    begin
        RAISERROR ('One or more steps failed. See job step history for details!', 16, 1)
    end
else
    begin
        print 'All steps completed successfully!'
    end

Kód se vloží do posledního kroku, který má vždy název "CHECK STEP ERROR" a jobu se nastaví posílání varovného emailu v případě chyby. Nastavení jobu pak vypadá asi takto:

 

Posted 27. července 2007 9:32 by radim | 0 Comments

Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

Zajímavý dokument nabízí ke stažení Excel 2007 team, doporučení pro design analytických kostek tak, aby byly využity všechny možnosti pivot tabulek v poslední verzi tabulkového kalkulátoru. Přestože ještě žádný ze zákazníků Office 2007 naplno nevyužívá a nemám proto ještě otestovánu odezvu dotazů, ze všech mých dosavadních pokusů vyplývá, že použitelnost (jak rychlost, tak uživatelské rozhraní) excelu jako prohlížecího nástroje dramaticky stoupla. Poukázal bych na dvě věci, které se mi líbí. První je pomocník pro výběr času - dnes, včera, minulý měsíc, YTD, od-do a podobně. Druhá věc, která se mi velmi zamlouvá je zobrazování členských vlastností, nejenom jako kontextová nápověda, ale i v dalších sloupcích vedle. Jako příklad pro zákazníka by šla zobrazit adresa nebo telefon.

Link  Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

Posted 11. července 2007 12:51 by radim | 0 Comments

Business Intelligence Architecture and Design Guide

I found a new version of BI architecture and design guide. I haven't read all chapter yet, but at least OLAP part looks very good. Not only you will find a quick introduction - why OLAP, what are alternatives and so, but also (and most importantly) you can find lot of good tips. I think today we still miss more similar guides, most people struggle to understand differences between natural and user hierarchies, types of relationships (rigid/flexible), they don't how how to set up aggregations, how to hint the optimizer and so.

Good stuff, keep up the good work!

I cannot resist publishing couple of sentences, I always wanted to give similar answer to complaining users, but I don't think that I did that in such a good way:


Current Member formulas with Subcube Filters
In 2005, Analysis Services added a new type of filter for queries: a subcube subquery. In many ways a subcube subquery behaves in a very intuitive way. Some client tools (for example Excel 2007) now use subcube subqueries to specify page filters. This can be a problem if you have a calculated member that uses the CurrentMember of the filtered dimension. For example, suppose you have an MDX formula that calculates the Monthly Growth Rate. To calculate, this formula looks at the current member of the time dimension and compares it to the previous member. You select March 2007 as the page filter. If the client query uses a subcube subquery, the data is properly filtered to include only March data, but the current member of the Date dimension is still All Time. The Monthly Growth Rate formula tries to retrieve the previous member of the Date dimension, and there is no previous member for All Time.
In a client tool such as Excel, you have no control over the MDX. The workaround in this situation is to put the Date dimension onto the Row or Column axis of the report, and then filter for the desired month. Putting the Date dimension onto an axis—even if there is only one member selected—sets the current member for the dimension and allows the formula to work.
In a client tool such as Reporting Services, the MDX designer defaults to put the filter parameter into a subcube subquery, but you can edit the MDX to put the parameter into a Where clause. If the selected month is in the Where clause, it does affect the current member, so the growth formula will work properly.

Posted 1. srpna 2006 15:51 by radim | 0 Comments

Vedeno pod:

How to kill query in Analysis Services 2005

Recently we're facing a quite common scenario - as our client's warehouse solution grows, it attracts more new users than in the beginning. Most of them use predefined fixed reports (Reporting Services). But there's also not small group of data analysis which like to dig really deep. They usually use Proclarity, Report Builder and Excel. Because not every analyst is really aware of what is happening underneath his client tool, so he might easily want to analyze combination of sales of standard products (1.1 mil), external salesmen (thousands) to middle sized customers (thousands). Apparently, this combination will end up in huge query running for hours. When he won't get the result in a minute or so, he would close the client tool and connect again - and even run the same query again :) Couple of such queries could take the whole server down.

So, I needed nice way how to kill those queries. There's no monitoring tool like Activity Monitor for SQL server, the only option for me was to restart the service (which cuts off all users from other cubes as well). The whole thing started to be more and more ridiculous, because it was necessary to do that every couple of days. Answer came from this forum post.

You can download samples for SQL Server 2005 and there's section for Analysis Services. Activity Viewer is utility that allows you to track user connections, sesstions and even locks and transactions in Analysis Services. You can check source code and write your own closing application (AMO Server.CloseConnection method), but I'm happy with this tool. You must only register AS instances in Management Studio if you want to work with them. Application is working on both 32 and 64bit systems.

One thing that is still couldy - why this is not internal part of the system? Isn't it very common requirement to close some connection manually? Why do I have to download “sample” to do everyday maintanance? Hopefuly we'll see that in Man. Studio after couple of months...

Posted 31. července 2006 10:04 by radim | 0 Comments

Vedeno pod:

Microsoft kupuje ProClarity

MS včera oznámil své rozhodnutí koupit ProClarity. Přestože mě osobně olap browser of Proclarity vůbec nezaujal (zejména po uživatelské stránce, ovládání není vůbec intuitivní a jakožto zkušenému uživateli mi trvalo dost dlouho, než jsem pochopil základní funkce), projekt, na kterém nyní pracuji, ho používá jako velmi důležitý klientský nástroj. Znamená to tedy (aspoň v to doufám), že napojení Proclarity na MS BI platformu bude těsnější, vždyť donedávna můj UDM model spolehlivě “boural” všechny dostupné verze Proclariy (tlustá aplikace, activex, html i dokonce Citrix připojení). Musela být pro to vyžádána nová verze, která už plně podporuje všechny nové SSAS 2005 fce (podpora measure groups, display folders, KPI, actions, uživatelské hierarchie). Je však taky pravda, že žádný jiný olap browser takto dobře s 2005 nepracuje (aspoň o něm nevím), nepočítám v to Report Builder, který je primárně určen jiným uživatelům a pro jiný typ práce.

Taky se těším, až bude oficiálně venku Excel 2007, zvlášť pro jeho podporu pivot tables a spolupráci s AS2005, ukázka pivot tables třeba tady. Když se k tomu přidá SharePoint 2007 a Scorecard Manager 2005, kruh se uzavírá a BI platforma je konečně kompletní. Takže, SQL2005 -> SSIS2005 -> SSAS2005 -> (SSRS2005 | SharePoint + Scorecard | Proclarity | Excel).

Posted 4. dubna 2006 8:01 by radim | 4 Comments

Vedeno pod:

Dundas Charts for Reporting Services 2005
Před čtrnácti dny byl oficiálně odznámen první doplňkový kontrol do Reporting Services (custom report item mi nezní moc česky) - Dundas Chart for Reporting Services. Měli jsme možnost se účastnit neveřejného testování, jako MS TAP partner pro SQL2005, takže jsem měl příležitost vyzkoušet beta verze, ale i tak jsem s nimi byl spokojený. Takže pokud vám v RS2005 chybí některý typ grafu, nebo se vám zdá návrhář nedokonalý (multiple series, automatická práce s časem, klientská práce s daty,...) vyzkoušejte Dundas Chart, časově omezená verze by měla být ke stažení. A cena je $1300/procesor a $700 za návrhář.

Posted 23. listopadu 2005 12:22 by radim | 1 Comments

Vedeno pod:

Sql quine

Již poměrně dlouhou dobu jsem byl zcela OOB(out of blog), ale rád bych znovu začal v nepravidelných intervalech uveřejňovat své poznámky. Protože máme za sebou oficiální uvolnění SQL Serveru 2005 do výroby(=zveřejnění odkazu na webu), a protože už několik měsíců s tímto nástrojem každodenně pracuji (instaluji nový build apod.), rád bych se zaměřil na něj. Větší část mojich zkušeností se týká oblasti BI, takže i mé poznámky se budou točit více okolo Integration, Analysis a Reporting Services než okolo enginu samotného. Snad někomu usnadním práci v tom, že nebude muset znovu prošlapat slepé uličky, které již byly zmapovány při mém takřka pátrání ( Tudy ne, přátelé! )

Pro milovníky hříček a hlavolamů mám několik quine sql příkazů:

print replace(char(0)+char(39)+char(0)+char(39)+char(41),char(0),'print replace(char(0)+char(39)+char(0)+char(39)+char(41),char(0),')

DECLARE @code varchar(500)
SET @code = '))93(rahc + edoc@ + )93(rahc ,)311(rahc ,)edoc@(ESREVER(ECALPER TNIRP
q = edoc@ TES
)005(rahcrav edoc@ ERALCED'
PRINT REPLACE(REVERSE(@code), char(113), char(39) + @code + char(39))

declare @ char(444)
set @ = 'declare @ char(444)
set @ = "declare @ char(444)"
exec sp_executesql N"print left(@,29)+char(39)+@+char(39) print right(replace(@,0x22,0x27),113)",N"@ char(444)",@'
exec sp_executesql N'print left(@,29)+char(39)+@+char(39) print right(replace(@,0x22,0x27),113)',N'@ char(444)',@

Původní diskuse byla vylolána na blogu Kena Hendersona.

Posted 31. října 2005 10:32 by radim | 0 Comments

Vedeno pod:

.NET prekladač pro PHP kód od českých studentů

Velmi mne pobavil rozhovor na MSDN Channel9 se studenty Karlovy univerzity. Dva členové z projektového týmu zde popisují okolnosti vzniku a samotný překladač - Phalanger. Kromě toho, že je to fakt studentský projekt(člověk se dobře seznámí se stavnou překladačů), je fajn, že i na akademické půdě se takto pracuje s 1. progresivními a 2. komerčními technologiemi.

Praktické použití takového překladače je samozřejmě omezené(podobně jako podobných ASP - ASP.NET konvertorů), ovšem za potěšení vidět PHP Nuke běžet na IIS v kompilovaném kódu to stojí :)

Posted 21. března 2005 14:41 by radim | 8 Comments

Vedeno pod:

Rekompilace uložených procedur

Optimalizátor SQL Serveru umožňuje opakovaně využívat prováděcí plány uložených procedur a zvyšovat tak celkový výkon. Dokáže také rozpoznat podmínky, za kterých je vhodné plán změnit a proceduru rekompiluje. V následujícím článku si ukážeme příčiny a důsledky rekompilací a jak je dostat pod naši programátorskou kontrolu.

Prováděcí plán

Při prvním spuštění uložené procedury je vytvořen tzv. prováděcí plán(execution plan). Prováděcí plán není zkompilovaná verze uložené procedury, není tvořen posloupností binárních příkazů, ale obsahuje odkazy na jednotlivé objekty využité v proceduře, seznamy indexů, které budou využity pro dotaz a také typy jednotlivých spojení. Prováděcí plán je odpovědný za správě vybranou strategii, za to, že sql dotaz bude vyřízen co nejrychleji. Tvorba prováděcího plánu je složitá a náročná operace(leckdy trvá déle než samotné vykonávání dotazu), přesto je však výhodné mít optimální prováděcí plán a ten opakovaně využívat. Při dotazech, které spojují několik tabulek se stovkami tisíc záznamů, může mít špatně vybraný prováděcí plán za následek zpomalení dotazu až o několik desítek sekund. Optimalizátor při vybírání vhodného prováděcího plánu porovnává několik variant, zkoumá dostupné indexy, jejich selektivitu a za pomocí statistik se snaží odhadnout diskovou, paměťovou a procesorovou zátěž. Pokud zkoumání trvá delší dobu a optimalizátor se stále nemůže rozhodnout, pro zkrácení času je prostě vybrán jeden z kandidátů a ten je dále používán.

 

Po nalezení optimálního prováděcího plánu je tento uložen do procedurální cache. Procedurální cache je část paměti sql serveru, kde se ukládají prováděcí plány jednotlivých procedur. Paměť určená pro prováděcí plány nemá pevnou velikost, ale je dynamicky určena množstvím používaných procedur a volnou pamětí. V cache se nalézají maximálně dvě kopie prováděcího plánu, které jsou využívány všemi uživateli. Jedna z těchto kopií je určena pro sériové a druhá pro případné paralelní vykovávání. Kromě prováděcích plánu se v cache nachází i datové sktruktury, které se vztahují k jednotlivých uživatelům, kteří vykonávají proceduru(execution context). Tato datová struktura obsahuje hodnoty parametrů a lokálních proměnných a nejsou vícenásobně použitelné.

 

Mít vytvořen prováděcí plán je tedy nezbytné, ovšem mohou nastat situace, kdy se optimalizátor rozhodne, že je nutné vytvořit prováděcí plán nový(rekompilace). V této chvíli se pozastaví případné současné provádění procedury, na prováděcí plán je uvalen kompilační zámek a čeká se na nový plán. Toto chování má samozřejmně negativní dopady. Proto si teď ukážeme, jak monitorovat rekompilace a jak se jim v požadovaných případech vyhnout.

 

Zde bych ještě rád uvedl dvě drobné poznámky k volání uložených procedur. První se týká vlastníka uložené procedury, ten by měl být při volání vždy uveden. Pokud tento uveden není, je na proceduru uvalen kompilační zámek do té doby, než je procedura nalezena v cache. Princip s uváděním vlastníka by měl být využit i pro všechny objekty použité v procedře. Uvedením vlastníka objektu před názvem objektu se jednak zabrání zjišťování názvu(name resolution) a jednam odpadne nutnost zjišťování práv uživatele na jednotlivé objekty. Druhá poznámka směřuje k příkazu pro vykonání procedury, EXEC. Tento příkaz není nutné vždy uvádět, stačí když volání procedury je prvním příkazem v dávce. Přes toto pravidlo je dobré se vždy přinutit k volání procedury přes EXEC, ušetří se zbytečné psaní v případě, že před procedurou přibyde nějaký kód. Ukázkové volání procedury tedy vypadá:

 

EXEC dbo.sp_help_tables

Zachytávání rekompilací

Na zjišťování četnosti a důvodu rekompilace využijeme SQL Profiler. Pro prohlížení počtu rekompilací bude plně stačit základní šablona SQLProfilerStandard. Na záložce Events k ní přidáme událost SP: Recompile(můžeme přidat i další, například SP:StmtStarting, abychom zjistili, který příkaz vynutil rekompilaci). Na záložce Data Columns pak vybereme hodnotu EventSubClass, která uvádí důvod rekompilace. Důvody mohou být následující(jsou důkladněji popsány v dalším odstavci):

 

  1. Změna databázového schématu
  2. Změna distribučních statistik
  3. Přístup na objekt, který neexistoval v době kompilace
  4. Vykonání SET příkazu
  5. Změna databázového schématu dočasné tabulky

 

Důvody rekompilace

  • Externě vynucená rekompilace. Přinutit proceduru k rekompilaci lze několika způsoby. Prvním je vytvořit proceduru s WITH RECOMPILE klauzulí, která přinutí optimalizátor použít při každém spuštění nový plán. Tato volba se používá v situacích, kdy se například vstupní parametry procedury výrazně a často liší a procedura pak využívá naprosto rozdílné plány. Je však potřeba důkladně zvážit, kdy opakovaná rekompilace převýší nevýhody z použití neaktuálního prováděcího plánu. Druhá možnost je volat proceduru opět s klauzulí WITH RECOMPILE. Tato možnost se dá využít v případě, že víme, že vstupní parametry vyžadují nový prováděcí plán, v praxi však takováto situace často nenastává. Další možností je vymazat procedurální cache příkazem DBCC FREEPROCCACHE, při dalším vykonání procedury se bude muset plán nově vytvořit. Poslení možností je procedura SP_RECOMPILE. Tato procedura očekává jako parametr buď jméno procedury(nebo triggeru), která má být určena k rekompilaci, nebo název tabulky. Pokud je parametrem tabulka, budou při příštím spuštění rekompilovány všechny procedury, které referencují tuto tabulku(pokud se podíváte do tabulky sysobjects, zjistíte, že sp_recompile mění sloupec schema_ver, který si hlídají všechny procedury).
  • Používání některých SET příkazů. Použití některých SET příkazů způsobí rekompilaci procedury, proto je vhodné, pokud to je možné, nastavit tyto hodnoty v rámci jenoho spojení, jiná pomoc zde neexistuje. Konkrétně se to týká: SET ANSI_WARNINGS, ANSI_DEFAULTS, ANSI_NULLS, ANSI_PADDING, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL a dalších.
  • Zestárnutí prováděcího plánu. Každý plán má ve své datové struktuře uloženu hodnotu obtížnosti sestavení plánu a také stáří plánu. Pokaždé, když je plán využit, je přičnena hodnota obtížnosti ke stáří plánu, tedy čím složitější plán a čím častěji využívaný, tím vyšší je hodnota stáří. Tato hodnota je naopak snižována na pozadí běžícím procesem. Plán je pak vyřazen z paměti, když je splněno několik podmínek. Hodnota stáří je rovna nule, plán není v této chvíli využit a je nedostatek paměti, při této konstelaci je plán vyřazen.
  • Změna velkého počtý řádků v tabulce. Pokud se změní dostatečně velký počet řádků v nějakém indexu, jsou automaticky přepočítány distribuční statistiky(těm se budu podrobněji věnovat v příštím článku). Po přepočítání statistik by se mohl stát, že některý index již není tak výhodný a výsledkem může být jiný plán. Místo klasických dočasných tabulek je proto lepší používat tabulkové proměnné, na které se toto omezení nevztahuje. Počet nutných změn přibližuje tato tabulka(prázdná tabulka znamená pro dočasnou tabulku méně než šest záznamů, pro normální tabulku méně než pět set):

 

Tabulka

Prázdná

Neprázdná

Dočasná

Změny > 6

Změny > 500 + 20% tab.

Normální

Změny > 500

Změny > 500 + 20% tab.

 

Příklad: Vytvořme si jednoduchou tabulku s jedním sloupcem a nad ním index, potom vytvořme proceduru, která bude měnit dostatečný počet záznamů. Při každém sluštění této procedury proběhne rekompilace:

 

CREATE TABLE dbo.Temp (lngID int)

CREATE INDEX IX_Temp ON dbo.Temp(lngID)

 

Create PROCEDURE dbo.TempProc

as

SET NOCOUNT ON

 

DECLARE @lngCounter INTEGER

 

DELETE FROM dbo.Temp

INSERT INTO dbo.Temp values(1)

SET @lngCounter = 1

 

WHILE @lngCounter < 10

BEGIN

      INSERT INTO dbo.Temp

      SELECT * FROM dbo.Temp

      SET @lngCounter = @lngCounter + 1

END

 

  • Změna databázového schématu. Tato podmínka často nastává, pokud se v kódu procedury míchají DDL a DML příkazy. Pokud v okamžiku kompilace neexistuje, nebo v průběhu vykovávání procedury se změní některý objekt, na který se procedura odkazuje, musí se opět rekompilovat. Mezi často používané DDL příkazy patří vytvoření dočasné tabulky a přidání nebo smazání indexu. Pro co změnšení počtu rekompilací je vhodné seskupit všechny DDL příkazy na začátek procedury. Příklad:

 

CREATE PROCEDURE dbo.MIXED AS

      CREATE TABLE dbo.a (A INT)

      SELECT * FROM dbo.a

     

      CREATE INDEX IDX ON dbo.a(A)

      SELECT * FROM dbo.a

     

      DROP INDEX dbo.a.IDX

      SELECT * FROM dbo.a

 

            Se v průběhu rekompiluje třikrát, kdežto následující procedura jen jednou:

 

      CREATE PROCEDURE dbo.NONMIXED AS

      CREATE TABLE dbo.a (A INT)

      CREATE INDEX IDX ON dbo.a(A)

      DROP INDEX dbo.a.IDX

 

      SELECT * FROM dbo.a

      SELECT * FROM dbo.a

      SELECT * FROM dbo.a

Posted 19. prosince 2004 8:29 by radim | 0 Comments

Vedeno pod:

Záměna datového typu za UDT v MS SQL 2000

Pro svůj projekt jsem potřeboval automaticky nahradit všechny výskyty určitého datového typu za UDT, typicky to byla náhrada varcharů s určitou délkou, nebo náhrada desetinných čísel s určitou přesností, opět na nově definovaný UDT. Třeba se někomu bude hodit, tady je:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AdminChangeType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AdminChangeType]

GO

Create procedure AdminChangeType
(
 @NewType varchar(100),   -- name of the new type
 @OldType varchar(100),  -- name of the old type
 @Length int,    -- length of the old type
 @Scale int = null,   -- not mandatory, decimal places
 @OverrideNullability int = 0  -- flag, if overwrite nullability of column with default null. of the new type
)
as
 Set Nocount ON

 -- Variables declaration
 Declare @TableName varchar(100)
 Declare @ColumnName varchar(100)
 Declare @Nullability tinyint
 Declare @Sql varchar(3000)

 -- Cursor declaration
 Declare Types Cursor Forward_only for
 select
  o.[name] as TableName,
  c.[name] as ColumnName,
  ( c.status & 8 ) / 8 as NullsAllowed
 from
  syscolumns as c join sysobjects o
  on c.id=o.id
 where
   o.xtype = 'U'
  and  c.xtype = (select xtype from systypes where [name] = @OldType)
  and  c.prec = @Length
  and IsNull(c.scale, -5) = IsNull(isnull(@Scale, c.scale), -5)
 order by o.name

 -- Get first data
 Open Types
 Fetch next from Types into @TableName, @ColumnName, @Nullability

 -- Loop through all columns
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
  -- Prepare the alter statement
  Set @Sql = 'Alter table ' + @TableName + ' alter column ' + @ColumnName
  Set @Sql = @Sql + ' ' + @NewType

  -- Change the nullability
  IF(@OverrideNullability = 0)
  BEGIN
   IF ( @Nullability = 0 ) SET @Sql = @Sql + ' NOT'
   SET @Sql = @Sql + ' NULL'
  END
  
  Print @Sql
  --Exec (@Sql)

  -- Get next data
  Fetch next from Types into @TableName, @ColumnName, @Nullability
 END


 -- Close used cursor
 Close Types
 Deallocate types

 

Posted 19. listopadu 2004 12:59 by radim | 0 Comments

Vedeno pod:

Návrhový vzor Iterátor a .Net FW

Návrhový vzor Iterátor je velmi často používán v objektově orientovaných systémech a knihovnách, obzvláště v knihovnách kolekčních tříd - bag, list, set, queue, tree, ... Iterátor poskytuje způsob, kterým je možno sekvenčně přistupovat k vnitřním prvkům objektu, aniž by objekt musel zveřejnit svou vnitřní strukturu. Základem tohoto vzoru je odebrání odpovědnosti za procházení prvky kolekce z objektu samotného a předání této na speciální Iterátor, který se stará o vše potřebné. Výhodou je, že není třeba rozšiřovat rozhraní kolekční třídy a že je možno definovat více iterátorů - tedy více možných způsobů procházení členy objektu. U GoF vypadá diagram takto: Obecná kolekční třída AbstractList obsahuje metody jako Count(), Add() a Remove(). Kromě nich obsahuje také CreateIterator(), která vrací instanci typu Iterator. Ten obsahuje metody First(), Next(), IsDone() a CurrentItem(). Konkrétné potomci AbstractListu - př. List a SkipList vracejí "své" iterátory - ListIterator a SkipIterator. CreateIterator je navíc takto typickým představitelem vzoru Tovární metoda.

Při různých implementacích vzoru Iterátor se musí řešit několik otázek. Umístění algoritmu: přestože se může zdát, že výkonný kód musí být vždy uložen v potomcích třídy Iterator, v některých situacích může být vhodnější implementovat procházení v samotné kolekční třídě a používat objekt Iterátoru pouze jako držitele stavu. Tento přístup má nevýhodu v tom, že omezuje použití procházecího algoritmu pouze pro danou třídu, kdežto algoritmus v Iterátoru by šlo použít pro množství podobných tříd. Další nevýhodou je zde neohrabanost využití několika způsobů procházení v tomto případě. Výhodou je, že se nemusí řešit problémy k přístupu ke privátním členům iterované třídy, což u komplikovanějších tříd může být problém.

Druhou důležitou otázkou je, kdo řídí průběh iterace. Nabízejí se dva způsoby; při prvním iteraci řídí klient a při druhém je iterace řízena iterátorem samotným. Častější je použití prvního způsobu, kdy klient sám volá metody Next() a CurrentItem(), dle jeho požadavků. Koho zajímá budoucnost C#, doporučuji díl MsdnTv, kde Anders Hjelsberg mluví o trendech, kterým se ubírá vývoj jazyka, zvláště povídání o generikách a anonymních metodách - výsledkem by měly být konstrukce typu new Iterator({ CurrentPosition mod 2 = 0}), kde iterátor bude vracet pouze sudé záznamy. Velmi se to podobá operátoru do: SmallTalku, nebo třeba abstrakci Haskellu: foldr f z (x:xs) = f x (foldr f z xs), což je fce f aplikovaná na všechny prvky seznamu.

Jak je tedy konkrétně implementuje Iterátro .Net FW? Celý systém stojí na dvou rozhraních, IEnumerable a IEnumerator. IEnumerable obsahuje jedinou metodu GetEnumerator(), která vrací objekt implementující rozhraní IEnumerable. To obsahuje metody Reset(), MoveNext() a vlastnost Current. Z toho vyplývá, že algoritmus procházení je vždy uložen v iterátoru a za řízení iterace je zodpovědný klient. Pokud chceme umožnit procházení vlastní třídou, nejprve bude nutné vytvořit iterátor(implementuje Enumerator). Tento bude kromě metod vyžadovaných rozhraním obsahovat i konstruktor, kterému se předá odkaz na iterovanou třídu. GetEnumerator pak bude vypadat nejspíš jako: { return new MyEnumerator(this); } K těmto dvoum rozhraním se pojí i konstrukce foreach, která se bez nich neobejde, je na nich založena:

foreach(string st in arrayList)
{
 Console.WriteLine(st);
}

je totiž kompilátorem rozvinuta na:

IEnumerator enum = arrayList.GetEnumerator();
// očekává se, že iterátor bude ve stavu po Reset, tedy před prvním prvkem v kolekci
try
{
 while(enum.MoveNext())
 {
  string st = (string)enum.Current;
  Console.WriteLine(st);
 }
}
finally
{
 if(enum is IDisposable)
 {
  enum.Dispose();
 }
}

 

Posted 1. listopadu 2004 11:23 by radim | 0 Comments

Vedeno pod:

Vyhodnocování SELECT příkazu

S jedním kolegou jsme nedávno zabředli do diskuze na téma standartizace SQL dialektů. Respektive já jsem se trochu podivil nad faktem, že v oracle forms projektu používají stále starou(pre ansi sql-92) join syntaxi. Ještě více mne udivilo to, že o sql-92 téměř neslyšel a novější join syntaxi viděl někde na obrázku. Sám jsem musel několikrát novější syntax prosazovat a většinou se to později setkalo s kladným ohlasem(jenom jednou jsem narazil na lidské omezení - parta outsourcingových indických vývojářů kdesi z Bangalore nebyla schopna toto podporovat, takže se psalo po staru), ale nikdy jsem se nesetkal s takovou neinformovaností.

Několikrát jsem také dostal dotaz, jak a kdy se vlastně vyhodnocují podmínky v ON a WHERE klauzuli, kdy DISTINCT a HAVING, což je věc, proč tento spot píšu. Třeba to někomu pomůže přejít a lépe pochopit novější join styl. Mějme třeba takovýto select:

Select *
from Company C
inner join Branch B
on (C.CompanyID = B.CompanyID)
left join Employee E
on (B.BranchID = E.BranchID)
where E.Sex = "alespon 2x tydne"

Select se bude vyhodnocovat následovně:

  • Nejdříve se vytvoří kartézský součit C a B(výsledná virt. tabulka tedy bude mít |C| * |B| prvků).
  • Z tohoto kart. součinu se oříznou všechny řádky, kde C.CompanyID <> B.CompanyID(a protože používáme inner join, s výsledem se rovnou pracuje dál)
  • K takto ořízlému výsledku se opět udělá kart. součin s tabulkou Employees
  • Opět se oříznou záznamy, kde B.BranchID <> E.BranchID
  • Další krok je odlišný. Protože je použit outer join(left) jsou k výsledku přičteny řádky z původní virt. tabulky, kde nebyla žádná shoda s Employee. V praxi to vypadá tak, že pro left je speciálním příznakem označena levá tabulka, pro right join pravá a pro full outer join obě. Kopírovány jsou pak záznamy z tabulek s příznakem.
  • Fajn, teď máme všechny tabulky spojené a nakonec na celé spojení aplikuji WHERE podmínka.

Pak následují další klauzule: výsledek se případně zgrupuje a odfiltruje pomocí having, případně se pouze odfiltrují duplikáty pomocí distinct. Nakonec se provede řazení(orde by) a na klienta se předá pouze určitý počet záznamů(top).

Důkladnější popis s několika příklady je možné najít třeba zde.

Pokud jste dočetli až sem, co používáte vy za sql normu, jaká jsou ve vaší firmě pravidla?

Posted 20. října 2004 16:07 by radim | 8 Comments

Vedeno pod:

Uživatelské datové typy v SQL2005 (2)

V minulém příspěvku o uživatelských datových typech(UDT) v SQL2005 jsem skončil vytvořením příslušné třídy. Nyní je tedy potřeba s tímto typem začít pracovat, vytvořme si tabulku:
  create table TestKrychle
  (
   ID int identity(1,1) not null,
   K1 kvadr null
  )

Naplňme ji daty:
  insert into TestKrychle(k1) values (cast('2;42;3' as kvadr))
  insert into TestKrychle(k1) values ('3;56;6')
  insert into TestKrychle(k1) values ('123;56;106')
  insert into TestKrychle(k1) values ('332;526;896')
  insert into TestKrychle(k1) values ('678;5;346')
  insert into TestKrychle(k1) values ('940;36;643')
  insert into TestKrychle(k1) values ('23;564;63')

A podívejme se na výsledek:
  select id, cast(k1 as varbinary),cast(k1 as varchar) from TestKrychle

Ve výpise je vidět, jak je udt uložen. Protože jsem zvolil Native formát, jsou za sebe jednoduše poskládány všechny tři integery. Díky tomuto faktu můžeme nastavit volbu IsByteOrdered, která říká, že bitové pořadí, v jakém je typ uložen, je vždy stejné. Pokud bychom třeba ukládali řetezec a číslo, už by toto pravidlo neplatilo(muselo by se implementovat vlastní ukládání a třeba zarovnat každý řetězec na určitý počet znaků). Volba IsByteOrdered umožňuje dvě věci. První z jich je porovnávání, díky zaručenému pořadí vlastností se může použít binární porovnávání, jakékoliv jiné by bylo výkonnostně neúnosné. V našem příkladu si můžeme vypsat pouze kvádry s trojcifernou X souřadnicí:

  select id, cast(k1 as varbinary),cast(k1 as varchar) from TestKrychle
  where k1 > convert(kvadr, '100;2;3')

Z možnosti porovnávání vyplývají další způsoby použití. S udt můžeme pracovat v ORDER BY či DISTINCT klauzulích:
  select cast(k1 as varbinary),cast(k1 as varchar) from TestKrychle
  group by k1
  order by k1.Y desc
  --order by k1 desc

Z těchto možností logicky vyplývá poslední funkce umožněná vlastností IsByteOrdered: indexování(k vytvoření indexu je potřeba pouze porovnávací funkce). Indexovat je možno buď celý sloupec:
  Create Clustered Index IDX1 on TestKrychle(K1 Desc)

anebo pouze určenou vlastnost nebo metodu(ty je nutné dekorovat atributem SqlMethod(IsDeterministic), který zakazuje jakýkoliv přístup k datům). Tyto specifické vlastnosti není možné indexovat přímo, je nutné nejprve vytvořit počítaný sloupec, pozměníme tedy strukturu tabulky:
  create table TestKrychle
  (
   ID int identity(1,1) not null,
   K1 kvadr null,
   K1Z as K1.Z persisted
     )

Nyní je možné vytvořit index:
  Create Clustered Index IDX2 on TestKrychle(K1Z Desc)

V T-SQL se mohou vyvolávat i jednotlivé třídní metody. Rozšiřme tedy původní třídu o dvě další metody, které již budou dekorovány IsDeterministic:
        #region Additional methods
        [SqlMethod(IsDeterministic = true)]
        public int Objem()
        {
            return X * Y * Z;
        }

        [SqlMethod(IsDeterministic = true)]
        public int Povrch()
        {
            return 2 * (X * Y + X * Y + Y * Z);
        }
        #endregion

Metody pak zavoláme:
  select id, cast(k1 as varbinary),cast(k1 as varchar),
  k1.X, k1.Y, k1.Z, k1.Objem(), k1.Povrch()
  from TestKrychle

Podobně jako na dimenzi kvádru je možné vytvořit index i na jeho objem. Nejprve je však nutné vytvořit počítaný sloupec:
  K1Objem as K1.Objem() persisted
a pak index:
  Create Clustered Index IDX3 on TestKrychle(K1Objem Desc)

... to be continued ...

Posted 19. října 2004 7:06 by radim | 2 Comments

Vedeno pod:

Vyvojar.cz na prodej!