Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pulling Data from a Database is slow

Status
Not open for further replies.
JuanjoZun

JuanjoZun

Programmer
Joined
Jul 20, 2002
Messages
82
Location
MX
Hello

I'm working in an ASP page premade (for another guy), so I'm trying to improve it. I realize that, He show a Query from two tables (cross reference) and it's very slow (the Query)... the maximun number of records in the query I've seen, is 61. He make multiple query's to the same table... I don't know why.

Is there a way to make the query faster?
Or, do you think I need to improve this multiquery?
Or, Do you know why is too slow?

Juanjo

Sorry, about this stoopid thread, but it's 8:30 pm and i'm dizzy after 9 hours working in this "beauty" page...

[morning] [pc3]


Follow the dark side, so you can reach the light. [/color}
 
Sort by date Sort by votes
61 rows returned is nothing and should execute immediately.

show the code...

=========================================================
try { succeed(); } catch(E) { tryAgain(); }
-jeff
 
Upvote 0 Downvote
here is the code: Any advice, no problem.
Sorry, is Spanish

Code: 
<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>



<html>
<head>
<title>Untitled Document</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>

<body>
<form>
<% Server.ScriptTimeOut = 1000 %>
<table width=&quot;100%&quot; border=&quot;1&quot; align=&quot;center&quot;>

					<% 'Crear Fechas %>

	<%  fecha_Ini=Request.form(&quot;ex4&quot;)
		fecha_fin=Request.Form(&quot;ex5&quot;)
		puesto = request.Form(&quot;puesto&quot;)
		templeado = request.Form(&quot;templeado&quot;)
	  	all_emp = templeado
	  	all_puesto = puesto
		response.Write(f_ini) & &quot;<br>&quot;
	  	response.Write(f_fin) & &quot;<br>&quot;
	%>

				 		
    	

      <% 'Asignar fechas Inicial y Final a Variables %>
      <% f_ini = Fecha_Ini %>
      <% f_fin = Fecha_Fin%>
      <% 'Query para consultar Fechas %>
      <%
       Response.write &quot;Fecha Ini: &quot; & f_ini
       Response.Write &quot; Fecha Fin: &quot; & f_fin 

		'empleado=request.form(&quot;empleado_name&quot;)
		set conn=Server.CreateObject(&quot;ADODB.Connection&quot;)
			constr = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & Server.MapPath(&quot;personal.mdb&quot;) & &quot;;Persist Security Info=False&quot;
			conn.Open constr
		set rs_f=Server.CreateObject(&quot;ADODB.recordset&quot;)
			sqltxt_f = &quot;select distinct(fecha) as fec from regent where fecha >='&quot;& f_ini&&quot;' and fecha <='&quot;& f_fin&&quot;' order by regent.fecha&quot;
			
			rs_f.Open sqltxt_f, conn,1,2
		    Response.Write &quot;Fecha: &quot; & rs_f.RecordCount & &quot;<br>&quot;
				
	%>
      <% 'Query para contar los registros %>
      <% 
		set rs_cuenta=Server.CreateObject(&quot;ADODB.recordset&quot;)
			sqltxt_cuenta = &quot;select count(id_regent) as cuenta_rgs from regent where fecha >='&quot;& f_ini&&quot;' and fecha <='&quot;& f_fin&&quot;'&quot;
			rs_cuenta.Open sqltxt_cuenta, conn, 1, 2
			cuenta_fecs = rs_cuenta.fields(&quot;cuenta_rgs&quot;)
			Response.Write &quot;RsCuenta: &quot; & rs_cuenta.RecordCount & &quot;<br>&quot;
			
	 %>
      	<% 'Llenar encabezados con fechas de cada dia %>
	
		<% if rs_cuenta.fields(&quot;cuenta_rgs&quot;) = 0 then %>
			<%else%>
				<tr bgcolor=&quot;#CCCCCC&quot;> 
					<td rowspan=&quot;2&quot; bordercolor=&quot;2&quot;> <div align=&quot;center&quot;><font color=&quot;#990000&quot;>Nombre del Empleado</font></div></td>
					<% rs_f.movefirst %>
					<%do until rs_f.eof%>
						<td colspan=&quot;3&quot; bordercolor=&quot;2&quot;> <div align=&quot;center&quot;><font color=&quot;#990000&quot;><%response.Write(rs_f.fields(&quot;fec&quot;))%></font></div><br></td>
						<%rs_f.movenext%>
					<%loop%>
				</tr>
				<tr bgcolor=&quot;#CCCCCC&quot;> 		
					<% rs_f.movefirst %>
					<%do until rs_f.eof%>
						<td colspan=&quot;1&quot; bordercolor=&quot;2&quot;> <div align=&quot;center&quot;><font color=&quot;#990000&quot;>Entrada</font></div></td>
						<td colspan=&quot;1&quot; bordercolor=&quot;2&quot;> <div align=&quot;center&quot;><font color=&quot;#990000&quot;>Salida</font></div></td>
						<td colspan=&quot;1&quot; bordercolor=&quot;2&quot;> <div align=&quot;center&quot;><font color=&quot;#990000&quot;>Justificado</font></div></td>
						<%rs_f.movenext%>
					<%loop%>
	
			<% 'Seleccionar Empleados deacuerdo a fechas seleccionadas %>
	        <% If all_emp = &quot;todos&quot; then all_emp= &quot;%&quot;
	           If all_puesto = &quot;todos&quot; then all_puesto= &quot;%&quot;
			%>
			<%
	    		set rs_nom=Server.CreateObject(&quot;ADODB.recordset&quot;)
				sqltxt_nom = &quot;SELECT d_gen.cod, d_gen.apat, d_gen.amat, d_gen.nom, d_gen.templeado, d_gen.puesto, regent.fecha &quot; _
						   & &quot;FROM regent RIGHT JOIN d_gen ON regent.cod = d_gen.cod &quot; _
						   & &quot;GROUP BY d_gen.cod, d_gen.apat, d_gen.amat, d_gen.nom, d_gen.templeado, d_gen.puesto, regent.fecha &quot; _
						   & &quot;HAVING (((d_gen.templeado) Like '&quot; & all_emp & &quot;') AND ((d_gen.puesto) Like '&quot; & all_puesto & &quot;') AND ((regent.fecha)>='&quot; & f_ini & &quot;'And (regent.fecha)<='&quot; & f_fin & &quot;')) &quot; _
						   & &quot;ORDER BY d_gen.apat, d_gen.amat, d_gen.nom&quot;
				
				
				rs_nom.Open sqltxt_nom, conn, 1, 2
				Response.Write sqltxt_nom & &quot;<br>&quot;
			    Response.Write all_emp & &quot; &quot; & all_puesto & &quot;<br>&quot;
                cuen_per = rs_nom.Recordcount
                Response.Write (cuen_per) & &quot;<br>&quot;
            
			'Query para contar los registros 

			
			
	 end if %>							<% 'Llena columna de nombres de Empleados %>

	<% if cuen_per = 0 then %>
	<tr>
      <td bordercolor=&quot;#FFFFFF&quot; colspan=&quot;<%= &quot;&quot;& cuenta_fecs&&quot;&quot; %>&quot; align=&quot;center&quot;><div align=&quot;center&quot;><font color=&quot;#FF0000&quot; size=&quot;4&quot;>¡No se encontraron 
          registros deacuerdo a los criterios seleccionados!</font></div></td>
    </tr>
	<tr>
      <td bordercolor=&quot;#FFFFFF&quot; colspan=&quot;<%= &quot;&quot;& cuenta_fecs&&quot;&quot; %>&quot; align=&quot;center&quot;><div align=&quot;center&quot;><font color=&quot;#FF0000&quot; size=&quot;4&quot;><a href=&quot;JavaScript:history.back()&quot; ><font color=&quot;#000000&quot; size=&quot;4&quot;>Regresar...</font></a> </font></div></td>
    </tr>
	<%else%>
					<% 'Desplegar Hora de Entrada y Hora de Salida %>

	
	<% rs_nom.movefirst %>
	<% do until rs_nom.eof %>
		<% codigo_hora = rs_nom.fields(&quot;cod&quot;) %>
		<tr bgcolor=&quot;#FFFFFF&quot;> 
			<td colspan=&quot;1&quot; bordercolor=&quot;2&quot;> <div align=&quot;left&quot;><font color=&quot;#000000&quot;><%response.Write(rs_nom.fields(&quot;nom&quot;))%> <%response.Write(rs_nom.fields(&quot;apat&quot;))%> <%response.Write(rs_nom.fields(&quot;amat&quot;))%></font></div></td>
			<% rs_f.movefirst %>
			<% do until rs_f.eof %>
				<% fecha_hora = rs_f.fields(&quot;fec&quot;) %>
				<% fecha_hora = cdate(fecha_hora) %>		
				<%
					set rs_h_max=Server.CreateObject(&quot;ADODB.recordset&quot;)
					sqltxt_h_max = &quot;select  max(regent.hora) as h_max from d_gen, regent where d_gen.cod = regent.cod and d_gen.cod = '&quot;& codigo_hora&&quot;' and cdate(regent.fecha) = '&quot;& fecha_hora&&quot;'&quot;			
					rs_h_max.Open sqltxt_h_max, conn
					
					hora_max_primera = rs_h_max.fields(&quot;h_max&quot;)
					
					set rs_h_min=Server.CreateObject(&quot;ADODB.recordset&quot;)
					sqltxt_h_min = &quot;select  min(regent.hora) as h_min  from d_gen, regent where d_gen.cod = regent.cod and d_gen.cod = '&quot;& codigo_hora&&quot;' and cdate(regent.fecha) = '&quot;& fecha_hora&&quot;'&quot;			
					rs_h_min.Open sqltxt_h_min, conn
					
					hora_min_primera = rs_h_min.fields(&quot;h_min&quot;)
					
					set rs_justifica=Server.CreateObject(&quot;ADODB.recordset&quot;)
					sqltxt_justifica = &quot;select  distinct(justifica) as c_justifica  from d_gen, regent where d_gen.cod = regent.cod and d_gen.cod = '&quot;& codigo_hora&&quot;' and cdate(regent.fecha) = '&quot;& fecha_hora&&quot;'&quot;			
					rs_justifica.Open sqltxt_justifica, conn

					cuenta_justifica = rs_justifica.fields(&quot;c_justifica&quot;)

				%>
				<% rs_justifica.movefirst %>
				<% fondo = &quot;#FFFFFF&quot; %>
				<% just = &quot;&quot; %>
				<% do until rs_justifica.eof %>
				<% If rs_justifica.fields(&quot;c_justifica&quot;) = 1 Then %>
				<% fondo = &quot;#009933&quot; %>
				<% just = &quot;justificado&quot; %>
				<% Else %>
				<% End If %>
				<% rs_justifica.movenext %>
				<% loop %>
				
				<% If hora_max_primera <> &quot;&quot; and hora_min_primera <> &quot;&quot; Then %>
					<% If cdate(hora_max_primera) > cdate(hora_min_primera) Then %>
				
						<td colspan=&quot;1&quot;> <div align=&quot;left&quot;><font color=&quot;#000000&quot;><%response.Write(rs_h_min.fields(&quot;h_min&quot;))%> </font></div></td>
						<td colspan=&quot;1&quot; > <div align=&quot;left&quot;><font color=&quot;#000000&quot;><%response.Write(rs_h_max.fields(&quot;h_max&quot;))%> </font></div></td>
						<td colspan=&quot;1&quot; bgcolor=&quot;<%= &quot;&quot;& fondo&&quot;&quot; %>&quot;> <div align=&quot;left&quot;><font color=&quot;#000000&quot;> <%=just%></font></div></td>
					<% Else %>
						<td colspan=&quot;1&quot; > <div align=&quot;left&quot;><font color=&quot;#000000&quot;><%response.Write(rs_h_max.fields(&quot;h_max&quot;))%> </font></div></td>
						<td colspan=&quot;1&quot; > <div align=&quot;left&quot;><font color=&quot;#000000&quot;><%response.Write(rs_h_min.fields(&quot;h_min&quot;))%> </font></div></td>
						<td colspan=&quot;1&quot; bgcolor=&quot;<%= &quot;&quot;& fondo&&quot;&quot; %>&quot;> <div align=&quot;left&quot;><font color=&quot;#000000&quot;> <%=just%></font></div></td>
				
					<% End If %>
				<% Else %>
									
				<% End If %>


	
		<% rs_f.movenext %>	
		<% loop %>
		</tr>
	<% rs_nom.movenext %>		
	<% loop %>
	
	
  <%end if%>
  
