Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Working with huge recordsets.  Would you cache these queries or ?

Posted on 2006-06-26
11
Medium Priority
?
150 Views
Last Modified: 2013-12-24
Here's a sample of some of my processing times.  I'm worried that caching queries this large may cause more problems.  Alternative suggestions to caching?  

(NOTE:  SQL tables being queried contain 100s of thousands of records and are indexed and optimized.)

(Datasource=datasource, Time=7625ms, Records=1138)
(Datasource=datasource, Time=2281ms, Records=2310)  

Thanks!
0
Comment
Question by:ptrick_whelan
  • 5
  • 3
8 Comments
 
LVL 7

Accepted Solution

by:
maUru earned 450 total points
ID: 16988257
caching usually helps only if the same things are being called over and over again and where the data in the table /does not change/ too much.

eg: i use caching on my language table, which returns a string from a query such as: "SELECT en FROM language WHERE Name = 'UnknownUser'"

since it will be called again and again caching helps immensly, and since i rarely change the language table, the cache is pretty much always there, remember, usually once a table is updated it will clear its cache.
0
 
LVL 7

Expert Comment

by:maUru
ID: 16988267
the size of the table matters only in the beginning when a new record is updated/inserted
0
 
LVL 13

Assisted Solution

by:usachrisk1983
usachrisk1983 earned 450 total points
ID: 16996598
Along with using the cache, look into using BLOCKFACTOR.  By default CF gets records one by one from the DB (tedious), but you can set it higher so that it grabs more in one grab.  Worth testing to see if your query times improve.
0
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

 
LVL 13

Expert Comment

by:usachrisk1983
ID: 17255792
I think that the original poster would have found both solutions getting him in the right direction.  If the requester is still interested, posting the Query could also help us to perhaps reduce the time it takes the query to run.
0
 
LVL 7

Expert Comment

by:maUru
ID: 17256389
...thousands of records and are indexed and optimized.)...

hes already done that

split in half?
0
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 17258751
Are you responding to me?  If so, I was saying that if the poster showed the query, perhaps we'd see something about the SQL that wasn't optimized -- perhaps they're selecting * from 50 tables to get the selected records, or are using some "col1 in (x,y,z)" syntax that we might be able to rewrite.  Having the records indexed does them no good if the query isn't.
0
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 17258753
I'd also recommend, if the poster returns, that they run their query in Query Analyzer (for MS SQL) or SQLPlus (for Oracle) and get the time, so that we can find out how much is CF overhead.
0
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 17407050
Mauru and I each provided more assistance with caching and other methods to speed up queries for the original poster, and anyone else who comes along (ex: blockfactor).  I recommend a point split.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
What You Need to Know when Searching for a Webhost Provider
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline

773 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