Best solution: SQLXML, XML Data Binding, or MSXML?

I need some help to determine what the best solution would be for the
following scenarios where the dataset is large and performance is key:
Scenario #1
I am trying to retrieve relational data in XML from a SQL Server 2000 stored
proc. My client is a VBA Access application. I want to be able to map the xml
elements to db tables/columns and use a stored proc to collect the data and
build the xml. Validate it against the xsd. I am assuming that with large
datasets it would be faster to create the xml on the server than on the
client - desktop pcs - since I would have to loop through data rows to
create the xml using MSXML on the client, thoughts. What about annotated
schemas. Do you have to use along with XPath queries even if your result is
already filtered in an SQL sproc.
Scenario #2
I have a .Net webservice that will be the recepient of this xml and validate
it and persisit it to SQL 2K. After looking at all available options it seems
to me that using XML Data Binding & creating .Net classes based on the xsd &
loading this .Net object from xml is the cleanest solution. Reading into
SQLXML, it seems as though every method to persist data to SQL Server uses
the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR to
COM. Cant see how that is good. And MSXML is also COM based parser.
Thoughts?
Scenario #1: I would look at the FOR XML functionality of SQL Server 2000 if
you want to do it in the database. Annotated schemas do not work with stored
procs. You can look into the client-side FOR XML of SQLXML 3.0 if you want
to run FOR XML over the result of a stored proc.
Re Scenario #2: The SQLXML Bulkload object can be called through the managed
providers (in the latest SP of SQLXML 3.0). You will go through COM interop,
which you don't have to for the data binding. But I think the loading may
still be faster due to the bulkload mechanism. I would suggest doing some
perf tests for your specific data...
Best regards
Michael
"Aazihh" <nowayjose@.newsgroupms.com> wrote in message
news:D56903DB-3984-4EE4-9CE1-5E1CCC9FBEA6@.microsoft.com...
>I need some help to determine what the best solution would be for the
> following scenarios where the dataset is large and performance is key:
> Scenario #1
> I am trying to retrieve relational data in XML from a SQL Server 2000
> stored
> proc. My client is a VBA Access application. I want to be able to map the
> xml
> elements to db tables/columns and use a stored proc to collect the data
> and
> build the xml. Validate it against the xsd. I am assuming that with large
> datasets it would be faster to create the xml on the server than on the
> client - desktop pcs - since I would have to loop through data rows to
> create the xml using MSXML on the client, thoughts. What about annotated
> schemas. Do you have to use along with XPath queries even if your result
> is
> already filtered in an SQL sproc.
> Scenario #2
> I have a .Net webservice that will be the recepient of this xml and
> validate
> it and persisit it to SQL 2K. After looking at all available options it
> seems
> to me that using XML Data Binding & creating .Net classes based on the xsd
> &
> loading this .Net object from xml is the cleanest solution. Reading into
> SQLXML, it seems as though every method to persist data to SQL Server uses
> the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR
> to
> COM. Cant see how that is good. And MSXML is also COM based parser.
> Thoughts?

Best solution: SQLXML, XML Data Binding, or MSXML?

I need some help to determine what the best solution would be for the
following scenarios where the dataset is large and performance is key:
Scenario #1
I am trying to retrieve relational data in XML from a SQL Server 2000 stored
proc. My client is a VBA Access application. I want to be able to map the xm
l
elements to db tables/columns and use a stored proc to collect the data and
build the xml. Validate it against the xsd. I am assuming that with large
datasets it would be faster to create the xml on the server than on the
client - desktop pcs - since I would have to loop through data rows to
create the xml using MSXML on the client, thoughts. What about annotated
schemas. Do you have to use along with XPath queries even if your result is
already filtered in an SQL sproc.
Scenario #2
I have a .Net webservice that will be the recepient of this xml and validate
it and persisit it to SQL 2K. After looking at all available options it seem
s
to me that using XML Data Binding & creating .Net classes based on the xsd &
loading this .Net object from xml is the cleanest solution. Reading into
SQLXML, it seems as though every method to persist data to SQL Server uses
the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR to
COM. Cant see how that is good. And MSXML is also COM based parser.
Thoughts?Scenario #1: I would look at the FOR XML functionality of SQL Server 2000 if
you want to do it in the database. Annotated schemas do not work with stored
procs. You can look into the client-side FOR XML of SQLXML 3.0 if you want
to run FOR XML over the result of a stored proc.
Re Scenario #2: The SQLXML Bulkload object can be called through the managed
providers (in the latest SP of SQLXML 3.0). You will go through COM interop,
which you don't have to for the data binding. But I think the loading may
still be faster due to the bulkload mechanism. I would suggest doing some
perf tests for your specific data...
Best regards
Michael
"Aazihh" <nowayjose@.newsgroupms.com> wrote in message
news:D56903DB-3984-4EE4-9CE1-5E1CCC9FBEA6@.microsoft.com...
>I need some help to determine what the best solution would be for the
> following scenarios where the dataset is large and performance is key:
> Scenario #1
> I am trying to retrieve relational data in XML from a SQL Server 2000
> stored
> proc. My client is a VBA Access application. I want to be able to map the
> xml
> elements to db tables/columns and use a stored proc to collect the data
> and
> build the xml. Validate it against the xsd. I am assuming that with large
> datasets it would be faster to create the xml on the server than on the
> client - desktop pcs - since I would have to loop through data rows to
> create the xml using MSXML on the client, thoughts. What about annotated
> schemas. Do you have to use along with XPath queries even if your result
> is
> already filtered in an SQL sproc.
> Scenario #2
> I have a .Net webservice that will be the recepient of this xml and
> validate
> it and persisit it to SQL 2K. After looking at all available options it
> seems
> to me that using XML Data Binding & creating .Net classes based on the xsd
> &
> loading this .Net object from xml is the cleanest solution. Reading into
> SQLXML, it seems as though every method to persist data to SQL Server uses
> the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR
> to
> COM. Cant see how that is good. And MSXML is also COM based parser.
> Thoughts?

Best and worst scenarios for Notification Services

Hi,

I am just looking for the best and worst scenarios to implement Notification services...This is more to come up with a design guildelines

I Would be more interested in the worst sceanrios :) Please share your inputs based on your experiences...

Regards,

Anjana

I'm not really sure that I understand what you mean by worst case scenario. Do you mean situations where SQLNS is not the best tool for the job? Or do you mean specific configurations that would cause SQLNS to perform poorly.

In either case, I guess, that's going to largely depend on the situation.

|||

I meant where SQLNS should not be used...

For example let's take three cases

News Alerts (changes with reasonable freqeuncy, and alerts must reach the subscriber in reasonable time)

Weather changes alerts(changes very frequently,and alerts can reach the subscriber in reasonable time)

Stock market alerts (changes very frequently and alert needs to be sent immedaitely to the subscriber with no delay).

In the above scenarios are there any scenarios where NS might perform a bit poorly?

Regards,

Anjana

|||

SQLNS is very good at matching lots of event data with lots of subscriber/subscription data and generating lots of events. It is designed to be "internet scalable" where you may not know just how many subscribers, subscriptions, or even events that you maybe processing over a given time period. It's equally as good for smaller applications, too.

For smaller, purely in-house notification applications, sometimes Query Notifications are a better choice. It just depends on what you're trying to accomplish.

For cases where sub-second notifications are required, SLQNS is *not* the best tool. The generator fires on a regular basis to evaluate whether there has been any activities that it needs to process. Same for the distributor. If you must be notified immediately, that is without a possible 2 second delay, SLQNS is not for you.

HTH...