Slow Queries
-
I have a couple of queries (basic selects) that are running really slowly.
The CPU utilisation is less than 15%, so its not processor power. If I run the query by hand and then rerun it, I figure there should be virtually no disc I/O because it is still in memory, but it doesn't cut down the time at all. Have bumped up my virtual memory to double my RAM.
Any suggestions are much appreciated.
-
There are many factors that can effect performance and speed of a query besides memory.
Look at the query execution plan and see if anything that is bad for performance is occurring, such as table scan, bad index choice, etc.
If you need further help do
SET SHOWPLAN_TEXT ON
GO
YourQuery
GO
And the query and the output results here so we can take a look and maybe someone will see right off what may help.
-
Thanks Antares.
I have run this query on our production server (a bit beefier but not too much) and it runs quickly. For this reason I was thinking it might have more to do with my DB config than the query itself. Nonetheless, here is my query:
select * from vfsrf00a where gl_account=7600 and program='15'
and the result:
StmtText
-----------------------------------------------------------------
select * from vfsrf00a where gl_account=7600 and program='15'
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Concatenation
|--Compute Scalar(DEFINE:([FINANCIAL_TRANS].[FT_AMOUNT_POSTED]=[FINANCIAL_TRANS].[FT_AMOUNT_POSTED], [FINANCIAL_TRANS].[FT_AMOUNT_POSTED]=[FINANCIAL_TRANS].[FT_AMOUNT_POSTED]))
| |--Compute Scalar(DEFINE:([Expr1038]=Convert([FINANCIAL_TRANS].[FT_FISCAL_YEAR])*100+Convert([FINANCIAL_TRANS].[FT_FISCAL_MONTH]), [Expr1039]=Convert([FINANCIAL_TRANS].[FT_FISCAL_YEAR]), [Expr1040]=Convert([FINANCIAL_TRANS].[FT_FISCAL_MONTH]),
| |--Nested Loops(Left Outer Join)
| |--Nested Loops(Left Outer Join)
| | |--Nested Loops(Inner Join)
| | | |--Nested Loops(Inner Join)
| | | | |--Nested Loops(Inner Join)
| | | | | |--Hash Match(Inner Join, HASH:([Expr1246], [Expr1248])=([Expr1247], [Expr1249]), RESIDUAL:([Expr1247]=[Expr1246] AND [Expr1249]=[Expr1248]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1246]=datepart(0, dateadd(2, 9, [Union1036])), [Expr1248]=datepart(2, dateadd(2, 9, [Union1036]))))
| | | | | | | |--Sort(DISTINCT ORDER BY:([Union1035] ASC, [Union1036] ASC, [Union1037] ASC))
| | | | | | | |--Concatenation
| | | | | | | |--Filter(WHERE:('DY'='ME'))
| | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | |--Compute Scalar(DEFINE:([Expr1016]=floor(Convert([GLOBAL_PARMS].[GP_YEAR_END_DATE])/100)))
| | | | | | | | | |--Table Scan(OBJECT:([fsdb504].[dbo].[GLOBAL_PARMS]))
| | | | | | | | |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[LOCAL_PARMS].[C_LP_PK]))
| | | | | | | |--Filter(WHERE:('CQ'='ME'))
| | | | | | | | |--Compute Scalar(DEFINE:([Expr1024]=If ([LOCAL_PARMS].[LP_PAY_TO_DATE]<>NULL) then [LOCAL_PARMS].[LP_PAY_TO_DATE] else [LOCAL_PARMS].[LP_NEXT_PROC_DATE]))
| | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | |--Compute Scalar(DEFINE:([Expr1022]=floor(Convert([GLOBAL_PARMS].[GP_YEAR_END_DATE])/100)))
| | | | | | | | | |--Table Scan(OBJECT:([fsdb504].[dbo].[GLOBAL_PARMS]))
| | | | | | | | |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[LOCAL_PARMS].[C_LP_PK]))
| | | | | | | |--Filter(WHERE:('ME'='ME'))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1034]=If ([LOCAL_PARMS].[LP_REPORT_DATE]<>NULL) then [LOCAL_PARMS].[LP_REPORT_DATE] else If ([LOCAL_PARMS].[LP_PRIOR_PERIOD_OP]='Y' AND ([LOCAL_PARMS].[LP_FIS
| | | | | | | |--Nested Loops(Inner Join)
| | | | | | | |--Compute Scalar(DEFINE:([Expr1032]=floor(Convert([GLOBAL_PARMS].[GP_YEAR_END_DATE])/100)))
| | | | | | | | |--Table Scan(OBJECT:([fsdb504].[dbo].[GLOBAL_PARMS]))
| | | | | | | |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[LOCAL_PARMS].[C_LP_PK]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1247]=Convert([FINANCIAL_TRANS].[FT_FISCAL_YEAR]), [Expr1249]=Convert([FINANCIAL_TRANS].[FT_FISCAL_MONTH])))
| | | | | | |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[FINANCIAL_TRANS].[C_FT_PK]), WHERE:([FINANCIAL_TRANS].[FT_GL_ACCOUNT]=7600 AND [FINANCIAL_TRANS].[FT_PROGRAM]='15'))
| | | | | |--Clustered Index Seek(OBJECT:([fsdb504].[dbo].[DESCRIPTOR_GL].[C_DG_PK]), SEEK:([DESCRIPTOR_GL].[DG_DESCRIPTOR_CODE]=If ([FINANCIAL_TRANS].[FT_BALANCE_SHEET]='99') then '02' else '01' AND [DESCRIPTOR_GL].[DG_GL_A
| | | | |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[LOCAL_PARMS].[C_LP_PK]))
| | | |--Row Count Spool
| | | |--Table Scan(OBJECT:([fsdb504].[dbo].[GLOBAL_PARMS]))
| | |--Clustered Index Seek(OBJECT:([fsdb504].[dbo].[GSN_ADDRESS].[C_GSNA_PK]), SEEK:([GSN_ADDRESS].[GA_ADDRESS_GSN]=[FINANCIAL_TRANS].[FT_ADDRESS_GSN]) ORDERED)
| |--Clustered Index Seek(OBJECT:([fsdb504].[dbo].[GSN_NAME].[C_GSNN_PK]), SEEK:([GSN_NAME].[GN_PRIMARY_GSN]=[GSN_ADDRESS].[GA_PRIMARY_GSN]) ORDERED)
|--Compute Scalar(DEFINE:([Expr1105]=[OPERATING_FILE].[OF_DIVISION]+[OPERATING_FILE].[OF_SECTION]+[OPERATING_FILE].[OF_UNIT], [Expr1106]=[OPERATING_FILE].[OF_CONTROL_OBJECT]+[OPERATING_FILE].[OF_STANDARD_OBJECT]+[OPERATING_FILE].[OF_GROUP_OBJECT]+[O
|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join)
| |--Nested Loops(Inner Join)
| | |--Hash Match(Inner Join, HASH:([Expr1251], [Expr1253])=([Expr1252], [Expr1254]), RESIDUAL:([Expr1252]=[Expr1251] AND [Expr1254]=[Expr1253]))
| | | |--Compute Scalar(DEFINE:([Expr1251]=datepart(0, dateadd(2, 9, [Union1100])), [Expr1253]=datepart(2, dateadd(2, 9, [Union1100]))))
| | | | |--Sort(DISTINCT ORDER BY:([Union1099] ASC, [Union1100] ASC, [Union1101] ASC))
| | | | |--Concatenation
| | | | |--Filter(WHERE:('DY'='ME'))
| | | | | |--Nested Loops(Inner Join)
| | | | | |--Compute Scalar(DEFINE:([Expr1080]=floor(Convert([GLOBAL_PARMS].[GP_YEAR_END_DATE])/100)))
| | | | | | |--Table Scan(OBJECT:([fsdb504].[dbo].[GLOBAL_PARMS]))
| | | | | |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[LOCAL_PARMS].[C_LP_PK]))
| | | | |--Filter(WHERE:('CQ'='ME'))
| | | | | |--Compute Scalar(DEFINE:([Expr1088]=If ([LOCAL_PARMS].[LP_PAY_TO_DATE]<>NULL) then [LOCAL_PARMS].[LP_PAY_TO_DATE] else [LOCAL_PARMS].[LP_NEXT_PROC_DATE]))
| | | | | |--Nested Loops(Inner Join)
| | | | | |--Compute Scalar(DEFINE:([Expr1086]=floor(Convert([GLOBAL_PARMS].[GP_YEAR_END_DATE])/100)))
| | | | | | |--Table Scan(OBJECT:([fsdb504].[dbo].[GLOBAL_PARMS]))
| | | | | |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[LOCAL_PARMS].[C_LP_PK]))
| | | | |--Filter(WHERE:('ME'='ME'))
| | | | |--Compute Scalar(DEFINE:([Expr1098]=If ([LOCAL_PARMS].[LP_REPORT_DATE]<>NULL) then [LOCAL_PARMS].[LP_REPORT_DATE] else If ([LOCAL_PARMS].[LP_PRIOR_PERIOD_OP]='Y' AND ([LOCAL_PARMS].[LP_FISCAL_MONTH]=1 OR
| | | | |--Nested Loops(Inner Join)
| | | | |--Compute Scalar(DEFINE:([Expr1096]=floor(Convert([GLOBAL_PARMS].[GP_YEAR_END_DATE])/100)))
| | | | | |--Table Scan(OBJECT:([fsdb504].[dbo].[GLOBAL_PARMS]))
| | | | |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[LOCAL_PARMS].[C_LP_PK]))
| | | |--Compute Scalar(DEFINE:([Expr1252]=Convert([OPERATING_FILE].[OF_FISCAL_YEAR]), [Expr1254]=Convert([OPERATING_FILE].[OF_FISCAL_MONTH])))
| | | |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[OPERATING_FILE].[C_OP_PK]), WHERE:([OPERATING_FILE].[OF_GL_ACCOUNT]=7600 AND [OPERATING_FILE].[OF_PROGRAM]='15'))
| | |--Clustered Index Seek(OBJECT:([fsdb504].[dbo].[DESCRIPTOR_GL].[C_DG_PK]), SEEK:([DESCRIPTOR_GL].[DG_DESCRIPTOR_CODE]=If ([OPERATING_FILE].[OF_BALANCE_SHEET]='99') then '02' else '01' AND [DESCRIPTOR_GL].[DG_GL_ACCOUNT]=7600) OR
| |--Clustered Index Scan(OBJECT:([fsdb504].[dbo].[LOCAL_PARMS].[C_LP_PK]))
|--Row Count Spool
|--Table Scan(OBJECT:([fsdb504].[dbo].[GLOBAL_PARMS]))
(68 row(s) affected)
-
I right off see several table scans. Now the fact that the size of the query doesn't match the output means this is a view right? Can you post the view code itself please so I know what the execution plan output is actually talking about.
-
Goodness, nested loops eleven deep, possibly three or four unioned tables with conditional computed scalars and distinct three column sorts in the middle, conditional elimination of nulls, throw in a few left outer joins, and some hash matches.....How large are the tables involved here? I count 21 tables in all, Is that right?
Don't take this badly, but I suggest a serious rework of the view this came from. There are obviously a lot of places here that indexes could help, but a re-structure of the order of steps would be the first area I explored. Move the sort out to the end of the execution, eliminate the multiple sorts, consolidate and move the criteria selections to the beginning to reduce the recordsets joined on, create the proper indexes to eliminate the table scans, etc....
Just looking at the structure, I would be willing to bet that this is actually a view based on several other views (minimum of two) or subqueries. I would LOVE to see the view definition.
-
Yes, is a view. Some of the table are quite large but I have a scaled down version on my test server.
Here is the script for the view:
create view VFSRF00A
(PROCESS_DATE,
REPORT_DATE,
FISCAL_PERIOD,
FISCAL_YEAR,
FISCAL_MONTH,
GL_ACCOUNT,
ACCOUNT_1ST_SUMM,
NORMAL_BALANCE,
PROGRAM,
ACTIVITY,
DIVISION,
SECTION,
UNIT,
ACCOUNTABILITY,
TASK,
VOTE,
CONTROL_OBJECT,
OBJECT,
LINE_OBJECT,
STANDARD_OBJECT,
GROUP_OBJECT,
DETAIL_OBJECT,
REGION_AREA,
REGION,
AREA,
SETTLEMENT,
COSTING,
COST_MAJOR,
COST_CENTRE,
COST_DETAIL,
PROJECT,
MAJOR_PROJECT,
PROJECT_DETAIL,
WORK_PHASE,
WORK_ORDER,
DT_BALANCE_SHEET,
BALANCE_SHEET,
MN_BALANCE_SHEET,
BATCH_ID,
BATCH_LOG,
BATCH_REGION,
BATCH_PROGRAM,
BATCH_SOURCE,
BATCH_NUMBER,
DOCUMENT_ID,
DOCUMENT_TYPE,
DOCUMENT_NUMBER,
DOCUMENT_LINE,
TRANSACTION_SEQ,
DATE_POSTED,
TRANS_CODES,
TRANSACTION_CODE,
REVERSE_CODE,
MODIFIER,
POSTING_CODE,
TD_AMOUNT,
YTD_AMOUNT,
DOCUMENT_REF,
BANK_NUMBER,
TREASURERS_DATE,
SUBSIDIARY,
SUBSID_PREFIX,
GSN,
PRIMARY_GSN,
GSN_PREFIX,
GSN_NAME,
GSN_SHORT_NAME,
INDEX_CODE,
ACTION_DATE,
DESCRIPTION,
SHORT_DESCRIPT,
HOLDBACK_PERCENT,
PERCENT_DISCOUNT,
DAYS_AVAILABLE,
DAYS_NET,
DOC_DISPOSITION,
HANDLING_CODE,
SPENDING_AUTH,
PAYMENT_AUTH,
SUPPLIER_INVOICE,
SUPPLIER_DATE,
ORIGINAL_BATCH,
MTD_AMOUNT,
TAB,
HALF_TAB)
as select
PROCESS_DATE,
REPORT_DATE,
(FT.FT_FISCAL_YEAR*100)+FT.FT_FISCAL_MONTH,
FT.FT_FISCAL_YEAR,
FT.FT_FISCAL_MONTH,
FT.FT_GL_ACCOUNT,
DG.DG_ACCOUNT_1ST_SUMM,
FT.FT_NORMAL_BALANCE,
FT.FT_PROGRAM,
FT.FT_DIVISION+FT.FT_SECTION+FT.FT_UNIT,
FT.FT_DIVISION,
FT.FT_SECTION,
FT.FT_UNIT,
FT.FT_ACCOUNTABILITY,
FT.FT_TASK,
FT.FT_VOTE,
FT.FT_CONTROL_OBJECT,
FT.FT_CONTROL_OBJECT+FT.FT_STANDARD_OBJECT+FT.FT_GROUP_OBJECT+FT.FT_DETAIL_OBJECT,
FT.FT_STANDARD_OBJECT+FT.FT_GROUP_OBJECT+FT_DETAIL_OBJECT,
FT.FT_STANDARD_OBJECT,
FT.FT_GROUP_OBJECT,
FT.FT_DETAIL_OBJECT,
FT.FT_REGION+FT.FT_AREA,
FT.FT_REGION,
FT.FT_AREA,
FT.FT_SETTLEMENT,
FT.FT_COST_MAJOR+FT.FT_COST_CENTRE+FT.FT_COST_DETAIL,
FT.FT_COST_MAJOR,
FT.FT_COST_CENTRE,
FT.FT_COST_DETAIL,
FT.FT_MAJOR_PROJECT+FT.FT_PROJECT_DETAIL+FT.FT_WORK_PHASE,
FT.FT_MAJOR_PROJECT,
FT.FT_PROJECT_DETAIL,
FT.FT_WORK_PHASE,
FT.FT_WORK_ORDER,
FT.FT_BALANCE_SHEET+FT.FT_MN_BALANCE_SHEET,
FT.FT_BALANCE_SHEET,
FT.FT_MN_BALANCE_SHEET,
FT.FT_BATCH_ID,
substring(FT.FT_BATCH_ID,1,5),
substring(FT.FT_BATCH_ID,1,1),
substring(FT.FT_BATCH_ID,2,2),
substring(FT.FT_BATCH_ID,4,2),
substring(FT.FT_BATCH_ID,6,3),
FT.FT_DOCUMENT_ID,
substring(FT.FT_DOCUMENT_ID,1,2),
substring(FT.FT_DOCUMENT_ID,3,6),
substring(FT.FT_DOCUMENT_ID,9,2),
FT.FT_TRANSACTION_SEQ,
FT.FT_DATE_POSTED,
FT.FT_TRANS_CODE+FT.FT_REVERSE_CODE+FT.FT_MODIFIER,
FT.FT_TRANS_CODE,
FT.FT_REVERSE_CODE,
FT.FT_MODIFIER,
FT.FT_POSTING_CODE,
FT.FT_AMOUNT_POSTED,
FT.FT_AMOUNT_POSTED,
FT.FT_DOCUMENT_REF,
FT.FT_BANK_NUMBER,
FT.FT_TREASURERS_DATE,
FT.FT_SUBSIDIARY,
substring(FT.FT_SUBSIDIARY,1,2),
FT.FT_ADDRESS_GSN,
GA.GA_PRIMARY_GSN,
substring(FT.FT_ADDRESS_GSN,1,1),
GN.GN_NAME_LINE_1,
substring(GN.GN_NAME_LINE_1,1,20),
FT.FT_INDEX_CODE,
FT.FT_ACTION_DATE,
FT.FT_DESCRIPTION,
substring(FT.FT_DESCRIPTION,1,20),
FT.FT_HOLDBACK_PERCENT,
FT.FT_PERCENT_DISCOUNT,
FT.FT_DAYS_AVAILABLE,
FT.FT_DAYS_NET,
FT.FT_DOC_DISPOSITION,
FT.FT_HANDLING_CODE,
FT.FT_SPENDING_AUTH,
FT.FT_PAYMENT_AUTH,
FT.FT_SUPPLIER_INVOICE,
FT.FT_SUPPLIER_DATE,
FT.FT_ORIGINAL_BATCH,
FT.FT_AMOUNT_POSTED,
space(10),
space(5)
from FINANCIAL_TRANS FT join VFSDATES
on DATE_TYPE = 'ME'
join DESCRIPTOR_GL DG
on DG.DG_GL_ACCOUNT=FT.FT_GL_ACCOUNT
and DG.DG_DESCRIPTOR_CODE=case when FT.FT_BALANCE_SHEET="99" then "02" else "01" end
left outer join GSN_ADDRESS GA
on FT.FT_ADDRESS_GSN=GA.GA_ADDRESS_GSN
left outer join GSN_NAME GN
on GA.GA_PRIMARY_GSN=GN.GN_PRIMARY_GSN
where FT.FT_FISCAL_YEAR=FISCAL_YYYY
and FT.FT_FISCAL_MONTH=FISCAL_MM
union all select
PROCESS_DATE,
REPORT_DATE,
0,
0,
0,
OP.OF_GL_ACCOUNT,
DG.DG_ACCOUNT_1ST_SUMM,
OP.OF_NORMAL_BALANCE,
OP.OF_PROGRAM,
OP.OF_DIVISION+OP.OF_SECTION+OP.OF_UNIT,
OP.OF_DIVISION,
OP.OF_SECTION,
OP.OF_UNIT,
OP.OF_ACCOUNTABILITY,
OP.OF_TASK,
OP.OF_VOTE,
OP.OF_CONTROL_OBJECT,
OP.OF_CONTROL_OBJECT+OP.OF_STANDARD_OBJECT+OP.OF_GROUP_OBJECT+OP.OF_DETAIL_OBJECT,
OP.OF_STANDARD_OBJECT+OP.OF_GROUP_OBJECT+OP.OF_DETAIL_OBJECT,
OP.OF_STANDARD_OBJECT,
OP.OF_GROUP_OBJECT,
OP.OF_DETAIL_OBJECT,
OP.OF_REGION+OP.OF_AREA,
OP.OF_REGION,
OP.OF_AREA,
OP.OF_SETTLEMENT,
OP.OF_COST_MAJOR+OP.OF_COST_CENTRE+OP.OF_COST_DETAIL,
OP.OF_COST_MAJOR,
OP.OF_COST_CENTRE,
OP.OF_COST_DETAIL,
OP.OF_MAJOR_PROJECT+OP.OF_PROJECT_DETAIL+OP.OF_WORK_PHASE,
OP.OF_MAJOR_PROJECT,
OP.OF_PROJECT_DETAIL,
OP.OF_WORK_PHASE,
OP.OF_WORK_ORDER,
OP.OF_BALANCE_SHEET+OP.OF_MN_BALANCE_SHEET,
OP.OF_BALANCE_SHEET,
OP.OF_MN_BALANCE_SHEET,
space(8),
space(5),
space(1),
space(2),
space(2),
space(3),
space(8),
space(2),
space(6),
space(2),
0,
NULL,
space(5),
space(3),
space(1),
space(1),
space(2),
OP.OF_TD_AMOUNT - OP.OF_MTD_AMOUNT,
OP.OF_YTD_AMOUNT - OP.OF_MTD_AMOUNT,
space(10),
0,
NULL,
space(10),
space(2),
"0000000000",
"00000000",
"0",
space(35),
space(20),
OP.OF_INDEX_CODE,
NULL,
"Balance Forward"+space(25),
"Balance Forward"+space(5),
0,
0,
0,
0,
space(1),
space(1),
space(4),
space(4),
space(10),
NULL,
space(8),
0,
space(10),
space(5)
from OPERATING_FILE OP join VFSDATES
on DATE_TYPE = 'ME'
join DESCRIPTOR_GL DG
on DG.DG_GL_ACCOUNT=OP.OF_GL_ACCOUNT
and DG.DG_DESCRIPTOR_CODE=case when OP.OF_BALANCE_SHEET="99" then "02" else "01" end
where OP.OF_FISCAL_YEAR=FISCAL_YYYY
and OP.OF_FISCAL_MONTH=FISCAL_MM
-
It IS a view based on other views. Could we get the view definitions for the ones referenced in this one?
Also, what fields are you actually using from this one?
-
Not sure what you mean by "what fields are you using from this one".
There is one other view in that first view. It is defined as:
CREATE VIEW vfsdates
as
select DATE_TYPE,
LP_PROCESS_DATE as PROCESS_DATE,
REPORT_DATE as REPORT_DATE,
convert(varchar(4), datename(yyyy, LP_PROCESS_DATE)) as PROCESS_YEAR,
convert(varchar(10), datename(mm, LP_PROCESS_DATE)) as PROCESS_MONTH,
convert(varchar(2), right('0' +datename(dd, LP_PROCESS_DATE), 2)) as PROCESS_DAY,
convert(varchar(4), datename(yyyy, REPORT_DATE)) as REPORT_YEAR,
convert(varchar(10), datename(mm, REPORT_DATE)) as REPORT_MONTH,
convert(varchar(2), right('0' +datename(dd, REPORT_DATE), 2)) as REPORT_DAY,
convert(varchar(4), datename(yyyy, dateadd(mm, 9, REPORT_DATE))) as FISCAL_YEAR,
datepart(yyyy, dateadd(mm, 9, REPORT_DATE)) as FISCAL_YYYY,
datepart(mm, dateadd(mm, 9, REPORT_DATE)) as FISCAL_MM,
(datepart(yyyy, dateadd(mm, 9, REPORT_DATE)) * 100) +
datepart(mm, dateadd(mm, 9, REPORT_DATE)) as FISCAL_PERIOD,
convert(varchar(4), datename(yyyy, dateadd(mm, -3, REPORT_DATE))) as PRIOR_YEAR,
convert(varchar(4), datename(yyyy, dateadd(mm, -15, REPORT_DATE))) as PRIOR_PRIOR_YEAR,
convert(varchar(10), datename(mm, dateadd(mm, -1, REPORT_DATE))) as PRIOR_MONTH,
convert(varchar(10), datename(mm, convert(datetime, '2000-' +
convert(varchar, YEM + 1) + '-01'))) as YEAR_START_MONTH,
convert(varchar(10), datename(mm, convert(datetime, '2000-' +
convert(varchar, YEM) + '-01'))) as YEAR_END_MONTH,
convert(varchar(2), right('0' + datename(dd, GP_YEAR_END_DATE), 2)) as YEAR_END_DAY
from LOCAL_PARMS GP cross join GLOBAL_PARMS cross join
(select 'DY' as DATE_TYPE, LP_PROCESS_DATE as REPORT_DATE, YEM
from LOCAL_PARMS cross join
(select floor(GP_YEAR_END_DATE/100) as YEM
from GLOBAL_PARMS) as YE
union
select 'CQ' as DATE_TYPE, coalesce(LP_PAY_TO_DATE, LP_NEXT_PROC_DATE) as REPORT_DATE, YEM
from LOCAL_PARMS cross join
(select floor(GP_YEAR_END_DATE/100) as YEM
from GLOBAL_PARMS) as YE
union
select 'ME' as DATE_TYPE, coalesce(LP_REPORT_DATE,
case when LP_PRIOR_PERIOD_OP = 'Y'
and ((LP_FISCAL_MONTH = 1) or (LP_PRIOR_YEAR_OPEN <> 'Y'))
then dateadd(dd, -1, dateadd(mm, YEM - 13, convert(datetime,
convert(varchar, LP_FISCAL_YEAR) + '-' +
convert(varchar, LP_FISCAL_MONTH) + '-01')))
when LP_PRIOR_YEAR_OPEN = 'Y'
then dateadd(dd, -1, convert(datetime,
convert(varchar, LP_FISCAL_YEAR - 1) + '-' +
convert(varchar, YEM) + '-01'))
else dateadd(dd, -1, dateadd(mm, YEM - 12, convert(datetime,
convert(varchar, LP_FISCAL_YEAR) + '-' +
convert(varchar, LP_FISCAL_MONTH) + '-01')))
end) as REPORT_DATE, YEM
from LOCAL_PARMS cross join
(select floor(GP_YEAR_END_DATE/100) as YEM
from GLOBAL_PARMS) as YE) as RD
-
The key is firs to look at the fields you actually need from the views and determine the if maybe a new view instead of your current view calling a view will be easier. The reason is when you use a view to call data it calls the underlying table and fields into memory as part of a subquery resultset. If you don't need all the fields you are doing more work than needed. I haven't looked at this too much yet but I will try to over the 3 day weekend.
-
Perhaps I need to rework this view a bit then. Your comments are much appreciated.
-
Christ, what a mother of a query.
I am sure that a LOT can be done with it, and I'll look over it in detail this weekend (probably take most of a day, at least). Hopefully, Antares686 can help too? And maybe 10 or twelve or more people as well?What I meant was, In the fifty or so fields your returning with your select * from statement that started this all, are you actually using all the fields? Are you using one or two? Ten? etc... I can't imagine the disk IO this must generate, and to be honest, I can't see the processor only hitting 15% or so unless it's so IO bound that the processor has to wait for the data, giving it slack time. A MAJOR gain could be realized if you were only using a handfull of the returned fields, and if this reduction of fields could be continued back through to the cross joins, the gain would become exponentially better. If, on the other hand, your actually needing ALL these fields in whatever this returns results too, that isn't an option. There are many ways to help this query, narrowing the returned resultsets, reducing the rows involved, moving the joins to after the unions, possibly only referencing the second view only once rather than twice, suggesting indexes, possibly changing to use a covered index in the seeks involved. I am trying to find out what your actual needs are for fields to be returned, as generally, when people are referencing views, it's so they don't have to deal with writing explicit statements themselves (a convenience), and nine times out of ten, they are not using many of the fields involved to start with, just discarding them and using the fields they wanted out of the set. When your dealing with something nested this deep, reducing a single field all the way back to the beginning would eliminate the reading, writing, sorting, joining of that one field. In this case, by 21 times. If we were to say that field only held 50 bytes of information (a typical default varchar field) that would reduce the IO involved by over 1050 bytes (way under exaggerated, as the overhead, and locks generated could possibly be changed as well). That's just a single field. If your talking about a cross join, with table scans, it's exponentially much more, and there are several cross joins here. If the tables contain many rows, it's quite easy to see how that single field could possibly generate many megabytes, possibly gigabytes, of disk activity.
I swear, I'm going to have to quit writing long explanations. Every time I finish, that Antares686 has done answered and been replied to....
Edited by - scorpion_66 on 01/17/2003 5:35:37 PM
-
Ha ha ha. Well if nothing else I am learning something here.
I don't need all the fields in the query/view but I do need most of them. The view is the basis for a user defined reporting system that we are developing. I need most of the fields because the system allows users to run adhoc reporting and select their own fields.
-
Sorry about that Scorpion. I just enjoy the challenge. I am trying to back off bu it is like a drug to me. So I am focusing here soon on writting an app and a book on the transaction log and how to read it. Would anyone still buy an app to read the TL (not quite as big and empowered as Log Explorer, something a lot lighter) if a book existed (and actually was published) on understaning the output from the transaction log?
-
Don't apologize. I was laughing about the fact that we seem to see these about the same times, and you type quicker than me or something. Besides that, your suggestions are generally more to the point and straight forward, hence more helpful to people needing it. I include a lot of information that I would imagine isn't really even needed, just good to know. You might find it interesting, but I watch for your answers quite often, as I have learned and been able to apply many helpful things from your discussions, and consider you to be a VERY GREAT asset to this site. I would feel really bad if you were to slow down on my account. B'sides, it keeps me on my toes....
About your Book on understanding the Log, I would read it. And I think it's a great idea, as I have yet to find any of much worth on the subject. Back in the 80's, I came across one that was excellent, but haven't been able to re-locate it.
-
I was having another look at some of the comments and I offer the following in case anyone is still trying to help me resolve this.
- The Parms tables (global and local) are one row table.
- Descriptor and GSN tables are accessed via unique clustered indexes so they should be quick lookups.
- Virtually all of the processing (95%) in done in the clustered index scans of 2 tables (financial_trans and operating_file). I suspect that the problem is that it is doing a complete scan of the clustered index rather than a subset scan. Have yet to prove this...
- My older (production) database that runs much faster is doing a clustered index seek and this appears to be absent from my new (test) db.
I am sure that a LOT can be done with it, and I'll look over it in detail this weekend (probably take most of a day, at least). Hopefully, Antares686 can help too? And maybe 10 or twelve or more people as well?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply