Help with query...

[fmrock]

Hey everyone,

I have the following query that i used a bunch of unions to get different counts. I want to compare all the self pay accounts to all the accounts per site location.

This is very slow due, and since i am pretty much running the same query for both types, is there away to get rid of the unions or make this more efficient?

Select 
	sSite,
	Category, 
	Sum(Num_of_Accounts) As Num_of_Accounts, 
	Sum(Collection_Accounts) As Collection_Accounts,
	Sum(Num_of_Statements) Num_of_Statements,
	SUM(Total_Charges_Amount) Total_Charges_Amount
from (
--------------------------------
--Self Pay--------------------- 
SELECT 
	ACCOUNT.CPCODE sSite, 
	'Self Pay Patients' Category,
	Count(ACCOUNT.CODE) Num_of_Accounts,
	SUM(CASE WHEN (ACCOUNT.EXCEPTION IN ('CA','CP')) THEN 1 ELSE 0 END) Collection_Accounts,
	sum(ACCOUNT.STATEMENTS) Num_of_Statements,
	0 Total_Charges_Amount
FROM   
	ACCOUNT ACCOUNT
WHERE 
	(ACCOUNT.BILLSTATUS='S')
	AND to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20070131'
GROUP BY
	ACCOUNT.CPCODE
UNION
SELECT 
	ACCOUNT.CPCODE sSite, 
	'Self Pay Patients' Category,
	0 Num_of_Accounts,
	0 Collection_Accounts,
	0 Num_of_Statements,
	SUM(CHARGES.PRAMOUNT) Total_Charges_Amount
FROM   
	ACCOUNT ACCOUNT
	JOIN ECI_LTD.CHARGES CHARGES ON ((ACCOUNT.CPCODE=CHARGES.CPCODE) AND (ACCOUNT.CODE=CHARGES.ACCOUNT) AND (CHARGES.TYPE='C') AND (CHARGES.SPLITFLAG IS  NULL))
WHERE 
	(ACCOUNT.BILLSTATUS='S')
	AND to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20070131'
GROUP BY
	ACCOUNT.CPCODE


--------------------------------
--ALL Accounts------------------

UNION
SELECT 
	ACCOUNT.CPCODE sSite, 
	'All Patients' Category,
	Count(ACCOUNT.CODE) Num_of_Accounts,
	SUM(CASE WHEN (ACCOUNT.EXCEPTION IN ('CA','CP')) THEN 1 ELSE 0 END) Collection_Accounts,
	sum(ACCOUNT.STATEMENTS) Num_of_Statements,
	0 Total_Charges_Amount
FROM   
	ACCOUNT ACCOUNT
WHERE  to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20070131'
GROUP BY
	ACCOUNT.CPCODE
UNION
SELECT 
	ACCOUNT.CPCODE sSite, 
	'All Patients' Category,
	0 Num_of_Accounts,
	0 Collection_Accounts,
	0 Num_of_Statements,
	SUM(CHARGES.PRAMOUNT) Total_Charges_Amount
FROM   
	ACCOUNT ACCOUNT
	JOIN ECI_LTD.CHARGES CHARGES ON ((ACCOUNT.CPCODE=CHARGES.CPCODE) AND (ACCOUNT.CODE=CHARGES.ACCOUNT) AND (CHARGES.TYPE='C') AND (CHARGES.SPLITFLAG IS  NULL))
WHERE to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20070131'
GROUP BY
	ACCOUNT.CPCODE
--------------------------------


) U
Group By sSite, Category

 

[fmrock]

also, there can be multple charges per each account. thats why i have that query seperate.

 

[cmmrfrds]

There are a number of ways to solve the problem, but the best solution depends on some statistics on your system. Can you supply these to start with.

1. How many records in the Account table?
2. What percent of the records in the table fall into the date range of 07/01/2007 to 07/31/2007?
3. Is logdate indexed?
4. How often do you run statistics on the Account table and it's indexes?
5. Is billstatus indexed?
6. How many fields in the Account table?

 

[cmmrfrds]

I forgot to add.
Please define slow in terms of minutes on the query.

 

[fmrock]

We have between 1.2 and 1.5 accounts per year, since 1997/1998.

The charges table has over 50million records.

this is also a third parties database so we only have the ability to query this information. Is there a way for me to query the system to see what index are put on the tables?

Also, one reason it may be slow is that the database is housed at a vendor, but we have a connection right to them.

I am trying to run this query for all of 2007 right now and its been running for close to 3 hours.

 

[fmrock]

Also, the accounts table has a 120 columns and charges has a 150 columns.

 

[cmmrfrds]

Provided the 3rd party vendor allows you to query the dictionary, you could run a query like this.
Put your schema name in owner where there is 'XXXX'

Select *
From
(
Select
'index' as type,
table_name,
null as last_analyzed,
index_name as index_name,
num_rows
from sys.all_indexes
where owner = 'XXXX'
Union All
Select
'table' as type,
table_name as name,
last_analyzed,
null as index_name,
num_rows
from sys.all_tables
where owner = 'XXXX'
) aview
order by table_name, index_name desc

 

[fmrock]

Worked perfect.

Logdate is indexed, but bill status is Not.

 

[cmmrfrds]

Since the Charge table is large relative to the Account table, one way is to eliminate 2 passes against the table by making a temporary table of just the fields you need (3 columns instead of 150) and using the temporary table in your query. Use this code to create the temporary table and see how long it takes to create and run as a test.

WITH CHARGES AS
(
SELECT
ACCOUNT,
CPCODE,
PRAMOUNT
FROM ECI_LTD.CHARGES CHARGES
WHERE (CHARGES.TYPE='C') AND (CHARGES.SPLITFLAG IS NULL))
)
SELECT COUNT(*) FROM CHARGES