How to speed up resultset from a query 3

[bnymk]

Hello all:

I have a website that is desgined using Coldfusion, Javascript and DHTML. I'm using SQL 2000 as a back end database server. The website has several pages and links that upon clicking on the links will invoke a query and displays the resultset in a table format on the page. The query that is being used is not that complicated and actually is a very simple query (that doesn't require me to use stored procedure). But the resultset that I get is huge, at times up to 5000 records at a time. Is there any way where I can speed up the time it takes to process the query and return the result set faster on a page??? Because as is now, it takes forever for a user to see result set on the page after they send their request. Any ideas would be greatly appreciated.

Thanks.

"Behind every great fortune there lies a great crime", Honore De Balzac

 

[r937]

tip: don't return more than you want to show

i doubt very much that you have any page that shows all 5000 rows

this'll make your query slightly more complex, but the transfer of data from the database to the cf server will be greatly improved

rudy

http://r937.com/

SQL Consulting

 

[ecobb]

Well, if the information doesn't change much you could cache the query. It would be slow the first time it's run after the cache expired, but then it would re-cache and be fast again.

If you're passing variables to the query, you can try using cfqueryparam. This will cause the database server to cache the query and use bind variables for whatever params(variables) you pass to it.

Have you tried a Stored Procedure? The query may not be complex, but it's returning 5000 results! That justifies a procedure in my book.



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[webmigit]

Is it the query that's being slow, or the output...

Right after the </cfquery> tag for the query in question, put <cfabort> and see how fast your page loads.

r937 is right, noone is going to read through 5,000 rows at a time.. so paging might be an option... (Next/Previous)

Another thing that will speed it up.. If you use

select * from table

and are not using all the field from the table in your output, then be more specific...

select field1,field2,field3 from table

This could give you a noticable performance increase.

Ecobb's note on caching queries may be a good idea... even 10 minute caches could help a lot.

You'll need to consult:

cachedwithin

http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/functi53.htm

and

cfquery

http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/Tags-pt218.htm

on how to do it.. (its very easy)

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[bnymk]

Thanks guys for responding to my questions. Like r937 mentioned I have tried displaying the records in chunks (50 records at a time) and showing the Next and Back link for the next bunch of records but it is still slow. Here is the code that I'm using.

cfparam name="URL.StartRow" default="1" type="numeric">
<!--- Number of records to display per Next/Back page --->
<cfset RowsPerPage = 50>
<!--- Total number of rows/records from the query --->
<cfset TotalRows = getClerkBooking.RecordCount>
<!--- Last Row is 10 rows past the starting row, or total number of query rows whichever is less --->
<cfset EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>
<!--- Next Button goes to 1 past current end row --->
<cfset StartRowNext = EndRow + 1>
<!--- Back button goes back N rows from start row --->
<cfset StartRowBack = URL.StartRow - RowsPerPage>

and then I build the table by calling the query like this.
<cfloop query="getClerkBooking" startrow="#URL.StartRow#" endrow="#EndRow#">

I will try the link webmigit posted and see what I can come up with.

Thanks everyone for your help. If have more ideas, I would be more than glad to try them out.

"Behind every great fortune there lies a great crime", Honore De Balzac

 

[r937]

returning 5000 rows from the database to cf and then only showing 50 of them with clever paging code using startrow and endrow is not the same thing as returning only 50 rows and showing them all



rudy

http://r937.com/

SQL Consulting

 

[bnymk]

Thanks all for your responses. I implemented "cachedwithin" in my query and it seems to improve the time it takes to load up the page. I will still do some tweaking and see what I can come up with.

Thanks all.

"Behind every great fortune there lies a great crime", Honore De Balzac

 

[webmigit]

I thought of this...

I've heard that CF doesn't cache a query by name but by the query text...

So I think if you did this...

<cfif not isDefined("url.rnum")>
  <cfset url.rnum=randrange(1,5000)>
</cfif>
<cfquery datasource="#request.dsn#" name="gr" cachedwithin="...">
  select whatever from table
   where (....where clause here...in parentheses...)
     and #rnum# = #rnum#
</cfquery>

And then in your paging links pass the rnum value.

The reason for this is that rnum will generate a random number each time... by saying "#rnum# = #rnum#" you won't filter the results but you generate different query text.

So the cache is made for the user the first time the query is run.. and then as the user cycles through, they're running of their cached results..

When someone else comes to the page, they get a different result set if its changed, but that's cached for them.

Just thoughts.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.