I have this view vw_RFQPartVendor
that's based on these two views vw_requestVendor
and vw_vendorEmail
and various other tables.
Every few days, it will cause time out error in my ASP.Net website and all I have to do is rebuild index of the underlying tables in view vw_RFQPartVendor
and the time out error is gone.
What can I do to resolve this? I don't want to rebuild index every now and then.
2016-11-24 08:40:34,319 [10] ERROR BasePage [(null)] - ObjectDataSourceReport_Selected Error System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
vw_RFQPartVendor
83250 rows.
SELECT TOP (100) PERCENT
dbo.SubconJobQ.Regno,
CASE
WHEN userName.userName IS NULL THEN UserAcc
ELSE userName.userName
END AS requesterName,
CASE dbo.SubconJobQ.IsPara
WHEN '1' THEN 'yes'
ELSE 'no'
END AS IsPara,
CASE dbo.SubconJobQ.IsDxf
WHEN '1' THEN 'yes'
ELSE 'no'
END AS IsDxf,
CASE dbo.SubconJobQ.IsPdf
WHEN '1' THEN 'yes'
ELSE 'no'
END AS IsPdf,
dbo.SubconJobQ.Rec_date,
dbo.SubconJobQ.Cov_date,
dbo.SubconJobQ.Status,
dbo.SubconJobQ.Remark,
dbo.SubconJobQ.plant,
dbo.SubconJobQ.doc_support,
CASE
WHEN approverName.userName IS NULL THEN approver
ELSE approverName.userName
END AS approverName,
dbo.SubconJobQ.Comment,
dbo.SubconJobQ.sync_status,
dbo.SubconJobQ.ModuleNo,
CASE dbo.SubconJobQ.req_type
WHEN 'sq' THEN 'SubContracting Part'
WHEN 'sd' THEN 'Resend Drawing'
WHEN 'pq' THEN 'New Part'
ELSE ''
END AS req_type,
dbo.SubconJobQ.projectNo,
dbo.Reg_parts.Partno,
dbo.Reg_parts.Quantity,
dbo.Reg_parts.Part_desc,
dbo.vw_requestVendor.vendorCode,
dbo.vw_vendorEmail.vendorEmails
FROM dbo.SubconJobQ
LEFT OUTER JOIN dbo.Reg_parts
ON dbo.SubconJobQ.Regno = dbo.Reg_parts.Regno
LEFT OUTER JOIN dbo.vw_requestVendor
ON dbo.SubconJobQ.Regno = dbo.vw_requestVendor.Regno
LEFT OUTER JOIN dbo.vw_vendorEmail
ON dbo.vw_requestVendor.vendorCode = dbo.vw_vendorEmail.vendor
AND dbo.SubconJobQ.plant = dbo.vw_vendorEmail.plant
LEFT OUTER JOIN
(
SELECT
user_acc,
userName
FROM dbo.t_user_auth
GROUP BY user_acc, userName
) AS userName
ON userName.user_acc = dbo.SubconJobQ.UserAcc
LEFT OUTER JOIN
(
SELECT
user_acc,
userName
FROM dbo.t_user_auth AS t_user_auth_1
GROUP BY user_acc, userName
) AS approverName
ON approverName.user_acc = dbo.SubconJobQ.approver
ORDER BY dbo.SubconJobQ.Regno, dbo.Reg_parts.Partno, dbo.vw_requestVendor.vendorCode
vw_requestVendor
WITH temp(Regno, vendorCode, vendor) AS
(
SELECT
Regno,
LEFT(CAST(vendor AS NVARCHAR(MAX)), CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';') - 1) AS Expr1,
STUFF(CAST(vendor AS NVARCHAR(MAX)), 1, CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';'), '') AS Expr2
FROM dbo.SubconJobQ
UNION ALL
SELECT
Regno,
LEFT(CAST(vendor AS NVARCHAR(MAX)), CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';') - 1) AS Expr1,
STUFF(CAST(vendor AS NVARCHAR(MAX)), 1, CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';'), '') AS Expr2
FROM temp AS temp_2
WHERE (vendor > '')
)
SELECT TOP (100) PERCENT
Regno,
vendorCode
FROM temp AS temp_1
ORDER BY Regno, vendorCode
vw_vendorEmail
SELECT TOP 100 PERCENT
*
FROM
(
SELECT
outerVendorEmail.comp_code,
outerVendorEmail.plant,
outerVendorEmail.vendor,
STUFF
(
(
SELECT ';' + LTRIM(RTRIM(innerVendorEmail.email))
FROM t_vendor_email AS innerVendorEmail
WHERE innerVendorEmail.status = 1
AND innerVendorEmail.comp_code = outerVendorEmail.comp_code
AND innerVendorEmail.plant = outerVendorEmail.plant
AND innerVendorEmail.vendor = outerVendorEmail.vendor
FOR XML PATH ('')
), 1, 1, ''
) AS [vendorEmails]
FROM t_vendor_email AS outerVendorEmail
) AS vendorEmails
GROUP BY vendorEmails.comp_code,
vendorEmails.plant,
vendorEmails.vendor,
vendorEmails.vendorEmails
ORDER BY vendorEmails.comp_code, vendorEmails.plant, vendorEmails.vendor
| TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME | COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME | DOMAIN_CATALOG | DOMAIN_SCHEMA | DOMAIN_NAME | |------------|----------------------|------------------|----------------|-------------|---------------|--------------------------|------------------------|-------------------|-------------------------|---------------|--------------------|-----------------------|----------------------|--------------------|-------------------|------------------|------------------------------|----------------|---------------|-------------| | SubconJobQ | Partnos | 1 | NULL | YES | ntext | 1073741823 | 2147483646 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | Regno | 2 | NULL | NO | char | 12 | 12 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | UserAcc | 3 | NULL | NO | varchar | 50 | 50 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | IsPara | 4 | ((0)) | NO | char | 1 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | IsDxf | 5 | ((0)) | NO | char | 1 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | IsPdf | 6 | ((1)) | NO | char | 1 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | Rec_date | 7 | NULL | NO | smalldatetime | NULL | NULL | NULL | NULL | NULL | 3 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | SubconJobQ | Cov_date | 8 | NULL | YES | smalldatetime | NULL | NULL | NULL | NULL | NULL | 3 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | SubconJobQ | Status | 9 | NULL | YES | char | 1 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | Remark | 10 | NULL | YES | text | 2147483647 | 2147483647 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | plant | 11 | NULL | YES | char | 4 | 4 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | vendor | 12 | NULL | NO | varchar | 200 | 200 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | doc_support | 13 | NULL | YES | char | 1 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | approver | 14 | NULL | YES | varchar | 50 | 50 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | Comment | 15 | NULL | YES | nvarchar | 100 | 200 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | sync_status | 16 | NULL | YES | char | 1 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | UserAccEmail | 17 | NULL | YES | nchar | 100 | 200 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | ModuleNo | 18 | NULL | YES | nchar | 20 | 40 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | manager_approver | 19 | NULL | YES | nvarchar | 50 | 100 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | req_type | 20 | NULL | YES | char | 2 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | purchasing_manager | 21 | NULL | YES | nvarchar | 50 | 100 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | change_buyer | 22 | NULL | YES | nvarchar | 50 | 100 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | change_buyer_manager | 23 | NULL | YES | nvarchar | 50 | 100 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | SubconJobQ | projectNo | 24 | NULL | YES | char | 20 | 20 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL |
| TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME | COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME | DOMAIN_CATALOG | DOMAIN_SCHEMA | DOMAIN_NAME | |----------------|-------------|------------------|----------------|-------------|-----------|--------------------------|------------------------|-------------------|-------------------------|---------------|--------------------|-----------------------|----------------------|--------------------|-------------------|------------------|------------------------------|----------------|---------------|-------------| | t_vendor_email | comp_code | 1 | NULL | YES | nchar | 4 | 8 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | t_vendor_email | plant | 2 | NULL | YES | nchar | 4 | 8 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | t_vendor_email | vendor | 3 | NULL | YES | nchar | 10 | 20 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | t_vendor_email | email | 4 | NULL | YES | nchar | 100 | 200 | NULL | NULL | NULL | NULL | NULL | NULL | UNICODE | NULL | NULL | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | | t_vendor_email | status | 5 | NULL | YES | int | NULL | NULL | 10 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
Statistics for INDEX 'ix_RecDate' table 'SubconJobQ'. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Name Updated Rows Rows Sampled Steps Density Average Key Length String Index -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ix_RecDate Nov 24 2016 8:41AM 15099 15099 177 0.9686973 22.82734 NO All Density Average Length Columns -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 6.879472E-05 4 Rec_date 6.622955E-05 16 Rec_date, Regno 6.622955E-05 22.82734 Rec_date, Regno, vendor Histogram Steps RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2010-02-26 4:40:00 PM 0 1 0 1 2010-03-02 3:12:00 PM 30 2 30 1
Statistics for INDEX 'PK_SubconJobQ' table 'SubconJobQ'. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Name Updated Rows Rows Sampled Steps Density Average Key Length String Index -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PK_SubconJobQ Nov 24 2016 8:41AM 15099 15099 148 1 18.82734 YES All Density Average Length Columns -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 6.622955E-05 12 Regno 6.622955E-05 18.82734 Regno, vendor Histogram Steps RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- D00000000016 0 1 0 1 D00000000111 94 1 94 1
sp_refreshview view_name
to generate a new plan. Also, there is a table scan onReg_parts
-- no index ?? Create your viewswith schemabinding
option to help optimizer help your queried :-)