0

I'm working on a legacy system (classic ASP) where the original code will execute an in-line dynamic query (based on user input) to determine the sort order. For example:

Dim query = "Select * from empTable order by " & //some user input

Besides the potential SQL injection, the other problem with this kind of method is, each time the user wants to sort the result, it will call to the database. (and thus - my boss claimed - will cause some performance issues).

I proposed to use an array to store the return results from the query (and then sort within the array) but it was not approved by the management (I do not know the reason). I cannot use Javascript either. My supervisor told me to explore the XML element. So my questions are:

  1. How does XML help in sorting the result without calling the database?
  2. If not, what are my other options?

Edited: So according to my supervisor, calling to the IIS server is fine, but calling to the database server IS NOT acceptable.

C.J.
  • 3,409
  • 8
  • 34
  • 51
  • I honestly can't figure out what your boss had in mind. Performance issues normally happen when you flood the DB with queries (e.g., running queries from within nested loops) or you don't have indexes. Running one extra query per page reload is very unlike to have impact by itself. Furthermore, where were you planning to store the array? – Álvaro González Jul 21 '14 at 15:18
  • Same here, I just cannot understand what my boss is thinking. I told her that the best solution is to put everything in Stored Procedure, and use the `CASE` statement to determine the sort order. – C.J. Jul 21 '14 at 15:20
  • Assuming your first question is still in play, XML in conjunction with XSLT will allow you to sort the data; and on the second question, an option to consider would be to work with a disconnected recordset. Here's a potentially helpful question/answer: http://stackoverflow.com/questions/992425/sort-xml-data-in-classic-asp – Bret Jul 21 '14 at 15:28

2 Answers2

0

you can apply a style to the XML elements.

Something like this (from w3schools.com):

<xsl:template match="/">
  <html>
  <body>
  <h2>My CD Collection</h2>
  <table border="1">
    <tr bgcolor="#9acd32">
      <th>Title</th>
      <th>Artist</th>
    </tr>
    <xsl:for-each select="catalog/cd">
      <xsl:sort select="artist"/>
      <tr>
        <td><xsl:value-of select="title"/></td>
        <td><xsl:value-of select="artist"/></td>
      </tr>
    </xsl:for-each>
  </table>
  </body>
  </html>
</xsl:template>

FYI: Array is not "sort" friendly. You should use one of Collections implementation.

Jama Djafarov
  • 358
  • 3
  • 11
  • I am not familiar working with `XML/XSL.` Does it mean I will return the result from the query and store them in the `Collection` then sort within the `Collection` itself? Will this eliminate the need to recall the database every time I need to do the sorting? – C.J. Jul 21 '14 at 15:39
  • Your boss told you to use XML/XSLT to sort the results on the page. – Jama Djafarov Jul 21 '14 at 15:47
  • No, she asked me to research whether XMl/XSL can eliminate the need to call the database for sorting – C.J. Jul 21 '14 at 15:51
  • My understanding, the management doesn't want you to change the code. Try to use stylesheet to sort the results on the page. – Jama Djafarov Jul 21 '14 at 15:58
0

If the results were all shown on one page then re-ordering the results client-side using JavaScript would be ideal, but you mention that JavaScript is not allowed.

So if you can't use JavaScript and you can't make another database call then your only option is to store the results somewhere for re-ordering on the server-side.

Option 1: You could store the results in some format (possibly JSON?) in a hidden field and post this back to the server for reordering and then redisplay.

Option 2: You could store the results in some format (possibly an array) in a Session variable on the server-side. Or alternately you could store it locally in the file system in some format (possible XML).

Option 3: Output your results to an XML file and use XSL to style and sort the output.

For option 1 and 2 you will then need to write some code to do the ordering in Classic ASP. Hopefully your ordering is only on one field!

Option 1 could make page rendering and post backs very slow if there is a lot of data to be stored.

Option 2 could make large demands on your server's memory and impact performance if there is a lot of data to be stored and many people using the page.

As both option 1 and 2 require server side ordering of the results this too could impact server performance.

Option 3 is not going to be at all user friendly but then I don't know what type of users you have.

The database is usually the best place for this to be done. If there are performance problems then maybe you/they should be looking at indexing or database structural improvements.

johna
  • 10,540
  • 14
  • 47
  • 72