SQL Server vs ACCESS - ACCESS Seemed Faster!

Hello Community

After having some scary times with a classic ASP app with an ACCESS back-end, I broke and migrated it to SQL Server (Bruce if you're watching, all is forgiven!)

The migration was remarkably painless thanks to past posts on forums like this one.

I know that in a perfect world you'd create stored procedures in a SQL Server database to make your queries happen a bit quicker, but the ACCESS queries were about 100% quicker than the the same ones I'm seeing since the migration (i.e., the SQL server ones take twice the time to execute). Everything else is the same; same data, same SQL queries. Just the data is in the SQL Server and the connection string is pointing there instead of to the MDB.

Would this be expected?

Perhaps I'm doing something horribly inefficient in opening the database. Below is a typical snippet for your comment:

Set conn = Server.CreateObject("ADODB.Connection")
strconn = "Provider=sqloledb;Data Source=mssql09;Initial Catalog=db_999999_My_DB;User Id=My_user;Password=My_Password;"
Conn.mode = 3
Conn.Open strconn

Thanks for your comments.

PeteB

 

... I forgot to mention that the main culprit is a query that returns 100 or so read-only records which Iloop through repeatedly looking for certain records, then do a movefirst and trawl through them some more.


This is a kind of messy, I know, but the trawling and page rendering used to take 4-5 seconds. Since going to SQL Server it takes 7-10.


The recordset creation looks like this:


strSQL = "SELECT Questions.Qu_QuestionNum, Questions.Qu_QuestionText, Questions.Qu_AnswerA, Questions.Qu_AnswerB, Questions.Qu_AnswerC, Questions.Qu_AnswerD, Questions.Qu_AnswerE, Questions.Qu_AnswerF, Questions.Qu_CorrectAnswer, Qu_Category, Exams.Ex_DisplayCandidateAnswers, Exams.Ex_DisplayCorrectAnswers, Exams.Ex_ShowAnswerLabels, Exams.Ex_Name, Exams.Ex_ShowLastXMins, Exams.Ex_QuestionsToAsk, Questions.Qu_ExamFK FROM Questions INNER JOIN Exams ON Questions.Qu_ExamFK = Exams.Ex_Id WHERE (((Questions.Qu_ExamFK)=" & rs("Rt_ExamFK") & "))"
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn


I don't know if it's the recordset retrieve (the joins?) or the looping (rs.movefirst) that's the slow bit, although there's very few records in the tables at present.


Any thoughts would be welcomed.


PeteB

 

Thanks Bruce

In the end I did a GETROWS and operated on the records in the resultant array.

This was much quicker and meant that I could release/close the database a lot quicker.

I haven't done a test with more than 3 people but it's looking promising. Before making this GETROWS change with 12 people I was getting regular 'Database not available' type errors.

I'm hopeful that by lightening the load on the recordsets that this will go away.

Thanks for the response, and the forum.

PeteB