|
 |
asp_database_setup thread: Timeout expired
Message #1 by venu <venumadhav_2000@y...> on Mon, 22 Jul 2002 21:50:09 -0700 (PDT)
|
|
Hi,
I am venu ..I have some problem with Timeout expired.
Ihave some 20K records in my table.We do a search in a
perticular called as jproject and the description of
the table is as follows
CREATE TABLE [dbo].[JPROJECT] (
[pid] [int] IDENTITY (1, 1) NOT NULL ,
[candid] [int] NOT NULL ,
[project] [text] COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Here we paste the entire resume in the project column.
so when I give a serch for a perticular word it give
as
Microsoft OLE DB Provider for ODBC Drivers error
'80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/asp/index_search.asp, line 81
it happens quite offen...and Ihave change
dbconnection.timeout =0 still I face the same problem
and the code at line number 81 is
set rsPro
server.CreateObject("ADODB.recordset")(line 81)
rsPro.Open sql_pro,dbConn
.
I request to please provide me the solution fo the
above mentioned quries...
Thanks in advance.
Cheers
Venu
__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
Message #2 by "Ken Schaefer" <ken@a...> on Tue, 23 Jul 2002 16:35:01 +1000
|
|
What is your Primary Key? What indexes have you defined? What is the SQL
statement that you are executing?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "venu" <venumadhav_2000@y...>
Subject: [asp_database_setup] Timeout expired
: I am venu ..I have some problem with Timeout expired.
: Ihave some 20K records in my table.We do a search in a
: perticular called as jproject and the description of
: the table is as follows
: CREATE TABLE [dbo].[JPROJECT] (
: [pid] [int] IDENTITY (1, 1) NOT NULL ,
: [candid] [int] NOT NULL ,
: [project] [text] COLLATE SQL_Latin1_General_CP1_CI_AS
: NOT NULL
: ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
: GO
:
: Here we paste the entire resume in the project column.
: so when I give a serch for a perticular word it give
: as
:
: Microsoft OLE DB Provider for ODBC Drivers error
: '80040e31'
:
: [Microsoft][ODBC SQL Server Driver]Timeout expired
:
: /asp/index_search.asp, line 81
:
: it happens quite offen...and Ihave change
: dbconnection.timeout =0 still I face the same problem
:
:
: and the code at line number 81 is
:
: set rsPro
: server.CreateObject("ADODB.recordset")(line 81)
: rsPro.Open sql_pro,dbConn
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by venu <venumadhav_2000@y...> on Mon, 22 Jul 2002 23:51:54 -0700 (PDT)
|
|
the primary key is pid and candid is foregin key.
and the qurey I execute is
select * from jpoject where project like '%java%' and
project like '%oracle% and project like '%9ias%'
Venu
What is your Primary Key? What indexes have you
defined? What is the
SQL
statement that you are executing?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "venu" <venumadhav_2000@y...>
Subject: [asp_database_setup] Timeout expired
: I am venu ..I have some problem with Timeout
expired.
: Ihave some 20K records in my table.We do a search in
a
: perticular called as jproject and the description of
: the table is as follows
: CREATE TABLE [dbo].[JPROJECT] (
: [pid] [int] IDENTITY (1, 1) NOT NULL ,
: [candid] [int] NOT NULL ,
: [project] [text] COLLATE
SQL_Latin1_General_CP1_CI_AS
: NOT NULL
: ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
: GO
:
: Here we paste the entire resume in the project
column.
: so when I give a serch for a perticular word it
give
: as
:
: Microsoft OLE DB Provider for ODBC Drivers error
: '80040e31'
:
: [Microsoft][ODBC SQL Server Driver]Timeout expired
:
: /asp/index_search.asp, line 81
:
: it happens quite offen...and Ihave change
: dbconnection.timeout =0 still I face the same
problem
:
:
: and the code at line number 81 is
:
: set rsPro
: server.CreateObject("ADODB.recordset")(line 81)
: rsPro.Open sql_pro,dbConn
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
=====
Venu madhav.c
__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
Message #4 by "Ken Schaefer" <ken@a...> on Tue, 23 Jul 2002 17:09:16 +1000
|
|
What does the Query Execution Plan show in Query Analyser? Is the any step
that is very expensive?
Unfortunately, indexing the text field is not going to help because all your
predicates begin with wildcard characters ( '%predicate%' )
Have you considered using Full Text Indexing on the Project column?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "venu" <venumadhav_2000@y...>
Subject: [asp_database_setup] Re: Timeout expired
: the primary key is pid and candid is foregin key.
:
: and the qurey I execute is
:
: select * from jpoject where project like '%java%' and
: project like '%oracle% and project like '%9ias%'
:
: Venu
:
:
:
: What is your Primary Key? What indexes have you
: defined? What is the
: SQL
: statement that you are executing?
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "venu" <venumadhav_2000@y...>
: Subject: [asp_database_setup] Timeout expired
:
:
: : I am venu ..I have some problem with Timeout
: expired.
: : Ihave some 20K records in my table.We do a search in
: a
: : perticular called as jproject and the description of
: : the table is as follows
: : CREATE TABLE [dbo].[JPROJECT] (
: : [pid] [int] IDENTITY (1, 1) NOT NULL ,
: : [candid] [int] NOT NULL ,
: : [project] [text] COLLATE
: SQL_Latin1_General_CP1_CI_AS
: : NOT NULL
: : ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
: : GO
: :
: : Here we paste the entire resume in the project
: column.
: : so when I give a serch for a perticular word it
: give
: : as
: :
: : Microsoft OLE DB Provider for ODBC Drivers error
: : '80040e31'
: :
: : [Microsoft][ODBC SQL Server Driver]Timeout expired
: :
: : /asp/index_search.asp, line 81
: :
: : it happens quite offen...and Ihave change
: : dbconnection.timeout =0 still I face the same
: problem
: :
: :
: : and the code at line number 81 is
: :
: : set rsPro
: : server.CreateObject("ADODB.recordset")(line 81)
: : rsPro.Open sql_pro,dbConn
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #5 by "Drew, Ron" <RDrew@B...> on Tue, 23 Jul 2002 08:05:15 -0400
|
|
Ken is correct. This sql statement will cause much pain. First you
should not be using the SELECT *, second by having 3 LIKE statements
with wildcards you are doing a full table search 3 times. Even nesting
the likes in nested selects will not help. Consider Ken's suggestion
for the Full Text Indexing.
Good luck,
Ron
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Tuesday, July 23, 2002 3:09 AM
To: ASP Database Setup
Subject: [asp_database_setup] Re: Timeout expired
What does the Query Execution Plan show in Query Analyser? Is the any
step that is very expensive?
Unfortunately, indexing the text field is not going to help because all
your predicates begin with wildcard characters ( '%predicate%' )
Have you considered using Full Text Indexing on the Project column?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "venu" <venumadhav_2000@y...>
Subject: [asp_database_setup] Re: Timeout expired
: the primary key is pid and candid is foregin key.
:
: and the qurey I execute is
:
: select * from jpoject where project like '%java%' and
: project like '%oracle% and project like '%9ias%'
:
: Venu
:
:
:
: What is your Primary Key? What indexes have you
: defined? What is the
: SQL
: statement that you are executing?
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "venu" <venumadhav_2000@y...>
: Subject: [asp_database_setup] Timeout expired
:
:
: : I am venu ..I have some problem with Timeout
: expired.
: : Ihave some 20K records in my table.We do a search in
: a
: : perticular called as jproject and the description of
: : the table is as follows
: : CREATE TABLE [dbo].[JPROJECT] (
: : [pid] [int] IDENTITY (1, 1) NOT NULL ,
: : [candid] [int] NOT NULL ,
: : [project] [text] COLLATE
: SQL_Latin1_General_CP1_CI_AS
: : NOT NULL
: : ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
: : GO
: :
: : Here we paste the entire resume in the project
: column.
: : so when I give a serch for a perticular word it
: give
: : as
: :
: : Microsoft OLE DB Provider for ODBC Drivers error
: : '80040e31'
: :
: : [Microsoft][ODBC SQL Server Driver]Timeout expired
: :
: : /asp/index_search.asp, line 81
: :
: : it happens quite offen...and Ihave change
: : dbconnection.timeout =3D0 still I face the same
: problem
: :
: :
: : and the code at line number 81 is
: :
: : set rsPro =3D
: : server.CreateObject("ADODB.recordset")(line 81)
: : rsPro.Open sql_pro,dbConn
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
%%email.unsub%%
|
|
|
View Cart
