Bug? Scripting DDL

Scripting DDL for objects in SQL Server is very slow (~5s/object). Any hints on what can possibly cause this?

It uses the same SMO interface that SQL Server Management Studio uses and it's just as slow as that.
We've made some optimizations in version 7.3 that improve the performance when scripting multiple objects, the overall performance is significantly better when scripting them 1 by 1.

In comparison to SMO, SQL Assistant's Schema Compare functions provide blazing fast generation of objects DDL, on top of that the code generation is driven by templates and fully customizable, while with SMO it's not that much.

That sounds interesting but provided that I only have one database that I want to export what am I going to compare it to?

It also seem to be a bit contradictory to what I experienced. I started the DDL export from the context menu in Database Explorer. After waiting a few minutes watching the progress in Messages pane (interestingly, the first few items took only milliseconds to script) I started SQL Server Management Studio and initiated another export from context menu > Tasks > Generate scripts. That one (including starting SSMS and configuring the export) finished several minutes before I decided to abort the export running in SQL Editor. I tried exporting from another server just to check if the performance issue was related to this specific one but the results were disappointing. I only selected 3 tables and they all failed after ~15 seconds each, with the results pane containing messages like

	Quote:
	DDL utility returned error: Cannot find source code for [dbo].[addresses]. DDL utility returned error: Connection error: Failed to connect to server MY-SERVERNAME.

Does the DDL utility address the server by its name (which was retrieved from the server upon connection) instead of the IP address which was given to establish the connection?

	Quote:
	I started SQL Server Management Studio and initiated another export from context menu > Tasks > Generate scripts

I'm not sure how this one is implemented, but that method seems to be fast. In comparison of you righ-click an object in the Object Explorer and choose fro mthe context menu to copy CREATE... script to new Editor Window, that will take much longer, on par with the timing of similar operation in SQL Assistant. Internally they use the same SMO (SQL Server Management Objects) API, which is kind of slow.

To have the same compare generate a script with DDL, you can compare your database against any virtually empty database or one that just doesn't have the same objects, for example, as a comparison target, you can use tempdb. When you run the comparison and then choose an option to generate synchronization script and save it or open in the editor, you should have DDL code for all selected objects. the contents of the script may also depend on the options selected, say if you want to skip permissions, uncheck them in the comparison scope.

I'll definitely give it a try, thank you very much for the tip.

EDIT: I've checked that and it looks very promising. Is this complete and thorough? I mean, I met a couple of comparison apps that failed miserably when the order of the objects to be created became important.

	Quote:
	EDIT: I've checked that and it looks very promising. Is this complete and thorough? I mean, I met a couple of comparison apps that failed miserably when the order of the objects to be created became important.

The code generation engine used by this function is a kind of different animal. Not sure what to say about completeness. But it can take for example into considerations differences in versions of source and target database, say if target is an older version, and automatically strip all features from the generated DDL that are not supported by the target. It event attempts to reconcile basic differences when compare different database system types, say SQL Server against MySQL and so on.

As for the the order of the objects, to the best of my knowledge, the engine tries to traverse the dependencies and choose the correct order, but in case of complex dependencies it may fail just like most other database tools. Many dependencies are either explicitly or implicitly recursive and may require multiple runs of the same code, for example, a table may have simple CHECK constraint or a computed field calling a user defined function, which which queries another table having a foreign key pointing to the first table. So just 2 tables and 1 functions, but if you know of a tool that can generate proper script for this situation, please let me know. In this case, you cannot start with a particular object, you need several passes to get this kind of schema created. Same case with recursive triggers and so on, there are myriads of such cases. AFAIK, one of the common solutions in such cases is to create bare bone objects first stripping all their constraints, indexes, triggers, and all other "meat", and then at the very last stage of the script when all table and code objects are created, create all previously ignored stuff, and even that is complicated, still may require multiple passes and still may require manual intervention. Use of SCHEMABINDING is a well known killer for such things. The schema comparison engine in SQL Assistant isn't designed for such complex cases, it treats each compared object individually and then at the end it generates a dependencies tree for non-recursive references and based on that tree it then generates the final DDL script.

Yes, you listed the very reasons I was asking :) I began to write a query that maps those complex dependencies and builds the proper script that in a single pass (but altering the same object multiple times if necessary) but after making deeper analysis of the issue it turned out that it would take months to make it complete and that's an effort nobody would pay for and definitely not something I could do in my spare time :(

I guess that leaves us with having to edit the outcoming scripts whenever those dependencies are important. Not a good practice when writing installer scripts...