November 29, 2007 at 12:44 pm
Two days ago we rebuilt all indexes in all tables using the maintenance plan. Would that not have created statistics for us?
Also, the specific tables where SQL SErver is scanning the indexes as opposed to seeking on them are in the views refererenced in the query.
November 29, 2007 at 12:50 pm
Two days ago we rebuilt all indexes in all tables using the maintenance plan. Would that not have created statistics for us?
I think this depends on auto create statistics in DB properties.
For Matt wow - you read all that code.
November 29, 2007 at 12:57 pm
On the DBs being used in this query, auto update and auto create statistics is enabled.
November 29, 2007 at 12:59 pm
Can we see the view definitions ?
Are the views doing anything that isn't supported under SQL2K5 ? Eg: the use of SELECT TOP 100 PERCENT in a view in conjunction with an ORDER BY, in order to force a view to do something it wasn't supposed to do (i.e. provide sorted results).
This behaviour changed in SQL2K5, and queries based on such views may generate different exec plans on SQL2K5.
November 29, 2007 at 1:05 pm
vwMinLTOXHTOXLinc:
Select TITLE_REFRNC_NBR,
LINC_NBR,
RIGHTS_IND,
NON_PATENT_IND,
MUNC_CODE,
PROPRTY_PARCEL_ID,
LincCount,
'LTOX' as LTOXHTOX
from vwMinLTOXLINC
union all
SelectTITLE_REFRNC_NBR,
LINC_NBR,
RIGHTS_IND,
NON_PATENT_IND,
MUNC_CODE,
PROPRTY_PARCEL_ID,
LincCount,
'HTOX' as LTOXHTOX
from vwMinHTOXLINC
vwMinHTOXLINC:
Select DHT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
HTXLINC.DHL_LINC_NBR as LINC_NBR,
DHL_RIGHTS_IND as RIGHTS_IND,
DPR_NON_PATENT_IND as NON_PATENT_IND,
DPR_MUNC_CODE MUNC_CODE,
DPR_PROPRTY_PARCEL_ID PROPRTY_PARCEL_ID,
(Select Count(*) from ALTA_Staging..DB_HTXLINC HTXLINCA
WHERE HTXLINCA.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC) as LincCount
from ALTA_Staging..DB_HTXLINC HTXLINC
inner join ALTA_Staging..DB_HTXTITL HTXTITL on HTXTITL.P_K = HTXLINC.F_K_HTXTITL_HTXLINC
left outer join ALTA_Staging..DB_PROPRTY PROPRTY on PROPRTY.DPR_LINC_NBR = HTXLINC.DHL_LINC_NBR
where HTXLINC.DHL_LINC_NBR = (Select Min(DHL_LINC_NBR) FROM ALTA_Staging..DB_HTXLINC HTXLINCB
WHERE HTXLINCB.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC)
vwMinLTOXLINC:
Select DXT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
LTXLINC.DXL_LINC_NBR as LINC_NBR,
DXL_RIGHTS_IND as RIGHTS_IND,
DPR_NON_PATENT_IND as NON_PATENT_IND,
DPR_MUNC_CODE as MUNC_CODE,
DPR_PROPRTY_PARCEL_ID as PROPRTY_PARCEL_ID,
(Select Count(*) from ALTA_Staging..DB_LTXLINC LTXLINCA
WHERE LTXLINCA.F_K_LTXTITL_LTXLINC = LTXLINC.F_K_LTXTITL_LTXLINC) as LincCount
from ALTA_Staging..DB_LTXLINC LTXLINC
inner join ALTA_Staging..DB_LTXTITL LTXTITL on LTXTITL.P_K = LTXLINC.F_K_LTXTITL_LTXLINC
left outer join ALTA_Staging..DB_PROPRTY PROPRTY on PROPRTY.DPR_LINC_NBR = LTXLINC.DXL_LINC_NBR
where LTXLINC.DXL_LINC_NBR = (Select Min(DXL_LINC_NBR) FROM ALTA_Staging..DB_LTXLINC LTXLINCB
WHERE LTXLINCB.F_K_LTXTITL_LTXLINC = LTXLINC.F_K_LTXTITL_LTXLINC)
vwTitl:
SelectDTI_TITLE_REFRNC_NBR,
DTI_TITLE_STATUS_IND,
DTI_CREATE_DATE,
DTI_EXPIRY_DATE,
DRE_TITLE_REFRNC_NBR
from ALTA_Staging..DB_TITLE TITLE
left outer join ALTA_Staging..DB_REFTITL REFTITL on REFTITL.F_K_TITLE_REFTITL = TITLE.P_K
November 29, 2007 at 1:10 pm
I don't see anything obvious that explains the diff between SQL2K and SQL2K5. However, I do see some less than optimal code that could be fixed in both versions:
Select DHT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
HTXLINC.DHL_LINC_NBR as LINC_NBR,
DHL_RIGHTS_IND as RIGHTS_IND,
DPR_NON_PATENT_IND as NON_PATENT_IND,
DPR_MUNC_CODE MUNC_CODE,
DPR_PROPRTY_PARCEL_ID PROPRTY_PARCEL_ID,
(Select Count(*) from ALTA_Staging..DB_HTXLINC HTXLINCA
WHERE HTXLINCA.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC) as LincCount
from ALTA_Staging..DB_HTXLINC HTXLINC
inner join ALTA_Staging..DB_HTXTITL HTXTITL on HTXTITL.P_K = HTXLINC.F_K_HTXTITL_HTXLINC
left outer join ALTA_Staging..DB_PROPRTY PROPRTY on PROPRTY.DPR_LINC_NBR = HTXLINC.DHL_LINC_NBR
where HTXLINC.DHL_LINC_NBR = (Select Min(DHL_LINC_NBR) FROM ALTA_Staging..DB_HTXLINC HTXLINCB
WHERE HTXLINCB.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC)
A (SELECT ...) within a SELECT is essentially a cursor. Change it to a join on a derived table and get the sub-select out of the main SELECT.
November 29, 2007 at 1:21 pm
Both vwMinLTOXLINC and vwMinHTOXLINC contain multiple correlated sub-queries. The LINC_COUNT is causing the DB_HTXLINC and DB_LTXLINC to be scanned once for EACH row in the outer view. That's going be VERY inefficient in SQL Server.
Rewrite (just one as an illustration):
Select
DHT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
HTXLINC.DHL_LINC_NBR as LINC_NBR,
DHL_RIGHTS_IND as RIGHTS_IND,
DPR_NON_PATENT_IND as NON_PATENT_IND,
DPR_MUNC_CODE MUNC_CODE,
DPR_PROPRTY_PARCEL_ID PROPRTY_PARCEL_ID,
Linccount
from ALTA_Staging..DB_HTXLINC HTXLINC
inner join
ALTA_Staging..DB_HTXTITL HTXTITL
on HTXTITL.P_K = HTXLINC.F_K_HTXTITL_HTXLINC
inner join
(Select Count(*) from ALTA_Staging..DB_HTXLINC group by F_K_HTXTITL_HTXLINC) HTXLINCA
ON HTXLINCA.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC
inner join
(Select Min(DHL_LINC_NBR) as MinDlincNBR FROM ALTA_Staging..DB_HTXLINC group by F_K_HTXTITL_HTXLINC) HTXLINCB
on HTXLINCB.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC
left outer join
ALTA_Staging..DB_PROPRTY PROPRTY
on PROPRTY.DPR_LINC_NBR = HTXLINC.DHL_LINC_NBR
where HTXLINC.DHL_LINC_NBR = MinDlincNBR
See if this one runs faster. If it does - re-write the LTXlinc one the same way.
You might want to read up on the difference between correlated sub-queries and derived tables. structuring sub-queries to run as a derived table will almost invariably result in MUCH faster running.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 29, 2007 at 1:22 pm
Could you provide an example on what you mean by getting the select out into a derived table?
Thanks!!!
November 29, 2007 at 1:24 pm
I'm thinking you were talking to PW - check out my example - should help a little.
The trick is not to have a reference to the OUTER query in the INNER query. That allows the INNER query to be run just once, and then related to the other values based on the JOIN.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 29, 2007 at 1:30 pm
Hi Matt,
I tried yours...but it complained.
First about the ) at the end of
(Select Min(DHL_LINC_NBR) as MinDlincNBR FROM ALTA_Staging..DB_HTXLINC group by F_K_HTXTITL_HTXLINC) HTXLINCB on HTXLINCB.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC).
I removed that...but then it complained again:
Msg 8155, Level 16, State 2, Procedure vwMinHTOXLinc, Line 20
No column was specified for column 1 of 'HTXLINCA'.
Msg 207, Level 16, State 1, Procedure vwMinHTOXLinc, Line 30
Invalid column name 'F_K_HTXTITL_HTXLINC'.
Msg 207, Level 16, State 1, Procedure vwMinHTOXLinc, Line 32
Invalid column name 'F_K_HTXTITL_HTXLINC'.
Msg 207, Level 16, State 1, Procedure vwMinHTOXLinc, Line 26
Invalid column name 'Linccount'.
November 29, 2007 at 1:35 pm
Oops - forgot one thing
Select
DHT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
HTXLINC.DHL_LINC_NBR as LINC_NBR,
DHL_RIGHTS_IND as RIGHTS_IND,
DPR_NON_PATENT_IND as NON_PATENT_IND,
DPR_MUNC_CODE MUNC_CODE,
DPR_PROPRTY_PARCEL_ID PROPRTY_PARCEL_ID,
Linccount
from ALTA_Staging..DB_HTXLINC HTXLINC
inner join
ALTA_Staging..DB_HTXTITL HTXTITL
on HTXTITL.P_K = HTXLINC.F_K_HTXTITL_HTXLINC
inner join
(Select Count(*) as Linccount --I forgot the label LincCount right here
from ALTA_Staging..DB_HTXLINC group by F_K_HTXTITL_HTXLINC) HTXLINCA
ON HTXLINCA.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC
inner join
(Select Min(DHL_LINC_NBR) as MinDlincNBR FROM ALTA_Staging..DB_HTXLINC group by F_K_HTXTITL_HTXLINC) HTXLINCB
on HTXLINCB.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC
left outer join
ALTA_Staging..DB_PROPRTY PROPRTY
on PROPRTY.DPR_LINC_NBR = HTXLINC.DHL_LINC_NBR
where HTXLINC.DHL_LINC_NBR = MinDlincNBR
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 29, 2007 at 1:40 pm
Hi Matt,
We are getting there.
Now I get this error:
Msg 207, Level 16, State 1, Procedure vwMinHTOXLinc, Line 32
Invalid column name 'F_K_HTXTITL_HTXLINC'.
Msg 207, Level 16, State 1, Procedure vwMinHTOXLinc, Line 35
Invalid column name 'F_K_HTXTITL_HTXLINC'.
November 29, 2007 at 1:49 pm
Okay...I change those two views as per Matt's suggestion. It didn't help. 🙁
CPU Utlization was still very high and the query was very slow to return.
Jessica
November 29, 2007 at 1:52 pm
The excution plan is still showing an index scan in 2005 vs a seek in 2000. 🙁 It's also got an estimated row count in the 100,000s where in 2000 it would have 1.
I have a case open with MS to help with the optimizer but keep the suggestions coming. This is all very helpful.
November 29, 2007 at 2:07 pm
question - is the execution on the view we were working on (old versus new) any faster?
Just curious, because the multiple nested views might be the issue.
It looks to me that your final product sould be returning a handful of records at mosts, so it might be worth looking at what could be done to hcange the process so that the views only return what is used in the select.
With this much nesting - after a while - the indexing is no longer going to be used (meaning - it will be used on the lowest level only, but as you move from the inner queries to the outer queries, the results will be treated as if they were unindexed tables, so, hash joins and scans everywhere.
for example... What would it take for that view we were working on to return just the rows relevant to a single @titlenumber?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply