bullo (4/3/2012)

---

Thank you all. I knew it was goona be a difficult post....I think that in order to pinpoint a problem there are things that should be taken for granted. I totally agree (who doesn't?) that the less times you query the db, the better, so i could write all in one query as suggested (select * ...where coach is active and vacationoverlap=0, and available=1..)in subqueries.... but trust me. Because the nature of the page and its options I need to make individual queries. Anyway...Bottom line is, that would be more of a design and data base theory. But if we forget for a minute about it, this is exactly what is happening here:

Forget Logic. I made a new test. I wrote to screen ALL generated queries and run a new page.

set db= server.CreateObject("ADODB.Connection")

db.open Application("conn_str")

Set rs = Server.CreateObject("ADODB.Recordset")

query1 = xxxxx

rs.open query1 ,db

query2 = xxxxx

rs.open query2,db

query3 = xxxxx

rs.open query3,db

query4 = xxxxx

rs.open query4,db

All Queries 1,2,3, and 4 run 250 times for 250 different coaches.

If I run this test against MS Access or MySql it takes aprox. 4000ms.

If I run this test against Sql Server takes aprox. 8800ms. !!!!!!

why is that?? again...if I measure latency for each query, it will be faster in Sql Sever, but all page is slower....

thanks again. Munscio