Merge Join - HELP

Hi,

I have a SQL Statatment:

SELECT * FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X
AND A.Y= B.Y

When i execute this code in sql server returns 549 lines. I created a package with two oledb sources one for each table, sorted the tables with fields X and Y after placed a Merge Join with the fields:

A.Y join B.Y order 1

A.X join B.X order 2

both fields with the Join Key checked

But my package return 411 lines.

What's happened? :(

When a i have the code:

SELECT A.X, A.Y, B.X, B.Y
FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X

When i did the join only one field SSIS worked fine, sql server returns 622 and SSIS returns 622 lines.

Please help-me...

Thanks,

Andr

Are your join keys all integers or does one of them have characters. SSIS is case senstive while SQL Server by default isn't. You may want to check this.|||

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

|||

Thanks Jay,

My columns are characters type and my oledb source 1 columns is lowercase and oledb source 2 columns is uppercase.

I created a Derived Column replaced my column to uppercase. :)

|||Character Map Transform might be more appropriate to change case|||

Rafael Salas wrote:

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

Very good point. And note that setting IsSorted=1 on an output does NOT sort the data.

-Jamie

Merge Join - HELP

Hi,

I have a SQL Statatment:

SELECT * FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X
AND A.Y= B.Y

When i execute this code in sql server returns 549 lines. I created a package with two oledb sources one for each table, sorted the tables with fields X and Y after placed a Merge Join with the fields:

A.Y join B.Y order 1

A.X join B.X order 2

both fields with the Join Key checked

But my package return 411 lines.

What's happened? :(

When a i have the code:

SELECT A.X, A.Y, B.X, B.Y
FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X

When i did the join only one field SSIS worked fine, sql server returns 622 and SSIS returns 622 lines.

Please help-me...

Thanks,

Andr

Are your join keys all integers or does one of them have characters. SSIS is case senstive while SQL Server by default isn't. You may want to check this.|||

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

|||

Thanks Jay,

My columns are characters type and my oledb source 1 columns is lowercase and oledb source 2 columns is uppercase.

I created a Derived Column replaced my column to uppercase. :)

|||Character Map Transform might be more appropriate to change case|||

Rafael Salas wrote:

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

Very good point. And note that setting IsSorted=1 on an output does NOT sort the data.

-Jamie

Merge Join

Hi,
I executed the following query in Northwind:
select c.* from customers c
join orders o on c.customerid=o.customerid
There are original indexes available on these two tables:
1) Clustered (Customers.CustomerID)
2) Clustered (Orders.OrderID)
3) nonClustered (Orders.CustomerID)
The execution plan shows that a nested loop is used to return the result.
But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
Now both tables are sorted based on the join predicate. But still nested
loop happens. I think Merge Join can be more efficient. Why nested loop
still is preferred by query optimizer?
Thanks in advance,
LeilaIt depends on how many rows there are. These are pretty small tables, so it
probably sees that it can return the data really fast and returns it. Just
a guess, but they don't keep looking for a plan when optimization would take
more time than running the query like the first plan they see.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:%23CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||We have found in our software that nested loop joins have far better
performance. In fact, when we get parallelism queries back, we sometimes
will scale up the parallelism cost to 25 from 5 because the query optimizer
"goes stupid" at times.
=-Chris
"Leila" <leilas@.hotpop.com> wrote in message
news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I executed the following query in Northwind:
> select c.* from customers c
> join orders o on c.customerid=o.customerid
> There are original indexes available on these two tables:
> 1) Clustered (Customers.CustomerID)
> 2) Clustered (Orders.OrderID)
> 3) nonClustered (Orders.CustomerID)
> The execution plan shows that a nested loop is used to return the result.
> But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> Now both tables are sorted based on the join predicate. But still nested
> loop happens. I think Merge Join can be more efficient. Why nested loop
> still is preferred by query optimizer?
> Thanks in advance,
> Leila
>|||If two tables are joined, and both tables have a clustered index on the
join key, the a MERGE JOIN is the most efficient join strategy (on
non-SMP systems).
I guess you were actually talking about LOOP JOINs versus HASH JOINs.
Gert-Jan
Christopher Conner wrote:
> We have found in our software that nested loop joins have far better
> performance. In fact, when we get parallelism queries back, we sometimes
> will scale up the parallelism cost to 25 from 5 because the query optimizer
> "goes stupid" at times.
> =-Chris
> "Leila" <leilas@.hotpop.com> wrote in message
> news:#CPJ4NhtEHA.904@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > I executed the following query in Northwind:
> >
> > select c.* from customers c
> > join orders o on c.customerid=o.customerid
> >
> > There are original indexes available on these two tables:
> >
> > 1) Clustered (Customers.CustomerID)
> > 2) Clustered (Orders.OrderID)
> > 3) nonClustered (Orders.CustomerID)
> >
> > The execution plan shows that a nested loop is used to return the result.
> > But I changed the index No 2 to nonCluestered and index No 3 to Clustered.
> > Now both tables are sorted based on the join predicate. But still nested
> > loop happens. I think Merge Join can be more efficient. Why nested loop
> > still is preferred by query optimizer?
> > Thanks in advance,
> > Leila
> >
> >sql

