Joins Performance Problem

Hello,
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation â'
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
-----
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> -----
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in message
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>> Hello,
>> It is taking too long to run the following query:
>> Note: I have indexes on all of the columns in conditions. My temp db
>> size
>> is 18 GB. Pds_txn table size is 165 GB.
>> The execution plan showing:
>> Table pool/easer spool operation -
>> Row Count: 9 M
>> Disk i/o: 11k
>> Row size: 1089
>> Estimated cost: 11 k (57%)
>> CPU cost: 3.3
>> Sub tree cost: 20 K
>> Any help/hint will be appreciated.
>> Thanks,
>> Alim
>> -----
>>
>> FROM
>> dbo.pds_txn T1
>> INNER JOIN
>> dbo.GROUPS T2 ON
>> T1.GROUP_ID = T2.ID_200
>> INNER JOIN
>> dbo.DIVISIONS T3 ON
>> T1.DIVISION = T3.ID_102
>> INNER JOIN
>> dbo.BILLING_AREAS T4 ON
>> T1.BILLING_AREA = T4.ID_202
>> INNER JOIN
>> dbo.PROVIDERS T6 ON
>> T1.PROVIDER = T6.ID_3
>> INNER JOIN
>> dbo.LOCATIONS T7 ON
>> T1.LOCATION = T7.ID_100
>> INNER JOIN
>> dbo.PROCEDURES T8 ON
>> T1.[PROCEDURE] = T8.ID_1
>> INNER JOIN
>> dbo.FSC T9 ON
>> T1.ORIG_FSC = T9.ID_19
>> INNER JOIN
>> dbo.DIAGNOSIS T10 ON
>> T1.TXN_DX_1 = T10.ID_36
>> INNER JOIN
>> dbo.pds_invoice T11 ON
>> T1.INVOICE_NUM = T11.INVOICE_NUM AND
>> T1.GROUP_ID = T11.GROUP_ID
>> LEFT OUTER JOIN
>> dbo.PROVIDERS T6A ON
>> T11.PERFORMING_PHYS = T6A.ID_3
>> WHERE
>> T1.POSTING_PD_DTE >= '05/01/2003' AND
>> T1.PAY_CODE = 21 AND
>> T2.EXCLUSION_FLAG = 0 AND
>> T3.DIV_NUM <> '2901'
>>
>

Joining 2 or more data sources.

I know in crystal I can join 2 different data sources with in the report . Is
this possible in RS ?
(note: by different data sources I mean 2 completely different machines) I
have he columns I can join on just cant find a way to join them . One way was
to bring both parts of the data to a consolidated location. But this is not
the case for some reports .
Any help would be great.
ThanksThe only way to do this is to either do this in a stored procedure or to use
subreports. You can not do a local join between two datasets.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"C.M" <CM@.discussions.microsoft.com> wrote in message
news:9797B692-086F-4325-88B6-5AD0ED038627@.microsoft.com...
>I know in crystal I can join 2 different data sources with in the report .
>Is
> this possible in RS ?
>
> (note: by different data sources I mean 2 completely different machines) I
> have he columns I can join on just cant find a way to join them . One way
> was
> to bring both parts of the data to a consolidated location. But this is
> not
> the case for some reports .
> Any help would be great.
> Thanks
>|||Do you know if they are going to allow users to join datasets in RS 2005?
"Bruce L-C [MVP]" wrote:
> The only way to do this is to either do this in a stored procedure or to use
> subreports. You can not do a local join between two datasets.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "C.M" <CM@.discussions.microsoft.com> wrote in message
> news:9797B692-086F-4325-88B6-5AD0ED038627@.microsoft.com...
> >I know in crystal I can join 2 different data sources with in the report .
> >Is
> > this possible in RS ?
> >
> >
> > (note: by different data sources I mean 2 completely different machines) I
> > have he columns I can join on just cant find a way to join them . One way
> > was
> > to bring both parts of the data to a consolidated location. But this is
> > not
> > the case for some reports .
> >
> > Any help would be great.
> >
> > Thanks
> >
>
>|||Not that I have heard.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Clark Kent" <Clark Kent@.discussions.microsoft.com> wrote in message
news:A0BC297B-0DB8-4721-9989-49EF918B7CF5@.microsoft.com...
> Do you know if they are going to allow users to join datasets in RS 2005?
> "Bruce L-C [MVP]" wrote:
>> The only way to do this is to either do this in a stored procedure or to
>> use
>> subreports. You can not do a local join between two datasets.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "C.M" <CM@.discussions.microsoft.com> wrote in message
>> news:9797B692-086F-4325-88B6-5AD0ED038627@.microsoft.com...
>> >I know in crystal I can join 2 different data sources with in the report
>> >.
>> >Is
>> > this possible in RS ?
>> >
>> >
>> > (note: by different data sources I mean 2 completely different
>> > machines) I
>> > have he columns I can join on just cant find a way to join them . One
>> > way
>> > was
>> > to bring both parts of the data to a consolidated location. But this is
>> > not
>> > the case for some reports .
>> >
>> > Any help would be great.
>> >
>> > Thanks
>> >
>>