One More Question On Running Parallel Queries

An Execute SQL task takes 1 min to run a statement "insert into Mytable select * from view_using_joins"

Output: 10,225 rows affected.

But a Dataflow task configured to fetch data from the same view_using_joins into MyTable takes hours to do the same.

Could you please explain why is it so ?

Thanks

Subhash Subramanyam

I am not surprised that the Execute SQL Task is quicker. When you're inserting from one table to another in the same database then SSIS isn't going to outperform the database engine.

I don't know why it is hours quicker. There isn't really enough information here to say. What destination adapter are you using? Is the package running on the same machine as the database? Are you doing transformations on the way?

-Jamie

|||

Hi Jamie,

Thanks for your reply.

1) I am running packages on a different server.

2) Using OLEDB adapters for Source and Destination . Here Database as well as the server are same for Source and Destination

3) No transformations in between

Wonder if we have to do some extra settings to here to achieve the same duration as that of execute sql task ?

Thanks

Subhash Subramanyam

|||

Have you chosen Fast Load on your destination?

Thanks.

|||

Subhash512525 wrote:

Hi Jamie,

Thanks for your reply.

1) I am running packages on a different server.

2) Using OLEDB adapters for Source and Destination . Here Database as well as the server are same for Source and Destination

3) No transformations in between

Wonder if we have to do some extra settings to here to achieve the same duration as that of execute sql task ?

Thanks

Subhash Subramanyam

You're running the package on a different server? I'd suggest that's yur problem right there. The data is going to have to go over the network - obviously this is going to take time.

Also, in your destination are you inserting with Fast Load?

I say again, in this scenario the data-flow isn't going to outperform the Execute SQL Task.

-Jamie

|||

Great Jamie, you figured out.

If you don't mind spending few minutes here, I am coming back to my actual scenario.

I surely expect specific views from experts here for each of the questions here: Phil B, Rafael S, Darren G, Jwelch, JayH, Scott B, Ashwin S, Brian, Bob, Donald F and many others I am still not aware of.

Scenario:

1) My SSIS Packages are run at US server. scheduleld during Nights.

2) Each Package runs 6-8 queries each having Joins Parallelly pulling data from Oracle Database Source (UNIX) in Europe, Total Data extracted do not exceed 5 Million rows)

3) Destination Database is at US.

4) Network Bandwidth (2 Mbps)

Problem is that It almost takes ages to execute these Packages (Ranging from 25 hours to 30 hours)

Questions are:

1) Where should I expect to run the SSIS Packages to give a better performance?

2) How can I perform only incremental load (using Dataflow task) taking into consideration performance aspects? (Any links for this can help)

3) Does the overlap of the Schedules for SSIS packages afffect the performance?

4) Are there any limits on running number of queries in parallell to pull data from oracle source

5) Will it be the best way, If I spool the query results into flat files on a local system where the source (oracle database) runs at Europe and then ftp them to a shared server at US, which I can use it for importing into Destination table

Waiting for your reply,

Many Thanks and Regards

Subhash Subramanyam

|||Thanks Bob, Please give your answers for my below questions if don't mind.|||The more work you can do to prevent keeping the data transmission "pipe" open, the better.

Perform your source query in Europe, export that to a file, compress it, and then FTP it to the US. Then uncompress it, and load it with SSIS.

The idea is to keep your transmissions across "the pond" as short as possible.|||

From your question #2, I'm assuming you are pulling all rows every night. As Phil mentioned, you want to minimize how much data you are actually moving, so I'd definately make this incremental. A common way to implement that is by checking modified dates on the source system via a WHERE clause in your source SELECT statements. Store the range of modified dates that you retreive, and when the package is run the next night, start from the end of the previous range.

If you don't have modified dates in the source system, consider adding them. Alternatives are using triggers to track changes, or using a change data capture tool - I believe Oracle has one, and SQL Server will have one with SQL Server 2008.

|||

One more question:

6) If I have 6-8 queries running in parallel, Whether having a common connection Manager (for an Oracle source) for all performs better or having Distinct Connection Manager performs better ?

Still expecting suggestions and the views of rest of the experts for six questions listed here.

Regards

Subhash Subramanyam

|||

Subhash512525 wrote:

6) If I have 6-8 queries running in parallel, Whether having a common connection Manager (for an Oracle source) for all performs better or having Distinct Connection Manager performs better ?

It depends Using a single one should result in the same performance as having several, assuming you are not using RetainSameConnection on them. Having a single connection manager doesn't mean that SSIS won't open multiple connections to the database. A Connection Manager manages multiple connections to the database, unless you force it to use only a single connection with RetainSameConnection.

A related note - in your scenario, have you tested whether performance is better if you run all queries sequentially or in parallel (by using precedence constraints on the data flow tasks)?

|||

jwelch wrote:

A related note - in your scenario, have you tested whether performance is better if you run all queries sequentially or in parallel (by using precedence constraints on the data flow tasks)?

Jwelch, This seem more practical. I'll test this and let you know..

Thanks

Subhash

One Date 5 Queries

