Long time execution of Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Long time execution of Query

Author

Topic

pantinosm
Starting Member

9 Posts

Posted - 2007-07-08 : 11:27:20

I have a asp page in which i do a search in a sql database using asp. The problem is that i get the results 10-15 seconds, that is a long time. Can anybody suggest an optimization of the code below?

strSQL = "Select TOP 5000 * from tbllinks where (title like '%" & ArraySTR(0) & "%'"
 
 for x = 0 to Wordcount - 1
  if  lcase(ArraySTR(x)) <> "and" then 
   if x > 0 then 
    strSQL = StrSQL & " AND title like '%" & ArraySTR(x) & "%'"
   end if
  end if
 next


strSQL = StrSQL & ") OR (Description like '%" & ArraySTR(0) & "%'"
for x = 0 to Wordcount - 1
  if  lcase(ArraySTR(x)) <> "and" then 
   if x > 0 then 
    strSQL = StrSQL & " AND Description like '%" & ArraySTR(x) & "%'"
   end if
  end if
 next

strSQL = StrSQL & ") OR (URL like '%" & ArraySTR(0) & "%'"
for x = 0 to Wordcount - 1
  if  lcase(ArraySTR(x)) <> "and" then 
   if x > 0 then 
    strSQL = StrSQL & " AND URL like '%" & ArraySTR(x) & "%'"
   end if
  end if
 next

 strSQL = StrSQL & ");"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 11:44:32

No way, since you are using dynamic SQL.

I would use a "word search table" in the database and calling a stored procedure with current user id
1) DELETE FROM WordTable WHERE UserID = <Current UserID>
2) INSERT WordTable VALUES (ArrayStr(x), <Current UserID>)
3) Call a stored procedure with parameter <Current UserID>

CREATE PROCEDURE dbo.uspWordSearch
(
	@CurrentUserID INT
)
AS

SET NOCOUNT ON

DECLARE	@Items INT

SELECT	@Items = COUNT(*)
FROM	dbo.WordTable
WHERE	UserID = @CurrentUserID

SELECT TOP 5000	s.*
FROM		dbo.tblLinks AS s
INNER JOIN	(
			SELECT		l.<PrimaryKey>
			FROM		tblLinks AS l
			INNER JOIN	WordTable AS w ON w.Word = l.Title
			WHERE		w.UserID = @CurrentUserID
			GROUP BY	l.<PrimaryKey>
			HAVING		COUNT(*) = @Items

			UNION

			SELECT		l.<PrimaryKey>
			FROM		tblLinks AS l
			INNER JOIN	WordTable AS w ON w.Word = l.Description
			WHERE		w.UserID = @CurrentUserID
			GROUP BY	l.<PrimaryKey>
			HAVING		COUNT(*) = @Items

			UNION

			SELECT		l.<PrimaryKey>
			FROM		tblLinks AS l
			INNER JOIN	WordTable AS w ON w.Word = l.URL
			WHERE		w.UserID = @CurrentUserID
			GROUP BY	l.<PrimaryKey>
			HAVING		COUNT(*) = @Items
		) AS d ON d.<PrimaryKey> = s.<PrimaryKey>

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources