Link to home
Start Free TrialLog in
Avatar of cdpMat
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.recordcount
     numbrecs = howmanyrecs
     oRs.pagesize=mypagesize
     maxcount=cint(oRs.pagecount)
     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
Avatar of dredge
dredge

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of John844
John844

there are a couple things that I would check

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.

Avatar of cdpMat

ASKER

What do you mean by this?

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.
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.

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.
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
Force/accepted by

Netminder
Community Support Moderator
Experts Exchange