How can I use one date range (begin / end) for 5 separate queries? I can't
combine the queries into one because I get a timeout. I'm writing queries
against 5 different tables and the results need to be combined.
Any help is appreciated...Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1DDL, sample data, desired output
http://www.aspfaq.com/5006
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6205a8242857e@.uwe...
> How can I use one date range (begin / end) for 5 separate queries? I can't
> combine the queries into one because I get a timeout. I'm writing queries
> against 5 different tables and the results need to be combined.
> Any help is appreciated...Thanks
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Due to security I can't send that info out. I am only trying to figure out
how to use a single point of reference for my date range to support multipl
e
views. I create viewA to add up the sums and I currently hard code the date
range in. I then write viewB to do calculations referencing viewA for the
sums.
I have combined viewA & viewb into viewC and it provides me what I need
(Derivedtbl). I can pass a date range from a Web form to substitue the hard
coding of the date range. I need to link 5 derived tables just like the
aforementioned example (viewA + viewB + ViewC) into one combined derived
table, but I don't know how or if its even possible to pass the date range
from the Web form to all the sub views?
I am probably making this too hard so your expertise is certainly valued.
Chamark wrote:
>How can I use one date range (begin / end) for 5 separate queries? I can't
>combine the queries into one because I get a timeout. I'm writing queries
>against 5 different tables and the results need to be combined.
>Any help is appreciated...Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||> Due to security I can't send that info out.
So you can't come up with a frivolous and generic structure of what you want
to do? Ok...|||You can't send DDL, and a small set of illustrative data? We don't need
sensitive data that needs to be protected-only the TYPE of data that you are
dealing with. Probalby not the entire table either, PK, FK, Date fields, and
data to be summed. Seems like not a big issue if it provides you the
opportunity to gain the assistance and expertise you desire.
It sounds like you just may need one View or query - not multiple views.
I do believe that a web form could easily pass date values (start and end)
into a Stored Procedure. And then the Stored Procedure could easily pass the
parameters into several queries if necessary.
There may be many ways to solve your delimma, but we can't even start to
help unless we know what we're working with.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:62060b87788fa@.uwe...
> Due to security I can't send that info out. I am only trying to figure out
> how to use a single point of reference for my date range to support
> multiple
> views. I create viewA to add up the sums and I currently hard code the
> date
> range in. I then write viewB to do calculations referencing viewA for the
> sums.
> I have combined viewA & viewb into viewC and it provides me what I need
> (Derivedtbl). I can pass a date range from a Web form to substitue the
> hard
> coding of the date range. I need to link 5 derived tables just like the
> aforementioned example (viewA + viewB + ViewC) into one combined derived
> table, but I don't know how or if its even possible to pass the date range
> from the Web form to all the sub views?
> I am probably making this too hard so your expertise is certainly valued.
>
> Chamark wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hopefully this will help. How would I send you partial table(s) data?
CREATE TABLE [CSI] (
[Part Surv Id] [float] NULL ,
[Seq C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prod Id C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [smalldatetime] NULL ,
[#] [float] NULL ,
[Resp Val Id] [float] NULL ,
[Adjusted Weight] [float] NULL ,
[ExtSatWt] [float] NULL ,
[VerSatWt] [float] NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Surv Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [National Call Stats] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep Ssn] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NchQty] [float] NULL ,
[SchdOpenSecsQty] [float] NULL ,
[LogOnSecsQty] [float] NULL ,
[InAdherenceSecsQty] [float] NULL ,
[OutOfAdherenceSecsQty] [float] NULL ,
[HoldSecsQty] [float] NULL ,
[TotalHandleTime] [float] NULL ,
[TalkHoldAvailable] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [Call Scores] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score] [float] NULL
) ON [PRIMARY]
CREATE TABLE [CSI-Disconnect] (
[Date] [smalldatetime] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Call Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [National AR] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN Count] [float] NULL ,
[RTF] [money] NULL ,
[RTC] [money] NULL ,
[CashedOut] [money] NULL
) ON [PRIMARY]
GO
REATE TABLE [OPA] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OPA Code] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Opa Seconds Qty] [float] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Arnie Rowland wrote:
>You can't send DDL, and a small set of illustrative data? We don't need
>sensitive data that needs to be protected-only the TYPE of data that you ar
e
>dealing with. Probalby not the entire table either, PK, FK, Date fields, an
d
>data to be summed. Seems like not a big issue if it provides you the
>opportunity to gain the assistance and expertise you desire.
>It sounds like you just may need one View or query - not multiple views.
>I do believe that a web form could easily pass date values (start and end)
>into a Stored Procedure. And then the Stored Procedure could easily pass th
e
>parameters into several queries if necessary.
>There may be many ways to solve your delimma, but we can't even start to
>help unless we know what we're working with.
>
>[quoted text clipped - 19 lines]
Message posted via http://www.webservertalk.com

One DataRegion(Table) Multiple DataSets

