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

Joining Many Tables in Loop

Hello,

I am very new to TSQL however I do have a little programming in my background. I am trying to run a query to select several datafields from about 45 tables within a database. I have written the below code to loop through all of the tables and extract the information however I need to find some way to join the results using PCTimeStamp as the key. Any help anyone can give me is greatly appreciated!

while (@.Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@.Table_Number=17990 or @.Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @.Table_Number=@.Table_Number+1;
END;
set @.Table_Insert = cast (@.Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/

set @.SQLString = N'SELECT Id, Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladecPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection1 FROM T_'+@.Table_Insert+'_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@.Table_Insert+'_10MINDATA)';

set @.Table_Number = @.Table_Number+1; /*Increment @.Table_Number to control Loop*/
if (@.Table_Number <18006 or @.Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@.SQLString); /*Execute String to retrieve data from table*/
END;
END;

I tried to format that in this text box as best I could but I apologize for the result. In summary Table_Number is used as the key to loop through the sequentially numbered tables (with some numbers missing) and extract the required fields from the most recent entry (PCTimeStamp=max).

Other notes: the code as written outputs all of the required data with no error messages but the data column titles are displayed i.e:

id windspeed direction time

1 12 000 1200

id windspeed direction time

2 12 010 1200

...................

looking for

id windspeed direction time

1 12 000 1200

2 12 010 1200

Thanks again

Consider using a temporary table. You can create the table outside the loop and change your dynamic query into an Insert...Select. You will return the combined results by selecting from the temporary table.|||

Hello,

You do not need to use a temp table for this.

You can use the UNION operator as such:

SELECT TOP 1 * FROM Table1 order by PCTimeStamp DESC

UNION ALL

SELECT TOP 1 * FROM Table2 order by PCTimeStamp DESC

UNION ALL

SELECT TOP 1 * FROM Table3 order by PCTimeStamp DESC

Cheers,

Rob

|||

Thanks to everyone for the help, I looked into the temporary table and here is my revised code:

declare @.SQLString NVARCHAR(500); /*Executable String to Run Query on Multiple Table in Looping Feature*/

declare @.WindLogics_Data TABLE(Status_Avg_NacellePos Int, Status_Avg_BladeAPos SMALLMONEY, Status_Avg_BladeBPos SMALLMONEY, Status_Avg_BladeCPos SMALLMONEY, Grid_Avg_ActivePower SMALLMONEY, Status_Avg_Windspeed1 SMALLMONEY,Status_Avg_Windspeed2 SMALLMONEY, Status_Avg_WindDirection1 SMALLMONEY, Status_Avg_WindDirection2 SMALLMONEY);

Set @.Table_Number = 17965; /*Initialize Value of Turbine Table to 17965*/

/*Practice Insert*/

Insert INTO @.WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_17965_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_17965_10MINDATA);
select * from @.WindLogics_Data

