Solved

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

Posted on 2006-06-26
11
141 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
 
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
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 
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

Title # Comments Views Activity
Time Sheet Help 6 44
How to redirect https://www to non-www? 6 72
IIS url rewrite rule for web application 3 52
Ways to assess https/ssl websites 3 83
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now