VBscript recordset Opens are very slow

Question

VBscript recordset Opens are very slow

StarNamer

SSCrazy Eights

Points: 8744
More actions
August 13, 2010 at 9:49 am

#103974

A couple of months ago I wrote an ASP menu page which used several simple database queries to retrieve information and build the menus. A section of the VBscript is:
SQL= "select MenuEntryID, IconFile, MenuLink, Description, MenuOrder from MenuEntry where MenuHeaderID=" & cstr(MenuRS("MenuHeaderID")) & " order by MenuOrder"
EntryRS.Open SQL,objDBConn
do while not EntryRS.EOF
SQL="select SessionVar, Compare, Value, TestOrder, useAND from MenuEntryTest where MenuEntryId = " & EntryRS("MenuEntryID") & " order by TestOrder"
TestRS.Open SQL, objDBConn
display = TestRS.EOF
As you can see, the queries are very simple, so the problem shouldn't be in the database.
A few weeks ago, the page would take only a few seconds to load, which is what I expected. Since then, I've needed to reinstall SQL Server 2008 R2 (it's a development system) and restored the database, but can't think if there's anything I haven't set correctly to 'break' things.
In any event, each of the VBscript Opens now takes approximately 2 seconds. This causes the page to take several minutes to load, which is obviously unacceptable.
By instrumenting the page to insert the sequence of SQL as comments, I've determined that running the entire series of queries in management studio takes about 4 seconds for all the queries (about 130).
So why are the VBscript Opens taking so long? Where should I be looking to fix this?
Thanks in advance.
Derek

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

whenever you upgrade a system, or restore a database to a higher version, as i understand it the statistics used to get the database may be mis-interpreted by the newer,higher version and performance can suck;

so the first thing you need to do is update statistics on all your tables, or rebuild the indexes, which als redoes the statistics anyway.

after that, I'd bet your performance issue goes away.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

StarNamer SSCrazy Eights Points: 8744 More actions · Answer 2

OK. I figured it out.

When I recreated the database, I must have changed the ODBC connector. Using a 'SQL Server' connector it's slow. Changing to a 'SQL Server Native Client 10.0' connector it's fine.

Derek