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 column missing from datasets even after refresh?

My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!

I have.

One thing you can do is check for a matching .rdl.data file and blow that away, see if next data pull rewrites the cached data.

Another thing you can probably do is just add it in manually. Rightclick on the *name* of the dataset (underneath the Report DataSets node in the Datasets window) and choose "Add..."

>L<

|||

Thanks for the help! The first method doesn't work. Even if I add a new report from scratch, the available fields are missing that column if I put the sp in the query builder from report build wizard.

Rightclick on the name of the dataset and add the missing column works, but after I deploy the report and go view the report from brower, I got the following message:

An error has occurred during report processing.

Query execution failed for data set 'RegionalLevelByWeek'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||Hmm. I think the first method doesn't work if you have a (usually generated) schema file associated

with the data -- I'm not looking at reports right now, so I could be wrong) -- you might have to "refresh" that file as well.

On the second one, do you have to re-deploy the datasource to the server or something? (remember it's "don't overwrite datasources during deploy" by default) Same is true with default values of params -- given your situation as you put it in this message, that might well be involved.

Now that I look at what you're saying here though... As far as your current thing with the two SP's, you could just put a dummy column into the first result set, couldn't you? The important thing to remember is that there is going to be *one* set of columns associated with that report, never two, so if your proc can return differently based on a param you pretty much have to make them match.

I'm sorry I can't do a worked example right now, it's always *something* find-able though...

>L<

sql

On what basis sql server sorts the rows

Hi,

When I execute the following statement:

Select * from table1;

On what basis, SQL server decides the sequence of these rows?

I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.

Thanks veyr much.

Regards,

Tim

hi Tim,

you always have to provide a sort (ORDER BY) as the engine can return data in no actual order at all.. data is scanned with IO operations that are not limited to a "physical matter", so, without an ORDER BY clause, they are returned in the order they are read.. if you have a multi cpu machine, different processors can get data in "whatever order" and merged in the actual results... usually the "physical order" of a clustered table (a table with a clustered index) is used, but, again, that order is not guaranteed.. if you need (as you usually do) a particular order, whatever it could be, you have to provide that "hint" to the query processor... this is even more "important" if you have to do it in chunck (where you should use the ROW_NUMBER() OVER( ORDER BY orderCol) new clause of SQL Server 2005).. this obviously makes the query "heavier", as the result must be first generated and then ordered, but gives you the "real" taste of correct data and not data found over again and again in the successive calls..

remember that the ORDER BY clause is "cursor clause" and not part of the actual query.. logically, it's the "last part" of a complete plan, where the actual query result is passed to a cursor operation to sort data as desired..

regards

|||

If you do not use an ORDER BY clause, SQL Server will produce the data in whatever order it deems efficient.

At times, that may be the order in which the data has been put in the table -but that is just a temporal fluke. There is no guarantee that you will get the data in the same order the next time you query.

To control the presentation, you MUST use an ORDER BY statement.

Here are some other ideas and help about what is often referred to as 'paging' queries:

Paging Queries
www.aspfaq.com/2120

|||

Thanks very much.

Regards,

Tim

Oledb stored procedure rowset binding

I have a strange problem with an OleDB call to a stored procedure that
returns a rowset.
Only the first time I execute the query, after I restart SqlServer, my
program crashes because
the rowset is empty. All next calls (after restarting my program, of
course) run successfully.

The context is:
1) if I do not bind the output rowset, my program doesn't crash
2) If I run the call without the binding and then I run the call with
the binding, it doesn't crash
3) It is not the first query I do in my session
4) the call crashes only if in the SP there are some inserts: it is a
well known problem with OleDB, but in many
other cases I have fixed it setting NOCOUNT to ONgigi (gigisoave@.libero.it) writes:
> I have a strange problem with an OleDB call to a stored procedure that
> returns a rowset.
> Only the first time I execute the query, after I restart SqlServer, my
> program crashes because
> the rowset is empty. All next calls (after restarting my program, of
> course) run successfully.
> The context is:
> 1) if I do not bind the output rowset, my program doesn't crash
> 2) If I run the call without the binding and then I run the call with
> the binding, it doesn't crash
> 3) It is not the first query I do in my session
> 4) the call crashes only if in the SP there are some inserts: it is a
> well known problem with OleDB, but in many
> other cases I have fixed it setting NOCOUNT to ON

Since you don't post any code, it is very difficult to tell. Then again,
even if you had, it would probably still have been difficult to tell.

First, exactly what API are you using? The OLE DB API itself? OLE DB
consumer templates? Something else? Which language do you use?

Next, let me try to see if I understand the scenario:

1) You start your machine.
2) SQL Server starts.
3) You start your application.
4) It runs some queries, and then it comes to this procedure that does
not return a rowset.
5) Your program crashes.
6) You restart your application, and now everything works fine.

