Solved

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

Posted on 2006-06-26
11
142 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 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