10 Replies - 3917 Views - Last Post: 20 July 2010 - 02:33 PM Rate Topic: -----

#1 Guest_jt107*


Reputation:

Query Performance

Posted 06 July 2010 - 08:23 AM

I have a query brings back thouands of records. In order not to impact performance of display and paging, what would be the best way to do it without using query caching? Is it possible to keep the recordset first time query it and using it while paging?

Thanks,
JT
Is This A Good Question/Topic? 0

Replies To: Query Performance

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1866
  • View blog
  • Posts: 3,391
  • Joined: 13-January 08

Re: Query Performance

Posted 06 July 2010 - 08:40 AM

View Postjt107, on 06 July 2010 - 07:23 AM, said:

I have a query brings back thouands of records. In order not to impact performance of display and paging, what would be the best way to do it without using query caching? Is it possible to keep the recordset first time query it and using it while paging?

Thanks,
JT



You're not real clear but I'll assume by "do it" you mean displaying the query results in a paged fashion. If that's what you mean, then yes, there are ways to do so. For example, consider this:
<cfquery name="getRecords" datasource="#some_datasource#">
   SELECT *
   FROM sometable
</cfquery>

<cfset session.queryResults = getRecords>

<cfdump var="#session.queryResults#">



In that example, you run your query and you store it in the user's session scope. At each page call, you can check for the existence of the session variable and then do a query of queries against it pulling only the records you want to display on that page. This method will work fine but it doesn't allow for displaying of new data that is created during the whole pagination process. In most cases, that's not a big deal.

Also, because storing the query in the session scope CAN load up the server's memory, you'll want to make sure you destroy the session variable holding the query when they leave the page(s) that need it.

Good luck!
Was This Post Helpful? 0
  • +
  • -

#3 Guest_Guest*


Reputation:

Re: Query Performance

Posted 06 July 2010 - 08:53 AM

Thank you very much for your quick reply Craig328. It is a very good solution. However it will not work for this case because of the load balance situation. Session is disabled. Could I use it with a client variable, or any other alternatives?

Thanks again.
JT
Was This Post Helpful? 0

#4 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1866
  • View blog
  • Posts: 3,391
  • Joined: 13-January 08

Re: Query Performance

Posted 06 July 2010 - 09:09 AM

Well, as easily as you can put it into a session variable you can put it into other variable scopes that the user can access. Some of those scopes won't allow a complex construct like a query result set to be stored in them. For those types of variable scopes what you'd want to do is to first serialize the query result set into a WDDX string and then store that. To use it on the next page call you'd deserialize the WDDX string from the scoped variable you stored it in and then use that.

That said, your sessions should be available in a properly set up load balanced environment. If they're not then I'd suggest the next best variable scope to use would be the client scope.

However, have you considered perhaps calling this query via a stored procedure (to speed up the query response time) and then simply use the cfoutput attribute "startRow" and pass in the startRow value? It could be that you're skipping a possibly useful alternative to skin this cat. Or, if you're using Oracle as your database backend you could make use of the ROWNUM attribute in the query. If using MySQL, you could use the LIMIT attribute in the query. An example of that would look like this:
<cfquery name="getRecords"  datasource="#some_datasource#">
   SELECT * FROM table LIMIT 0, 10
</cfquery>


What that will do for you is limit the number of records returned to 10 staring at record 0. Obviously, you can pass in different values to your query like LIMIT 11,10 which will return 10 records starting with record 11 and so on. If that query is kept as a stored proc and you pass in the limit values, it ought to be very quick indeed and you circumvent the need to store the query data altogether.

Good luck!
Was This Post Helpful? 0
  • +
  • -

#5 Guest_Guest*


Reputation:

Re: Query Performance

Posted 06 July 2010 - 09:38 AM

It is db2, I will check to use either stored procedure or client veriable. Appreciate your help.
Thanks.
Was This Post Helpful? 0

#6 Guest_Guest*


Reputation:

Re: Query Performance

Posted 06 July 2010 - 12:29 PM

View PostCraig328, on 06 July 2010 - 08:09 AM, said:

Well, as easily as you can put it into a session variable you can put it into other variable scopes that the user can access. Some of those scopes won't allow a complex construct like a query result set to be stored in them. For those types of variable scopes what you'd want to do is to first serialize the query result set into a WDDX string and then store that. To use it on the next page call you'd deserialize the WDDX string from the scoped variable you stored it in and then use that.

That said, your sessions should be available in a properly set up load balanced environment. If they're not then I'd suggest the next best variable scope to use would be the client scope.

However, have you considered perhaps calling this query via a stored procedure (to speed up the query response time) and then simply use the cfoutput attribute "startRow" and pass in the startRow value? It could be that you're skipping a possibly useful alternative to skin this cat. Or, if you're using Oracle as your database backend you could make use of the ROWNUM attribute in the query. If using MySQL, you could use the LIMIT attribute in the query. An example of that would look like this:
<cfquery name="getRecords"  datasource="#some_datasource#">
   SELECT * FROM table LIMIT 0, 10
