?
Solved

Filter stored procedures recordset results

Posted on 2006-05-03
11
Medium Priority
?
892 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:MilburnDrysdale
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 28

Expert Comment

by:sybe
ID: 16596377
> 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
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 16596386
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
 
LVL 20

Expert Comment

by:jitganguly
ID: 16596499
Show us your SP code. Filtering on SP side is definately faster than any client side filtering.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MilburnDrysdale
ID: 16596553
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
 
LVL 20

Expert Comment

by:jitganguly
ID: 16596585
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
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 16596675
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
 

Author Comment

by:MilburnDrysdale
ID: 16596694
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
 
LVL 28

Expert Comment

by:sybe
ID: 16596793
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
 
LVL 9

Accepted Solution

by:
HappyFunBall earned 2000 total points
ID: 16596811
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
 

Author Comment

by:MilburnDrysdale
ID: 16602079
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
 

Author Comment

by:MilburnDrysdale
ID: 16602162
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question