Here I assumed that SQL Server ran locally. What happens if you have
SQL Server on a remote machine? Does the app still crash on first
access of SQL Server restart? What if you reboot the client machine
and run again?

Finally, while the start and stop of SQL Server could have something
to do with it, it could also be as trivial as buffer overrun, assuming
that you are programming in C++.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I can answer all your questions:

1) If I connect to a remote sqlserver the situation remains the same
2) I use AOleDB consuner template from VisualC++, but I know the
problem is in call to ICommand::Execute at oledb level.
3) when I restrat the server, the connection and the application are
closed.
4) Your scenario description is good.
Bye and thank you

> Since you don't post any code, it is very difficult to tell. Then again,
> even if you had, it would probably still have been difficult to tell.
> First, exactly what API are you using? The OLE DB API itself? OLE DB
> consumer templates? Something else? Which language do you use?
> Next, let me try to see if I understand the scenario:
> 1) You start your machine.
> 2) SQL Server starts.
> 3) You start your application.
> 4) It runs some queries, and then it comes to this procedure that does
> not return a rowset.
> 5) Your program crashes.
> 6) You restart your application, and now everything works fine.
> Here I assumed that SQL Server ran locally. What happens if you have
> SQL Server on a remote machine? Does the app still crash on first
> access of SQL Server restart? What if you reboot the client machine
> and run again?
> Finally, while the start and stop of SQL Server could have something
> to do with it, it could also be as trivial as buffer overrun, assuming
> that you are programming in C++.

OLEDB error encountered calling ICommandText:: execute

OLEDB error encountered calling ICommandText:: execute ; hr = 0x80040e14 .

SQLSTATE : 42000 , Native Error : 3013

Error state 1 , Severity : 16

Source : Microsoft OLE DB Provider for SQL Server

Error Message : Backup Database is terminating abnormally

What are you expecting by flooding the forums with your error message and no further explanations where this error occurs, which version of the database you are using, which Service pack, etc. ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

OLEDB driver omits SP return value

Hi,

I have a problem with some ATL7/OLEDB code. I'm trying to execute stored procedures from my application. I do get a result set back, but I never get a correct return value back. I've been going through MSDN articles as well as other articles on the web, and I just cannot see what I'm doing wrong!

I have this piece of ATL code:
<pre>
// code generated on den 6 juni 2002, 10:56

[
db_command(L"{ ? = CALL dbo.MyProc(?) }")
]
class CMyProc
{
public:
[ db_column(1, status=m_dwIdStatus, length=m_dwIdLength) ] LONG m_Id;
/* and so on - 7 columns total */

[ db_param(1, DBPARAMIO_OUTPUT) ] LONG m_RETURN_VALUE;
[ db_param(2, DBPARAMIO_INPUT) ] LONG m_id;

void GetRowsetProperties(CDBPropSet* pPropSet)
{
pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
}
};

</pre>

When I use this code I do:
<pre>
CSession dbsession;
CDataSource dbsource;
CDBPropSet dbinit(DBPROPSET_DBINIT);
/* fill dbinit with connection parameters */
/* HRESULTs not checked for brevity*/
dbsource.Open(_T("SQLOLEDB.1"), &dbinit);
dbsession.Open(dbsource);

CMyProc proc;
proc.m_id = 1;
proc.Open(dbsession);

TRACE("Return value is %d\n", proc.m_RETURN_VALUE);

/* Take care of result set - which exists and is valid! */
</pre>

The return value is <tt>-858993460</tt> which is <tt>0xcccccccc</tt>. In Visual C++ this value is used as an "uninitialized" stack value during debug. It seems as if ATL never bothers to update my return value parameters! I even made a little experiment with an extra output parameter - same thing there.

I'm using Visual C++ .NET and the SQL 2000 OLEDB provider (SP2). I've also tried this without attributed programming in both Visual C++ .NET and Visual C++ 6.

Does anybody have a clue what is going on??Is the stored procedure using set nocount on ? If not, put it in the stored procedure.

Good luck.|||Thanks for replying!

I tried with and without nocount and had no success.

I tried something else the other day though; I generated a non-attributed CCommand-class using CMultipleResults as template parameter. Then I could get all out parameters by calling GetNextResult() on the command object.

So my question is now; how do I make the attribute-injector generate a CCommand class for me using CMultipleResults?

OLEDB Destination question

Situation

I have a package with an execute SQL task that truncates the destination table as the first step in the control flow and a data flow task that reads data from a flat file and loads a sql server table.

Once in a while the package bombs because it cannot get access to the flat file. The end result is that the table is empty because the truncate runs first. Obviously, I need to address the file contention, but I was wondering how to address this issue in general since anything that causes the data flow to blow up would leave the table empty.

I would rather have the table with day old data than empty, since it is not mission critical and the users can at least look at yesterday's data as opposed to nothing.

Question

