cdpMat
asked on
Performance troubles
I'm using sql server 7 with asp. The database the script is searching contains around 5000 records.
There is a real time difference between these two queries:
SELECT * FROM classicalconcerts where vetted = 'Yes' and performancedate >= '30/05/01' and performancedate <= '30/05/01' and publishdate <= '30/05/01' order by performancedate, country, county, city, venueid asc
(returns 15 results)
and
SELECT * FROM classicalconcerts where vetted = 'Yes' and performancedate >= '30/05/00' and performancedate <= '30/05/02' and publishdate <= '30/05/01' order by performancedate, country, county, city, venueid asc
(returns 4000 results)
The only difference is the date span.
The code for returning the records is as follows:
Set oRs = Server.CreateObject("ADODB .Recordset ")
oRs.ActiveConnection = conn
ors.CursorLocation = adUseClient
oRs.Open(SQL)
if ors.EOF then
call NoRecordsFound
end if
ors.movefirst
oRs.cachesize=mypagesize
howmanyrecs=oRs.recordcoun t
numbrecs = howmanyrecs
oRs.pagesize=mypagesize
maxcount=cint(oRs.pagecoun t)
oRs.absolutepage=mypage
allconcertdata = ors.getrows
ors.Close
set ors = nothing
The script then displays the first 15 records on the page and a 'next' button.
The time difference is about 30 seconds and I can't work out why there should be a difference. Any ideas?
Thanks.
There is a real time difference between these two queries:
SELECT * FROM classicalconcerts where vetted = 'Yes' and performancedate >= '30/05/01' and performancedate <= '30/05/01' and publishdate <= '30/05/01' order by performancedate, country, county, city, venueid asc
(returns 15 results)
and
SELECT * FROM classicalconcerts where vetted = 'Yes' and performancedate >= '30/05/00' and performancedate <= '30/05/02' and publishdate <= '30/05/01' order by performancedate, country, county, city, venueid asc
(returns 4000 results)
The only difference is the date span.
The code for returning the records is as follows:
Set oRs = Server.CreateObject("ADODB
oRs.ActiveConnection = conn
ors.CursorLocation = adUseClient
oRs.Open(SQL)
if ors.EOF then
call NoRecordsFound
end if
ors.movefirst
oRs.cachesize=mypagesize
howmanyrecs=oRs.recordcoun
numbrecs = howmanyrecs
oRs.pagesize=mypagesize
maxcount=cint(oRs.pagecoun
oRs.absolutepage=mypage
allconcertdata = ors.getrows
ors.Close
set ors = nothing
The script then displays the first 15 records on the page and a 'next' button.
The time difference is about 30 seconds and I can't work out why there should be a difference. Any ideas?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What do you mean by this?
1. check to make sure that you are using indexes other than the primary key.
1. check to make sure that you are using indexes other than the primary key.
go buy "Special Edition Using SQL Server 7.0" Form QUE press, and read the chapter about "Indexes" - they speed things up alot.
i also just remembered - set your ADO cursor type to Server - this will tell the server not to send over any records until they are actually being used.
i also just remembered - set your ADO cursor type to Server - this will tell the server not to send over any records until they are actually being used.
Hi cdpMat,
hmm... If you are trying to display 15 records at a time, you might want to try this technique that I came up with when I needed to display x number of items from a catalogue per page, but my catalogue only contain a few hundred items, nothing like 5000 records so I don't know how it would perform for you, but it's worth a try.
Let's say I have a table of cars. And each car has a CarID, which will be the primary key. And two fields, called CarName, and MakeDate. So now I want to display 20 cars at a time starting from a make date to the current date.
1st. SELECT TOP 20 * FROM tblCars WHERE MakeDate >= '01/01/1990' AND CarID > LastCarID ORDER BY CarID (check that SQL statement I don't remember if that is the proper syntax)
2nd. Print out the Recordset
3rd. Place a link in your next button which will call back this page, but will pass the CarID of the last record in the record set. Use this CarID the next time around to start the selecting of SELECT TOP 20.
Hope this helps.
hmm... If you are trying to display 15 records at a time, you might want to try this technique that I came up with when I needed to display x number of items from a catalogue per page, but my catalogue only contain a few hundred items, nothing like 5000 records so I don't know how it would perform for you, but it's worth a try.
Let's say I have a table of cars. And each car has a CarID, which will be the primary key. And two fields, called CarName, and MakeDate. So now I want to display 20 cars at a time starting from a make date to the current date.
1st. SELECT TOP 20 * FROM tblCars WHERE MakeDate >= '01/01/1990' AND CarID > LastCarID ORDER BY CarID (check that SQL statement I don't remember if that is the proper syntax)
2nd. Print out the Recordset
3rd. Place a link in your next button which will call back this page, but will pass the CarID of the last record in the record set. Use this CarID the next time around to start the selecting of SELECT TOP 20.
Hope this helps.
You could use the fast hint so that the server optimises to return the records you need early before returnnig the rest.
Means you can't use disconnected record sets but doesn't look as though you are anyway.
Means you can't use disconnected record sets but doesn't look as though you are anyway.
cdpMat,
Some of these questions have been open for some time. Please resolve them appropriately as soon as possible.
https://www.experts-exchange.com/jsp/qShow.jsp?ta=macintosh&qid=20156951
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20258488
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=11500198
https://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=20238437
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20246764
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20147617
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20126948
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20124264
https://www.experts-exchange.com/jsp/qShow.jsp?ta=xml&qid=20147856
https://www.experts-exchange.com/jsp/qShow.jsp?ta=director&qid=20152874
Thanks,
Netminder
Community Support Moderator
Experts Exchange
Some of these questions have been open for some time. Please resolve them appropriately as soon as possible.
https://www.experts-exchange.com/jsp/qShow.jsp?ta=macintosh&qid=20156951
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20258488
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=11500198
https://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=20238437
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20246764
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20147617
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20126948
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20124264
https://www.experts-exchange.com/jsp/qShow.jsp?ta=xml&qid=20147856
https://www.experts-exchange.com/jsp/qShow.jsp?ta=director&qid=20152874
Thanks,
Netminder
Community Support Moderator
Experts Exchange
There has been no activity in this question in quite some time, and it looks like it has been abandoned. As part of our ongoing mission to clean up the topic areas, a Moderator will finalize this question within the next seven (7) days. At that time, either I or one of the other Moderators will force/accept the comment of dredge.
DO NOT ACCEPT THIS COMMENT AS AN ANSWER. If you have further comments on this question or the recommendation, please leave them here.
Netminder
Community Support Moderator
Experts Exchange
DO NOT ACCEPT THIS COMMENT AS AN ANSWER. If you have further comments on this question or the recommendation, please leave them here.
Netminder
Community Support Moderator
Experts Exchange
Force/accepted by
Netminder
Community Support Moderator
Experts Exchange
Netminder
Community Support Moderator
Experts Exchange
1. check to make sure that you are using indexes other than the primary key.
2. If you can create a stored procedure that accepts your page size, and current page desired and only return the records needed, that will speed things up drastically. I believe that ado returns all 4000 records for you to parse. that is where your time consumption is.