optimising my query,

[taval]

Currently I have two queries that access different inforamtion from tables, I was wondering if I could get the same inforamtion from using just one query. Currently it takes too long to process.<br>---------------------------------------------------------<br><br>StrQuery = &quot;SELECT CustName from Customer where CustID = &quot; & rsbac(&quot;CustId&quot<br><br>Set rsba = Server.CreateObject (&quot;ADODB.Recordset&quot<br> rsba.Open StrQuery ,&quot;DSN=Architron; UID=sa; PWD=&quot;<br><br>StrQ = &quot;SELECT Max(FileDescription.UploadDate) as LastBackup from FileDescription where FileDescription.CustId = &quot; & rsbac(&quot;CustId&quot<br><br>Set rsbacus = Server.CreateObject (&quot;ADODB.Recordset&quot<br> rsbacus.Open StrQ ,&quot;DSN=Architron; UID=sa; PWD=&quot;<br><br>---------------------------------------------------------<br><br>Grateful for any help thanks. If you need more info just post telling me, thanks.<br>

 

[Guest_imported]

It would be:<br>&quot;Select c.CustName,max(fd.uploaddate)from Customer c,FileDescription fd where c.custID = &quot; & rbac(&quot;custID&quot& &quot;c.custID = fd.custID&quot;

 

[taval]

thank you guestg, you're query help speed up my code.<br>Thanks again.<br><br>Taha

 

[taval]

The script still runs slowly I have several ideas on how to speed my code up, but I need to know how. My code currently looks like this:<br><br>----------------------------------------------<br><br>Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot<br>conn.open &quot;DSN=Architron;uid=sa;pwd=&quot;<br>Set rsbac = Conn.Execute(&quot;sp_usersnotbackedup -&quot;&numDays&&quot;&quot<br><br>if NOT rsbac.EOF then<br><br>dLastMod = DateAdd(&quot;d&quot;, -5, Now())<br> datelast = datelast<br> Response.Write dotype & &quot;&lt;br&gt;&quot;<br> dLastMod = dLastMod & &quot;12:00:00 AM&quot;<br> Response.Write &quot;Current Date: &quot; & Now() & &quot;&lt;br&gt;&quot;<br> Response.Write message<br><br>rsbac.MoveFirst<br><br>%&gt;<br>&lt;Table align=&quot;center&quot; WIDTH=&quot;600&quot;&gt;<br>&lt;tr&gt;<br>&lt;TD bgcolor=#CCCCCC WIDTH=&quot;100&quot;&gt;Customer ID&lt;/TD&gt;<br>&lt;TD bgcolor=#CCCCCC WIDTH=&quot;160&quot;&gt;Customer Name&lt;/TD&gt;<br>&lt;TD bgcolor=#CCCCCC WIDTH=&quot;160&quot;&gt;Last Backup&lt;/TD&gt;<br> &lt;%<br>While Not rsbac.EOF<br><br>StrQuery = &quot;Select c.CustName,max(fd.UploadDate) as LastBackup from Customer c,FileDescription fd where c.CustID = &quot; & rsbac(&quot;CustId&quot & &quot; and c.CustID = fd.CustId group by c.CustName&quot; <br><br>Set rsDetail = Server.CreateObject (&quot;ADODB.Recordset&quot<br>Detail.Open StrQuery ,&quot;DSN=Architron; UID=sa; PWD=&quot;<br><br>%&gt;<br><br> <br>&lt;tr&gt;&lt;TD&nbsp;&nbsp;align = center BGCOLOR=#FFFFCC&gt;&lt;% Response.Write rsbac(&quot;CustId&quot %&gt;&lt;/TD&gt;<br>&lt;TD BGCOLOR=#FFFFCC&gt;&lt;% Response.Write rsDetail(&quot;CustName&quot %&gt;&lt;/TD&gt;<br>&lt;TD BGCOLOR=#FFFFCC&gt;&lt;% Response.Write rsDetail&quot;LastBackup&quot %&gt;&lt;/TD&gt;<br>&lt;/tr&gt;<br>&lt;% <br>rsbac.MoveNext<br> Wend<br>%&gt;<br> &lt;/table&gt;<br><br>-------------------------------------------------<br><br>Firstly I open I recordset (rsbac) from which I get all the CustID numbers, I then open another recordset (rsDetail) which contains the details of the customers which uses the CustID from rsbac.<br><br>In the loop&nbsp;&nbsp;While Not rsbac.EOF<br>I have <br>Set rsDetail = Server.CreateObject (&quot;ADODB.Recordset&quot<br>Detail.Open StrQuery ,&quot;DSN=Architron; UID=sa; PWD=&quot;<br>which I think slows everthing down since I keep on having to set the recordset. I've tried moving the line<br>Set rsDetail = Server.CreateObject (&quot;ADODB.Recordset&quot<br>out of the loop, but I get errors saying the operation is not allowed if the object is open. Would it be faster if I left &quot;Set rsDetail = Server.CreateObject (&quot;ADODB.Recordset&quot&quot; out of the loop? how would I then make it work?<br><br>Another thing a reckon that could be slowing the process is where I have to use the rsbac. Since this gets all the custID, would it be possible to extract all the values into an array and then close the recordset. And then use an array loop to feed the values into rsDetail part of the code, this would mean I would only have to deal with one recordset. How would I do this?<br><br>Grateful for any help.<br>Thanks.<br><br><br><br>