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 large database or a few smaller databases?

Does it make sense to break up data into a few smaller (identical)
databases to keep from having hundreds of millions of rows in a few
tables in one single database?
I ask because I've got a website that's pumping about 500k new records
into 3 or 4 tables per day. In several months I'll probably be near 400
or 500 million rows in some of them.
Users to the website query the data, and so far, it's working well
(with only a few million rows in each). My indexes look good and my
queries only join 2 or 3 tables at most, but I am mostly doing
aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
rows at a time.
My app is set up to handle the data being in separate identical
databases, but I'd like to keep it all in the same place to keep things
more simple (for backups & upgrades, etc..)
But my fear is when I start to reach half a billion rows my query times
are going to suck, even if I cram more memory into my database server
(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
Anyone have any experience with this much data?
Thanks!!
You should not split up your solution into more than one database. A
good approach would be to use the analysis services from Sql Server as
you are requireing a lot of aggregation data which could be
precalculated. Therefore these cubes will provide a much feaster
response than quering just an OLTP database.
HTH, Jens Suessmeyer.
|||Jens, thanks for the quick reply. I'm using SQL 2005 standard... Does
that I mean need to upgrade to Enterprise?
Forgive my lack of knowledge on AS and data cubes, but are they kept in
real-time? Ideally the queries would be doing in "near-time" to when
the data has been inserted.
Thanks for your help.
|||No, AS is already included in SQL Server 2k5, but has to be instaleld
if not.
You sure can design the cube that it can fallback on non aggregated
realtime data , but perhaps the Analysis Services newsgroup can help
you further Within this they should know was is possible in your case.
HTH, Jens Suessmeyer.
|||On 28 Jan 2006 11:29:29 -0800, Jeff Turner wrote:

>Does it make sense to break up data into a few smaller (identical)
>databases to keep from having hundreds of millions of rows in a few
>tables in one single database?
>I ask because I've got a website that's pumping about 500k new records
>into 3 or 4 tables per day. In several months I'll probably be near 400
>or 500 million rows in some of them.
>Users to the website query the data, and so far, it's working well
>(with only a few million rows in each). My indexes look good and my
>queries only join 2 or 3 tables at most, but I am mostly doing
>aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
>rows at a time.
>My app is set up to handle the data being in separate identical
>databases, but I'd like to keep it all in the same place to keep things
>more simple (for backups & upgrades, etc..)
>But my fear is when I start to reach half a billion rows my query times
>are going to suck, even if I cram more memory into my database server
>(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
>Anyone have any experience with this much data?
>Thanks!!
Hi Jeff,
I agree with Jens - keep it in one database.
In addition to Jens' suggestion to use analysis server, here are some
other ideas that might help you gain speed as the amount of data grows.
1. Use indexed views. If your queries are usually for aggregates, they
could prove to be an excellent way to speed up the process, since they
are precalculated. Because SQL Server has to modify each indexed view as
the base data changes, indexed views do cause a slowdown for data
modifications, though. Don't use indexed views if you need millisecond
performance on insert and update operations.
2. Consider the use of partitioned views (SQL Server 2000) or table
partitioning (SQL Server 2005).
Hugo Kornelis, SQL Server MVP
|||Hi Jeff,
Some extra thoughts:
1. The optimizer is going to do better work for you if it's in one database.
2. Backups/restores become much more complicated if you have to keep
multiple databases in sync. Consider what you might have to do to restore to
a point in time. There are methods but it just starts to complicate things.
HTH,
Greg
"Jeff Turner" <zigjst@.gmail.com> wrote in message
news:1138476569.362520.240210@.f14g2000cwb.googlegr oups.com...
> Does it make sense to break up data into a few smaller (identical)
> databases to keep from having hundreds of millions of rows in a few
> tables in one single database?
> I ask because I've got a website that's pumping about 500k new records
> into 3 or 4 tables per day. In several months I'll probably be near 400
> or 500 million rows in some of them.
> Users to the website query the data, and so far, it's working well
> (with only a few million rows in each). My indexes look good and my
> queries only join 2 or 3 tables at most, but I am mostly doing
> aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
> rows at a time.
> My app is set up to handle the data being in separate identical
> databases, but I'd like to keep it all in the same place to keep things
> more simple (for backups & upgrades, etc..)
> But my fear is when I start to reach half a billion rows my query times
> are going to suck, even if I cram more memory into my database server
> (dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
> Anyone have any experience with this much data?
> Thanks!!
>
|||Thanks to everyone for replying.
One thing I should point out is the data in these tables is broken down
by users to the website.
For example, if I did split this up by database, I could have one
database that holds all of the records for users with last names of
A-G. They would never have a need to query against the data in the
other databases.
So the practical minded side of me says, why bother running queries for
an A-G user in tables that hold everyone's data, when I could
physically have it narrowed down already.
Does that make sense? Or is SQL just smart & efficient enough with
indexing that the performance gain would be negligable compared to my
backup/maintenance costs.
Thanks again!

One large database or a few smaller databases?

Does it make sense to break up data into a few smaller (identical)
databases to keep from having hundreds of millions of rows in a few
tables in one single database?
I ask because I've got a website that's pumping about 500k new records
into 3 or 4 tables per day. In several months I'll probably be near 400
or 500 million rows in some of them.
Users to the website query the data, and so far, it's working well
(with only a few million rows in each). My indexes look good and my
queries only join 2 or 3 tables at most, but I am mostly doing
aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
rows at a time.
My app is set up to handle the data being in separate identical
databases, but I'd like to keep it all in the same place to keep things
more simple (for backups & upgrades, etc..)
But my fear is when I start to reach half a billion rows my query times
are going to suck, even if I cram more memory into my database server
(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
Anyone have any experience with this much data?
Thanks!!You should not split up your solution into more than one database. A
good approach would be to use the analysis services from Sql Server as
you are requireing a lot of aggregation data which could be
precalculated. Therefore these cubes will provide a much feaster
response than quering just an OLTP database.
HTH, Jens Suessmeyer.|||Jens, thanks for the quick reply. I'm using SQL 2005 standard... Does
that I mean need to upgrade to Enterprise?
Forgive my lack of knowledge on AS and data cubes, but are they kept in
real-time? Ideally the queries would be doing in "near-time" to when
the data has been inserted.
Thanks for your help.|||No, AS is already included in SQL Server 2k5, but has to be instaleld
if not.
You sure can design the cube that it can fallback on non aggregated
realtime data , but perhaps the Analysis Services newsgroup can help
you further Within this they should know was is possible in your case.
HTH, Jens Suessmeyer.|||On 28 Jan 2006 11:29:29 -0800, Jeff Turner wrote:

>Does it make sense to break up data into a few smaller (identical)
>databases to keep from having hundreds of millions of rows in a few
>tables in one single database?
>I ask because I've got a website that's pumping about 500k new records
>into 3 or 4 tables per day. In several months I'll probably be near 400
>or 500 million rows in some of them.
>Users to the website query the data, and so far, it's working well
>(with only a few million rows in each). My indexes look good and my
>queries only join 2 or 3 tables at most, but I am mostly doing
>aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
>rows at a time.
>My app is set up to handle the data being in separate identical
>databases, but I'd like to keep it all in the same place to keep things
>more simple (for backups & upgrades, etc..)
>But my fear is when I start to reach half a billion rows my query times
>are going to suck, even if I cram more memory into my database server
>(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
>Anyone have any experience with this much data?
>Thanks!!
Hi Jeff,
I agree with Jens - keep it in one database.
In addition to Jens' suggestion to use analysis server, here are some
other ideas that might help you gain speed as the amount of data grows.
1. Use indexed views. If your queries are usually for aggregates, they
could prove to be an excellent way to speed up the process, since they
are precalculated. Because SQL Server has to modify each indexed view as
the base data changes, indexed views do cause a slowdown for data
modifications, though. Don't use indexed views if you need millisecond
performance on insert and update operations.
2. Consider the use of partitioned views (SQL Server 2000) or table
partitioning (SQL Server 2005).
Hugo Kornelis, SQL Server MVP|||Hi Jeff,
Some extra thoughts:
1. The optimizer is going to do better work for you if it's in one database.
2. Backups/restores become much more complicated if you have to keep
multiple databases in sync. Consider what you might have to do to restore to
a point in time. There are methods but it just starts to complicate things.
HTH,
Greg
"Jeff Turner" <zigjst@.gmail.com> wrote in message
news:1138476569.362520.240210@.f14g2000cwb.googlegroups.com...
> Does it make sense to break up data into a few smaller (identical)
> databases to keep from having hundreds of millions of rows in a few
> tables in one single database?
> I ask because I've got a website that's pumping about 500k new records
> into 3 or 4 tables per day. In several months I'll probably be near 400
> or 500 million rows in some of them.
> Users to the website query the data, and so far, it's working well
> (with only a few million rows in each). My indexes look good and my
> queries only join 2 or 3 tables at most, but I am mostly doing
> aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
> rows at a time.
> My app is set up to handle the data being in separate identical
> databases, but I'd like to keep it all in the same place to keep things
> more simple (for backups & upgrades, etc..)
> But my fear is when I start to reach half a billion rows my query times
> are going to suck, even if I cram more memory into my database server
> (dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
> Anyone have any experience with this much data?
> Thanks!!
>|||Thanks to everyone for replying.
One thing I should point out is the data in these tables is broken down
by users to the website.
For example, if I did split this up by database, I could have one
database that holds all of the records for users with last names of
A-G. They would never have a need to query against the data in the
other databases.
So the practical minded side of me says, why bother running queries for
an A-G user in tables that hold everyone's data, when I could
physically have it narrowed down already.
Does that make sense? Or is SQL just smart & efficient enough with
indexing that the performance gain would be negligable compared to my
backup/maintenance costs.
Thanks again!

One large database or a few smaller databases?

Does it make sense to break up data into a few smaller (identical)
databases to keep from having hundreds of millions of rows in a few
tables in one single database?
I ask because I've got a website that's pumping about 500k new records
into 3 or 4 tables per day. In several months I'll probably be near 400
or 500 million rows in some of them.
Users to the website query the data, and so far, it's working well
(with only a few million rows in each). My indexes look good and my
queries only join 2 or 3 tables at most, but I am mostly doing
aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
rows at a time.
My app is set up to handle the data being in separate identical
databases, but I'd like to keep it all in the same place to keep things
more simple (for backups & upgrades, etc..)
But my fear is when I start to reach half a billion rows my query times
are going to suck, even if I cram more memory into my database server
(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
Anyone have any experience with this much data?
Thanks!!You should not split up your solution into more than one database. A
good approach would be to use the analysis services from Sql Server as
you are requireing a lot of aggregation data which could be
precalculated. Therefore these cubes will provide a much feaster
response than quering just an OLTP database.
HTH, Jens Suessmeyer.|||Jens, thanks for the quick reply. I'm using SQL 2005 standard... Does
that I mean need to upgrade to Enterprise?
Forgive my lack of knowledge on AS and data cubes, but are they kept in
real-time? Ideally the queries would be doing in "near-time" to when
the data has been inserted.
Thanks for your help.|||No, AS is already included in SQL Server 2k5, but has to be instaleld
if not.
You sure can design the cube that it can fallback on non aggregated
realtime data , but perhaps the Analysis Services newsgroup can help
you further Within this they should know was is possible in your case.
HTH, Jens Suessmeyer.|||On 28 Jan 2006 11:29:29 -0800, Jeff Turner wrote:
>Does it make sense to break up data into a few smaller (identical)
>databases to keep from having hundreds of millions of rows in a few
>tables in one single database?
>I ask because I've got a website that's pumping about 500k new records
>into 3 or 4 tables per day. In several months I'll probably be near 400
>or 500 million rows in some of them.
>Users to the website query the data, and so far, it's working well
>(with only a few million rows in each). My indexes look good and my
>queries only join 2 or 3 tables at most, but I am mostly doing
>aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
>rows at a time.
>My app is set up to handle the data being in separate identical
>databases, but I'd like to keep it all in the same place to keep things
>more simple (for backups & upgrades, etc..)
>But my fear is when I start to reach half a billion rows my query times
>are going to suck, even if I cram more memory into my database server
>(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
>Anyone have any experience with this much data?
>Thanks!!
Hi Jeff,
I agree with Jens - keep it in one database.
In addition to Jens' suggestion to use analysis server, here are some
other ideas that might help you gain speed as the amount of data grows.
1. Use indexed views. If your queries are usually for aggregates, they
could prove to be an excellent way to speed up the process, since they
are precalculated. Because SQL Server has to modify each indexed view as
the base data changes, indexed views do cause a slowdown for data
modifications, though. Don't use indexed views if you need millisecond
performance on insert and update operations.
2. Consider the use of partitioned views (SQL Server 2000) or table
partitioning (SQL Server 2005).
--
Hugo Kornelis, SQL Server MVP|||Hi Jeff,
Some extra thoughts:
1. The optimizer is going to do better work for you if it's in one database.
2. Backups/restores become much more complicated if you have to keep
multiple databases in sync. Consider what you might have to do to restore to
a point in time. There are methods but it just starts to complicate things.
HTH,
Greg
"Jeff Turner" <zigjst@.gmail.com> wrote in message
news:1138476569.362520.240210@.f14g2000cwb.googlegroups.com...
> Does it make sense to break up data into a few smaller (identical)
> databases to keep from having hundreds of millions of rows in a few
> tables in one single database?
> I ask because I've got a website that's pumping about 500k new records
> into 3 or 4 tables per day. In several months I'll probably be near 400
> or 500 million rows in some of them.
> Users to the website query the data, and so far, it's working well
> (with only a few million rows in each). My indexes look good and my
> queries only join 2 or 3 tables at most, but I am mostly doing
> aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
> rows at a time.
> My app is set up to handle the data being in separate identical
> databases, but I'd like to keep it all in the same place to keep things
> more simple (for backups & upgrades, etc..)
> But my fear is when I start to reach half a billion rows my query times
> are going to suck, even if I cram more memory into my database server
> (dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
> Anyone have any experience with this much data?
> Thanks!!
>|||Thanks to everyone for replying.
One thing I should point out is the data in these tables is broken down
by users to the website.
For example, if I did split this up by database, I could have one
database that holds all of the records for users with last names of
A-G. They would never have a need to query against the data in the
other databases.
So the practical minded side of me says, why bother running queries for
an A-G user in tables that hold everyone's data, when I could
physically have it narrowed down already.
Does that make sense? Or is SQL just smart & efficient enough with
indexing that the performance gain would be negligable compared to my
backup/maintenance costs.
Thanks again!

One for the SQL Gurus: Split a delimited field into rows

Hi.

I'm trying to write an SQL Query that will take a delimited field and return each item as a row.

Example

Take the AuthorizedRoles and TabID fields from the Tabs table

AuthorizedRoles TabID
0;11;__________1
0; 15 ;17;______6
-2;____________7

I would like to return a unique record for each Authorized Role

AuthorizedRole TabID
0____________1
11___________1
0____________6
15___________6
17___________6
-2___________7

Any ideas?

Cheers
Davecheck if thisarticle helps

hth|||Thanks Dinakar. I was hoping to do this entirely within the SELECT statement but realise that's probably impossible without built in SQL commands like split etc..

This example looks like a good place to start. All other examples I've seen require temp tables and Lookup tables.

Cheers
Dave

One fact table/cube or multiple fact tables/cube

Hi,

I am now starting on my 2nd analysis server project and I have 8 dimensions and 6 fact tables. One fact table has 90 million rows, the other one has 30 million rows. The other four are less then 1 million rows big.

Should I create one cube with 6 fact tables in it or 6 cubes with one fact table ?

The advantage of the first one is that if you need to make a report you can have all the data in one query which is great for the users.

The advantage of the last one is that if you develop, you can easily calculate and test a small cube.

I am also thinking to get the best of both worlds namely going for the last option and a a 7th cube which has links to all the other six cubes.

Any suggestions ?

Constantijn Enders

This will have thoughts or considerations to address most of your questions.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1358193&SiteID=1

I would love to hear other people's thoughts, too.

|||

And here's a separate discussion on the same topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1402506&SiteID=1

|||

Hi,

I finally ended up at this blog http://prologika.com/CS/blogs/blog/archive/2006/06/27/1331.aspx

This confirmed my final thought, split into smaller and then one cube to rule them all

CE

one column causing duplicate rows - wrong join used?

Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Na
me
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Na
me
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qhChange the LEFT JOIN to a JOIN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1133522905.310939.208490@.g49g2000cwa.googlegroups.com...
Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qh|||Follow-up: If that doesn't fix it, could you please post your DDL for the
two tables + INSERT's of the sample data? We may have to change your query
further.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uTb$rXz9FHA.916@.TK2MSFTNGP10.phx.gbl...
Change the LEFT JOIN to a JOIN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1133522905.310939.208490@.g49g2000cwa.googlegroups.com...
Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qh|||Hi Tom,
thanks for both replies. Unfortunately using JOIN did not work. when
I type the following
select * from tblPNProperties
where pnid = '28'
I get 1 row
select * from tblBusinessboard
where pnid = '28'
however here I get two rows
I have a hunch that the data is incorrect.
Back to the drawing board
cheers
qh|||On 2 Dec 2005 03:28:25 -0800, Quackhandle wrote:

>Hi,
>Consider the following result set:
> PNID PN_NUMBER Date1 Date2 Status PN_Na
me
> ========================================
========================
>27 2051 08 Sep 1941 NULL Received NULL
>28 2143 01 Jan 1945 NULL Accepted NULL
>28 2143 01 Jan 1945 NULL Accepted R Anderson
>29 2151 NULL NULL Accepted NULL
>29 2151 NULL NULL Accepted W Yarwood
>30 1579 17 Nov 1925 NULL Received NULL
>31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
>
>Here is the sql that returns the above:
>SELECT
> DISTINCT(PNP.PNID) AS 'PN_ID',
> PNP.PNNumber AS 'PN_NUMBER',
> CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
> CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
> PNP.PNStatus AS 'Status',
> BB.Name AS 'PN_NAME',
>FROM
> tblPNProperties PNP
> LEFT JOIN tblBusinessBoard BB
> ON PNP.PNID = BB.PNID
>My desired resultset would be to have pnids 28 and 29 to be unique,
>however because I am
>selecting PN_Name it causes the rows to have duplicates. How would I be
>able to obtain my desired resultset? (see below) Is my join correct?
>
> PNID PN_NUMBER Date1 Date2 Status PN_Na
me
> ========================================
========================
>27 2051 08 Sep 1941 NULL Received NULL
>28 2143 01 Jan 1945 NULL Accepted R Anderson
>29 2151 NULL NULL Accepted W Yarwood
>30 1579 17 Nov 1925 NULL Received NULL
>31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
>
>Any ideas?
>
>Thanks
>qh
Hi qh,
Since you didn't post CREATE TABLE and INSERT statements, here's a wild
and completely untested guess:
SELECT
PNP.PNID AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
MAX(BB.Name) AS 'PN_NAME'
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
GROUP BY
PNP.PNID,
PNP.PNNumber,
PNP.PNDate,
PNP.InspectionDate,
PNP.PNStatus
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

one line for 3 rows

for MS SQL 2000
I am having :
Table1
ID | N1 | N2 | N3 |
------
12 | A | | |
12 | | B| |
12 | | | C|
how can I get :
12,A,B,C ?
thank you for helpingselect id, max(n1), max(n2), max(n3) from Table1 group by id|||Thank you roac|||Just to ask a quick question : why is your table laid out like that?
I'm just curious to know if this can be related to a real world example.|||we are getting datas in this way from another application and we cannot change it
I am taking the datas to put them in another table to read them normally for our application|||what happens when you get more than 1 value in any given column?|||In what format is this other application giving you data? Is the application writing directly to your database?

If you are handling the data before it gets entered into your database then I would seriously reconsider a redesign of the table format and of the way you are inserting data. There is no normalisation here, which is half the point of a database structure.

I don't see why the data cannot just be written into one row:
i.e.

ID| N1|N2|N3
------
12 | A | B | C

Do you have a more meaningful example of the data you are using? What datatypes are you utilising?

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

ole db transformation - redirect rows problem

Hi, i have a OLE DB transformation that executes a stored procedure. The stored procedure RAISERROR when an error is caught. I have set the OLE DB transformation to redirect rows. But instead, the task turns red and fails. Did i do something wrong here ?Did you set the error output to redirect rows, or is it still set to the default of failing the component?|||Of course, if the source stored proc is failing, what rows will you be diverting?|||

ah... i compared Lookup transform and OLE DB transform. For lookup transform, you can control "ErrorRowDisposition" for both Command Input and Command Output....

But for OLE DB Transform, you can control "ErrorRowDisposition" for only input, not output !!

So i guess I cannot RAISERROR in the stored proc and let OLE DB transform separate the "bad" records from the "good" records.

Perhaps I need to modify the stored proc to catch all errors - and return an errorCode parameter as output ? And then check this output parameter to see what records are not processed correctly ?

please advice !

OLE DB error

I have a stored procedure that consists 4 set of statements (delete rows and
then insert rows from excel files with openrowset). It returns error when
execute the 4th insert statement. The error message is:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file ''. It is already opened exclusively by another user, or you
need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Its strange, I think, that there is no file in the error message... BTW,
the stored procedure can be executed successfully if any one insert
statement is commented. Does openrowset or MS Jet database engine has any
limitation? Could anyone please tell me how can I solve this issue?
Any help would be appreciated.
P.S. I'm running SQL Server 2000
SChi Squirrel,
Could you please be so kind to post the aforementioned stored procedure here
?
"Squirrel" wrote:

> I have a stored procedure that consists 4 set of statements (delete rows a
nd
> then insert rows from excel files with openrowset). It returns error when
> execute the 4th insert statement. The error message is:
>
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine canno
t
> open the file ''. It is already opened exclusively by another user, or yo
u
> need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
>
> It?|s strange, I think, that there is no file in the error message... B
TW,
> the stored procedure can be executed successfully if any one insert
> statement is commented. Does openrowset or MS Jet database engine has any
> limitation? Could anyone please tell me how can I solve this issue?
>
> Any help would be appreciated.
>
> P.S. I'm running SQL Server 2000
>
> SC
>
>|||here. thanks.
CREATE PROCEDURE convert_data
@.userid varchar(8)
as
BEGIN TRANSACTION UpdateAll
DELETE table1
INSERT INTO table1
SELECT id, name, cat, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table1.xls', 'select * from [sheet1$]')
DELETE table2
INSERT INTO table2
SELECT id, serial, add_1, add_2, add_3, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table2.xls', 'select * from [sheet1$]')
DELETE table3
INSERT INTO table3
SELECT table1_id, table2_id, serial, type, amount, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table3.xls', 'select * from [sheet1$]')
DELETE table4
INSERT INTO table4
SELECT code, num, description, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table4.xls', 'select * from [sheet1$]')
COMMIT TRANSACTION UpdateAll
GO
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:EC55EB71-31E0-4A15-8BAA-5A04C45E0640@.microsoft.com...
> hi Squirrel,
> Could you please be so kind to post the aforementioned stored procedure
> here?
> "Squirrel" wrote:
>

OLE DB Destination

I am proceesing a file and using a OLE Db destination for inserting the rows present into a file to the table.

As of now OLE db destination points to the table and having the fileds mapping of input column and destination column.

now i want to perform some calculation and want insert the rows into table based on the value of some other column for ex.

if (column2 == 1)

{

column1 = column4 - column5

}

if (column3== 1)

{

column1 = column4 - Column6

}

Please let me know how to do this.

Use a Derived Column transformation.

Select "Replace Column 1" as the column to work on in the lower grid, then use the conditional operator.

To just one conditional -

column2 == 1 ? column4 - column 5 : coumn1

if column2 equals 1 then result is column4 minus column5, else use current column1 value.

To do the two if clauses -

column2 == 1 ? column4 - column 5 : column3 = 1 ? column4 - column 6 : column1

|||

I have the same problem

Totally,Thanks

|||

I want to implement all this four condition in one derived column expression.

if (Column1== 1)

{

OutputColumn = ColumnA- Column1RATE
}

if (Column2 == 1)

{

OUtputColumn = ColumnA- Column2RATE
}

if (Column3 == 1)

{

OUtputColumn = ColumnA- Column3RATE
}

if (Column4 == 1)

{

OUtputColumn = ColumnA- Column4RATE
}

please suggest

OLE DB Command and Destination writing to the same table

Hi,

I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.

The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.

Could this be due to a table-locking issue? Any suggestions?

Thanks
ray

Just to confirm you haven't set the table lock check box on the oledb destination?

|||

You might look at the slowly changing dimension task... I'm not that familiar witrh it.

A quick and easy solution would send one of the outputs, probably the updates to a raw file destination. Then read that raw file in a different data flow task and perform the update there.

Doing a lot of updates in a sqlCommand - row by row - can be time consuming and I usually try to find another way to do it using some kind of set based operation. For example, if I can identify unique properties of from the source data, a date range or a specific field value; I would use that to construct a sqlCommand that would update all of the records at one time.

|||

Here is 2 links to pages that talk about different techniques for doing an "upsert". This is the

first time I have heard that term. I like it!

This is my web site:
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

This is Jamie Thompson's cool

blog:
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks,

Greg Van

Mullem

|||

rlee1003 wrote:

Hi,

I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.

The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.

Could this be due to a table-locking issue? Any suggestions?

Thanks
ray

Yes, this is absolutely a locking (actually a blocking) issue. Execute sp_lock or sp_who2 to confirm it.

You can get around this problem by executing the insert and update operations in different data-flows. Use a raw file to pass data from one data-flow to another. This technique is covered (albeit for use in a differrent context) here:

Splitting order detail and order header information from one file into multiple tables
(http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx)

-Jamie

|||Oho, I get the question now. My previous reply was useful but a little off topic. Are you use the "Fast Load" option in your OLE DB Destination? Turning this off might help.

You could also uses 2 "OLE DB Command" components (running insert and update commands). That's what I'm doing so I know it works quite well.

Thanks,
Greg

Old, deleted Identity values re-used?

If I have an Identity-declared column, the table has lots of rows and I start deleteting records - will any old, deleted identities be re-used (without an explicit re-seed) ?
(I'm talking about numerical Identities, not these GUID things)No. Not without manipulating the IDENTITY column. The exception being that if you TRUNCATE the table, then the seed will reset to the floor value.

Regards,

hmscott|||yeah,it can be used
need to set identity off
insert statement
reset identity on