merge input output selection dialog is hanging

Hello

I'm trying to use the Merge component. When i attach a datasource to the the component, the Select Input/Output dialog box should popup.. It does, but VS.NET is hanging and i can only shutdown the procesess...

Any idea how i should solve this? how can i re-register this component?

ps. sql 2005 sp1 is installed.

Thanks
Marco

I am experiencing the same problem.

I have SQL Server 2005 SP1 and Visual Studio 2005 SP1.

I thought I had worked around this in the past by adding the components to the designer in a different order, but that does not seem to make a difference any longer.

|||

If this is something that happened very randomly and could be worked around by just closing/killing the project and open it again, then it's a known issue, the fix will be in SP2. However if you bumped into a constant repro of this, then likely it is something new, in that case please file a bug at our beta place with a brief description of that you got, we will get back to you soon.

Thanks

wenyang

|||

Beta Place closed some time ago.

You can of course raise a PSS support case for full paid support, or just log the bug/request online at MS Connect, effectively the replacement for Beta Place - http://connect.microsoft.com

merge input output selection dialog is hanging

Hello

I'm trying to use the Merge component. When i attach a datasource to the the component, the Select Input/Output dialog box should popup.. It does, but VS.NET is hanging and i can only shutdown the procesess...

Any idea how i should solve this? how can i re-register this component?

ps. sql 2005 sp1 is installed.

Thanks
Marco

I am experiencing the same problem.

I have SQL Server 2005 SP1 and Visual Studio 2005 SP1.

I thought I had worked around this in the past by adding the components to the designer in a different order, but that does not seem to make a difference any longer.

|||

If this is something that happened very randomly and could be worked around by just closing/killing the project and open it again, then it's a known issue, the fix will be in SP2. However if you bumped into a constant repro of this, then likely it is something new, in that case please file a bug at our beta place with a brief description of that you got, we will get back to you soon.

Thanks

wenyang

|||

Beta Place closed some time ago.

You can of course raise a PSS support case for full paid support, or just log the bug/request online at MS Connect, effectively the replacement for Beta Place - http://connect.microsoft.com

Merge Cells Vertically

Any way to merge cells vertically in SSRS 2005? I know w can do it horizontally by right clik, then select "merge cells". How about vertically? Thanks.

Why do you want to do that?|||

Because I have a "comment" field which has long text.

sql

Merge apparent duplcate rows into 1 row?

Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
ThanksUse a SELECT DISTINCT:
SELECT DISTINCT
tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125055041.994620.74900@.g14g2000cwa.googlegroups.com...
Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
Thanks|||Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!|||If you're new to SQL, this is the place to hang out. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125058223.511501.191000@.o13g2000cwo.googlegroups.com...
Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!

Mental Block

