Hello Toby Anscombe,



   Thanks for your guidance. Could you pls suggest me

any custom COM component that i can get frm the

internet?



  I have constructed the Query using inner joins and

all. It was working fine expect only one problem.

The problem is it's not retriving any results for two

authors search with And logical condition. 



   If you try this, U'll realize. so that I have

changed my coding and constructing SQl Query for each

and every search text. Note that there are four search

text boxes which can be combined with logical

operators. So many limiting fields also there. 

  Some what complicated Search Interface, it is. 



  May be the coding is large. 



Thank you,



pls reply soon.



with luv,

Navani

  



--- Toby Anscombe <tanscombe@m...> 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

> 

=== message truncated ===





=====

With luv,

Navani