|
 |
asp_web_howto thread: SORT
Message #1 by williams@s... on Fri, 24 Aug 2001 10:05:02 -0400
|
|
Hi all,
Please give me help on how to use
Recordset.Sort
Thanks
William
Message #2 by "Ken Schaefer" <ken@a...> on Mon, 27 Aug 2001 14:57:12 +1000
|
|
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
mdprosortpropertyado.asp
Why do you want to use this? It requires expensive cursors, and doesn't
really tell you anything that you shouldn't know already (ie which columns
the recordset is sorted by...)
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <williams@s...>
Subject: [asp_web_howto] SORT
:
: Hi all,
:
: Please give me help on how to use
:
: Recordset.Sort
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by williams@s... on Mon, 27 Aug 2001 08:26:02 -0400
|
|
Ken,
The reason is I have a sql statement using 'LIKE strVar%' followed by
'ORDER BY'. It takes about 40~90seconds to process a record. I suspect the
ORDER BY may cuase the delay because of indexing (I guess). Before saving ,
the sql create a RS with none to 10 records from a database table of 7
millions records. So I tried to create a recordset first(0~10 records) and
then use SORT to save the processing time. However, the processing time was
not improved with the SORT. The long processing time is due to the creation
of the RS alone , not ORDER BY.
In my case, strVar is one of the input from user. Under a condition I need
to add "R" as suffix of strVar in order to distinguise it from last record
. That is,
record SN
1st abcef
2nd abcefR
3rd abcefRR
4th abcefRRR
etc
Everytime user input is abc, the asp program has to create a RS including
abc and any records starts with 'abc' and ends with 'R'. In practice, the
user input is a 20 alphanumeric characters and 90% of the 7 millions
records are starts with 'abc'. I think it is the reason for the long
processing time.
Now I changed my sql statement from 'LIKE strVar%' to
SN LIKE strVar%R OR SN=strVar
The processing time has been reduced to 5 secs.
Would you give me some opinion.
Thanks
William
"Ken Schaefer"
<ken@a... To: "ASP Web HowTo" <asp_web_howto@p...>
tic.com> cc:
Subject: [asp_web_howto] Re: SORT
08/27/2001
12:57 AM
Please respond
to "ASP Web
HowTo"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
mdprosortpropertyado.asp
Why do you want to use this? It requires expensive cursors, and doesn't
really tell you anything that you shouldn't know already (ie which columns
the recordset is sorted by...)
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <williams@s...>
Subject: [asp_web_howto] SORT
:
: Hi all,
:
: Please give me help on how to use
:
: Recordset.Sort
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by Kyle Burns <kburns@c...> on Mon, 27 Aug 2001 13:10:42 -0500
|
|
Have you tried using a stored procedure to help reduce your processing time?
=================================
Kyle M. Burns, MCSD
ECommerce Technology Manager
Centra Credit Union
kburns@c...
-----Original Message-----
From: williams@s... [mailto:williams@s...]
Sent: Monday, August 27, 2001 7:26 AM
To: ASP Web HowTo
Subject: [asp_web_howto] Re: SORT
Ken,
The reason is I have a sql statement using 'LIKE strVar%' followed by
'ORDER BY'. It takes about 40~90seconds to process a record. I suspect the
ORDER BY may cuase the delay because of indexing (I guess). Before saving ,
the sql create a RS with none to 10 records from a database table of 7
millions records. So I tried to create a recordset first(0~10 records) and
then use SORT to save the processing time. However, the processing time was
not improved with the SORT. The long processing time is due to the creation
of the RS alone , not ORDER BY.
In my case, strVar is one of the input from user. Under a condition I need
to add "R" as suffix of strVar in order to distinguise it from last record
. That is,
record SN
1st abcef
2nd abcefR
3rd abcefRR
4th abcefRRR
etc
Everytime user input is abc, the asp program has to create a RS including
abc and any records starts with 'abc' and ends with 'R'. In practice, the
user input is a 20 alphanumeric characters and 90% of the 7 millions
records are starts with 'abc'. I think it is the reason for the long
processing time.
Now I changed my sql statement from 'LIKE strVar%' to
SN LIKE strVar%R OR SN=strVar
The processing time has been reduced to 5 secs.
Would you give me some opinion.
Thanks
William
Message #5 by williams@s... on Mon, 27 Aug 2001 15:47:22 -0400
|
|
I haven't tried to use stored procedures as I'm not too familiar with it
and I get tight schedule on my program. It should be not too difficult,
right. Any short cut.?
BTW, my asp always set at Script.TimeOut = 3 . Sometimes it gives me an
script timeout error after 15sec, sometimes after 17secs. However,
sometimes it completed a transaction after 21sec without script timeout
error.
I don't quite understand when the script timeout error happen. Please give
me some ideas.
Thanks
William
Message #6 by "Ken Schaefer" <ken@a...> on Tue, 28 Aug 2001 16:29:05 +1000
|
|
Do you have an index on this field?
Are you using a stored procedure?
What type of cursor/locktype are you using?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <williams@s...>
Subject: [asp_web_howto] Re: SORT
: Ken,
:
: The reason is I have a sql statement using 'LIKE strVar%' followed by
: 'ORDER BY'. It takes about 40~90seconds to process a record. I suspect the
: ORDER BY may cuase the delay because of indexing (I guess). Before saving
,
: the sql create a RS with none to 10 records from a database table of 7
: millions records. So I tried to create a recordset first(0~10 records) and
: then use SORT to save the processing time. However, the processing time
was
: not improved with the SORT. The long processing time is due to the
creation
: of the RS alone , not ORDER BY.
:
: In my case, strVar is one of the input from user. Under a condition I need
: to add "R" as suffix of strVar in order to distinguise it from last record
: . That is,
:
: record SN
: 1st abcef
: 2nd abcefR
: 3rd abcefRR
: 4th abcefRRR
:
: etc
:
: Everytime user input is abc, the asp program has to create a RS including
: abc and any records starts with 'abc' and ends with 'R'. In practice, the
: user input is a 20 alphanumeric characters and 90% of the 7 millions
: records are starts with 'abc'. I think it is the reason for the long
: processing time.
:
: Now I changed my sql statement from 'LIKE strVar%' to
:
: SN LIKE strVar%R OR SN=strVar
:
: The processing time has been reduced to 5 secs.
:
: Would you give me some opinion.
:
: Thanks
: William
:
Message #7 by williams@s... on Tue, 28 Aug 2001 09:12:12 -0400
|
|
I'm using LockOptimistic
No strored procedure is being used.
I'm not familiar with Index. I think the table is indexed from what I see
in the Manage Indexes dialogbox
IX_table No field1,SN, field3, field4
IX_sn No SN
IX_field1 No field1
IX_field5 No field5
However, when I opened the table on one of my user, I don't see the records
are indexed on any of the field. On the other hand, when I open the same
table(of same Manage Indexes dialogbox as above) of the other user with the
long transaction time problem(7 millions records in the table), the reocrds
are sorted by date(field6).
Note: every user has his own sql server
William
"Ken Schaefer" <ken@a...>
To: "ASP Web HowTo" <asp_web_howto@p...>
cc:
Subject: [asp_web_howto] Re: SORT
08/28/2001 02:29 AM
Do you have an index on this field?
Are you using a stored procedure?
What type of cursor/locktype are you using?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <williams@s...>
Subject: [asp_web_howto] Re: SORT
: Ken,
:
: The reason is I have a sql statement using 'LIKE strVar%' followed by
: 'ORDER BY'. It takes about 40~90seconds to process a record. I suspect
the
: ORDER BY may cuase the delay because of indexing (I guess). Before saving
,
: the sql create a RS with none to 10 records from a database table of 7
: millions records. So I tried to create a recordset first(0~10 records)
and
: then use SORT to save the processing time. However, the processing time
was
: not improved with the SORT. The long processing time is due to the
creation
: of the RS alone , not ORDER BY.
:
: In my case, strVar is one of the input from user. Under a condition I
need
: to add "R" as suffix of strVar in order to distinguise it from last
record
: . That is,
:
: record SN
: 1st abcef
: 2nd abcefR
: 3rd abcefRR
: 4th abcefRRR
:
: etc
:
: Everytime user input is abc, the asp program has to create a RS including
: abc and any records starts with 'abc' and ends with 'R'. In practice, the
: user input is a 20 alphanumeric characters and 90% of the 7 millions
: records are starts with 'abc'. I think it is the reason for the long
: processing time.
:
: Now I changed my sql statement from 'LIKE strVar%' to
:
: SN LIKE strVar%R OR SN=strVar
:
: The processing time has been reduced to 5 secs.
:
: Would you give me some opinion.
:
: Thanks
: William
Message #8 by "George Draper" <gdraper@c...> on Tue, 28 Aug 2001 16:45:47 -0400
|
|
I suspect your problem is related to running a LIKE constraint on the
field, which must be a non-unique field, otherwise you would probably use
a different comparison. The longer the length of the field you're looking
at, the longer the query time. Running a LIKE comparison on a 7 million
record table could give you the processing times you are reporting
depending on all the other factors such as other load on the db server,
etc.
>>> williams@s... 08/27/01 03:47PM >>>
I haven't tried to use stored procedures as I'm not too familiar with it
and I get tight schedule on my program. It should be not too difficult,
right. Any short cut.?
BTW, my asp always set at Script.TimeOut =3D 3 . Sometimes it gives me an
script timeout error after 15sec, sometimes after 17secs. However,
sometimes it completed a transaction after 21sec without script timeout
error.
I don't quite understand when the script timeout error happen. Please give
me some ideas.
Thanks
William
|
|
|
View Cart
