CODEWITHSUNDEEP
sqlasp-classic
Spanky
Spanky

Reputation: 709

Would like a more effective way to do a mutiple query using SQL in ASP

So I have two queries made against the same table. The query works but my results come back a bit slow. I'm also using two different variables (Shipment_D2 and Count_D2) Is there a better way to combine multiple queries while using different variables instead of opening a new connection every time I make a query?

 <% 
Set rscount11 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT Count FROM DivisionNew WHERE JMS_UpdateDateTime >= DATEADD(day,-7, GETDATE()) AND
JMSDay = '" & smDateArray (Weekday(date)) &"' AND Section = 'D2' order by JMS_UpdateDateTime desc" 
rscount11.Open sql, db

Count_D2 = rscount11 ("Count")

%>


 <% 
Set rscount12 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT Shipment FROM DivisionNew WHERE JMS_UpdateDateTime >= DATEADD(day,-7, GETDATE()) AND
 JMSDay = '" & smDateArray (Weekday(date)) &"' AND Section = 'D2' order by JMS_UpdateDateTime desc" 
rscount12.Open sql, db

Shipment_D2 = rscount12 ("Shipment")

 %>

Upvotes: 0

Views: 94

Answers (2)

John Saunders
John Saunders

Reputation: 161783

Why not include both columns in the SELECT query?

 <% 
Set rscount11 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT Count, Shipment FROM DivisionNew WHERE JMS_UpdateDateTime >= DATEADD(day,-7, GETDATE()) AND
JMSDay = '" & smDateArray (Weekday(date)) &"' AND Section = 'D2' order by JMS_UpdateDateTime desc" 
rscount11.Open sql, db

Count_D2 = rscount11 ("Count")
Shipment_D2 = rscount11 ("Shipment")

%>

Upvotes: 1

Declan_K
Declan_K

Reputation: 6826

Don't ever use select * if all you need is a count. You are transfering all the records from the DB into memory which is horribly inefficient. Let the Database do the work for you.

Your first query can be rewritten as follows, where the DB does the work and you only retrieve a sinlge value into memory rather than a pottentially huge dataset. Use the same apporach for your second query.

SELECT Count(*) FROM DivisionNew WHERE ...

Upvotes: 3

Related Questions