Vấn đề với SQL Server Express
Chào các bác!
Bí bách và ức chế quá mới lên đây xin thỉnh giáo các bác cao thủ giúp em vụ này. Chả là em làm web có bộ CMS viết trên CSDL SQL Server 2005.
Bình thường những lần trước thuê các host có hỗ trợ SQL Server 2005 thì vẫn chạy được. Xong khi thuê host bên mắt bão, thì bên đó restore dùm cho database, chạy được 1 lúc rất nhanh, rồi 2 tiếng sau không chạy được với các chức năng có truy gọi Stored Procedure có truy vấn nhiều một chút (Các host trước thì không sao)
Kiểm tra thì thấy mắt bão sử dụng sql server express, và các chức năng có sử dụng truy vấn nhiều 1 là bị lỗi Timeout Expred. Dưới đây là 1 trong những Stored Procedure bị lỗi Timeout, các báo chỉ dùm với (kêu hỗ trợ mắt bão chưa thấy hồi âm)
<code>
IF @order = 1
BEGIN
-- display photo file
IF @tid = 2 And @starred=1
BEGIN
SELECT dbo.Advertises.*, dbo.Directories.CreationTime, dbo.Directories.CreationByName, dbo.Directories.ParentID
FROM dbo.Advertises INNER JOIN dbo.Directories ON dbo.Advertises.DirectoryID = dbo.Directories.DirectoryID
WHERE (CAST(dbo.Advertises.CategoryID AS varchar(4)) LIKE @sCategory) AND
dbo.Directories.ToFrontPage = @starred AND
dbo.Directories.IsTrash = 0 AND
dbo.Directories.IsLocked = 0 AND
dbo.Directories.IsCheckOut = 1
ORDER BY dbo.Directories.CreationTime DESC
END
IF @tid = 2 And @starred=0
BEGIN
SELECT dbo.Advertises.*, dbo.Directories.CreationTime, dbo.Directories.CreationByName, dbo.Directories.ParentID
FROM dbo.Advertises INNER JOIN dbo.Directories ON dbo.Advertises.DirectoryID = dbo.Directories.DirectoryID
WHERE (CAST(dbo.Advertises.CategoryID AS varchar(4)) LIKE @sCategory) AND
dbo.Directories.IsTrash = 0 AND
dbo.Directories.IsLocked = 0 AND
dbo.Directories.IsCheckOut = 1
ORDER BY dbo.Directories.CreationTime DESC
END
-- display news file
IF @tid = 3 And @starred=1
BEGIN
SELECT dbo.News.*, dbo.Directories.CreationTime, dbo.Directories.CreationByName, dbo.Directories.ParentID
FROM dbo.News INNER JOIN dbo.Directories ON dbo.News.DirectoryID = dbo.Directories.DirectoryID
WHERE (CAST(dbo.News.CategoryID AS varchar(4)) LIKE @sCategory) AND
dbo.Directories.ToFrontPage = @starred AND
dbo.Directories.IsTrash = 0 AND
dbo.Directories.IsLocked = 0 AND
dbo.Directories.IsCheckOut = 1
ORDER BY dbo.Directories.CreationTime DESC
END
IF @tid = 3 And @starred=0
BEGIN
SELECT dbo.News.*, dbo.Directories.CreationTime, dbo.Directories.CreationByName, dbo.Directories.ParentID
FROM dbo.News INNER JOIN dbo.Directories ON dbo.News.DirectoryID = dbo.Directories.DirectoryID
WHERE (CAST(dbo.News.CategoryID AS varchar(4)) LIKE @sCategory) AND
dbo.Directories.IsTrash = 0 AND
dbo.Directories.IsLocked = 0 AND
dbo.Directories.IsCheckOut = 1
ORDER BY dbo.Directories.CreationTime DESC
END
-- display link file
IF @tid = 4 And @starred=1
BEGIN
SELECT dbo.Links.*, dbo.Directories.CreationTime, dbo.Directories.CreationByName, dbo.Directories.ParentID
FROM dbo.Links INNER JOIN dbo.Directories ON dbo.Links.DirectoryID = dbo.Directories.DirectoryID
WHERE (CAST(dbo.Links.CategoryID AS varchar(4)) LIKE @sCategory) AND
dbo.Directories.ToFrontPage = @starred AND
dbo.Directories.IsTrash = 0 AND
dbo.Directories.IsLocked = 0 AND
dbo.Directories.IsCheckOut = 1
ORDER BY dbo.Directories.CreationTime DESC
END
IF @tid = 4 And @starred=0
BEGIN
SELECT dbo.Links.*, dbo.Directories.CreationTime, dbo.Directories.CreationByName, dbo.Directories.ParentID
FROM dbo.Links INNER JOIN dbo.Directories ON dbo.Links.DirectoryID = dbo.Directories.DirectoryID
WHERE (CAST(dbo.Links.CategoryID AS varchar(4)) LIKE @sCategory) AND
dbo.Directories.IsTrash = 0 AND
dbo.Directories.IsLocked = 0 AND
dbo.Directories.IsCheckOut = 1
ORDER BY dbo.Directories.CreationTime DESC
END
END
</code>
Các bác cao thủ lên tiếng giúp em với, hix.
[=========> Bổ sung bài viết <=========]
À dạng procedure này cũng bị:
DECLARE @sFrontPage varchar(3)
DECLARE @sCheckOut varchar(3)
DECLARE @sPublish varchar(3)
DECLARE @sCategory varchar(3)
DECLARE @sSection varchar(3)
IF @fp = 2
SET @sFrontPage = '%'
ELSE
SET @sFrontPage = CAST(@fp As varchar(3))
IF @check = 2
SET @sCheckOut = '%'
ELSE
SET @sCheckOut = CAST(@check As varchar(3))
IF @pub = 2
SET @sPublish = '%'
ELSE
SET @sPublish = CAST(@pub As varchar(3))
IF @cat = -1
SET @sCategory = '%'
ELSE
SET @sCategory = CAST(@cat As varchar(4))
IF @sec = -1
SET @sSection = '%'
ELSE
SET @sSection = CAST(@sec As varchar(4))
IF @order = 0
BEGIN
SELECT TOP 100 PERCENT dbo.Directories.*, dbo.FileTypes.Name AS FileType, dbo.FileTypes.IconURL, dbo.FileTypes.IsBrowser, dbo.Modules.LinkURL AS EditFileURL, dbo.Directories.ParentID,
dbo.News.PictureURL, dbo.News.Excerpt, dbo.News.Hits, dbo.News.Authors,
dbo.mosCategories.CategoryID As CategoryID, dbo.mosCategories.Name As CategoryName,
dbo.mosSections.SectionID As SectionID, dbo.mosSections.Name As SectionName
FROM dbo.Directories INNER JOIN
dbo.FileTypes ON dbo.Directories.FileTypeID = dbo.FileTypes.FileTypeID INNER JOIN
dbo.Modules ON dbo.FileTypes.ModuleID = dbo.Modules.ModuleID INNER JOIN
dbo.News ON dbo.Directories.DirectoryID = dbo.News.DirectoryID INNER JOIN
dbo.mosCategories ON dbo.News.CategoryID = dbo.mosCategories.CategoryID INNER JOIN
dbo.mosSections ON dbo.mosCategories.SectionID = dbo.mosSections.SectionID
WHERE (CAST(dbo.Directories.ToFrontPage AS varchar(3)) LIKE @sFrontPage) AND
(CAST(dbo.Directories.IsCheckOut AS varchar(3)) LIKE @sCheckOut) AND
(CAST(dbo.Directories.Visible AS varchar(3)) LIKE @sPublish) AND
(CAST(dbo.mosCategories.CategoryID AS varchar(4)) LIKE @sCategory) AND
(CAST(dbo.mosSections.SectionID AS varchar(4)) LIKE @sSection) AND
(dbo.Directories.IsTrash = 0) AND
(dbo.Directories.FileTypeID = 3) AND
(dbo.Directories.Name LIKE '%'+@name+'%') AND
(dbo.Directories.CreationByName LIKE '%'+@author+'%')
ORDER BY dbo.Directories.VisibleOrder
END
Được sửa bởi alone_hero lúc 10:30 ngày 09-06-2010
Reason: Bổ sung bài viết
IQWEB[.]VN - Xây dựng Website Chuyên Nghiệp.
Bookmarks