while (@.Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@.Table_Number=17990 or @.Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @.Table_Number=@.Table_Number+1;
END;
set @.Table_Insert = cast (@.Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/
set @.SQLString = N'Insert INTO @.WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_17965_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) FROM T_'+@.Table_Insert+'_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@.Table_Insert+'_10MINDATA)';
set @.Table_Number = @.Table_Number+1; /*Increment @.Table_Number to control Loop*/
if (@.Table_Number <18006 or @.Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@.SQLString); /*Execute String to retrieve data from table*/
END;
END;

**************************************

Practice insert works perfectly so I know structure of @.WindLogics_Data is correct however SQL will not recognize the database name in the SQLString. Says "Must declare @.Wind_Logics", is there a stipulation against table variable name in a string running through the exec command?

Thank you so much for the help, table var was a lifesaver!

|||

I don't see the variable that you mention in the code you posted. Yes, there are restrictions as to what you can do with dynamic SQL. You cannot access variables declared outside the scope of the dynamic SQL for example. Also, using a table variable may not be the best thing to do depending on the number of rows in each table. Performance will worsen as you populate more rows into table variables. It is more suited for smaller results in the range of 1000s of rows or so. So using a temporary table will provide better performance.

In any case, it seems like you should use table partitioning in SQL Server 2005 or partitioned views in SQL Server 2000/70. With either of the approach, you don't need to write such complicated code. You can achieve what you are doing by writing simple SELECT statements. See below links for more information on partitioning. And BOL also has topics that cover this feature.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

http://www.microsoft.com/technet/prodtechnol/sql/2005/spdw.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part5/c1861.mspx

|||

Umachandar,

Thank you for replying I should have provided more background, using a temporary table seems to get me closer to working and for informational purposes the results will only be 10columns with 45 rows so in this case efficiency is not a HUGE concern (you probably disagree!) here is the code sample with highlight where problem is. Now I get error message as

Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'select'. (repeated over and over for loop)

Again my test insert into the temporary table is operational so once the above error is resolved I think I am good to go.

I looked into the partitioning and to be honest it would require me a while to absorb the structure I think, I was hoping there would be a quicker fix for the above error.

****************************************************************************************************************************************************

use Windman /*Defines database to be used for commands*/

declare @.Table_Number INT; /*Number of Table in Numerical Format for Looping*/
declare @.Table_Insert NVARCHAR(5); /*Number of Table in Char for String Insertion*/
declare @.SQLString NVARCHAR(500); /*Executable String to Run Query on Multiple Table in Looping Feature*/
create table #WindLogics_Data (Status_Avg_NacellePos Int, Status_Avg_BladeAPos SMALLMONEY, Status_Avg_BladeBPos SMALLMONEY, Status_Avg_BladeCPos SMALLMONEY, Grid_Avg_ActivePower SMALLMONEY, Status_Avg_Windspeed1 SMALLMONEY,Status_Avg_Windspeed2 SMALLMONEY, Status_Avg_WindDirection1 SMALLMONEY, Status_Avg_WindDirection2 SMALLMONEY);

Set @.Table_Number = 17965; /*Initialize Value of Turbine Table to 17965*/

Insert INTO #WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_17965_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_17965_10MINDATA);
select * from #WindLogics_Data

while (@.Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@.Table_Number=17990 or @.Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @.Table_Number=@.Table_Number+1;
END;
set @.Table_Insert = cast (@.Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/
set @.SQLString = N'INSERT INTO #WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_'+@.Table_Insert+'10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@.Table_Insert+'_10MINDATA)';

if (@.Table_Number <18006 or @.Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@.SQLString); /*Execute String to retrieve data from table*/
END;

set @.Table_Number = @.Table_Number+1; /*Increment @.Table_Number to control Loop*/
END;

Drop Table #WindLogics_Data

/*END OF QUERY******************************************************************************/

|||My goodness I am so sorry, I realized what it was, I had only made my variable for the string 400 characters and my command had exceeded that, I apologize and thanks for all of the help!

Join with Subselect and Sum

I have an appointment table (appts) and a charge table (charge).
For the day the query is run, I want to return anyone with an appointment
that has a balance on the charge table.
I am looking at doing a subselect but I'm having a syntax problem.
select APPTS.ACCOUNT
from APPTS
where APPTS.DATE = getdate()
and company = 'main'
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
What am I doing incorrectly on the subselect?
THANKS,
MEGyou are missing criteria to justify your first where clause
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0) = [whatever][/colo
r]
"MEG" wrote:
> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||Try,
select
a.ACCOUNT
from
APPTS as a
INNER JOIN
CHARGE as c
on a.COMPANY = c.COMPANY
and a.ACCOUNT = c.ACCOUNT
where
a.[DATE] = convert(char(8), getdate(), 112)
and a.company = 'main'
group by
a.ACCOUNT
having
SUM(c.CHGAMOUNT - c.PAYINS1 - c.PAYINS2 - c.PAYGUAR - c.WRITEOFF - c.ADJUST
+ c.DEDUCTIBLE - c.ONACCT) > 0
AMB
"MEG" wrote:

> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||On Wed, 7 Sep 2005 12:16:03 -0700, MEG wrote:

>I have an appointment table (appts) and a charge table (charge).
>For the day the query is run, I want to return anyone with an appointment
>that has a balance on the charge table.
>I am looking at doing a subselect but I'm having a syntax problem.
>select APPTS.ACCOUNT
>from APPTS
>where APPTS.DATE = getdate()
>and company = 'main'
>INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
>where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
>SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
>FROM CHARGE
>WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
>What am I doing incorrectly on the subselect?
Hi MEG,
The subselect in itself looks okay. But the order of the phrases in the
complete query is not quite right. Try:
select APPTS.ACCOUNT
from APPTS
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where APPTS.DATE = getdate()
and company = 'main'
and ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) -
SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) >
0)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Join tables and exclude records...

Hi

I am trying to write a SQL query to run against 2 Oracle tables - tblNames and tblAbsence.

Let's say the tables look like this:

tblNames:

Andrew
David
John
Michael

and tblAbsence:

Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment

What I need to do is to create a report that lists all of the absences from tblAbsence, plus a row for anyone in the tblNames table with no date and "In Office" in the third column if they don't have an entry in tblAbsence.

To use the above values, what I should have in my resultset is:

Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment
John Null In Office
Michael Null In Office

I'm not sure how to do this. I did try a UNION of two queries, but the nearest I have been able to get is to reproduce the entries in the tblAbsence table plus all of the records in tblNames. I need to exclude the names from tblNames if they have an entry in tblAbsence.

Any ideas?

Thanks

MichaelDifferent versions of Oracle have different levels of support for standard SQL, but the way that I'd do it would be:SELECT n.Name
, a.date, Coalesce(a.comment, 'In Office)
FROM tblNames AS n
LEFT OUTER JOIN tblAbsence AS a
ON (a.name = n.name)-PatP|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);Good point, but if the poster is just starting out I think it would be better to start them with standard SQL and only use eingine specific features if they are required. The closer we can keep the new users to standards, the less likely they are to get hurt by the odd quirks that we've come to know and love!