</cfquery>


What that will do for you is limit the number of records returned to 10 staring at record 0. Obviously, you can pass in different values to your query like LIMIT 11,10 which will return 10 records starting with record 11 and so on. If that query is kept as a stored proc and you pass in the limit values, it ought to be very quick indeed and you circumvent the need to store the query data altogether.

Good luck!

Instead of Session variable, I use application variable. Initial test seems ok.
thanks.
Was This Post Helpful? 0

#7 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1866
  • View blog
  • Posts: 3,391
  • Joined: 13-January 08

Re: Query Performance

Posted 06 July 2010 - 01:04 PM

Agh...that probably will NOT work for application variable scope. That is, it WILL hold the query result set nicely, but all users share the application scope. If user "a" runs a query and it gets stored in the application scope and user "b" runs the same page but with a different query, unless you're storing the query set in the application scope under different variable names, the second user's query result set will overwrite the first user's.

And if you ARE storing query result sets in the application scope in different, user specific variable names (application.user_A_results vs. application.user_B_results) then you're going to find your application scope will start consuming larger and larger amounts of server memory.

I don't know your app so it may not matter but if the two user's result sets could be different and you're using the same application variable name, you're going to definitely have issues.
Was This Post Helpful? 0
  • +
  • -

#8 Guest_Guest*


Reputation:

Re: Query Performance

Posted 07 July 2010 - 07:07 AM

Yes. Thanks. That is right Application Variable does not work. So I am pretty much on square one.
Was This Post Helpful? 0

#9 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1866
  • View blog
  • Posts: 3,391
  • Joined: 13-January 08

Re: Query Performance

Posted 07 July 2010 - 07:18 AM

Well, the client scope is probably your best bet if you're not using the session scope due to the load balancing.

That said, a little Googling turned up this item on how to use the ROW_NUMBER function in DB2. I've never used DB2 so I have no idea whether that's your solution or not. However, you might also consider using the native CF query ability in a QofQ to try and pull the specific record rows you need.

Cache your query (yeah, I know you said you didn't want to do that but it might work for you), get your query result set, do a query of queries on it and try to use one of the previously mentioned SQL commands and see what the CF supports. Caching the original query would allow you to devote the page processing time to the query of queries that's trying to do the specific record selection/sorting you need.

Good luck!
Was This Post Helpful? 0
  • +
  • -

#10 Guest_londong*


Reputation:

Re: Query Performance

Posted 20 July 2010 - 02:08 PM

You are looking at it from the wrong perspective.
Do not attempt to hold the records in memory instead make your sql query return only the subset of records you require

That is the correct way to page (or pagination as it is sometimes called)

Google pagination to find good examples of limiting your query to only the records required.
Was This Post Helpful? 0

#11 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1866
  • View blog
  • Posts: 3,391
  • Joined: 13-January 08

Re: Query Performance

Posted 20 July 2010 - 02:33 PM

View Postlondong, on 20 July 2010 - 01:08 PM, said:

You are looking at it from the wrong perspective.
Do not attempt to hold the records in memory instead make your sql query return only the subset of records you require

That is the correct way to page (or pagination as it is sometimes called)

Google pagination to find good examples of limiting your query to only the records required.


My last response (the reference to ROW_NUMBER being DB2's version of pagination) pretty much covered that. However, I'd disagree that my other potential solution (caching the query then using QofQ to work with it) is "wrong" or incorrect.

Your and my suggestion of using pagination would be a superior solution (amongst more than 1) IF a number of conditions also exist:

  • the connection to the database is sufficiently large and quick
  • the number of concurrent requests to that database isn't too large
  • the database tables themselves have been properly indexed and normalized
  • the kind of query you're running isn't excessively complex and long running


If you can't put a big green checkmark next to each of those conditions then you may find that running and re-running the same query time after time after time with only the difference being the WHERE clause condition may not be the best way to tackle the problem. Caching the query might indeed be more efficient in such a circumstance because the query has to only be run one time on the database and then CF manages the retrieved dataset against QofQ requests against it. If the original query that generated the dataset contains an excessive number of joins, does any kind of data relationship based on a string search, accesses tables that aren't properly indexed or normalized or if there are too many requests to the database such that the queue time is too long...you may find that pulling the query once and storing it on the CF server is the superior solution.

As in most things CF, there is usually more than one way to solve a problem...the only thing you have to determine is which way is best for the circumstances your app presents. That's why, without more complete knowledge of the particular situation, you can't typically say that any suggested solution is "wrong" or "correct". It's also why I suggested more than one course of action.

This post has been edited by Craig328: 20 July 2010 - 02:38 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1