June 25, 2009 at 12:14 pm
I have a huge sql query that takes way to long and bombs out my asp page whare it fills a datagrid. Is there a way to break this query into smaller chunks that keep adding to a datagrid that fills from the query. I can't run stored procedures on it, otheriwse then I need to tag data with datetime and user info, so I am trying to provide real time data. Is there a way to run portions of the query, fill the datagrid and then run another portion and then another porition until all the queries are ran and the datagrid is filled. I want to break out the counts since they are what is taking up the bulk of time and might go faster if I can. thanks
SELECTat.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE, substring(at.POSC, 1,4) as DMOS,
--Get the Gender
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
--Get the value in () in the AUTH_DOC_POSN_TITLE field
CASE WHEN PATINDEX('%_(%',AUTH_DOC_POSN_TITLE) > 0 and PATINDEX('%_)%',AUTH_DOC_POSN_TITLE) > 0 Then
CASE WHEN ISNUMERIC(SUBSTRING(AUTH_DOC_POSN_TITLE,PATINDEX('%_(%',AUTH_DOC_POSN_TITLE)+2, PATINDEX('%_)%',AUTH_DOC_POSN_TITLE) - PATINDEX('%_(%',AUTH_DOC_POSN_TITLE)-1)) = 1 Then
CAST(SUBSTRING(AUTH_DOC_POSN_TITLE,PATINDEX('%_(%',AUTH_DOC_POSN_TITLE)+2, PATINDEX('%_)%',AUTH_DOC_POSN_TITLE) - PATINDEX('%_(%',AUTH_DOC_POSN_TITLE)-1) as Int) ELSE 0 END ELSE 0 END as OS,
--Get the count of current Personnel in assigned positions for ASGN_STR
(Select(Count(ps.SSN_SM)) from PERS_SVCMBR_TBL as ps Inner Join PERS_DUTY_POSN_TBL as dp on ps.MPC = dp.MPC
and ps.ASG_SEQ_NBR = dp.ASG_SEQ_NBR where dp.UPC = at.UPC and dp.AUTH_Para_DSG = at.Auth_Para_Dsg and dp.AUTH_Line_DSG = at.Auth_Line_Dsg
and ps.REC_PREC = '99999999' and ps.POSN_NBR_EXCESS_IND not in ('999F','9991','999C')) as ASGN_STR,
--Get the count of ACN's that have been requested agianst a Paragraph and line number and UIC
(Select(Count(ac.strAcn)) from tblACNREquest ac where ac.strUic = at.UPC and
ac.strPara = at.Auth_Para_Dsg and ac.strLine = at.Auth_Line_Dsg and dtExpire >= getdate() and dtCancelACN is null)as ACN,
--Get the value of allowd Vacancies by AUTH_STR + OS - ASGN - ACN = VAC
--Take care of this in Tables
--Get the Unit type
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as Unit_Type,
--Get the Unit Status
(Select top 1 case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end
from tblStatUIC as st where st.dtExpire > Getdate() and st.strUIC = ut.UPC) as UNIT_Stat
FROMPERS_UNIT_TBL as ut Inner join
PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC
whereat.UPC = '77726' and Substring(at.grade, 1,1) = 'E' And ut.OESTS = 'N'
Group by at.UPC, ut.upc, at.Auth_Para_Dsg, at.Auth_Line_Dsg, ut.ADDR_CITY, at.GRADE, substring(at.POSC, 1,4), at.AUTH_PERS_IDENT,
at.AUTH_DOC_POSN_TITLE, at.AUTH_STR, at.DOC_NBR
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg
June 25, 2009 at 2:36 pm
This query "may" be better:
SELECT
at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
--Get the Gender
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
--Get the value in () in the AUTH_DOC_POSN_TITLE field
CASE WHEN PATINDEX('%_(%',AUTH_DOC_POSN_TITLE) > 0 and PATINDEX('%_)%',AUTH_DOC_POSN_TITLE) > 0 Then
CASE WHEN ISNUMERIC(SUBSTRING(AUTH_DOC_POSN_TITLE,PATINDEX('%_(%',AUTH_DOC_POSN_TITLE)+2, PATINDEX('%_)%',AUTH_DOC_POSN_TITLE) - PATINDEX('%_(%',AUTH_DOC_POSN_TITLE)-1)) = 1 Then
CAST(SUBSTRING(AUTH_DOC_POSN_TITLE,PATINDEX('%_(%',AUTH_DOC_POSN_TITLE)+2, PATINDEX('%_)%',AUTH_DOC_POSN_TITLE) - PATINDEX('%_(%',AUTH_DOC_POSN_TITLE)-1) as Int) ELSE 0 END ELSE 0 END as OS,
ASGN_STR.Count_SSN_SM,
ACN.COUNT_strACN
--Get the value of allowd Vacancies by AUTH_STR + OS - ASGN - ACN = VAC
--Take care of this in Tables
--Get the Unit type
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as Unit_Type,
UNIT_Stat.strStat
FROM
PERS_UNIT_TBL as ut Inner join
PERS_AUTH_STR_TBL as at on
at.UPC = ut.UPC JOIN
--Get the count of current Personnel in assigned positions for ASGN_STR
(
SELECT
dp.UPC,
dp.AUTH_Para_DSG,
dp.AUTH_Line_DSG,
Count(ps.SSN_SM) AS COUNT_SSN_SM
from
PERS_SVCMBR_TBL as ps Inner Join
PERS_DUTY_POSN_TBL as dp on
ps.MPC = dp.MPC and
ps.ASG_SEQ_NBR = dp.ASG_SEQ_NBR
where
ps.REC_PREC = '99999999' and
ps.POSN_NBR_EXCESS_IND not in ('999F','9991','999C')
GROUP BY
dp.UPC,
dp.AUTH_Para_DSG,
dp.AUTH_Line_DSG
) as ASGN_STR ON
ASGN_STR.UPC = at.UPC and
ASGN_STR.AUTH_Para_DSG = at.Auth_Para_Dsg and
ASGN_STR.AUTH_Line_DSG = at.Auth_Line_Dsg JOIN
--Get the count of ACN's that have been requested agianst a Paragraph and line number and UIC
(
SELECT
strUic,
strPara,
strLine,
Count(ac.strAcn) AS COUNT_strAcn
from
tblACNREquest ac
where
dtExpire >= getdate() and
dtCancelACN is NULL
GROUP BY
strUic,
strPara,
strLine
)as ACN ON
ACN.strUic = at.UPC and
ACN.strPara = at.Auth_Para_Dsg and
ACN.strLine = at.Auth_Line_Dsg JOIN
--Get the Unit Status
(
Select top 1
st.strUic,
case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' END AS strStat
from
tblStatUIC as st
where
st.dtExpire > Getdate()
) as UNIT_Stat ON
UNIT_Stat.strUIC = ut.UPC
where
at.UPC = '77726' and
--Substring(at.grade, 1,1) = 'E' And
at.grade LIKE 'E%' -- this should use an index on grade if it exists
ut.OESTS = 'N'
Group by
at.UPC,
ut.upc,
at.Auth_Para_Dsg,
at.Auth_Line_Dsg,
ut.ADDR_CITY, at.GRADE,
substring(at.POSC, 1,4),
at.AUTH_PERS_IDENT,
at.AUTH_DOC_POSN_TITLE,
at.AUTH_STR, at.DOC_NBR
Order by
at.UPC,
at.Auth_Para_Dsg,
at.Auth_Line_Dsg
I replaced your correlated sub-queries with derived tables (selects in the FROM) and changed the WHERE clause to use LIKE instead of the SUBSTRING function so you would be more likely to use an index. Without some test data (see the link in my signature) I can't guarantee the same results nor can I guarantee better performance.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 25, 2009 at 2:40 pm
Greetings Kevin,
You might first want to try modifying the .NET code by adding:
protected void _Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.CommandTimeout = 0;
}
What this will do is stop the SQL query from timing out. Then it will be easier to optimize it.
Have A Good Day.
Terry Steadman
June 25, 2009 at 2:44 pm
And since this is a performance problem, please have a look at this article. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply