|
 |
asp_databases thread: Help!to improve the performance of a Search Engine
Message #1 by Navani <navanid@y...> on Fri, 24 Nov 2000 02:35:24 -0800 (PST)
|
|
Hello All,
I have some performance problem with sqlserver.
There are more than 2,00,000 records in the database
and keep on growing.
So sql queries which i am executing frm the ASP
script(Search Engine) becomes very slow. I have
explained my problem
in detail as below:
What should i do to improve the retrival speed of
sql
query? I need to maintain any index server? If yes
then, How?
Anyone can help me?
Here I am explained the problem in detail. kindly
have a look at this,
There are three Tables in the Database.They are
AuthorTB, ArticleTB and Auth-ArtiTB.
The structure of Each table is as follows:
1)AuthorTB
AuthorId (Pk)
AuthorName
2)Auth-artiTB (PK) combinational Primary key
ArticleId (FK)
AuthorId (Fk)
3)ArticleTB
ArticleId (PK)
ArticleName,etc.,
Full-Text Indixing has been created on Articlename
and authorname. Clustered Index has been created on
AuthorID and ArticleId of ArticleTB and Auth-ArtiTB.
In Asp, i have written a Search Engine, and it'll
search on ArticleName, Authornames with boolean
and,or,not conditions and some limit fields also.
This search interface is working fine for around 1
GB Records in the Table.
As the Size of the Database is keep on growing, Search
Interface started becoming slow.
Kindly give me the solution for this problem that
How to improve the speed of the search Interface for
large Number of Records in the Database.
Please reply me asap as it's my immediate
requirement.
Note: Following is the code of search Interface
searchResult.asp for your kind reference.
Thank you,
Regards,
Navani
This is the part of my searchResults.asp program. Here
shown How i am constructing the search Query. I am
using temperory table also, since i have to compare 3
tables.
<% Option Explicit
Response.Expires=0
Response.Buffer = true
'Declaring the variables
Dim search1,search2,search3,search4
Dim type1,type2,type3,type4
Dim logical1,logical2,logical3
Dim strTable, strSearch
'Dynamially constructing the Query
strTable="#temp"
If lcase(type1)="author" Then
strSQL="select ArticleID into #Temp from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search1 & "%')"
strSearch= ucase(Search1)
ElseIf lcase(type1)="article" Then
strSQL="select ArticleId into #Temp from
ArticleTB where
ArticleName like '%" & Search1 &"%'"
strSearch= ucase(Search1)
End If
conSQL.Execute strSQl
If search2<>"" Then
strTable="#temp1"
If lcase(type2)="author" Then
strSearch= strSearch & " " & lcase(logical1)
& " " & ucase(Search2)
Select case lcase(logical1)
Case "and"
strsql="Select articleid into #temp1 from
articletb where
articleid in ("
strSQL=strsql & "select a.ArticleID from
[auth-artiTB] a, #temp
t where a.AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search2 & "%') and a.articleid=t.articleid
)"
Case "or"
conSQL.Execute "select articleid into #temp1
from #temp"
strSQl="insert #temp1 select articleid from
[auth-artiTB]
where Authorid in (select authorid from authortb where
authorname like '" &
Search2 & "%')"
Case "not"
strSQl="Select articleid into #temp1 from
#temp where
articleid not in "
strSQL=strsql & "(select articleid from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search2 & "%'))"
End Select
ElseIf lcase(type2)="article" Then
strSearch= strSearch & " " &
lcase(logical1) & " " & ucase(Search2)
Select case lcase(logical1)
Case "and"
strSQL="Select a.articleID into #temp1
from ArticleTB a, #temp
t where a.ArticleName like '%" & Search2 & "%' and
a.articleid=t.articleid "
Case "or"
conSQL.Execute "select
articleid into #temp1 from #temp"
strSQl= "insert #temp1 select
articleid from articletb where
articlename like '%" & Search2 & "%'"
Case "not"
strSQL="Select articleID into #temp1 from
#temp where
articleid not in (select articleid from articletb
where ArticleName like '%"
& Search2 & "%') "
End Select
End If
conSQL.Execute strsql
conSQL.Execute "Drop table #temp"
End If
If search3<>"" Then
strTable="#temp"
If lcase(type3)="author" Then
strSearch= strSearch & " " &
lcase(logical2) & " " & ucase(Search3)
Select case lcase(logical2)
Case "and"
strsql="Select articleid into #temp from
articletb where
articleid in ("
strSQL=strsql & "select a.ArticleID from
[auth-artiTB] a, #temp1
t where a.AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search3 & "%') and a.articleid=t.articleid
)"
Case "or"
conSQL.Execute "select articleid into #temp
from #temp1"
strsql="insert #temp select articleid from
[auth-artiTB] where
Authorid in (select authorid from authortb where
authorname like '" &
Search3 & "%')"
Case "not"
strSQl="Select articleid into #temp from
#temp1 where
articleid not in ("
strSQL=strsql & "(select articleid from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search3 & "%'))"
End Select
ElseIf lcase(type3)="article" Then
strSearch= strSearch & " " &
lcase(logical2) & " " & ucase(Search3)
Select case lcase(logical2)
Case "and"
strSQL="Select a.articleID into #temp
from ArticleTB a, #temp1
t where a.ArticleName like '%" & Search3 & "%' and
a.articleid=t.articleid "
Case "or"
conSQL.Execute "select articleid into #temp
from #temp1"
strsql="insert #temp select articleid from
articletb where
articlename like '%" & Search3 & "%'"
Case "not"
strSQL="Select articleID into #temp from
#temp1 where
articleid not in (select articleid from articletb
where ArticleName like
'%" & Search3 & "%')"
End Select
End If
conSQL.Execute strsql
conSQL.Execute "Drop table #temp1"
End If
If search4<>"" Then
strTable="#temp1"
If lcase(type4)="author" Then
strSearch= strSearch & " " &
lcase(logical3) & " " &
ucase(Search4)
Select case lcase(logical3)
Case "and"
strsql="Select articleid into #temp1 from
articletb where
articleid in ("
strSQL=strsql & "select a.ArticleID from
[auth-artiTB] a, #temp
t where a.AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search4 & "%') and a.articleid=t.articleid
)"
Case "or"
conSQL.Execute "select articleid into #temp1
from #temp"
strsql="insert #temp1 select articleid from
[auth-artiTB]
where Authorid in (select authorid from authortb where
authorname like '" &
Search4 & "%')"
Case "not"
strsql="Select articleid into #temp from
#temp1 where
articleid not in ("
strSQL=strsql & "(select articleid from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
not like '" & Search4 & "%')"
End Select
ElseIf lcase(type4)="article" Then
strSearch= strSearch & " " &
lcase(logical3) & lcase(Search4)
Select case lcase(logical3)
Case "and"
strSQL="Select a.articleID into #temp1
from ArticleTB a, #temp
t where a.ArticleName like '%" & Search4 & "%' and
a.articleid=t.articleid "
Case "or"
conSQL.Execute "select articleid into
#temp1 from #temp"
strsql="insert #temp1 select articleid from
articletb where
articlename like '%" & Search4 & "%'"
Case "not"
strSQL="Select articleID into #temp1 from
#temp where
articleid not in (select articleid from ArticleTB
where ArticleName like
'%" & Search4 & "%')"
End Select
End If
conSQL.Execute strsql
conSQL.Execute "Drop table #temp"
End If
strSQL="Select
ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
Journal_URL,volumeno, "
strSQL=strSQL & "issueno,yrofpub,pagn from
ArticleTB where Articleid
in (Select articleid from " & strTable
strSQL=strSQL & ") group by
ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
Journal_URL,volumeno,"
strSQL=strSQL & "issueno,yrofpub,pagn"
'Response.Write strsql
'Response.End()
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
Transitional//EN">
On Error Resume Next
rsSQL.Open strSql,conSQL,1,2
If Err.number <> 0 Then
Response.Write err.description & "<h3>:
Sorry, Could not able
to process your request, please try again later or
contact Us!</h3>"
rsSQL.Close
conSQL.Close
set rsSQl=nothing
set conSQL=nothing
Response.End()
End If
=====
With luv,
Navani
__________________________________________________
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/
Message #2 by "Toby Anscombe" <tanscombe@m...> on Fri, 24 Nov 2000 13:06:32 -0000
|
|
Use custom COM components to handle data access - even if you just use them
to return a resultset or a command.
Don't use dynamic SQL - use a stored proc instead
Do you really have to search this way ? why not just do an inner join on all
tables and search on the fields you need ??
Without knowing too much about your system, dare I say it seems to be a bit
over-engineered ??
Regards
T
-----Original Message-----
From: Navani [mailto:navanid@y...]
Sent: 24 November 2000 10:35
To: ASP Databases
Cc: navanid@y...
Subject: [asp_databases] Help!to improve the performance of a Search
Engine
Hello All,
I have some performance problem with sqlserver.
There are more than 2,00,000 records in the database
and keep on growing.
So sql queries which i am executing frm the ASP
script(Search Engine) becomes very slow. I have
explained my problem
in detail as below:
What should i do to improve the retrival speed of
sql
query? I need to maintain any index server? If yes
then, How?
Anyone can help me?
Here I am explained the problem in detail. kindly
have a look at this,
There are three Tables in the Database.They are
AuthorTB, ArticleTB and Auth-ArtiTB.
The structure of Each table is as follows:
1)AuthorTB
AuthorId (Pk)
AuthorName
2)Auth-artiTB (PK) combinational Primary key
ArticleId (FK)
AuthorId (Fk)
3)ArticleTB
ArticleId (PK)
ArticleName,etc.,
Full-Text Indixing has been created on Articlename
and authorname. Clustered Index has been created on
AuthorID and ArticleId of ArticleTB and Auth-ArtiTB.
In Asp, i have written a Search Engine, and it'll
search on ArticleName, Authornames with boolean
and,or,not conditions and some limit fields also.
This search interface is working fine for around 1
GB Records in the Table.
As the Size of the Database is keep on growing, Search
Interface started becoming slow.
Kindly give me the solution for this problem that
How to improve the speed of the search Interface for
large Number of Records in the Database.
Please reply me asap as it's my immediate
requirement.
Note: Following is the code of search Interface
searchResult.asp for your kind reference.
Thank you,
Regards,
Navani
This is the part of my searchResults.asp program. Here
shown How i am constructing the search Query. I am
using temperory table also, since i have to compare 3
tables.
<% Option Explicit
Response.Expires=0
Response.Buffer = true
'Declaring the variables
Dim search1,search2,search3,search4
Dim type1,type2,type3,type4
Dim logical1,logical2,logical3
Dim strTable, strSearch
'Dynamially constructing the Query
strTable="#temp"
If lcase(type1)="author" Then
strSQL="select ArticleID into #Temp from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search1 & "%')"
strSearch= ucase(Search1)
ElseIf lcase(type1)="article" Then
strSQL="select ArticleId into #Temp from
ArticleTB where
ArticleName like '%" & Search1 &"%'"
strSearch= ucase(Search1)
End If
conSQL.Execute strSQl
If search2<>"" Then
strTable="#temp1"
If lcase(type2)="author" Then
strSearch= strSearch & " " & lcase(logical1)
& " " & ucase(Search2)
Select case lcase(logical1)
Case "and"
strsql="Select articleid into #temp1 from
articletb where
articleid in ("
strSQL=strsql & "select a.ArticleID from
[auth-artiTB] a, #temp
t where a.AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search2 & "%') and a.articleid=t.articleid
)"
Case "or"
conSQL.Execute "select articleid into #temp1
from #temp"
strSQl="insert #temp1 select articleid from
[auth-artiTB]
where Authorid in (select authorid from authortb where
authorname like '" &
Search2 & "%')"
Case "not"
strSQl="Select articleid into #temp1 from
#temp where
articleid not in "
strSQL=strsql & "(select articleid from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search2 & "%'))"
End Select
ElseIf lcase(type2)="article" Then
strSearch= strSearch & " " &
lcase(logical1) & " " & ucase(Search2)
Select case lcase(logical1)
Case "and"
strSQL="Select a.articleID into #temp1
from ArticleTB a, #temp
t where a.ArticleName like '%" & Search2 & "%' and
a.articleid=t.articleid "
Case "or"
conSQL.Execute "select
articleid into #temp1 from #temp"
strSQl= "insert #temp1 select
articleid from articletb where
articlename like '%" & Search2 & "%'"
Case "not"
strSQL="Select articleID into #temp1 from
#temp where
articleid not in (select articleid from articletb
where ArticleName like '%"
& Search2 & "%') "
End Select
End If
conSQL.Execute strsql
conSQL.Execute "Drop table #temp"
End If
If search3<>"" Then
strTable="#temp"
If lcase(type3)="author" Then
strSearch= strSearch & " " &
lcase(logical2) & " " & ucase(Search3)
Select case lcase(logical2)
Case "and"
strsql="Select articleid into #temp from
articletb where
articleid in ("
strSQL=strsql & "select a.ArticleID from
[auth-artiTB] a, #temp1
t where a.AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search3 & "%') and a.articleid=t.articleid
)"
Case "or"
conSQL.Execute "select articleid into #temp
from #temp1"
strsql="insert #temp select articleid from
[auth-artiTB] where
Authorid in (select authorid from authortb where
authorname like '" &
Search3 & "%')"
Case "not"
strSQl="Select articleid into #temp from
#temp1 where
articleid not in ("
strSQL=strsql & "(select articleid from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search3 & "%'))"
End Select
ElseIf lcase(type3)="article" Then
strSearch= strSearch & " " &
lcase(logical2) & " " & ucase(Search3)
Select case lcase(logical2)
Case "and"
strSQL="Select a.articleID into #temp
from ArticleTB a, #temp1
t where a.ArticleName like '%" & Search3 & "%' and
a.articleid=t.articleid "
Case "or"
conSQL.Execute "select articleid into #temp
from #temp1"
strsql="insert #temp select articleid from
articletb where
articlename like '%" & Search3 & "%'"
Case "not"
strSQL="Select articleID into #temp from
#temp1 where
articleid not in (select articleid from articletb
where ArticleName like
'%" & Search3 & "%')"
End Select
End If
conSQL.Execute strsql
conSQL.Execute "Drop table #temp1"
End If
If search4<>"" Then
strTable="#temp1"
If lcase(type4)="author" Then
strSearch= strSearch & " " &
lcase(logical3) & " " &
ucase(Search4)
Select case lcase(logical3)
Case "and"
strsql="Select articleid into #temp1 from
articletb where
articleid in ("
strSQL=strsql & "select a.ArticleID from
[auth-artiTB] a, #temp
t where a.AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search4 & "%') and a.articleid=t.articleid
)"
Case "or"
conSQL.Execute "select articleid into #temp1
from #temp"
strsql="insert #temp1 select articleid from
[auth-artiTB]
where Authorid in (select authorid from authortb where
authorname like '" &
Search4 & "%')"
Case "not"
strsql="Select articleid into #temp from
#temp1 where
articleid not in ("
strSQL=strsql & "(select articleid from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
not like '" & Search4 & "%')"
End Select
ElseIf lcase(type4)="article" Then
strSearch= strSearch & " " &
lcase(logical3) & lcase(Search4)
Select case lcase(logical3)
Case "and"
strSQL="Select a.articleID into #temp1
from ArticleTB a, #temp
t where a.ArticleName like '%" & Search4 & "%' and
a.articleid=t.articleid "
Case "or"
conSQL.Execute "select articleid into
#temp1 from #temp"
strsql="insert #temp1 select articleid from
articletb where
articlename like '%" & Search4 & "%'"
Case "not"
strSQL="Select articleID into #temp1 from
#temp where
articleid not in (select articleid from ArticleTB
where ArticleName like
'%" & Search4 & "%')"
End Select
End If
conSQL.Execute strsql
conSQL.Execute "Drop table #temp"
End If
strSQL="Select
ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
Journal_URL,volumeno, "
strSQL=strSQL & "issueno,yrofpub,pagn from
ArticleTB where Articleid
in (Select articleid from " & strTable
strSQL=strSQL & ") group by
ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
Journal_URL,volumeno,"
strSQL=strSQL & "issueno,yrofpub,pagn"
'Response.Write strsql
'Response.End()
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
Transitional//EN">
On Error Resume Next
rsSQL.Open strSql,conSQL,1,2
If Err.number <> 0 Then
Response.Write err.description & "<h3>:
Sorry, Could not able
to process your request, please try again later or
contact Us!</h3>"
rsSQL.Close
conSQL.Close
set rsSQl=nothing
set conSQL=nothing
Response.End()
End If
=====
With luv,
Navani
Message #3 by Robert Chartier <rchartierh@a...> on Fri, 24 Nov 2000 08:29:50 -0500
|
|
speaking of a custom COM object check out the FMStocks.com application
provided to us from Microsoft.
download their source, and root through it..you will come across a .CLS
file that contains all of the database access.
use and compile this....it has a number of very useful functions that help
most people, that do not have a full understanding of ADO
this approach is recommended by microsoft, and more importantly by me :)
At 08:06 AM 11/24/00, you wrote:
>Use custom COM components to handle data access - even if you just use them
>to return a resultset or a command.
>Don't use dynamic SQL - use a stored proc instead
>Do you really have to search this way ? why not just do an inner join on all
>tables and search on the fields you need ??
>
>Without knowing too much about your system, dare I say it seems to be a bit
>over-engineered ??
>
>Regards
>
>T
>
>
>
>
>-----Original Message-----
>From: Navani [mailto:navanid@y...]
>Sent: 24 November 2000 10:35
>To: ASP Databases
>Cc: navanid@y...
>Subject: [asp_databases] Help!to improve the performance of a Search
>Engine
>
>
>Hello All,
>
> I have some performance problem with sqlserver.
>There are more than 2,00,000 records in the database
>and keep on growing.
>
> So sql queries which i am executing frm the ASP
>script(Search Engine) becomes very slow. I have
>explained my problem
>in detail as below:
>
> What should i do to improve the retrival speed of
>sql
>query? I need to maintain any index server? If yes
>then, How?
>
> Anyone can help me?
>
>
> Here I am explained the problem in detail. kindly
>have a look at this,
>
> There are three Tables in the Database.They are
>AuthorTB, ArticleTB and Auth-ArtiTB.
>
> The structure of Each table is as follows:
>
> 1)AuthorTB
> AuthorId (Pk)
> AuthorName
> 2)Auth-artiTB (PK) combinational Primary key
> ArticleId (FK)
> AuthorId (Fk)
> 3)ArticleTB
> ArticleId (PK)
> ArticleName,etc.,
>
> Full-Text Indixing has been created on Articlename
>and authorname. Clustered Index has been created on
>AuthorID and ArticleId of ArticleTB and Auth-ArtiTB.
>
>
> In Asp, i have written a Search Engine, and it'll
>search on ArticleName, Authornames with boolean
>and,or,not conditions and some limit fields also.
>
> This search interface is working fine for around 1
>GB Records in the Table.
>
>As the Size of the Database is keep on growing, Search
>Interface started becoming slow.
>
> Kindly give me the solution for this problem that
>How to improve the speed of the search Interface for
>large Number of Records in the Database.
>
> Please reply me asap as it's my immediate
>requirement.
>
>Note: Following is the code of search Interface
>searchResult.asp for your kind reference.
>
>Thank you,
>
>Regards,
>Navani
>
>This is the part of my searchResults.asp program. Here
>shown How i am constructing the search Query. I am
>using temperory table also, since i have to compare 3
>tables.
>
><% Option Explicit
> Response.Expires=0
> Response.Buffer = true
>
> 'Declaring the variables
> Dim search1,search2,search3,search4
> Dim type1,type2,type3,type4
> Dim logical1,logical2,logical3
> Dim strTable, strSearch
>
>'Dynamially constructing the Query
>
> strTable="#temp"
> If lcase(type1)="author" Then
> strSQL="select ArticleID into #Temp from
>[auth-artiTB] where
>AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
>AuthorTB where AuthorName
>like '" & Search1 & "%')"
> strSearch= ucase(Search1)
> ElseIf lcase(type1)="article" Then
> strSQL="select ArticleId into #Temp from
>ArticleTB where
>ArticleName like '%" & Search1 &"%'"
> strSearch= ucase(Search1)
> End If
> conSQL.Execute strSQl
>
> If search2<>"" Then
> strTable="#temp1"
> If lcase(type2)="author" Then
> strSearch= strSearch & " " & lcase(logical1)
>& " " & ucase(Search2)
> Select case lcase(logical1)
> Case "and"
> strsql="Select articleid into #temp1 from
>articletb where
>articleid in ("
> strSQL=strsql & "select a.ArticleID from
>[auth-artiTB] a, #temp
>t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
>AuthorTB where AuthorName
>like '" & Search2 & "%') and a.articleid=t.articleid
>)"
> Case "or"
> conSQL.Execute "select articleid into #temp1
>from #temp"
> strSQl="insert #temp1 select articleid from
>[auth-artiTB]
>where Authorid in (select authorid from authortb where
>authorname like '" &
>Search2 & "%')"
> Case "not"
> strSQl="Select articleid into #temp1 from
>#temp where
>articleid not in "
> strSQL=strsql & "(select articleid from
>[auth-artiTB] where
>AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
>AuthorTB where AuthorName
>like '" & Search2 & "%'))"
> End Select
>
> ElseIf lcase(type2)="article" Then
> strSearch= strSearch & " " &
>lcase(logical1) & " " & ucase(Search2)
> Select case lcase(logical1)
> Case "and"
> strSQL="Select a.articleID into #temp1
>from ArticleTB a, #temp
>t where a.ArticleName like '%" & Search2 & "%' and
>a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select
>articleid into #temp1 from #temp"
> strSQl= "insert #temp1 select
>articleid from articletb where
>articlename like '%" & Search2 & "%'"
> Case "not"
> strSQL="Select articleID into #temp1 from
>#temp where
>articleid not in (select articleid from articletb
>where ArticleName like '%"
>& Search2 & "%') "
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp"
> End If
>
> If search3<>"" Then
> strTable="#temp"
> If lcase(type3)="author" Then
> strSearch= strSearch & " " &
>lcase(logical2) & " " & ucase(Search3)
> Select case lcase(logical2)
> Case "and"
> strsql="Select articleid into #temp from
>articletb where
>articleid in ("
> strSQL=strsql & "select a.ArticleID from
>[auth-artiTB] a, #temp1
>t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
>AuthorTB where AuthorName
>like '" & Search3 & "%') and a.articleid=t.articleid
>)"
> Case "or"
> conSQL.Execute "select articleid into #temp
>from #temp1"
> strsql="insert #temp select articleid from
>[auth-artiTB] where
>Authorid in (select authorid from authortb where
>authorname like '" &
>Search3 & "%')"
> Case "not"
> strSQl="Select articleid into #temp from
>#temp1 where
>articleid not in ("
> strSQL=strsql & "(select articleid from
>[auth-artiTB] where
>AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
>AuthorTB where AuthorName
>like '" & Search3 & "%'))"
> End Select
>
> ElseIf lcase(type3)="article" Then
> strSearch= strSearch & " " &
>lcase(logical2) & " " & ucase(Search3)
> Select case lcase(logical2)
> Case "and"
> strSQL="Select a.articleID into #temp
>from ArticleTB a, #temp1
>t where a.ArticleName like '%" & Search3 & "%' and
>a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select articleid into #temp
>from #temp1"
> strsql="insert #temp select articleid from
>articletb where
>articlename like '%" & Search3 & "%'"
> Case "not"
> strSQL="Select articleID into #temp from
>#temp1 where
>articleid not in (select articleid from articletb
>where ArticleName like
>'%" & Search3 & "%')"
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp1"
> End If
>
> If search4<>"" Then
> strTable="#temp1"
> If lcase(type4)="author" Then
> strSearch= strSearch & " " &
>lcase(logical3) & " " &
>ucase(Search4)
> Select case lcase(logical3)
> Case "and"
> strsql="Select articleid into #temp1 from
>articletb where
>articleid in ("
> strSQL=strsql & "select a.ArticleID from
>[auth-artiTB] a, #temp
>t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
>AuthorTB where AuthorName
>like '" & Search4 & "%') and a.articleid=t.articleid
>)"
> Case "or"
> conSQL.Execute "select articleid into #temp1
>from #temp"
> strsql="insert #temp1 select articleid from
>[auth-artiTB]
>where Authorid in (select authorid from authortb where
>authorname like '" &
>Search4 & "%')"
> Case "not"
> strsql="Select articleid into #temp from
>#temp1 where
>articleid not in ("
> strSQL=strsql & "(select articleid from
>[auth-artiTB] where
>AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
>AuthorTB where AuthorName
>not like '" & Search4 & "%')"
> End Select
>
> ElseIf lcase(type4)="article" Then
> strSearch= strSearch & " " &
>lcase(logical3) & lcase(Search4)
> Select case lcase(logical3)
> Case "and"
> strSQL="Select a.articleID into #temp1
>from ArticleTB a, #temp
>t where a.ArticleName like '%" & Search4 & "%' and
>a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select articleid into
>#temp1 from #temp"
> strsql="insert #temp1 select articleid from
> articletb where
>articlename like '%" & Search4 & "%'"
> Case "not"
> strSQL="Select articleID into #temp1 from
>#temp where
>articleid not in (select articleid from ArticleTB
>where ArticleName like
>'%" & Search4 & "%')"
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp"
> End If
>
>
> strSQL="Select
>ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
>Journal_URL,volumeno, "
> strSQL=strSQL & "issueno,yrofpub,pagn from
>ArticleTB where Articleid
>in (Select articleid from " & strTable
> strSQL=strSQL & ") group by
>ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
>Journal_URL,volumeno,"
> strSQL=strSQL & "issueno,yrofpub,pagn"
>
> 'Response.Write strsql
> 'Response.End()
>
> %>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
>Transitional//EN">
>
> On Error Resume Next
> rsSQL.Open strSql,conSQL,1,2
>
> If Err.number <> 0 Then
> Response.Write err.description & "<h3>:
>Sorry, Could not able
>to process your request, please try again later or
>contact Us!</h3>"
> rsSQL.Close
> conSQL.Close
> set rsSQl=nothing
> set conSQL=nothing
> Response.End()
>
> End If
>
>
>=====
>With luv,
>Navani
>
>
Robert Chartier
Author, AspFree.com
xxx-xxx-xxxx
rchartierh@a...
http://www.aspfree.com/devlinks
http://www.aspfree.com/authors/robert
http://www.aspalliance.com/nothingmn
Message #4 by Van Tolhuyzen Koen <Koen.VanTolhuyzen@c...> on Fri, 24 Nov 2000 14:07:21 +0100
|
|
Hi,
Put the logic of your search engine in a component under MYS, and your
perfomance will increase like hell
Koen
-----Original Message-----
From: Navani
To: ASP Databases
Cc: navanid@y...
Sent: 24/11/00 11:35
Subject: [asp_databases] Help!to improve the performance of a Search Engine
Hello All,
I have some performance problem with sqlserver.
There are more than 2,00,000 records in the database
and keep on growing.
So sql queries which i am executing frm the ASP
script(Search Engine) becomes very slow. I have
explained my problem
in detail as below:
What should i do to improve the retrival speed of
sql
query? I need to maintain any index server? If yes
then, How?
Anyone can help me?
Here I am explained the problem in detail. kindly
have a look at this,
There are three Tables in the Database.They are
AuthorTB, ArticleTB and Auth-ArtiTB.
The structure of Each table is as follows:
1)AuthorTB
AuthorId (Pk)
AuthorName
2)Auth-artiTB (PK) combinational Primary key
ArticleId (FK)
AuthorId (Fk)
3)ArticleTB
ArticleId (PK)
ArticleName,etc.,
Full-Text Indixing has been created on Articlename
and authorname. Clustered Index has been created on
AuthorID and ArticleId of ArticleTB and Auth-ArtiTB.
In Asp, i have written a Search Engine, and it'll
search on ArticleName, Authornames with boolean
and,or,not conditions and some limit fields also.
This search interface is working fine for around 1
GB Records in the Table.
As the Size of the Database is keep on growing, Search
Interface started becoming slow.
Kindly give me the solution for this problem that
How to improve the speed of the search Interface for
large Number of Records in the Database.
Please reply me asap as it's my immediate
requirement.
Note: Following is the code of search Interface
searchResult.asp for your kind reference.
Thank you,
Regards,
Navani
This is the part of my searchResults.asp program. Here
shown How i am constructing the search Query. I am
using temperory table also, since i have to compare 3
tables.
<% Option Explicit
Response.Expires=0
Response.Buffer = true
'Declaring the variables
Dim search1,search2,search3,search4
Dim type1,type2,type3,type4
Dim logical1,logical2,logical3
Dim strTable, strSearch
'Dynamially constructing the Query
strTable="#temp"
If lcase(type1)="author" Then
strSQL="select ArticleID into #Temp from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search1 & "%')"
strSearch= ucase(Search1)
ElseIf lcase(type1)="article" Then
strSQL="select ArticleId into #Temp from
ArticleTB where
ArticleName like '%" & Search1 &"%'"
strSearch= ucase(Search1)
End If
conSQL.Execute strSQl
If search2<>"" Then
strTable="#temp1"
If lcase(type2)="author" Then
strSearch= strSearch & " " & lcase(logical1)
& " " & ucase(Search2)
Select case lcase(logical1)
Case "and"
strsql="Select articleid into #temp1 from
articletb where
articleid in ("
strSQL=strsql & "select a.ArticleID from
[auth-artiTB] a, #temp
t where a.AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search2 & "%') and a.articleid=t.articleid
)"
Case "or"
conSQL.Execute "select articleid into #temp1
from #temp"
strSQl="insert #temp1 select articleid from
[auth-artiTB]
where Authorid in (select authorid from authortb where
authorname like '" &
Search2 & "%')"
Case "not"
strSQl="Select articleid into #temp1 from
#temp where
articleid not in "
strSQL=strsql & "(select articleid from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search2 & "%'))"
End Select
ElseIf lcase(type2)="article" Then
strSearch= strSearch & " " &
lcase(logical1) & " " & ucase(Search2)
Select case lcase(logical1)
Case "and"
strSQL="Select a.articleID into #temp1
from ArticleTB a, #temp
t where a.ArticleName like '%" & Search2 & "%' and
a.articleid=t.articleid "
Case "or"
conSQL.Execute "select
articleid into #temp1 from #temp"
strSQl= "insert #temp1 select
articleid from articletb where
articlename like '%" & Search2 & "%'"
Case "not"
strSQL="Select articleID into #temp1 from
#temp where
articleid not in (select articleid from articletb
where ArticleName like '%"
& Search2 & "%') "
End Select
End If
conSQL.Execute strsql
conSQL.Execute "Drop table #temp"
End If
If search3<>"" Then
strTable="#temp"
If lcase(type3)="author" Then
strSearch= strSearch & " " &
lcase(logical2) & " " & ucase(Search3)
Select case lcase(logical2)
Case "and"
strsql="Select articleid into #temp from
articletb where
articleid in ("
strSQL=strsql & "select a.ArticleID from
[auth-artiTB] a, #temp1
t where a.AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search3 & "%') and a.articleid=t.articleid
)"
Case "or"
conSQL.Execute "select articleid into #temp
from #temp1"
strsql="insert #temp select articleid from
[auth-artiTB] where
Authorid in (select authorid from authortb where
authorname like '" &
Search3 & "%')"
Case "not"
strSQl="Select articleid into #temp from
#temp1 where
articleid not in ("
strSQL=strsql & "(select articleid from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search3 & "%'))"
End Select
ElseIf lcase(type3)="article" Then
strSearch= strSearch & " " &
lcase(logical2) & " " & ucase(Search3)
Select case lcase(logical2)
Case "and"
strSQL="Select a.articleID into #temp
from ArticleTB a, #temp1
t where a.ArticleName like '%" & Search3 & "%' and
a.articleid=t.articleid "
Case "or"
conSQL.Execute "select articleid into #temp
from #temp1"
strsql="insert #temp select articleid from
articletb where
articlename like '%" & Search3 & "%'"
Case "not"
strSQL="Select articleID into #temp from
#temp1 where
articleid not in (select articleid from articletb
where ArticleName like
'%" & Search3 & "%')"
End Select
End If
conSQL.Execute strsql
conSQL.Execute "Drop table #temp1"
End If
If search4<>"" Then
strTable="#temp1"
If lcase(type4)="author" Then
strSearch= strSearch & " " &
lcase(logical3) & " " &
ucase(Search4)
Select case lcase(logical3)
Case "and"
strsql="Select articleid into #temp1 from
articletb where
articleid in ("
strSQL=strsql & "select a.ArticleID from
[auth-artiTB] a, #temp
t where a.AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
like '" & Search4 & "%') and a.articleid=t.articleid
)"
Case "or"
conSQL.Execute "select articleid into #temp1
from #temp"
strsql="insert #temp1 select articleid from
[auth-artiTB]
where Authorid in (select authorid from authortb where
authorname like '" &
Search4 & "%')"
Case "not"
strsql="Select articleid into #temp from
#temp1 where
articleid not in ("
strSQL=strsql & "(select articleid from
[auth-artiTB] where
AuthorID in"
strSQL=strSQL & " (Select AuthorID from
AuthorTB where AuthorName
not like '" & Search4 & "%')"
End Select
ElseIf lcase(type4)="article" Then
strSearch= strSearch & " " &
lcase(logical3) & lcase(Search4)
Select case lcase(logical3)
Case "and"
strSQL="Select a.articleID into #temp1
from ArticleTB a, #temp
t where a.ArticleName like '%" & Search4 & "%' and
a.articleid=t.articleid "
Case "or"
conSQL.Execute "select articleid into
#temp1 from #temp"
strsql="insert #temp1 select articleid from
articletb where
articlename like '%" & Search4 & "%'"
Case "not"
strSQL="Select articleID into #temp1 from
#temp where
articleid not in (select articleid from ArticleTB
where ArticleName like
'%" & Search4 & "%')"
End Select
End If
conSQL.Execute strsql
conSQL.Execute "Drop table #temp"
End If
strSQL="Select
ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
Journal_URL,volumeno, "
strSQL=strSQL & "issueno,yrofpub,pagn from
ArticleTB where Articleid
in (Select articleid from " & strTable
strSQL=strSQL & ") group by
ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
Journal_URL,volumeno,"
strSQL=strSQL & "issueno,yrofpub,pagn"
'Response.Write strsql
'Response.End()
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
Transitional//EN">
On Error Resume Next
rsSQL.Open strSql,conSQL,1,2
If Err.number <> 0 Then
Response.Write err.description & "<h3>:
Sorry, Could not able
to process your request, please try again later or
contact Us!</h3>"
rsSQL.Close
conSQL.Close
set rsSQl=nothing
set conSQL=nothing
Response.End()
End If
=====
With luv,
Navani
Message #5 by Navani <navanid@y...> on Fri, 24 Nov 2000 07:42:08 -0800 (PST)
|
|
Hello Van Tolhuyzen koen,
Thanks for your kind reply. Could you pls explain
me in detail how to put the logic of my search engine
in a component under MYS????
what do u mean by MYS?
Thanks in advance.
pls guide me.
with luv,
Navani
--- Van Tolhuyzen Koen <Koen.VanTolhuyzen@c...>
wrote:
>
> Hi,
>
> Put the logic of your search engine in a component
> under MYS, and your
> perfomance will increase like hell
>
> Koen
> -----Original Message-----
> From: Navani
> To: ASP Databases
> Cc: navanid@y...
> Sent: 24/11/00 11:35
> Subject: [asp_databases] Help!to improve the
> performance of a Search Engine
>
> Hello All,
>
> I have some performance problem with sqlserver.
> There are more than 2,00,000 records in the database
> and keep on growing.
>
> So sql queries which i am executing frm the ASP
> script(Search Engine) becomes very slow. I have
> explained my problem
> in detail as below:
>
> What should i do to improve the retrival speed of
> sql
> query? I need to maintain any index server? If yes
> then, How?
>
> Anyone can help me?
>
>
> Here I am explained the problem in detail. kindly
> have a look at this,
>
> There are three Tables in the Database.They are
> AuthorTB, ArticleTB and Auth-ArtiTB.
>
> The structure of Each table is as follows:
>
> 1)AuthorTB
> AuthorId (Pk)
> AuthorName
> 2)Auth-artiTB (PK) combinational Primary key
> ArticleId (FK)
> AuthorId (Fk)
> 3)ArticleTB
> ArticleId (PK)
> ArticleName,etc.,
>
> Full-Text Indixing has been created on Articlename
> and authorname. Clustered Index has been created on
> AuthorID and ArticleId of ArticleTB and Auth-ArtiTB.
>
>
> In Asp, i have written a Search Engine, and it'll
>
> search on ArticleName, Authornames with boolean
> and,or,not conditions and some limit fields also.
>
> This search interface is working fine for around
> 1
> GB Records in the Table.
>
> As the Size of the Database is keep on growing,
> Search
> Interface started becoming slow.
>
> Kindly give me the solution for this problem that
> How to improve the speed of the search Interface for
> large Number of Records in the Database.
>
> Please reply me asap as it's my immediate
> requirement.
>
> Note: Following is the code of search Interface
> searchResult.asp for your kind reference.
>
> Thank you,
>
> Regards,
> Navani
>
> This is the part of my searchResults.asp program.
> Here
> shown How i am constructing the search Query. I am
> using temperory table also, since i have to compare
> 3
> tables.
>
> <% Option Explicit
> Response.Expires=0
> Response.Buffer = true
>
> 'Declaring the variables
> Dim search1,search2,search3,search4
> Dim type1,type2,type3,type4
> Dim logical1,logical2,logical3
> Dim strTable, strSearch
>
> 'Dynamially constructing the Query
>
> strTable="#temp"
> If lcase(type1)="author" Then
> strSQL="select ArticleID into #Temp from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search1 & "%')"
> strSearch= ucase(Search1)
> ElseIf lcase(type1)="article" Then
> strSQL="select ArticleId into #Temp from
> ArticleTB where
> ArticleName like '%" & Search1 &"%'"
> strSearch= ucase(Search1)
> End If
> conSQL.Execute strSQl
>
> If search2<>"" Then
> strTable="#temp1"
> If lcase(type2)="author" Then
> strSearch= strSearch & " " &
> lcase(logical1)
> & " " & ucase(Search2)
> Select case lcase(logical1)
> Case "and"
> strsql="Select articleid into #temp1
> from
> articletb where
> articleid in ("
> strSQL=strsql & "select a.ArticleID
> from
> [auth-artiTB] a, #temp
> t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search2 & "%') and a.articleid=t.articleid
> )"
> Case "or"
> conSQL.Execute "select articleid into
> #temp1
> from #temp"
> strSQl="insert #temp1 select articleid from
> [auth-artiTB]
> where Authorid in (select authorid from authortb
> where
> authorname like '" &
> Search2 & "%')"
> Case "not"
> strSQl="Select articleid into #temp1 from
> #temp where
> articleid not in "
> strSQL=strsql & "(select articleid from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search2 & "%'))"
> End Select
>
> ElseIf lcase(type2)="article" Then
> strSearch= strSearch & " " &
> lcase(logical1) & " " & ucase(Search2)
> Select case lcase(logical1)
> Case "and"
> strSQL="Select a.articleID into
> #temp1
> from ArticleTB a, #temp
> t where a.ArticleName like '%" & Search2 & "%' and
> a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select
> articleid into #temp1 from #temp"
> strSQl= "insert #temp1
> select
> articleid from articletb where
> articlename like '%" & Search2 & "%'"
> Case "not"
> strSQL="Select articleID into #temp1 from
>
> #temp where
> articleid not in (select articleid from articletb
> where ArticleName like '%"
> & Search2 & "%') "
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp"
> End If
>
> If search3<>"" Then
> strTable="#temp"
> If lcase(type3)="author" Then
> strSearch= strSearch & " " &
> lcase(logical2) & " " & ucase(Search3)
> Select case lcase(logical2)
> Case "and"
>
=== message truncated ===
Message #6 by "yls" <yls177@h...> on Sat, 25 Nov 2000 00:23:22 -0800
|
|
emmm... what does over-engineer means? puzzled....
Toby Anscombe <tanscombe@m...> wrote in message
news:21182@a..._databases...
>
> Use custom COM components to handle data access - even if you just use
them
> to return a resultset or a command.
> Don't use dynamic SQL - use a stored proc instead
> Do you really have to search this way ? why not just do an inner join on
all
> tables and search on the fields you need ??
>
> Without knowing too much about your system, dare I say it seems to be a
bit
> over-engineered ??
>
> Regards
>
> T
>
>
>
>
> -----Original Message-----
> From: Navani [mailto:navanid@y...]
> Sent: 24 November 2000 10:35
> To: ASP Databases
> Cc: navanid@y...
> Subject: [asp_databases] Help!to improve the performance of a Search
> Engine
>
>
> Hello All,
>
> I have some performance problem with sqlserver.
> There are more than 2,00,000 records in the database
> and keep on growing.
>
> So sql queries which i am executing frm the ASP
> script(Search Engine) becomes very slow. I have
> explained my problem
> in detail as below:
>
> What should i do to improve the retrival speed of
> sql
> query? I need to maintain any index server? If yes
> then, How?
>
> Anyone can help me?
>
>
> Here I am explained the problem in detail. kindly
> have a look at this,
>
> There are three Tables in the Database.They are
> AuthorTB, ArticleTB and Auth-ArtiTB.
>
> The structure of Each table is as follows:
>
> 1)AuthorTB
> AuthorId (Pk)
> AuthorName
> 2)Auth-artiTB (PK) combinational Primary key
> ArticleId (FK)
> AuthorId (Fk)
> 3)ArticleTB
> ArticleId (PK)
> ArticleName,etc.,
>
> Full-Text Indixing has been created on Articlename
> and authorname. Clustered Index has been created on
> AuthorID and ArticleId of ArticleTB and Auth-ArtiTB.
>
>
> In Asp, i have written a Search Engine, and it'll
> search on ArticleName, Authornames with boolean
> and,or,not conditions and some limit fields also.
>
> This search interface is working fine for around 1
> GB Records in the Table.
>
> As the Size of the Database is keep on growing, Search
> Interface started becoming slow.
>
> Kindly give me the solution for this problem that
> How to improve the speed of the search Interface for
> large Number of Records in the Database.
>
> Please reply me asap as it's my immediate
> requirement.
>
> Note: Following is the code of search Interface
> searchResult.asp for your kind reference.
>
> Thank you,
>
> Regards,
> Navani
>
> This is the part of my searchResults.asp program. Here
> shown How i am constructing the search Query. I am
> using temperory table also, since i have to compare 3
> tables.
>
> <% Option Explicit
> Response.Expires=0
> Response.Buffer = true
>
> 'Declaring the variables
> Dim search1,search2,search3,search4
> Dim type1,type2,type3,type4
> Dim logical1,logical2,logical3
> Dim strTable, strSearch
>
> 'Dynamially constructing the Query
>
> strTable="#temp"
> If lcase(type1)="author" Then
> strSQL="select ArticleID into #Temp from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search1 & "%')"
> strSearch= ucase(Search1)
> ElseIf lcase(type1)="article" Then
> strSQL="select ArticleId into #Temp from
> ArticleTB where
> ArticleName like '%" & Search1 &"%'"
> strSearch= ucase(Search1)
> End If
> conSQL.Execute strSQl
>
> If search2<>"" Then
> strTable="#temp1"
> If lcase(type2)="author" Then
> strSearch= strSearch & " " & lcase(logical1)
> & " " & ucase(Search2)
> Select case lcase(logical1)
> Case "and"
> strsql="Select articleid into #temp1 from
> articletb where
> articleid in ("
> strSQL=strsql & "select a.ArticleID from
> [auth-artiTB] a, #temp
> t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search2 & "%') and a.articleid=t.articleid
> )"
> Case "or"
> conSQL.Execute "select articleid into #temp1
> from #temp"
> strSQl="insert #temp1 select articleid from
> [auth-artiTB]
> where Authorid in (select authorid from authortb where
> authorname like '" &
> Search2 & "%')"
> Case "not"
> strSQl="Select articleid into #temp1 from
> #temp where
> articleid not in "
> strSQL=strsql & "(select articleid from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search2 & "%'))"
> End Select
>
> ElseIf lcase(type2)="article" Then
> strSearch= strSearch & " " &
> lcase(logical1) & " " & ucase(Search2)
> Select case lcase(logical1)
> Case "and"
> strSQL="Select a.articleID into #temp1
> from ArticleTB a, #temp
> t where a.ArticleName like '%" & Search2 & "%' and
> a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select
> articleid into #temp1 from #temp"
> strSQl= "insert #temp1 select
> articleid from articletb where
> articlename like '%" & Search2 & "%'"
> Case "not"
> strSQL="Select articleID into #temp1 from
> #temp where
> articleid not in (select articleid from articletb
> where ArticleName like '%"
> & Search2 & "%') "
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp"
> End If
>
> If search3<>"" Then
> strTable="#temp"
> If lcase(type3)="author" Then
> strSearch= strSearch & " " &
> lcase(logical2) & " " & ucase(Search3)
> Select case lcase(logical2)
> Case "and"
> strsql="Select articleid into #temp from
> articletb where
> articleid in ("
> strSQL=strsql & "select a.ArticleID from
> [auth-artiTB] a, #temp1
> t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search3 & "%') and a.articleid=t.articleid
> )"
> Case "or"
> conSQL.Execute "select articleid into #temp
> from #temp1"
> strsql="insert #temp select articleid from
> [auth-artiTB] where
> Authorid in (select authorid from authortb where
> authorname like '" &
> Search3 & "%')"
> Case "not"
> strSQl="Select articleid into #temp from
> #temp1 where
> articleid not in ("
> strSQL=strsql & "(select articleid from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search3 & "%'))"
> End Select
>
> ElseIf lcase(type3)="article" Then
> strSearch= strSearch & " " &
> lcase(logical2) & " " & ucase(Search3)
> Select case lcase(logical2)
> Case "and"
> strSQL="Select a.articleID into #temp
> from ArticleTB a, #temp1
> t where a.ArticleName like '%" & Search3 & "%' and
> a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select articleid into #temp
> from #temp1"
> strsql="insert #temp select articleid from
> articletb where
> articlename like '%" & Search3 & "%'"
> Case "not"
> strSQL="Select articleID into #temp from
> #temp1 where
> articleid not in (select articleid from articletb
> where ArticleName like
> '%" & Search3 & "%')"
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp1"
> End If
>
> If search4<>"" Then
> strTable="#temp1"
> If lcase(type4)="author" Then
> strSearch= strSearch & " " &
> lcase(logical3) & " " &
> ucase(Search4)
> Select case lcase(logical3)
> Case "and"
> strsql="Select articleid into #temp1 from
> articletb where
> articleid in ("
> strSQL=strsql & "select a.ArticleID from
> [auth-artiTB] a, #temp
> t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search4 & "%') and a.articleid=t.articleid
> )"
> Case "or"
> conSQL.Execute "select articleid into #temp1
> from #temp"
> strsql="insert #temp1 select articleid from
> [auth-artiTB]
> where Authorid in (select authorid from authortb where
> authorname like '" &
> Search4 & "%')"
> Case "not"
> strsql="Select articleid into #temp from
> #temp1 where
> articleid not in ("
> strSQL=strsql & "(select articleid from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> not like '" & Search4 & "%')"
> End Select
>
> ElseIf lcase(type4)="article" Then
> strSearch= strSearch & " " &
> lcase(logical3) & lcase(Search4)
> Select case lcase(logical3)
> Case "and"
> strSQL="Select a.articleID into #temp1
> from ArticleTB a, #temp
> t where a.ArticleName like '%" & Search4 & "%' and
> a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select articleid into
> #temp1 from #temp"
> strsql="insert #temp1 select articleid from
> articletb where
> articlename like '%" & Search4 & "%'"
> Case "not"
> strSQL="Select articleID into #temp1 from
> #temp where
> articleid not in (select articleid from ArticleTB
> where ArticleName like
> '%" & Search4 & "%')"
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp"
> End If
>
>
> strSQL="Select
> ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
> Journal_URL,volumeno, "
> strSQL=strSQL & "issueno,yrofpub,pagn from
> ArticleTB where Articleid
> in (Select articleid from " & strTable
> strSQL=strSQL & ") group by
> ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
> Journal_URL,volumeno,"
> strSQL=strSQL & "issueno,yrofpub,pagn"
>
> 'Response.Write strsql
> 'Response.End()
>
> %>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
> Transitional//EN">
>
> On Error Resume Next
> rsSQL.Open strSql,conSQL,1,2
>
> If Err.number <> 0 Then
> Response.Write err.description & "<h3>:
> Sorry, Could not able
> to process your request, please try again later or
> contact Us!</h3>"
> rsSQL.Close
> conSQL.Close
> set rsSQl=nothing
> set conSQL=nothing
> Response.End()
>
> End If
>
>
> =====
> With luv,
> Navani
>
>
>
Message #7 by "yls" <yls177@h...> on Sat, 25 Nov 2000 00:24:31 -0800
|
|
WOW.. there exists such a "thing".. where to get it, documentation, other
pple reviews, and if got 10 pros how about the cons even if there is only
one ;)
Robert Chartier <rchartierh@a...> wrote in message
news:21202@a..._databases...
>
> speaking of a custom COM object check out the FMStocks.com application
> provided to us from Microsoft.
>
> download their source, and root through it..you will come across a .CLS
> file that contains all of the database access.
>
> use and compile this....it has a number of very useful functions that help
> most people, that do not have a full understanding of ADO
>
> this approach is recommended by microsoft, and more importantly by me :)
>
>
>
>
> At 08:06 AM 11/24/00, you wrote:
> >Use custom COM components to handle data access - even if you just use
them
> >to return a resultset or a command.
> >Don't use dynamic SQL - use a stored proc instead
> >Do you really have to search this way ? why not just do an inner join on
all
> >tables and search on the fields you need ??
> >
> >Without knowing too much about your system, dare I say it seems to be a
bit
> >over-engineered ??
> >
> >Regards
> >
> >T
> >
> >
> >
> >
> >-----Original Message-----
> >From: Navani [mailto:navanid@y...]
> >Sent: 24 November 2000 10:35
> >To: ASP Databases
> >Cc: navanid@y...
> >Subject: [asp_databases] Help!to improve the performance of a Search
> >Engine
> >
> >
> >Hello All,
> >
> > I have some performance problem with sqlserver.
> >There are more than 2,00,000 records in the database
> >and keep on growing.
> >
> > So sql queries which i am executing frm the ASP
> >script(Search Engine) becomes very slow. I have
> >explained my problem
> >in detail as below:
> >
> > What should i do to improve the retrival speed of
> >sql
> >query? I need to maintain any index server? If yes
> >then, How?
> >
> > Anyone can help me?
> >
> >
> > Here I am explained the problem in detail. kindly
> >have a look at this,
> >
> > There are three Tables in the Database.They are
> >AuthorTB, ArticleTB and Auth-ArtiTB.
> >
> > The structure of Each table is as follows:
> >
> > 1)AuthorTB
> > AuthorId (Pk)
> > AuthorName
> > 2)Auth-artiTB (PK) combinational Primary key
> > ArticleId (FK)
> > AuthorId (Fk)
> > 3)ArticleTB
> > ArticleId (PK)
> > ArticleName,etc.,
> >
> > Full-Text Indixing has been created on Articlename
> >and authorname. Clustered Index has been created on
> >AuthorID and ArticleId of ArticleTB and Auth-ArtiTB.
> >
> >
> > In Asp, i have written a Search Engine, and it'll
> >search on ArticleName, Authornames with boolean
> >and,or,not conditions and some limit fields also.
> >
> > This search interface is working fine for around 1
> >GB Records in the Table.
> >
> >As the Size of the Database is keep on growing, Search
> >Interface started becoming slow.
> >
> > Kindly give me the solution for this problem that
> >How to improve the speed of the search Interface for
> >large Number of Records in the Database.
> >
> > Please reply me asap as it's my immediate
> >requirement.
> >
> >Note: Following is the code of search Interface
> >searchResult.asp for your kind reference.
> >
> >Thank you,
> >
> >Regards,
> >Navani
> >
> >This is the part of my searchResults.asp program. Here
> >shown How i am constructing the search Query. I am
> >using temperory table also, since i have to compare 3
> >tables.
> >
> ><% Option Explicit
> > Response.Expires=0
> > Response.Buffer = true
> >
> > 'Declaring the variables
> > Dim search1,search2,search3,search4
> > Dim type1,type2,type3,type4
> > Dim logical1,logical2,logical3
> > Dim strTable, strSearch
> >
> >'Dynamially constructing the Query
> >
> > strTable="#temp"
> > If lcase(type1)="author" Then
> > strSQL="select ArticleID into #Temp from
> >[auth-artiTB] where
> >AuthorID in"
> > strSQL=strSQL & " (Select AuthorID from
> >AuthorTB where AuthorName
> >like '" & Search1 & "%')"
> > strSearch= ucase(Search1)
> > ElseIf lcase(type1)="article" Then
> > strSQL="select ArticleId into #Temp from
> >ArticleTB where
> >ArticleName like '%" & Search1 &"%'"
> > strSearch= ucase(Search1)
> > End If
> > conSQL.Execute strSQl
> >
> > If search2<>"" Then
> > strTable="#temp1"
> > If lcase(type2)="author" Then
> > strSearch= strSearch & " " & lcase(logical1)
> >& " " & ucase(Search2)
> > Select case lcase(logical1)
> > Case "and"
> > strsql="Select articleid into #temp1 from
> >articletb where
> >articleid in ("
> > strSQL=strsql & "select a.ArticleID from
> >[auth-artiTB] a, #temp
> >t where a.AuthorID in"
> > strSQL=strSQL & " (Select AuthorID from
> >AuthorTB where AuthorName
> >like '" & Search2 & "%') and a.articleid=t.articleid
> >)"
> > Case "or"
> > conSQL.Execute "select articleid into #temp1
> >from #temp"
> > strSQl="insert #temp1 select articleid from
> >[auth-artiTB]
> >where Authorid in (select authorid from authortb where
> >authorname like '" &
> >Search2 & "%')"
> > Case "not"
> > strSQl="Select articleid into #temp1 from
> >#temp where
> >articleid not in "
> > strSQL=strsql & "(select articleid from
> >[auth-artiTB] where
> >AuthorID in"
> > strSQL=strSQL & " (Select AuthorID from
> >AuthorTB where AuthorName
> >like '" & Search2 & "%'))"
> > End Select
> >
> > ElseIf lcase(type2)="article" Then
> > strSearch= strSearch & " " &
> >lcase(logical1) & " " & ucase(Search2)
> > Select case lcase(logical1)
> > Case "and"
> > strSQL="Select a.articleID into #temp1
> >from ArticleTB a, #temp
> >t where a.ArticleName like '%" & Search2 & "%' and
> >a.articleid=t.articleid "
> > Case "or"
> > conSQL.Execute "select
> >articleid into #temp1 from #temp"
> > strSQl= "insert #temp1 select
> >articleid from articletb where
> >articlename like '%" & Search2 & "%'"
> > Case "not"
> > strSQL="Select articleID into #temp1 from
> >#temp where
> >articleid not in (select articleid from articletb
> >where ArticleName like '%"
> >& Search2 & "%') "
> > End Select
> > End If
> > conSQL.Execute strsql
> > conSQL.Execute "Drop table #temp"
> > End If
> >
> > If search3<>"" Then
> > strTable="#temp"
> > If lcase(type3)="author" Then
> > strSearch= strSearch & " " &
> >lcase(logical2) & " " & ucase(Search3)
> > Select case lcase(logical2)
> > Case "and"
> > strsql="Select articleid into #temp from
> >articletb where
> >articleid in ("
> > strSQL=strsql & "select a.ArticleID from
> >[auth-artiTB] a, #temp1
> >t where a.AuthorID in"
> > strSQL=strSQL & " (Select AuthorID from
> >AuthorTB where AuthorName
> >like '" & Search3 & "%') and a.articleid=t.articleid
> >)"
> > Case "or"
> > conSQL.Execute "select articleid into #temp
> >from #temp1"
> > strsql="insert #temp select articleid from
> >[auth-artiTB] where
> >Authorid in (select authorid from authortb where
> >authorname like '" &
> >Search3 & "%')"
> > Case "not"
> > strSQl="Select articleid into #temp from
> >#temp1 where
> >articleid not in ("
> > strSQL=strsql & "(select articleid from
> >[auth-artiTB] where
> >AuthorID in"
> > strSQL=strSQL & " (Select AuthorID from
> >AuthorTB where AuthorName
> >like '" & Search3 & "%'))"
> > End Select
> >
> > ElseIf lcase(type3)="article" Then
> > strSearch= strSearch & " " &
> >lcase(logical2) & " " & ucase(Search3)
> > Select case lcase(logical2)
> > Case "and"
> > strSQL="Select a.articleID into #temp
> >from ArticleTB a, #temp1
> >t where a.ArticleName like '%" & Search3 & "%' and
> >a.articleid=t.articleid "
> > Case "or"
> > conSQL.Execute "select articleid into #temp
> >from #temp1"
> > strsql="insert #temp select articleid from
> >articletb where
> >articlename like '%" & Search3 & "%'"
> > Case "not"
> > strSQL="Select articleID into #temp from
> >#temp1 where
> >articleid not in (select articleid from articletb
> >where ArticleName like
> >'%" & Search3 & "%')"
> > End Select
> > End If
> > conSQL.Execute strsql
> > conSQL.Execute "Drop table #temp1"
> > End If
> >
> > If search4<>"" Then
> > strTable="#temp1"
> > If lcase(type4)="author" Then
> > strSearch= strSearch & " " &
> >lcase(logical3) & " " &
> >ucase(Search4)
> > Select case lcase(logical3)
> > Case "and"
> > strsql="Select articleid into #temp1 from
> >articletb where
> >articleid in ("
> > strSQL=strsql & "select a.ArticleID from
> >[auth-artiTB] a, #temp
> >t where a.AuthorID in"
> > strSQL=strSQL & " (Select AuthorID from
> >AuthorTB where AuthorName
> >like '" & Search4 & "%') and a.articleid=t.articleid
> >)"
> > Case "or"
> > conSQL.Execute "select articleid into #temp1
> >from #temp"
> > strsql="insert #temp1 select articleid from
> >[auth-artiTB]
> >where Authorid in (select authorid from authortb where
> >authorname like '" &
> >Search4 & "%')"
> > Case "not"
> > strsql="Select articleid into #temp from
> >#temp1 where
> >articleid not in ("
> > strSQL=strsql & "(select articleid from
> >[auth-artiTB] where
> >AuthorID in"
> > strSQL=strSQL & " (Select AuthorID from
> >AuthorTB where AuthorName
> >not like '" & Search4 & "%')"
> > End Select
> >
> > ElseIf lcase(type4)="article" Then
> > strSearch= strSearch & " " &
> >lcase(logical3) & lcase(Search4)
> > Select case lcase(logical3)
> > Case "and"
> > strSQL="Select a.articleID into #temp1
> >from ArticleTB a, #temp
> >t where a.ArticleName like '%" & Search4 & "%' and
> >a.articleid=t.articleid "
> > Case "or"
> > conSQL.Execute "select articleid into
> >#temp1 from #temp"
> > strsql="insert #temp1 select articleid from
> > articletb where
> >articlename like '%" & Search4 & "%'"
> > Case "not"
> > strSQL="Select articleID into #temp1 from
> >#temp where
> >articleid not in (select articleid from ArticleTB
> >where ArticleName like
> >'%" & Search4 & "%')"
> > End Select
> > End If
> > conSQL.Execute strsql
> > conSQL.Execute "Drop table #temp"
> > End If
> >
> >
> > strSQL="Select
> >ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
> >Journal_URL,volumeno, "
> > strSQL=strSQL & "issueno,yrofpub,pagn from
> >ArticleTB where Articleid
> >in (Select articleid from " & strTable
> > strSQL=strSQL & ") group by
> >ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
> >Journal_URL,volumeno,"
> > strSQL=strSQL & "issueno,yrofpub,pagn"
> >
> > 'Response.Write strsql
> > 'Response.End()
> >
> > %>
> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
> >Transitional//EN">
> >
> > On Error Resume Next
> > rsSQL.Open strSql,conSQL,1,2
> >
> > If Err.number <> 0 Then
> > Response.Write err.description & "<h3>:
> >Sorry, Could not able
> >to process your request, please try again later or
> >contact Us!</h3>"
> > rsSQL.Close
> > conSQL.Close
> > set rsSQl=nothing
> > set conSQL=nothing
> > Response.End()
> >
> > End If
> >
> >
> >=====
> >With luv,
> >Navani
> >
> >
> Robert Chartier
> Author, AspFree.com
> xxx-xxx-xxxx
> rchartierh@a...
> http://www.aspfree.com/devlinks
> http://www.aspfree.com/authors/robert
> http://www.aspalliance.com/nothingmn
>
>
>
Message #8 by "Toby Anscombe" <tanscombe@m...> on Fri, 24 Nov 2000 17:24:45 -0000
|
|
too complicated for it's own good....
*like using a sledge hammer to crack a nut*
-----Original Message-----
From: yls [mailto:yls177@h...]
Sent: 25 November 2000 08:23
To: ASP Databases
Subject: [asp_databases] Re: Help!to improve the performance of a Search
Engine
emmm... what does over-engineer means? puzzled....
Toby Anscombe <tanscombe@m...> wrote in message
news:21182@a..._databases...
>
> Use custom COM components to handle data access - even if you just use
them
> to return a resultset or a command.
> Don't use dynamic SQL - use a stored proc instead
> Do you really have to search this way ? why not just do an inner join on
all
> tables and search on the fields you need ??
>
> Without knowing too much about your system, dare I say it seems to be a
bit
> over-engineered ??
>
> Regards
>
> T
>
>
>
>
> -----Original Message-----
> From: Navani [mailto:navanid@y...]
> Sent: 24 November 2000 10:35
> To: ASP Databases
> Cc: navanid@y...
> Subject: [asp_databases] Help!to improve the performance of a Search
> Engine
>
>
> Hello All,
>
> I have some performance problem with sqlserver.
> There are more than 2,00,000 records in the database
> and keep on growing.
>
> So sql queries which i am executing frm the ASP
> script(Search Engine) becomes very slow. I have
> explained my problem
> in detail as below:
>
> What should i do to improve the retrival speed of
> sql
> query? I need to maintain any index server? If yes
> then, How?
>
> Anyone can help me?
>
>
> Here I am explained the problem in detail. kindly
> have a look at this,
>
> There are three Tables in the Database.They are
> AuthorTB, ArticleTB and Auth-ArtiTB.
>
> The structure of Each table is as follows:
>
> 1)AuthorTB
> AuthorId (Pk)
> AuthorName
> 2)Auth-artiTB (PK) combinational Primary key
> ArticleId (FK)
> AuthorId (Fk)
> 3)ArticleTB
> ArticleId (PK)
> ArticleName,etc.,
>
> Full-Text Indixing has been created on Articlename
> and authorname. Clustered Index has been created on
> AuthorID and ArticleId of ArticleTB and Auth-ArtiTB.
>
>
> In Asp, i have written a Search Engine, and it'll
> search on ArticleName, Authornames with boolean
> and,or,not conditions and some limit fields also.
>
> This search interface is working fine for around 1
> GB Records in the Table.
>
> As the Size of the Database is keep on growing, Search
> Interface started becoming slow.
>
> Kindly give me the solution for this problem that
> How to improve the speed of the search Interface for
> large Number of Records in the Database.
>
> Please reply me asap as it's my immediate
> requirement.
>
> Note: Following is the code of search Interface
> searchResult.asp for your kind reference.
>
> Thank you,
>
> Regards,
> Navani
>
> This is the part of my searchResults.asp program. Here
> shown How i am constructing the search Query. I am
> using temperory table also, since i have to compare 3
> tables.
>
> <% Option Explicit
> Response.Expires=0
> Response.Buffer = true
>
> 'Declaring the variables
> Dim search1,search2,search3,search4
> Dim type1,type2,type3,type4
> Dim logical1,logical2,logical3
> Dim strTable, strSearch
>
> 'Dynamially constructing the Query
>
> strTable="#temp"
> If lcase(type1)="author" Then
> strSQL="select ArticleID into #Temp from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search1 & "%')"
> strSearch= ucase(Search1)
> ElseIf lcase(type1)="article" Then
> strSQL="select ArticleId into #Temp from
> ArticleTB where
> ArticleName like '%" & Search1 &"%'"
> strSearch= ucase(Search1)
> End If
> conSQL.Execute strSQl
>
> If search2<>"" Then
> strTable="#temp1"
> If lcase(type2)="author" Then
> strSearch= strSearch & " " & lcase(logical1)
> & " " & ucase(Search2)
> Select case lcase(logical1)
> Case "and"
> strsql="Select articleid into #temp1 from
> articletb where
> articleid in ("
> strSQL=strsql & "select a.ArticleID from
> [auth-artiTB] a, #temp
> t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search2 & "%') and a.articleid=t.articleid
> )"
> Case "or"
> conSQL.Execute "select articleid into #temp1
> from #temp"
> strSQl="insert #temp1 select articleid from
> [auth-artiTB]
> where Authorid in (select authorid from authortb where
> authorname like '" &
> Search2 & "%')"
> Case "not"
> strSQl="Select articleid into #temp1 from
> #temp where
> articleid not in "
> strSQL=strsql & "(select articleid from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search2 & "%'))"
> End Select
>
> ElseIf lcase(type2)="article" Then
> strSearch= strSearch & " " &
> lcase(logical1) & " " & ucase(Search2)
> Select case lcase(logical1)
> Case "and"
> strSQL="Select a.articleID into #temp1
> from ArticleTB a, #temp
> t where a.ArticleName like '%" & Search2 & "%' and
> a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select
> articleid into #temp1 from #temp"
> strSQl= "insert #temp1 select
> articleid from articletb where
> articlename like '%" & Search2 & "%'"
> Case "not"
> strSQL="Select articleID into #temp1 from
> #temp where
> articleid not in (select articleid from articletb
> where ArticleName like '%"
> & Search2 & "%') "
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp"
> End If
>
> If search3<>"" Then
> strTable="#temp"
> If lcase(type3)="author" Then
> strSearch= strSearch & " " &
> lcase(logical2) & " " & ucase(Search3)
> Select case lcase(logical2)
> Case "and"
> strsql="Select articleid into #temp from
> articletb where
> articleid in ("
> strSQL=strsql & "select a.ArticleID from
> [auth-artiTB] a, #temp1
> t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search3 & "%') and a.articleid=t.articleid
> )"
> Case "or"
> conSQL.Execute "select articleid into #temp
> from #temp1"
> strsql="insert #temp select articleid from
> [auth-artiTB] where
> Authorid in (select authorid from authortb where
> authorname like '" &
> Search3 & "%')"
> Case "not"
> strSQl="Select articleid into #temp from
> #temp1 where
> articleid not in ("
> strSQL=strsql & "(select articleid from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search3 & "%'))"
> End Select
>
> ElseIf lcase(type3)="article" Then
> strSearch= strSearch & " " &
> lcase(logical2) & " " & ucase(Search3)
> Select case lcase(logical2)
> Case "and"
> strSQL="Select a.articleID into #temp
> from ArticleTB a, #temp1
> t where a.ArticleName like '%" & Search3 & "%' and
> a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select articleid into #temp
> from #temp1"
> strsql="insert #temp select articleid from
> articletb where
> articlename like '%" & Search3 & "%'"
> Case "not"
> strSQL="Select articleID into #temp from
> #temp1 where
> articleid not in (select articleid from articletb
> where ArticleName like
> '%" & Search3 & "%')"
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp1"
> End If
>
> If search4<>"" Then
> strTable="#temp1"
> If lcase(type4)="author" Then
> strSearch= strSearch & " " &
> lcase(logical3) & " " &
> ucase(Search4)
> Select case lcase(logical3)
> Case "and"
> strsql="Select articleid into #temp1 from
> articletb where
> articleid in ("
> strSQL=strsql & "select a.ArticleID from
> [auth-artiTB] a, #temp
> t where a.AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> like '" & Search4 & "%') and a.articleid=t.articleid
> )"
> Case "or"
> conSQL.Execute "select articleid into #temp1
> from #temp"
> strsql="insert #temp1 select articleid from
> [auth-artiTB]
> where Authorid in (select authorid from authortb where
> authorname like '" &
> Search4 & "%')"
> Case "not"
> strsql="Select articleid into #temp from
> #temp1 where
> articleid not in ("
> strSQL=strsql & "(select articleid from
> [auth-artiTB] where
> AuthorID in"
> strSQL=strSQL & " (Select AuthorID from
> AuthorTB where AuthorName
> not like '" & Search4 & "%')"
> End Select
>
> ElseIf lcase(type4)="article" Then
> strSearch= strSearch & " " &
> lcase(logical3) & lcase(Search4)
> Select case lcase(logical3)
> Case "and"
> strSQL="Select a.articleID into #temp1
> from ArticleTB a, #temp
> t where a.ArticleName like '%" & Search4 & "%' and
> a.articleid=t.articleid "
> Case "or"
> conSQL.Execute "select articleid into
> #temp1 from #temp"
> strsql="insert #temp1 select articleid from
> articletb where
> articlename like '%" & Search4 & "%'"
> Case "not"
> strSQL="Select articleID into #temp1 from
> #temp where
> articleid not in (select articleid from ArticleTB
> where ArticleName like
> '%" & Search4 & "%')"
> End Select
> End If
> conSQL.Execute strsql
> conSQL.Execute "Drop table #temp"
> End If
>
>
> strSQL="Select
> ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
> Journal_URL,volumeno, "
> strSQL=strSQL & "issueno,yrofpub,pagn from
> ArticleTB where Articleid
> in (Select articleid from " & strTable
> strSQL=strSQL & ") group by
> ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
> Journal_URL,volumeno,"
> strSQL=strSQL & "issueno,yrofpub,pagn"
>
> 'Response.Write strsql
> 'Response.End()
>
> %>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
> Transitional//EN">
>
> On Error Resume Next
> rsSQL.Open strSql,conSQL,1,2
>
> If Err.number <> 0 Then
> Response.Write err.description & "<h3>:
> Sorry, Could not able
> to process your request, please try again later or
> contact Us!</h3>"
> rsSQL.Close
> conSQL.Close
> set rsSQl=nothing
> set conSQL=nothing
> Response.End()
>
> End If
>
>
> =====
> With luv,
> Navani
>
>
>
---
FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
IN YOUR INBOX!
Get the latest and best HTML, XML, and JavaScript tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb's
FREE IT newsletters at http://www.earthweb.com today!
Message #9 by Robert Chartier <rchartierh@a...> on Fri, 24 Nov 2000 13:22:48 -0500
|
|
www.fmstocks.com
At 03:24 AM 11/25/00, you wrote:
>WOW.. there exists such a "thing".. where to get it, documentation, other
>pple reviews, and if got 10 pros how about the cons even if there is only
>one ;)
>Robert Chartier <rchartierh@a...> wrote in message
>news:21202@a..._databases...
> >
> > speaking of a custom COM object check out the FMStocks.com application
> > provided to us from Microsoft.
> >
> > download their source, and root through it..you will come across a .CLS
> > file that contains all of the database access.
> >
> > use and compile this....it has a number of very useful functions that help
> > most people, that do not have a full understanding of ADO
> >
> > this approach is recommended by microsoft, and more importantly by me :)
> >
> >
> >
> >
> > At 08:06 AM 11/24/00, you wrote:
> > >Use custom COM components to handle data access - even if you just use
>them
> > >to return a resultset or a command.
> > >Don't use dynamic SQL - use a stored proc instead
> > >Do you really have to search this way ? why not just do an inner join on
>all
> > >tables and search on the fields you need ??
> > >
> > >Without knowing too much about your system, dare I say it seems to be a
>bit
> > >over-engineered ??
> > >
> > >Regards
> > >
> > >T
> > >
> > >
> > >
> > >
> > >-----Original Message-----
> > >From: Navani [mailto:navanid@y...]
> > >Sent: 24 November 2000 10:35
> > >To: ASP Databases
> > >Cc: navanid@y...
> > >Subject: [asp_databases] Help!to improve the performance of a Search
> > >Engine
> > >
> > >
> > >Hello All,
> > >
> > > I have some performance problem with sqlserver.
> > >There are more than 2,00,000 records in the database
> > >and keep on growing.
> > >
> > > So sql queries which i am executing frm the ASP
> > >script(Search Engine) becomes very slow. I have
> > >explained my problem
> > >in detail as below:
> > >
> > > What should i do to improve the retrival speed of
> > >sql
> > >query? I need to maintain any index server? If yes
> > >then, How?
> > >
> > > Anyone can help me?
> > >
> > >
> > > Here I am explained the problem in detail. kindly
> > >have a look at this,
> > >
> > > There are three Tables in the Database.They are
> > >AuthorTB, ArticleTB and Auth-ArtiTB.
> > >
> > > The structure of Each table is as follows:
> > >
> > > 1)AuthorTB
> > > AuthorId (Pk)
> > > AuthorName
> > > 2)Auth-artiTB (PK) combinational Primary key
> > > ArticleId (FK)
> > > AuthorId (Fk)
> > > 3)ArticleTB
> > > ArticleId (PK)
> > > ArticleName,etc.,
> > >
> > > Full-Text Indixing has been created on Articlename
> > >and authorname. Clustered Index has been created on
> > >AuthorID and ArticleId of ArticleTB and Auth-ArtiTB.
> > >
> > >
> > > In Asp, i have written a Search Engine, and it'll
> > >search on ArticleName, Authornames with boolean
> > >and,or,not conditions and some limit fields also.
> > >
> > > This search interface is working fine for around 1
> > >GB Records in the Table.
> > >
> > >As the Size of the Database is keep on growing, Search
> > >Interface started becoming slow.
> > >
> > > Kindly give me the solution for this problem that
> > >How to improve the speed of the search Interface for
> > >large Number of Records in the Database.
> > >
> > > Please reply me asap as it's my immediate
> > >requirement.
> > >
> > >Note: Following is the code of search Interface
> > >searchResult.asp for your kind reference.
> > >
> > >Thank you,
> > >
> > >Regards,
> > >Navani
> > >
> > >This is the part of my searchResults.asp program. Here
> > >shown How i am constructing the search Query. I am
> > >using temperory table also, since i have to compare 3
> > >tables.
> > >
> > ><% Option Explicit
> > > Response.Expires=0
> > > Response.Buffer = true
> > >
> > > 'Declaring the variables
> > > Dim search1,search2,search3,search4
> > > Dim type1,type2,type3,type4
> > > Dim logical1,logical2,logical3
> > > Dim strTable, strSearch
> > >
> > >'Dynamially constructing the Query
> > >
> > > strTable="#temp"
> > > If lcase(type1)="author" Then
> > > strSQL="select ArticleID into #Temp from
> > >[auth-artiTB] where
> > >AuthorID in"
> > > strSQL=strSQL & " (Select AuthorID from
> > >AuthorTB where AuthorName
> > >like '" & Search1 & "%')"
> > > strSearch= ucase(Search1)
> > > ElseIf lcase(type1)="article" Then
> > > strSQL="select ArticleId into #Temp from
> > >ArticleTB where
> > >ArticleName like '%" & Search1 &"%'"
> > > strSearch= ucase(Search1)
> > > End If
> > > conSQL.Execute strSQl
> > >
> > > If search2<>"" Then
> > > strTable="#temp1"
> > > If lcase(type2)="author" Then
> > > strSearch= strSearch & " " & lcase(logical1)
> > >& " " & ucase(Search2)
> > > Select case lcase(logical1)
> > > Case "and"
> > > strsql="Select articleid into #temp1 from
> > >articletb where
> > >articleid in ("
> > > strSQL=strsql & "select a.ArticleID from
> > >[auth-artiTB] a, #temp
> > >t where a.AuthorID in"
> > > strSQL=strSQL & " (Select AuthorID from
> > >AuthorTB where AuthorName
> > >like '" & Search2 & "%') and a.articleid=t.articleid
> > >)"
> > > Case "or"
> > > conSQL.Execute "select articleid into #temp1
> > >from #temp"
> > > strSQl="insert #temp1 select articleid from
> > >[auth-artiTB]
> > >where Authorid in (select authorid from authortb where
> > >authorname like '" &
> > >Search2 & "%')"
> > > Case "not"
> > > strSQl="Select articleid into #temp1 from
> > >#temp where
> > >articleid not in "
> > > strSQL=strsql & "(select articleid from
> > >[auth-artiTB] where
> > >AuthorID in"
> > > strSQL=strSQL & " (Select AuthorID from
> > >AuthorTB where AuthorName
> > >like '" & Search2 & "%'))"
> > > End Select
> > >
> > > ElseIf lcase(type2)="article" Then
> > > strSearch= strSearch & " " &
> > >lcase(logical1) & " " & ucase(Search2)
> > > Select case lcase(logical1)
> > > Case "and"
> > > strSQL="Select a.articleID into #temp1
> > >from ArticleTB a, #temp
> > >t where a.ArticleName like '%" & Search2 & "%' and
> > >a.articleid=t.articleid "
> > > Case "or"
> > > conSQL.Execute "select
> > >articleid into #temp1 from #temp"
> > > strSQl= "insert #temp1 select
> > >articleid from articletb where
> > >articlename like '%" & Search2 & "%'"
> > > Case "not"
> > > strSQL="Select articleID into #temp1 from
> > >#temp where
> > >articleid not in (select articleid from articletb
> > >where ArticleName like '%"
> > >& Search2 & "%') "
> > > End Select
> > > End If
> > > conSQL.Execute strsql
> > > conSQL.Execute "Drop table #temp"
> > > End If
> > >
> > > If search3<>"" Then
> > > strTable="#temp"
> > > If lcase(type3)="author" Then
> > > strSearch= strSearch & " " &
> > >lcase(logical2) & " " & ucase(Search3)
> > > Select case lcase(logical2)
> > > Case "and"
> > > strsql="Select articleid into #temp from
> > >articletb where
> > >articleid in ("
> > > strSQL=strsql & "select a.ArticleID from
> > >[auth-artiTB] a, #temp1
> > >t where a.AuthorID in"
> > > strSQL=strSQL & " (Select AuthorID from
> > >AuthorTB where AuthorName
> > >like '" & Search3 & "%') and a.articleid=t.articleid
> > >)"
> > > Case "or"
> > > conSQL.Execute "select articleid into #temp
> > >from #temp1"
> > > strsql="insert #temp select articleid from
> > >[auth-artiTB] where
> > >Authorid in (select authorid from authortb where
> > >authorname like '" &
> > >Search3 & "%')"
> > > Case "not"
> > > strSQl="Select articleid into #temp from
> > >#temp1 where
> > >articleid not in ("
> > > strSQL=strsql & "(select articleid from
> > >[auth-artiTB] where
> > >AuthorID in"
> > > strSQL=strSQL & " (Select AuthorID from
> > >AuthorTB where AuthorName
> > >like '" & Search3 & "%'))"
> > > End Select
> > >
> > > ElseIf lcase(type3)="article" Then
> > > strSearch= strSearch & " " &
> > >lcase(logical2) & " " & ucase(Search3)
> > > Select case lcase(logical2)
> > > Case "and"
> > > strSQL="Select a.articleID into #temp
> > >from ArticleTB a, #temp1
> > >t where a.ArticleName like '%" & Search3 & "%' and
> > >a.articleid=t.articleid "
> > > Case "or"
> > > conSQL.Execute "select articleid into #temp
> > >from #temp1"
> > > strsql="insert #temp select articleid from
> > >articletb where
> > >articlename like '%" & Search3 & "%'"
> > > Case "not"
> > > strSQL="Select articleID into #temp from
> > >#temp1 where
> > >articleid not in (select articleid from articletb
> > >where ArticleName like
> > >'%" & Search3 & "%')"
> > > End Select
> > > End If
> > > conSQL.Execute strsql
> > > conSQL.Execute "Drop table #temp1"
> > > End If
> > >
> > > If search4<>"" Then
> > > strTable="#temp1"
> > > If lcase(type4)="author" Then
> > > strSearch= strSearch & " " &
> > >lcase(logical3) & " " &
> > >ucase(Search4)
> > > Select case lcase(logical3)
> > > Case "and"
> > > strsql="Select articleid into #temp1 from
> > >articletb where
> > >articleid in ("
> > > strSQL=strsql & "select a.ArticleID from
> > >[auth-artiTB] a, #temp
> > >t where a.AuthorID in"
> > > strSQL=strSQL & " (Select AuthorID from
> > >AuthorTB where AuthorName
> > >like '" & Search4 & "%') and a.articleid=t.articleid
> > >)"
> > > Case "or"
> > > conSQL.Execute "select articleid into #temp1
> > >from #temp"
> > > strsql="insert #temp1 select articleid from
> > >[auth-artiTB]
> > >where Authorid in (select authorid from authortb where
> > >authorname like '" &
> > >Search4 & "%')"
> > > Case "not"
> > > strsql="Select articleid into #temp from
> > >#temp1 where
> > >articleid not in ("
> > > strSQL=strsql & "(select articleid from
> > >[auth-artiTB] where
> > >AuthorID in"
> > > strSQL=strSQL & " (Select AuthorID from
> > >AuthorTB where AuthorName
> > >not like '" & Search4 & "%')"
> > > End Select
> > >
> > > ElseIf lcase(type4)="article" Then
> > > strSearch= strSearch & " " &
> > >lcase(logical3) & lcase(Search4)
> > > Select case lcase(logical3)
> > > Case "and"
> > > strSQL="Select a.articleID into #temp1
> > >from ArticleTB a, #temp
> > >t where a.ArticleName like '%" & Search4 & "%' and
> > >a.articleid=t.articleid "
> > > Case "or"
> > > conSQL.Execute "select articleid into
> > >#temp1 from #temp"
> > > strsql="insert #temp1 select articleid from
> > > articletb where
> > >articlename like '%" & Search4 & "%'"
> > > Case "not"
> > > strSQL="Select articleID into #temp1 from
> > >#temp where
> > >articleid not in (select articleid from ArticleTB
> > >where ArticleName like
> > >'%" & Search4 & "%')"
> > > End Select
> > > End If
> > > conSQL.Execute strsql
> > > conSQL.Execute "Drop table #temp"
> > > End If
> > >
> > >
> > > strSQL="Select
> > >ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
> > >Journal_URL,volumeno, "
> > > strSQL=strSQL & "issueno,yrofpub,pagn from
> > >ArticleTB where Articleid
> > >in (Select articleid from " & strTable
> > > strSQL=strSQL & ") group by
> > >ArticleName,ArticleID,AuthorNames,ArticleURL,Journal_name,
> > >Journal_URL,volumeno,"
> > > strSQL=strSQL & "issueno,yrofpub,pagn"
> > >
> > > 'Response.Write strsql
> > > 'Response.End()
> > >
> > > %>
> > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
> > >Transitional//EN">
> > >
> > > On Error Resume Next
> > > rsSQL.Open strSql,conSQL,1,2
> > >
> > > If Err.number <> 0 Then
> > > Response.Write err.description & "<h3>:
> > >Sorry, Could not able
> > >to process your request, please try again later or
> > >contact Us!</h3>"
> > > rsSQL.Close
> > > conSQL.Close
> > > set rsSQl=nothing
> > > set conSQL=nothing
> > > Response.End()
> > >
> > > End If
> > >
> > >
> > >=====
> > >With luv,
> > >Navani
> > >
> > >
Robert Chartier
Author, AspFree.com
xxx-xxx-xxxx
rchartierh@a...
http://www.aspfree.com/devlinks
http://www.aspfree.com/authors/robert
http://www.aspalliance.com/nothingmn
|
|
|
View Cart
