Solved

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

Posted on 2006-06-26
11
144 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

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…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

735 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