Multithreading SMO

I have been experimenting with multithreading the SMO database objects to increase performance in my application but i never seem to beable to push the cpu load of the system above 25% (4 processor server).

Has anyone successfully been multithreading these objects?

Niklas,

SMO can be used in multithreaded scenario, but improvements in performance highly depend on the design on your application. SMO applications are usually not CPU-bound, and the best strategy is usually to optmize the number of queries to the SQL Server SMO has to perform.

Here is a couple of great articles written by one of SMO architects Michiel Wories:

http://blogs.msdn.com/mwories/archive/2005/05/02/smoperf1.aspx

http://blogs.msdn.com/mwories/archive/2005/05/02/smoperf2.aspx

Multithreading SMO

I have been experimenting with multithreading the SMO database objects to increase performance in my application but i never seem to beable to push the cpu load of the system above 25% (4 processor server).

Has anyone successfully been multithreading these objects?

Niklas,

SMO can be used in multithreaded scenario, but improvements in performance highly depend on the design on your application. SMO applications are usually not CPU-bound, and the best strategy is usually to optmize the number of queries to the SQL Server SMO has to perform.

Here is a couple of great articles written by one of SMO architects Michiel Wories:

http://blogs.msdn.com/mwories/archive/2005/05/02/smoperf1.aspx

http://blogs.msdn.com/mwories/archive/2005/05/02/smoperf2.aspx

Multithreaded run of SSIS packages

Is it safe to run several SSIS packages in parallel in a multithreaded application? The package objects are not accessed from multiple threads: every thread is handling its own package (see below).

void task_inside_thread()
{
System::String ^s = "...";
Microsoft::SqlServer::Dts::Runtime::Package p;
p.LoadFromXML(s, nullptr);

// callback handler
MyEventsClass ^ eventsClass = gcnew MyEventsClass();

DTSExecResult result = p.Execute(nullptr, nullptr, eventsClass, nullptr, nullptr);
return;

}

Is there any relevant documentation with respect to this issue?

Thanks,

Bogdan

Yes, this should be safe, packages are independent of each other.|||

I worried more about the fact the package instances might use global objects for registration and for signalling their current status.

Thanks for your answer.

Bogdan

|||Yes, they sometime do. I have to re-phrase it - they behave as independent objects, taking care of locking global state when needed.

Multiple SqlDataSource on a page

Hi,

I placed several SqlDataSource objects on my page which work with the same database (same connection string).

As I know, connection operation to database is costly in the performance prespective.

Do these SqlDataSource controls work with the same connection object or each of them create his own connecton object?

If each create it's own, then can they be changed to work with one connection object?

Thanks

As long as the connections strings are exactly identical, then the connection will get reused automatically as connections are automatically pooled.

Read tip #3:http://msdn.microsoft.com/msdnmag/issues/05/01/ASPNETPerformance/#S4

|||

Thanks,

Yet, another question:

If a SqlDataSource is not connected to a data UI control than does it still go retrieving the data or does it wait to be useed?

|||

It waits for the data to be requestsed such as by calling its Select() method

Multiple rows into one query result row...

I have an abstract relational database underneath some business objects... For instance, say I have two tables...

TABLE 1: A simple list of people...

ID USER
---
1 Mike
2 John

TABLE 2: Name/Value pairs of attributes linked to table 1 by ID...

ID NAME VALUE
------
1 Hair Brown
1 Eyes Blue
2 Weight 200

So you can see from this that Mike has brown hair and blue eyes, and that John weighs 200 lbs.

I want a query that selects a person and all their attributes (whatever they may be), and returns the results on one row like this (when run with a WHERE clause that selects user Mike).

USER HAIR EYES
------
Mike Brown Blue

And returns this when run with a WHERE clause that selects user John...

USER WEIGHT
-----
John 200

Any ideas? Thanks in advance!Wow! Talk about stuff that will give you nightmares!

-PatP|||This question has been asked and anwered hundreds of times in all forums. Do some research!
Hint: Search for PIVOT TABLE, CROSSTAB QUERY, etc... :mad:|||This question has been asked and anwered hundreds of times in all forums. Do some research!
Hint: Search for PIVOT TABLE, CROSSTAB QUERY, etc...I think this one is a twist on the usual cross tab... It looks like they want the query schema to change from row to row, which goes way beyond a cross-tab in my mind. It makes my head hurt just thinking about it. A cross-tab seems simple to code and use in comparison to this request.

-PatP|||Thanks, Pat. I'm being bludgeoned on a few boards over this. People keep saying cross-tab, which is not what I'm after here...

It does indeed make the head hurt...|||I would classify this as a candidate for a recursive query. Search through this forum for some good ideas.|||candidate for dynamic sql

step 1: select distinct name from table2 where id=n

if you don't know n, do a join and use WHERE table1.name = 'fred'

step 2: construct multiple LEFT OUTER JOIN query, from table1 to table2 as many times as there are attributes that fred has (from step1), aliasing each table2.value to the corresponding column name

step 3: execute the dynamic query

easy peasy|||I think that Rudy's suggestion is a good one, if you can allow all of the rows in a given result set to have the same schema. This is probably the closest answer possible to what you want using standard SQL tools. Supporting irregularly shaped result sets is possible using some tools, but not using standard recordset-oriented tools.

As Fibber used to say: "T'ain't pretty, McGee!"

-PatP