I have a complexed report
It makes use of two queries and 2 tables
I need to use a group in order to display the information correctly,
If I had one query it would have worked perfectly, But the data I am
retrieving is so complexed that I need to make use of two queries other wise
I get duplicate data
Table 1 contains section1, and 2 of the displayed info
Table 2 contains the 3rd section
it looks like this;
Page 1
header
Section1
Section 2
Section 3
Footer
Page 2
header
Section1
Section 2
Section 3
Footer
So in order to accomplish this I take two tables link them to one dataset.
Add a group, But this results in the following. I need page breaks so I set
the page break option in the group properties
Page 1
header
section 1
section2
Footer
Page 2
section1
section2
Page 3
Section 3
Page 4 Section 3
I then put the 2 tables in a list box, and set the grouping on the list, And
This works 100 %. It groups all the data brilliantly. The problem is I cant
use one query, I need to use two!
SO Is their a work around or some way to link 2 datasets to one list
control.By adding the full path or something. The only way I can currently
reference more than one dataset per table is by using aggeragate funtions.
But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1 result
so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that returns
an errorData regions, in SQL Server 2000 Reporting Services, can only be bound to a
single data set with once exception: All secondary data references must be
contained in an aggregate function with the dataset specified. For example,
First(=Fields!<SomeField>.Value), "<SomeDataSet>"), is allowed. To achieve
the effect you want will have to be done in the query. Some of the tools
available to you are joins, unions, openrowset, or linked servers.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griffen" <Griffen@.discussions.microsoft.com> wrote in message
news:B7A2C3C0-4522-466A-B5D2-08ECCD3471C1@.microsoft.com...
> I have a complexed report
> It makes use of two queries and 2 tables
> I need to use a group in order to display the information correctly,
> If I had one query it would have worked perfectly, But the data I am
> retrieving is so complexed that I need to make use of two queries other
wise
> I get duplicate data
> Table 1 contains section1, and 2 of the displayed info
> Table 2 contains the 3rd section
> it looks like this;
> Page 1
> header
> Section1
> Section 2
> Section 3
> Footer
>
> Page 2
> header
> Section1
> Section 2
> Section 3
> Footer
> So in order to accomplish this I take two tables link them to one dataset.
> Add a group, But this results in the following. I need page breaks so I
set
> the page break option in the group properties
>
> Page 1
> header
> section 1
> section2
> Footer
> Page 2
> section1
> section2
> Page 3
> Section 3
> Page 4 Section 3
> I then put the 2 tables in a list box, and set the grouping on the list,
And
> This works 100 %. It groups all the data brilliantly. The problem is I
cant
> use one query, I need to use two!
> SO Is their a work around or some way to link 2 datasets to one list
> control.By adding the full path or something. The only way I can currently
> reference more than one dataset per table is by using aggeragate funtions.
> But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1
result
> so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that
returns
> an errorsql

One Connection Several queries?

I need to make one connection to a database and return 4 different pieces of data from the database so that I can maximize effciency. Well doing it that way is a guess on my part about maximizing effeciency.

Databases and me don't know a lot about each other. I can do some basic sql queries but that is about it and as far as my knowledge seems to go. ADO.NET is something I am still trying to learn well.

Any help would be great.

You can use Sp_Executesql Microsoft have provided some code to get you started. Hope this helps.

http://support.microsoft.com/default.aspx?scid=kb;en-us;262499

|||

It is advisable to minimize your connections, so you're off to a good start. You have two options for the multiple queries. One would be to open the connection, execute and process query 1, execute and process query 2, etc. For each query, you just specify the same connection. Your other option would be to put all your queries in a single stored procedure. You'd send down the command to execute the stored procedure with any paramers, and all 4 recordsets would be returned as children of one large recordset. You'd then process the first recordset, use the .movenext method, process the next recordset, etc.

Which way you choose depends on what you're doing with the data, how your app is architected, and your level of comfort.

|||Thank you both for your resposnes this has given me a direction to look in. I guess my next step is to learn Stored Procedures. Always new that not learning it would come back and haunt me.

OLEDB query responsiveness vs. .NET/SQL Server Management Studio

I am using OLEDB in C++ and the ATL templates to access a Compact Edition database and SQL Server Management Studio to test queries on it.

The problem I'm having is that SQL Server Management Studio seems to have much better query responsiveness (time to display of first result) than OLEDB, which sort of invalidates my reason for using Studio. Specifically this is the time to just return from ICommand::Execute.

Does .NET (which I assume Studio is using) have an advantage over OLEDB when it comes to query responsiveness? For example, a query that takes two or three seconds to display in OLEDB shows instantaneously in Studio. I even had one query that took 10 seconds in Studio but a minute in OLEDB. This is especially true for queries that involve sorting or distinct. Are there any settings (such as OLEDB properties) I might be missing that would influence this, or is SQL Server Compact Edition fundamentally slower in OLEDB for C++? I'm using a Forward Only cursor which should be the faster one for queries according to the docs.

So apparently the problem is being caused by the parameterized queries I am using... Why would a parameterized query work but take significantly longer for ICommand->Execute to return? Is there some data conversion going on somewhere? I haven't yet tried calling the OLEDB interfaces directly, but it seems to happen with the ATL template classes at least.