</table>
</form>
</body>
</html>
Thanks...

juanjo

Follow the dark side, so you can reach the light. [/color}
 
Upvote 0 Downvote
The Table Regent has 26,742 more or less, maybe that is the problem.... [thumbsup]

Regent table is used to save employees' arrive (check) and employees' left, so is very huge... I think I need to work on that.

whatever, tell me any idea

juanjo


Follow the dark side, so you can reach the light. [/color}
 
Upvote 0 Downvote
from what i understand, &quot;select distinct&quot; is a very costly operation.

it also looks like you have some database calls inside some nested loops, which can multiply quickly into thousands of database calls per page hit depending on how many results are in rs_nom and rs_f

i had a situation like this where nested loops caused about 250,000 calls to the db per page hit...i cut it down to about a hundred by getting one large recordset and filtering server-side if necessary. rendering time for the page dropped from 1.5 minutes to 13 seconds.



=========================================================
try { succeed(); } catch(E) { tryAgain(); }
-jeff
 
Upvote 0 Downvote
ok... just now, I'm working in cut down some querys... and make a Larger recordset... what do you mean with &quot;filtering server-side if necessary&quot;?

Follow the dark side, so you can reach the light. [/color}
 
Upvote 0 Downvote
ok... just now, I'm working in cut down some querys... and make a Larger recordset... what do you mean with &quot;filtering server-side if necessary&quot;?
Thanks