-PatP|||I agree, Pat ... however, Robojan said it is about Oracle tables so I guess this Oracle specific code won't hurt much :)|||Thanks guys - this helped me to write the query. I simplified it greatly for this (removing joins, etc.), and using the joins correctly fixed it for me.

Thanks

Join problem

Hi - I have a problem with an update (with a join) that I'm attempting to run on a table with 53 million records in.

Basically I set this query off for the first time and it chugged away for 96 hours before I killed it - something was awry.
I've restored the database to another server (just in case we have an issue with disks, compress, memory etc) and run some tests on samples of the 53 million and for low samples, the update runs in a reasonable time which increases in time directly in line with the increase in sample size:

Top # sample from database tests:
1,000,000 - 36 seconds
2,000,000 - 71 seconds
3,000,000 - 122 seconds
but once I try 4,000,000 it runs and runs - got up to 36 minutes before I cancelled it.

Can anyone see any reason for this? I don't think the query size is going up exponentially - because if you graph the sample size & time from 1-3 million, the line is linear.

I'm currently adding a record ID to the table so I can select the bottom 4,000,000 so I can be sure that there's not some weird data somewhere between 3-4mill that is making the query go whoopsie.

Here are the tables & query I am attemping to run:

UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM ListCategoryHierarchy2
WHERE MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]

Each table is clustered on SourceCode ID/Sourcecode

(varchars are COLLATE Latin1_General_CI_AS)

CREATE TABLE [ListCategoryHierarchy2] (
[Campaign ID] [varchar] (255) NULL,
[Media ID] [varchar] (255) NULL,
[Media Description] [varchar] (255) NULL ,
[Media Selections] [varchar] (255) NULL ,
[SourceCode ID] [varchar] (7) NULL ,
[List ID] [varchar] (255) NULL ,
[Mailing Date] [smalldatetime] NULL ,
[List Category] [varchar] (255) NULL ,
[Source Code Offer] [varchar] (255) NULL ,
[ERI Value] [float] NULL
) ON [PRIMARY]
(9,923 records)

CREATE TABLE [MailingHistory_sample] (
[MatchKey] [binary] (20) NULL ,
[SourceCode] [varchar] (6) NULL ,
[ListID] [varchar] (7) NULL ,
[StationeryCode] [varchar] (5) NULL ,
[PDYear] [varchar] (2) NULL ,
[NaadID] [varchar] (10) NULL ,
[OrderNumber] [char] (9) NOT NULL ,
[CustomerNumber] [binary] (8) NULL ,
[CampaignCode] [varchar] (4) NULL ,
[ProductCode] [varchar] (4) NULL ,
[ResponseType] [varchar] (1) NULL ,
[HouseholdNumber] [bigint] NULL ,
[IndividualNumber] [bigint] NULL ,
[DataType] [varchar] (1) NULL ,
[AddressNumber] [bigint] NULL ,
[DateStamp] [char] (8) NULL ,
[CountColumn] [int] NULL ,
[MailingInstance] [int] NULL ,
[PCPrizm] [char] (5) NULL ,
[Postcode] [char] (7) NULL ,
[PostalArea] [varchar] (2) NULL ,
[TVRegion] [varchar] (3) NULL ,
[ERIRange] [int] NULL ,
[ERIValue] [float] NULL ,
[MediaID] [varchar] (10) NULL
) ON [PRIMARY]

SQL 2000

Any thoughts? Could there be some critical mass of temp table size etc that I am hitting?
thx
wAh, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?|||Well, I'm not too fond of your syntax. You should link your tables in a join rather than the WHERE clause, though I don't know that this would be impacting your execution time. It might.

UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM MailingHistory_Sample
inner join ListCategoryHierarchy2 on MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]

Also, drop any indexes on MailingHistory_Sample except one on [SourceCode]. This column should be indexed in both tables.|||Ah, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?

Well that's one problem...

Where are the Indexes for these tables?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.

We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.

Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.

Any ideas what this might be?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.

We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.

Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.

Any ideas what this might be?

If your database is running in Full Recovery mode, a complete before and after image of the update must be stored in the transaction log. If the log file is situated on the same physical drive as the primary data store, has too small an auto-grow value, or is badly physically fragmented, the overall update time can become very, very long.

Options include: issuing an ALTER DATABASE command and backup to take the database into Simple Recovery before the update; relocate the transaction log to a dedicated physical drive; set the transaction log to very, very big and do not truncate its space to filing system when backing up.|||Ah thanks - that makes sense. I only learnt this morning about putting data files and transaction logs on different physical disks. That is the case for this database so we are going to slap in a new drive and split the two.

thanks
w