The journal of Paul M. Watson.

Friday, April 07, 2006

SQL SMO performance and preloading

The SQL Management Objects in SQL2005 are a nice replacement to SQL-DMO. I was having performance problems while enumerating through objects in a database though. I wanted to filter out any system objects using the IsSystemObject property of Table, View and StoredProcedure. The if (object.IsSystemObject) call though took a sub 5 second loop and turned it into over a minute of crunching. Why on earth was a simple bool check fuxoring performance so badly?

Turns out that IsSystemObject is not returned as part of the initial data. SMO is running off and fetching that property from SQL Server 2005 each time you hit it. Ouch.

Euan Garden on SqlJunkies explains the reasons for this and tells you about the Server.SetDefaultInitFields setting which preloads that specific property. Performance is restored.

1 Comments:

Anonymous Derek Lakin said...

Glad you found the answer in the end. Looking forward to the public release ;)

9:13 PM

 

Post a Comment

Links to this post:

Create a Link

<< Home