November 25, 2008 at 11:39 pm
HI all
I have generated SQLScript of existing Database using Management Studio and added some more script for creating Database by passing variable .
the total size of Script is 6.5 MB out of which XML Schema Collection is of 6MB size . when i execute this script from command line it takes around 3 minute to execute which is huge for my requirement i want it to be executed with in 2 minutes.
My doubt is does Creating XML Schema Collections takes more execution time than Creating Tables using script?
Is there any other way to reduce the Execution time of my SqlScript using sqlcmd option?
Is there any maximum Size limit for Script File ?
Now I cannot reduce the size of the ScriptFile , can any one there help me with some tips in creating Efficient script file .
Thanks
November 26, 2008 at 6:22 am
If you're scripting database creation, two areas are going to cause you problems in terms of time. First, however big the database you create determines, on a given system, how long it takes to create that database. An empty database 1mb in size is going to be created a lot faster than an empty database 100mb. However, you can't really create the database small or you'll just have to grow it later, at cost, anyway.
Second, I assume you're using a bulk insert for the data. That's about the fastest way to load data. The only thing you can do to speed that up is drop all indexes first, then load the data. The amount of data you load determines the time on this one.
You'll have to post more details about what you're doing and how you're doing it to get more detailed than this, but these are your principal areas of concern.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2008 at 9:11 am
The OS matters as well because you might be able to create the 1MB Db as quick as 100MB if zero file initialization is in effect.
November 26, 2008 at 9:12 am
Also, you can't necessarily control the time to create. If there is something else going on in the host system, a user watching YouTube, for example, things can take longer.
Size of the system matters as well. CPU, RAM, etc.
November 26, 2008 at 9:07 pm
Thanks to all for your quick replies .
a part from considering the above cases does XML SCHEMAS in data base
will effect execution .
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply