{"id":210,"date":"2007-03-06T11:22:36","date_gmt":"2007-03-06T10:22:36","guid":{"rendered":"http:\/\/soci.hu\/blog\/index.php\/2007\/03\/06\/sql-adatbazis-scriptelo\/"},"modified":"2007-03-06T11:23:57","modified_gmt":"2007-03-06T10:23:57","slug":"sql-adatbazis-scriptelo","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2007\/03\/06\/sql-adatbazis-scriptelo\/","title":{"rendered":"SQL adatb\u00e1zis scriptel\u0151"},"content":{"rendered":"

Egyszer\u0171 kis open source projekt<\/a>, amely SMO seg\u00edts\u00e9g\u00e9vel leszkripteli az adatb\u00e1zis objektumokat.<\/p>\n

Ha m\u00e1sra nem, h\u00e1t SMO p\u00e9ld\u00e1nak j\u00f3l j\u00f6het.<\/p>\n

A sebess\u00e9ge egy\u00e9bk\u00e9nt meglep\u0151en gyal\u00e1zatos, 12 perc alatt v\u00e9gzett az AdventureWorks-szel, 800 objektummal (j\u00f3, a replik\u00e1ci\u00f3 be van benne izz\u00edtva, az ad hozz\u00e1 sok objektumot).<\/p>\n

Megn\u00e9ztem m\u00e1r munka k\u00f6zben profilerrel, mert \u00e9rdekelt, mi\u00e9rt ilyen lass\u00fa. Nos, minden egyes objektumhoz lefuttatott egy j\u00f3 nagy rendszert\u00e1bl\u00e1kra vonatkoz\u00f3 lek\u00e9rdez\u00e9st, amelyek v\u00e9grehajt\u00e1si ideje 100 \u00e9s 500 ms k\u00f6z\u00f6tt volt. Ezt beszorozva 800-zal kij\u00f6n a fenti id\u0151.<\/p>\n

\u00cdzel\u00edt\u0151\u00fcl egy 650 ms-os \u00e1llatfajta:<\/p>\n

[source:SQL]
\nSELECT
\nSCHEMA_NAME(v.schema_id) AS [View_Schema],
\nv.name AS [View_Name],
\ntr.name AS [Name],
\ntrr.is_instead_of_trigger AS [InsteadOf],
\nCAST(ISNULL(tei.object_id,0) AS bit) AS [Insert],
\nCAST(ISNULL(ted.object_id,0) AS bit) AS [Delete],
\nCAST(ISNULL(teu.object_id,0) AS bit) AS [Update],
\nCASE WHEN tr.type = N’TR’ THEN 1 WHEN tr.type = N’TA’ THEN 2 ELSE 1 END AS [ImplementationType],
\nCAST(OBJECTPROPERTYEX(tr.object_id,N’ExecIsAnsiNullsOn’) AS bit) AS [AnsiNullsStatus],
\nCAST(OBJECTPROPERTYEX(tr.object_id,N’ExecIsQuotedIdentOn’) AS bit) AS [QuotedIdentifierStatus],
\nNULL AS [Text],
\nCAST(
\n tr.is_ms_shipped
\n AS bit) AS [IsSystemObject],
\nCASE WHEN ted.is_first = 1 THEN 0 WHEN ted.is_last = 1 THEN 2\tELSE 1 END AS [DeleteOrder],
\nCASE WHEN tei.is_first = 1 THEN 0 WHEN tei.is_last = 1 THEN 2 ELSE 1 END AS [InsertOrder],
\nCASE WHEN teu.is_first = 1 THEN 0 WHEN teu.is_last = 1 THEN 2\tELSE 1 END AS [UpdateOrder],
\nISNULL(smtr.definition, ssmtr.definition) AS [Definition]
\nFROM
\nsys.all_views AS v
\nINNER JOIN sys.objects AS tr ON (tr.type in (‘TR’, ‘TA’)) AND (tr.parent_object_id=v.object_id)
\nLEFT OUTER JOIN sys.assembly_modules AS mod ON mod.object_id = tr.object_id
\nINNER JOIN sys.triggers AS trr ON trr.object_id = tr.object_id
\nLEFT OUTER JOIN sys.trigger_events AS tei ON tei.object_id = tr.object_id and tei.type=1
\nLEFT OUTER JOIN sys.trigger_events AS ted ON ted.object_id = tr.object_id and ted.type=3
\nLEFT OUTER JOIN sys.trigger_events AS teu ON teu.object_id = tr.object_id and teu.type=2
\nLEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
\nLEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id
\nWHERE
\n(v.type = ‘V’)and(v.name=N’MSmerge_ppmv_B78A4C9C984B46D6BD69FEF04493340F’
\nand SCHEMA_NAME(v.schema_id)=N’dbo’)
\nORDER BY
\n[View_Schema] ASC,[View_Name] ASC,[Name] ASC
\n[\/source]<\/p>\n","protected":false},"excerpt":{"rendered":"

Egyszer\u0171 kis open source projekt, amely SMO seg\u00edts\u00e9g\u00e9vel leszkripteli az adatb\u00e1zis objektumokat. Ha m\u00e1sra nem, h\u00e1t SMO p\u00e9ld\u00e1nak j\u00f3l j\u00f6het. A sebess\u00e9ge…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,21],"tags":[],"class_list":["post-210","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2005"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/210","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=210"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/210\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}