Is there a way to specify a "load replace" on the OLEDB destination? I haven't seen one and I guess it makes sense because the data flow task transformations run row by row.

The only solution that I have come up with is to have the following on the control flow:

1) data flow task which reads flat file and loads a temp table

2) execute sql task to truncate the "real" destination

3) data flow task to move data from temp table to real table.

Anyone else come up with a better way to handle this?

Thanks!!

I guess from my perspective, I'd never truncate a table as the "first" step of an import process. You're guaranteed to have upset users when an issue that's completely out of your control (meaning, the missing flat file) asserts itself.

I'm using the following logic in a similar application:

1) In a Script Task, I verify the flat file is available, and contains at least one row of data.

2) If the "flat file verification" succeeds, a "Go_NoGo" boolean variable is set to True; otherwise, it remains False

3) An expression (@.[User::Go_NoGo]==True) on the constraint between the Script Task and the next task in the control flow effectively prevents the truncate / replace operation from running if the variable is False.

The code to verify the existance of the flat file is pretty simple:

Private Function GetFileExists(ByVal ImportFile_Path As String) As Boolean
Dim fi As System.IO.FileInfo

Try

fi = New System.IO.FileInfo(ImportFile_Path)

If Not fi.Exists Then
'No point in continuing - cannot find file:
'Discard FileInfo object:
fi = Nothing
Return False
End If

If fi.Length = 0 Then
fi = Nothing
Return False
End If

'Discard FileInfo object:
fi = Nothing

Return True

Catch ex As Exception

Throw ex

End Try

End Function 'GetFileExists

The FileInfo "Length" property tells you how big the flat file is, so it's a pretty good measure of whether or not the flat file contains data.

Obviously, the two If statement can be combined with an Or. In my application I actually check the count of lines in the flat file, and save it to make sure I import all the rows by comparing the line count with the row count in the destination table:

Private Function GetLineCount(ByVal ImportFile_Path As String) As Long
Dim LineCount As Long
Dim sr As System.IO.StreamReader
Dim str As String
Dim i As Long

Try
sr = New System.IO.StreamReader(ImportFile_Path)

Do
str = sr.ReadLine

If Not str Is Nothing Then
If str.Length > 1 Then
LineCount += 1
End If

str = String.Empty

End If

Loop Until str Is Nothing

'Discard StreamReader object:
sr.Close()
sr = Nothing

Return LineCount

Catch ex As Exception

Throw ex

End Try

End Function

I don't run the GetLineCount function until I know the file is there and is more than zero length.

|||

Interesting approach....I may add something similar to my process.

Thanks for taking the time to provide an answer!

OLEDB Command giving error for decalre and set statements at the top of the SQL script

Hi All,

I have an OLEDB command in my package that has to execute some SQL script.

But when I declare and set a variable at the top of all code, The OLEDB gives an error in column mappings tab.

My DQL script is as shown below

DECLARE @.Cost AS money

SET @.Cost=?

--Some update statements a table

OLEDB Command works if write the declare and set statements after update statements. Like below. But I don’t need it.

--Some update statements a table

DECLARE @.Cost AS money

SET @.Cost=?

I also observer that,Oledb Command gives error for the code given below.

Just paste the following Script in OLEDB command, it gives error in column mapping tab

DECLARE @.Cost AS money

SET @.Cost=?

Any Idea on this behaviour?

Thanks in advance..

Is there a solution for this?
|||I don't think you can parameterize a SET statement like that. You should use a stored procedure.
|||

You can parameterize a SET statement provided you must have INSERT/UPDATE statements at the top.

for example :

INSET INTO table(col1)

values(?)

DECLARE @.VAR1 as int

Set @.VAR1=?

Works fine...

In my case ,I have only two queries,Do you think putting those in SP is a better option.

Ole DB type

Is there a reason why Ole DB is missing interger data type? I am using Execute Sql task with Ole DB Connection Manager and cannot find Interger type. Any ideas?Nevermind. I was unfamiliar with the Ole db types for SSIS. Long = integer.

OLE DB Destination problem - with keywords

Destination table has a column name - Partition that is a key word in the DB2 database. But database would execute the query fine if there is "" around the column name - "Partition". When an OLE DB Destination component is used, it throws an error saying column name is a reserved word and that it cannot be used. When put "" around the column name, it fails in the pre-execute phase thinking that "Partition" is the column name instead of Partition. Any ideas to fix this problem would be greatly appreciated?

What OLE driver are you using for DB2?|||

Phil,

I am using 'IBM OLE DB Provider for DB2'. Any suggestions on what I should do to fix the problem?

|||First, do square brakets work? [Partition]

Second, is your data flow column called Partition, or something else and you're mapping it to a column named Partition? What I'm getting at here, is you can alias the column in the data flow to be something other than "Partition." That might help.

Also, you could try using the Microsoft OLE for DB2 driver.