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'
>>
>

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 messa
ge
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...
>

join on the server or the client?

Actually Steve, I can't imagine why you would want to join on the client.
On the server you have all the benifts of statics, indexes, server grade
memory & cpu & I/O subsystem. Not to mention the SQL Server Buffer cache,
etc.
If you proccess on the client you are going to have to send both full tables
across the network and do something like a hash join on the client.
Have I missed something?
"sklett" <sklett@.mddirect.com> wrote in message
news:OBmjjdQXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> I've got your typical one to many relationship between 2 tables.
> CREATE TABLE tbl_ft_tests (
> ID smallint(6) AUTO_INCREMENT NOT NULL,
> DateCreated datetime NOT NULL
> )
> CREATE TABLE tbl_ft_testsegments (
> SegmentID int(11) AUTO_INCREMENT NOT NULL,
> TestID smallint(6) NOT NULL,
> CarrierFreq smallint(6) NOT NULL
> )
> A test will have many segments.
> I'm designing my new sprocs and not sure if it's a better idea to do a
join
> on the server (INNER JOIN tbl_ft_testsegments ON
tbl_ft_testsegments.TestID
> = tbl_ft_tests.ID) then parse out the separate tbl_ft_tests records or if
> it's better to get 2 results sets, one for each table and perform the join
> on the client.
> My gut tells me that I should get 2 result sets and join on the client,
but
> I'm not sure...
> Any guidelines or suggestions?
> Thanks for reading,
> Steve
>I don't think you've missed anything. I will give more details just to be
clear. Once I get the results on the client, I will wrap everything in a
series of classes ("Buiness objects"). So what I have done in the past is
to create all the top level objects (tbl_ft_tests) then I iterate over the
tbl_ft_testsegments results adding each one to the correct top level object.
Something like this:
<pseudo code>
class Test
{
TestSegments[] tests;
}
class TestSegment{}
</pseudo code>
It seems that it would be more graceful to built up the business objects
from the separate tables then to parse through one long collection of
pre-joined results checking for new Tests.
Since I have a 1 to many, I will have results like this:
[ID] [DateCreated] [SegmentID] [CarrierFreq]
1 date 1 123
1 date 2 456
1 date 3 789
2 date 4 111
2 date 5 222
2 date 6 333
It just seems "wrong" to return redundant data (ID, DateCreated, etc)
I hope that makes sense.
Thanks for the post, I'm very interested to know if this new information
sheds more light on my situation.
-SK
"Ryan Hunt" <ryan.hunt@.highwoods.com> wrote in message
news:%23q6iyDRXGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Actually Steve, I can't imagine why you would want to join on the client.
> On the server you have all the benifts of statics, indexes, server grade
> memory & cpu & I/O subsystem. Not to mention the SQL Server Buffer cache,
> etc.
> If you proccess on the client you are going to have to send both full
> tables
> across the network and do something like a hash join on the client.
> Have I missed something?
> "sklett" <sklett@.mddirect.com> wrote in message
> news:OBmjjdQXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> join
> tbl_ft_testsegments.TestID
> but
>|||> It just seems "wrong" to return redundant data (ID, DateCreated, etc)
It seems more wrong (or as Andy Dick would say, "wronger") to use a client
application to do the work that the database was designed to do (which is to
perform joins on relational data and produce a result set).
Of course, you know your application way better than any of us, so by all
means, test it. But 99% of the time, unless the database was designed
poorly or you have a less than optimal data model, it will make more sense
to perform the join in the database.|||Understood, understood.
One more question: If I have multiple 1 to many relationships (A test has
multiple targets and multiple segments) would you still advocate joining it
all on the server?
I want to learn the correct way, I'm just not seeing a clear picture of how
to work with my particular data. Is it poorly designed? I don't think so,
I've normalized it as much as I know how.. and that is what has resulted in
the multiple 1 to many relationships.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e05GJ0RXGHA.196@.TK2MSFTNGP04.phx.gbl...
> It seems more wrong (or as Andy Dick would say, "wronger") to use a client
> application to do the work that the database was designed to do (which is
> to perform joins on relational data and produce a result set).
> Of course, you know your application way better than any of us, so by all
> means, test it. But 99% of the time, unless the database was designed
> poorly or you have a less than optimal data model, it will make more sense
> to perform the join in the database.
>|||> One more question: If I have multiple 1 to many relationships (A test has
> multiple targets and multiple segments) would you still advocate joining
> it all on the server?
YES! As the number of tables in your join goes up, so does the motivation
to keep the join logic on the server!
Use the presentation layer for its intended purpose... to present data, not
to be a relational engine.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uze5QXWXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> YES! As the number of tables in your join goes up, so does the motivation
> to keep the join logic on the server!
> Use the presentation layer for its intended purpose... to present data,
> not to be a relational engine.
OK, so it's common to join everything together on the server, then break it
back apart on the client. I will rethink some of my approaches to things.
The presentation layer doesn't do the work I had discussed, it would all be
done in my DA layer.
Thanks again!