Juanjo

Follow the dark side, so you can reach the light. [/color}
 
Upvote 0 Downvote
you can use rs.filter to filter a recordset without going back to the server: just assign it a valid sql where clause without the word &quot;where&quot;

set rs = conn.execute(&quot;select * from bigTable&quot;)
rs.filter = &quot;f_name = 'joe'&quot;
rs.filter = &quot;l_name = 'smith'&quot;

to remove the filter, use
rs.filter = 0
or (from adovbs.inc)
rs.filter = adFilterNone


=========================================================
try { succeed(); } catch(E) { tryAgain(); }
-jeff
 
Upvote 0 Downvote
&quot;He make multiple query's to the same table... I don't know why&quot;
how else would you join-display-querry related tabels that have split info on the same record?

have you looked at using StoredProcedures to create/filter recordsets and execute them on the &quot;server side&quot; vs. having ASP pages process SQL statements?
also what Juanjo said:
cut down on ASP+HTML+ASP+HTML code blocks switching IF possible keep the VBScript_ASP blocks together (ASP+HTML); also remove the mix of:
</font></div></td> if not necessary....maybe use 1 CSS that will remeder faster
...I guess we are talking about &quot;good&quot; vs. &quot;bad&quot; programming habbits!
All the Best!
 
Upvote 0 Downvote
Yes, I agree with u, lebisol!... I'm fixin' it right now,

Jemminger: I deleted the 'distinct' statement and works better.. time is 2-3 secs... Thanx. Also, i made only 3 recordsets... sometimes the solution is only a word, but is difficult to find it whitout help.

This Forum is the Best! I have made a lot of threads and I have learned a lot from all you, &quot;the experts&quot;.

Thanks partners!

[2thumbsup]
Juanjo

Follow the dark side, so you can reach the light. [/color}
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
723
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question Question
How to check if a datatable is empty ?
Replies
0
Views
799
leifoet
leifoet
penguinspeaks
  • Locked
  • Question Question
Start loop from a specified point
Replies
0
Views
645
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
747
JScannell
JScannell
bslintx2021
  • Locked
  • Question Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
1K
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top