Solved

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

Posted on 2006-06-26
11
147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
11 Comments
 
LVL 7

Accepted Solution

by:
maUru earned 150 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 150 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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