Can some please help with this SQL problem:
I have tables in select query and i want to show all
dbo.TblTerritory.Description even is there is no data in the other columns,
ie there are no records of type 'lost' in ProjectStatus
SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int,
CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats INNER JOIN
dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
dbo.tblCalendar_jpM RIGHT OUTER JOIN
dbo.TblTerritory ON
dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
(dbo.jp_tblproject_stats.ProjectStatus = 'lost')
GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
pleae help
Regards
John"John" <topguy75@.hotmail.com> wrote in message
news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other
columns,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname,
CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON
dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int,
CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
John,
-- The query realigned and aliased for readability:
SELECT TOP 100 PERCENT
T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr)) AS dtsort
FROM dbo.jp_tblproject_stats AS PS1
INNER JOIN
dbo.tblCalendar_jp AS C1
-- Note right here there is no column name
-- On the right-hand side of the = operator,
-- just the table alias.
ON PS1.Mnth = C1
RIGHT OUTER JOIN
dbo.TblTerritory AS T1
ON PS1.Territory_Code = T1.Code
WHERE (T1.Manager = 'clive wallom')
AND (PS1.ProjectStatus = 'lost')
GROUP BY T1.Description
,PS1.Counter
,C1.monthname
,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
ORDER BY T1.Description
,CONVERT(int, CONVERT(varchar, PS1.Mnth)
+ CONVERT(varchar, PS1.Yr))
The above originally appeared as:
FROM dbo.jp_tblproject_stats
INNER JOIN
dbo.tblCalendar_jp
ON dbo.jp_tblproject_stats.Mnth
= dbo.tblCalendar_jpM
-- .<some column name goes here>
-- Probably should be .Mnth
Sincerely,
Chris O.|||John:
When there is a requirement of having all row returned for a particular
column no matter if it has records in other tables you are joining to, one
should be very careful in putting in the where clause.
When your where clause is not properly designed, it will filter out the rows
having null value in that column. You can modify your where clause to let it
include null values or you can put these condition while you join two tables
.
(the way Chris suggested you)
Putting this in ON conditions while you are joining two table at times might
become quite complex.
An easier way would be to modify the where clause to let it include the null
s
say for example:
instead of ( dbo.TblTerritory.Manager = 'clive wallom' )
you cna put ( dbo.TblTerritory.Manager = 'clive wallom'
or dbo.TblTerritory.Manager is null)
Either of the approach will give you expected result and will perform equall
y.
Abhishek
"John" wrote:

> Can some please help with this SQL problem:
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other columns
,
> ie there are no records of type 'lost' in ProjectStatus
> SELECT TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int
,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats INNER JOIN
> dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> pleae help
> Regards
> John
>
>|||Thanks for your assistance, suprising what a bit of organising acheives. it
was '.m'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:vaKdnYt0T5kyIu7eRVn-sg@.comcast.com...
> "John" <topguy75@.hotmail.com> wrote in message
> news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> columns,
> CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth =
> dbo.jp_tblproject_stats.Counter,
> CONVERT(varchar,
> CONVERT(varchar,
> John,
> -- The query realigned and aliased for readability:
> SELECT TOP 100 PERCENT
> T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr)) AS dtsort
> FROM dbo.jp_tblproject_stats AS PS1
> INNER JOIN
> dbo.tblCalendar_jp AS C1
> -- Note right here there is no column name
> -- On the right-hand side of the = operator,
> -- just the table alias.
> ON PS1.Mnth = C1
> RIGHT OUTER JOIN
> dbo.TblTerritory AS T1
> ON PS1.Territory_Code = T1.Code
> WHERE (T1.Manager = 'clive wallom')
> AND (PS1.ProjectStatus = 'lost')
> GROUP BY T1.Description
> ,PS1.Counter
> ,C1.monthname
> ,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> ORDER BY T1.Description
> ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
> + CONVERT(varchar, PS1.Yr))
> The above originally appeared as:
> FROM dbo.jp_tblproject_stats
> INNER JOIN
> dbo.tblCalendar_jp
> ON dbo.jp_tblproject_stats.Mnth
> = dbo.tblCalendar_jpM
> -- .<some column name goes here>
> -- Probably should be .Mnth
> Sincerely,
> Chris O.
>