Filter stored procedures recordset results

Hello...running IIS, ASP/VBScript, iSeries backend...I have a stored procedure that returns a small recordset (<100 records). If I edit the SP to accept a parameter from a drop down list to filter the results down further, it works but it is very slow (page load is >1 minute). If I take the parameter out, load time is much faster but I need to be able to filter the results. My question is, is there a way to filter the results without a parameter being passed...something I can do client side? Thanks!
MilburnDrysdaleAsked:
Who is Participating?
 
HappyFunBallConnect With a Mentor Commented:
Sorry to take your original question on a tangent.  You asked if there is anything you can do on the client side.  Well, if you're getting only 100 rows returned, there's no reason you can't write some custom code to loop through the results and only display the relevant ones.  It's still happening 'server-side', but I'm assuming that won't affect performance in this case.

Assuming the ASP page contains some looping code to iterate through each record in the recordset, you could add an if statement that filters out the records you don't want displayed.  Something like this:

'get recordset ...
rs=connection.Execute SQL

While not rs.EOF
    If rs("MHTID") = Request.Querystring("MMTERMINAL") Then
        'Some processing code here to use recordset
    End If
    rs.MoveNext
Wend
0
 
sybeCommented:
> page load is >1 minute

That surely is quite long for 100 records. Are you sure you aren't doing something strange? Why should a parnmeter cause such a delay?

Of course you can do filtering on the client too, with javascript. You need to store all records in javascript variables and write a javascript function that displays just the variables that meet the filter condition.

0
 
HappyFunBallCommented:
You'll probably get the best speed improvement by using the stored procedure to filter the results, rather than writing your own filtering code on the client side or ASP side.  However, you can make the stored procedure run faster if you add an index on the column or columns that are being filtered.  

For example, say you're searching a table of employees and filtering by job title:

    SELECT * FROM employees WHERE job_title = @JOBTITLEFILTER

In Enterprise Manager, right click the employees table and add an index for just the job_title column.  You can add a few indexes, one for each column, if you're filtering by more than one column.  That should make a big difference.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jitgangulyCommented:
Show us your SP code. Filtering on SP side is definately faster than any client side filtering.
0
 
MilburnDrysdaleAuthor Commented:
I am new to using SP's so that could be the issue...here is the code for the SP...

CREATE PROCEDURE LTL400TAF1/BMS_READY(@MMTERMINAL CHAR(4))  
       RESULT SETS 1                                        
       LANGUAGE SQL                                        
 BEGIN                                                      
 DECLARE BMSRDY1 CURSOR WITH RETURN TO CALLER              
 FOR SELECT CHAR(DATE) AS MANFDAT, MHTID, SCSCNO,          
 EMNAME, EMEMP, MHSEQ, MHLHS, MHDIST, MHARRT                
 FROM LTL400TAF3/BMS_RDYA, LTL400TAF3/DSP020, EMSLIB/EMPSVC
 WHERE SCCODE=MHTID AND MHDR1=EMEMP AND MHTID=@MMTERMINAL;  
   OPEN BMSRDY1;                                            
  END                                                      
0
 
jitgangulyCommented:
What are teh common keys between thease tables ? LTL400TAF3/BMS_RDYA, LTL400TAF3/DSP020, EMSLIB/EMPSVC

You have to join by those.
BTW - What kind of SQL/SP is this ?
0
 
HappyFunBallCommented:
I'm not familiar with the backend code, but I still stand behind my thought process:  if you put indexes on the columns used in your joins, the procedure will run faster.  Make sure the columns used in the WHERE clause have indexes on them.  SCCODE, MHTID, MHDR1, EMEMP, and MHTID
0
 
MilburnDrysdaleAuthor Commented:
This is on a AS400 box...I'm sure the sql is solid. When I run it in the 400 development environment, it runs nearly instantaneously...definitely no missing joins and there are pre-existing indexes on the approprate fields (MHTID).
0
 
sybeCommented:
As HappyFunBall  says, adding an index to the table might be the solution.

Doing the filtering on the client is more programming work, and requires some (basic) javascript capacities of the browser. In the end it is of course (lots) faster, because you don't have to return to the server to use a different filter.
0
 
MilburnDrysdaleAuthor Commented:
HappyFunBall - I've tinkered with your solution but without luck (I use DMX to develop web pages so hand coding is trial & error for me). Here's the recordset display code that DMX generates. How could I reference a Request.Form to filter results?

 <% While ((Repeat1__numRows <> 0) AND (NOT rs1.EOF) ) %>  
   <tr>
      <td><%=(rs1.Fields.Item("MANFDAT").Value)%></td>
      <td><%=(rs1.Fields.Item("MHTID").Value)%></td>
      <td><%=(rs1.Fields.Item("SCSCNO").Value)%></td>
      <td><%=(rs1.Fields.Item("EMNAME").Value)%></td>
      <td><%=(rs1.Fields.Item("EMEMP").Value)%></td>
      <td><%=(rs1.Fields.Item("MHSEQ").Value)%></td>
      <td><%=(rs1.Fields.Item("MHLHS").Value)%></td>
      <td><%=(rs1.Fields.Item("MHDIST").Value)%></td>
      <td><%=(rs1.Fields.Item("MHARRT").Value)%></td>
    </tr>

    <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rs1.MoveNext()
Wend
%>      
0
 
MilburnDrysdaleAuthor Commented:
HappyFunBall - disregard...I found a reference and plugged in this line of code and it worked like a charm...

rs1.Filter = "MHTID = '" + Replace(rs1__MMColParam, "'", "''") + "'"

You got me headed in the right direction. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.