RE: Large count query > caching
Jochem, Can I just say that I sometimes stand in awe of you :) Mark A. Kruger, CFG, MCSE www.cfwebtools.com www.necfug.com http://mkruger.cfwebtools.com -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 2:49 PM To: CF-Talk Subject: Re: Large count query > caching Aaron Rouse wrote: > What is a bitmap index? The short explanation: Most types of database index contain the value of the indexed field plus a pointer to the rest of the row. So if you are indexing a bigint, that is 8 bytes for the value, another 8 bytes for the pointer and some room for metadata. After alignment issues that means each tuple will be 20 bytes. The index for a table with N tuples with X distinct values will have a size of 20 N byte. A bitmap index is structured so that each tuple in the table costs only 1 bit, but has that price for each distict value. That means that the total index size will be X * N bit. For the long explanation check the manual of your database. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235687 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large count query > caching
Aaron Rouse wrote: > What is a bitmap index? The short explanation: Most types of database index contain the value of the indexed field plus a pointer to the rest of the row. So if you are indexing a bigint, that is 8 bytes for the value, another 8 bytes for the pointer and some room for metadata. After alignment issues that means each tuple will be 20 bytes. The index for a table with N tuples with X distinct values will have a size of 20 N byte. A bitmap index is structured so that each tuple in the table costs only 1 bit, but has that price for each distict value. That means that the total index size will be X * N bit. For the long explanation check the manual of your database. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235664 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large count query > caching
Wolfe, Aaron wrote: > You should use a field name in the count instead of the *. It should > speed it up. > SELECT COUNT( fieldname ) FROM myTable > With the * it has to return all records and all fields. COUNT(fieldname) has a different meaning then COUNT(*). Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235662 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Large count query > caching
Mingo, The actually "counting" is unlikely to be affected, but you are forcing the planner to look up the column names in the sys tables for no good reason - and that initial task while neglible is superflous. -mark -Original Message- From: Mingo Hagen [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 10:29 AM To: CF-Talk Subject: Re: Large count query > caching Hi Aaron, Are you sure about that? I just tried three different queries (count(*), count(field) and count(0)) on two joined tables with a date selection which return a count of about 50 records. all three queries run in exactly the same time (1.5sec). I think I read somewhere that it should even be the opposite (although my tests don't show it) something to do with a shortcut to the recordcount of a table mapped to count( * ) or something. Mingo. Wolfe, Aaron wrote: > You should use a field name in the count instead of the *. It should > speed it up. > SELECT COUNT( fieldname ) FROM myTable > With the * it has to return all records and all fields. > > > -Original Message- > From: Jeremy Bunton [mailto:[EMAIL PROTECTED] > Sent: Friday, March 17, 2006 9:28 AM > To: CF-Talk > Subject: Large count query > caching > > I have a query counting records in a 25 million plus database. Even with > indexing and what not it takes about 25 seconds to run this query. > (count * from table where date > 19000101) Is there a way I could cache > the query so that if it is ran in that same way it very quickly returns > the full count. > I.E. this is a dynamically build query so sometimes it will have a long > where clause that I may be different than the straight just count > everything thing like above. It seems to me that if that query has been > ran one time in the same way that query should not have to recount the > next time, and should just output the count. > > Jeremy > > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235656 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large count query > caching
Hi Aaron, Are you sure about that? I just tried three different queries (count(*), count(field) and count(0)) on two joined tables with a date selection which return a count of about 50 records. all three queries run in exactly the same time (1.5sec). I think I read somewhere that it should even be the opposite (although my tests don't show it) something to do with a shortcut to the recordcount of a table mapped to count( * ) or something. Mingo. Wolfe, Aaron wrote: > You should use a field name in the count instead of the *. It should > speed it up. > SELECT COUNT( fieldname ) FROM myTable > With the * it has to return all records and all fields. > > > -Original Message- > From: Jeremy Bunton [mailto:[EMAIL PROTECTED] > Sent: Friday, March 17, 2006 9:28 AM > To: CF-Talk > Subject: Large count query > caching > > I have a query counting records in a 25 million plus database. Even with > indexing and what not it takes about 25 seconds to run this query. > (count * from table where date > 19000101) Is there a way I could cache > the query so that if it is ran in that same way it very quickly returns > the full count. > I.E. this is a dynamically build query so sometimes it will have a long > where clause that I may be different than the straight just count > everything thing like above. It seems to me that if that query has been > ran one time in the same way that query should not have to recount the > next time, and should just output the count. > > Jeremy > > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235636 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Large count query > caching
I'll check that out, I am not using them now. Jeremy -Original Message- From: Mark A Kruger [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 11:09 AM To: CF-Talk Subject: RE: Large count query > caching If you cannot cache on the web server - make sure and bind the data using CFQUERYPARAM. This will enable the RDMBS to use a cached execution plan on it's end. I have a blog on why this is important. http://mkruger.cfwebtools.com/index.cfm?mode=alias&alias=cfqueryparam Secondly, change the " * " to the PK field of the table. This is largely for the same reason. Why make the DB server look up all your columns? It's an unecessary step and it will cost you in execution time - maybe not a lot, but when you are dealing with 25 million rows ever millisecond counts :) -Mark -Original Message- From: Jeremy Bunton [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 8:28 AM To: CF-Talk Subject: Large count query > caching I have a query counting records in a 25 million plus database. Even with indexing and what not it takes about 25 seconds to run this query. (count * from table where date > 19000101) Is there a way I could cache the query so that if it is ran in that same way it very quickly returns the full count. I.E. this is a dynamically build query so sometimes it will have a long where clause that I may be different than the straight just count everything thing like above. It seems to me that if that query has been ran one time in the same way that query should not have to recount the next time, and should just output the count. Jeremy ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235634 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Large count query > caching
If you cannot cache on the web server - make sure and bind the data using CFQUERYPARAM. This will enable the RDMBS to use a cached execution plan on it's end. I have a blog on why this is important. http://mkruger.cfwebtools.com/index.cfm?mode=alias&alias=cfqueryparam Secondly, change the " * " to the PK field of the table. This is largely for the same reason. Why make the DB server look up all your columns? It's an unecessary step and it will cost you in execution time - maybe not a lot, but when you are dealing with 25 million rows ever millisecond counts :) -Mark -Original Message- From: Jeremy Bunton [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 8:28 AM To: CF-Talk Subject: Large count query > caching I have a query counting records in a 25 million plus database. Even with indexing and what not it takes about 25 seconds to run this query. (count * from table where date > 19000101) Is there a way I could cache the query so that if it is ran in that same way it very quickly returns the full count. I.E. this is a dynamically build query so sometimes it will have a long where clause that I may be different than the straight just count everything thing like above. It seems to me that if that query has been ran one time in the same way that query should not have to recount the next time, and should just output the count. Jeremy ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235631 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Large count query > caching
> oddly enough the more "ands" in the where clause > the faster the > clustered index seems to go. That does seem odd, but that's the way it usually works. The 'ands' are cutting off chunks of data from the whole, and making the search cover smaller areas. Also, if your 'ands' are part of your indexes, that will dramatically speed things up. -- This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. A1. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235630 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large count query > caching
What is a bitmap index? On 3/17/06, Jeremy Bunton <[EMAIL PROTECTED]> wrote: > > There is a pretty large number of options. Considering zip codes(43,000 of > in the US), area code, state and about 16 other fields are available. I > think what it does now will work, the main problem was with nothing > selected, oddly enough the more "ands" in the where clause the faster the > clustered index seems to go. I need oracle's bitmap indexes I think to do > it > right. I'm pretty sure my indexes are ok. > > Jeremy > > -Original Message- > From: mark [mailto:[EMAIL PROTECTED] > Sent: Friday, March 17, 2006 10:43 AM > To: CF-Talk > Subject: RE: Large count query > caching > > Is there a finite number of options for a count? Even if the number is > 50, > you could run the queries late at night and cache the results for 24 > hours? > It wouldn't take any shorter time, but to the user it would be much > better. > > Mark > > -Original Message- > From: Aaron Rouse [mailto:[EMAIL PROTECTED] > Sent: Friday, March 17, 2006 10:08 AM > To: CF-Talk > Subject: Re: Large count query > caching > > > If you were taking the route of a cached query then yes/no that you are > wrong. If the count() is going to change based upon the where clause then > you would have to have a single master cached query that pulled out > everything possibly needed so that you could then do QoQ on that for the > dynamic where clauses. That means that your master cached query is not > pulling a count() and would probably be massive in space. I think it > would > be best to pursue solutions through the DB to speed things up. > > On 3/17/06, Jeremy Bunton <[EMAIL PROTECTED]> wrote: > > > > Yeah I thought about that also, but since all my queries, regardless of > > the > > ever changing where clause only return a count, never a full record set, > I > > would think that the memory usage would be small or am I wrong? This > > machine > > sql 2005 has 4 gig of ram, but the data as a .csv was over 8 gig so I > know > > I > > can't fit the whole record set in ram. > > > > Jeremy > > > > > > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235629 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Large count query > caching
There is a pretty large number of options. Considering zip codes(43,000 of in the US), area code, state and about 16 other fields are available. I think what it does now will work, the main problem was with nothing selected, oddly enough the more "ands" in the where clause the faster the clustered index seems to go. I need oracle's bitmap indexes I think to do it right. I'm pretty sure my indexes are ok. Jeremy -Original Message- From: mark [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 10:43 AM To: CF-Talk Subject: RE: Large count query > caching Is there a finite number of options for a count? Even if the number is 50, you could run the queries late at night and cache the results for 24 hours? It wouldn't take any shorter time, but to the user it would be much better. Mark -Original Message- From: Aaron Rouse [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 10:08 AM To: CF-Talk Subject: Re: Large count query > caching If you were taking the route of a cached query then yes/no that you are wrong. If the count() is going to change based upon the where clause then you would have to have a single master cached query that pulled out everything possibly needed so that you could then do QoQ on that for the dynamic where clauses. That means that your master cached query is not pulling a count() and would probably be massive in space. I think it would be best to pursue solutions through the DB to speed things up. On 3/17/06, Jeremy Bunton <[EMAIL PROTECTED]> wrote: > > Yeah I thought about that also, but since all my queries, regardless of > the > ever changing where clause only return a count, never a full record set, I > would think that the memory usage would be small or am I wrong? This > machine > sql 2005 has 4 gig of ram, but the data as a .csv was over 8 gig so I know > I > can't fit the whole record set in ram. > > Jeremy > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235628 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Large count query > caching
Is there a finite number of options for a count? Even if the number is 50, you could run the queries late at night and cache the results for 24 hours? It wouldn't take any shorter time, but to the user it would be much better. Mark -Original Message- From: Aaron Rouse [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 10:08 AM To: CF-Talk Subject: Re: Large count query > caching If you were taking the route of a cached query then yes/no that you are wrong. If the count() is going to change based upon the where clause then you would have to have a single master cached query that pulled out everything possibly needed so that you could then do QoQ on that for the dynamic where clauses. That means that your master cached query is not pulling a count() and would probably be massive in space. I think it would be best to pursue solutions through the DB to speed things up. On 3/17/06, Jeremy Bunton <[EMAIL PROTECTED]> wrote: > > Yeah I thought about that also, but since all my queries, regardless of > the > ever changing where clause only return a count, never a full record set, I > would think that the memory usage would be small or am I wrong? This > machine > sql 2005 has 4 gig of ram, but the data as a .csv was over 8 gig so I know > I > can't fit the whole record set in ram. > > Jeremy > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235627 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Large count query > caching
You should use a field name in the count instead of the *. It should speed it up. SELECT COUNT( fieldname ) FROM myTable With the * it has to return all records and all fields. -Original Message- From: Jeremy Bunton [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 9:28 AM To: CF-Talk Subject: Large count query > caching I have a query counting records in a 25 million plus database. Even with indexing and what not it takes about 25 seconds to run this query. (count * from table where date > 19000101) Is there a way I could cache the query so that if it is ran in that same way it very quickly returns the full count. I.E. this is a dynamically build query so sometimes it will have a long where clause that I may be different than the straight just count everything thing like above. It seems to me that if that query has been ran one time in the same way that query should not have to recount the next time, and should just output the count. Jeremy ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235626 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large count query > caching
If you were taking the route of a cached query then yes/no that you are wrong. If the count() is going to change based upon the where clause then you would have to have a single master cached query that pulled out everything possibly needed so that you could then do QoQ on that for the dynamic where clauses. That means that your master cached query is not pulling a count() and would probably be massive in space. I think it would be best to pursue solutions through the DB to speed things up. On 3/17/06, Jeremy Bunton <[EMAIL PROTECTED]> wrote: > > Yeah I thought about that also, but since all my queries, regardless of > the > ever changing where clause only return a count, never a full record set, I > would think that the memory usage would be small or am I wrong? This > machine > sql 2005 has 4 gig of ram, but the data as a .csv was over 8 gig so I know > I > can't fit the whole record set in ram. > > Jeremy > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235623 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Large count query > caching
Yeah I thought about that also, but since all my queries, regardless of the ever changing where clause only return a count, never a full record set, I would think that the memory usage would be small or am I wrong? This machine sql 2005 has 4 gig of ram, but the data as a .csv was over 8 gig so I know I can't fit the whole record set in ram. Jeremy -Original Message- From: Aaron Rouse [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 9:44 AM To: CF-Talk Subject: Re: Large count query > caching The only problem with this is the query can change due to the dynamic building of the where clause. Could do a cached query that selects everything that could be in the where clause then do QoQ on that for dynamic where clause needs. I'd just be a little fearful of how much would need to be selected for that cached query, seems like it could get rather big if accessing 25 million records. On 3/17/06, Mingo Hagen <[EMAIL PROTECTED]> wrote: > > Hi Jeremy, > > Yes, you can very easily cache queries using the cachedwithin="" > attribute of > > Like so: >cachedwithin="#createTimeSpan( 1, 0, 0, 0 )#"> > SELECT COUNT( * ) FROM myTable WHERE myDate > '1900-01-01' > > > This will cache your query for one day. You could also put the result of > the query into an application variable or something like that, but this > way the query will only be cached if the query text is the same. > > M. > > > Jeremy Bunton wrote: > > I have a query counting records in a 25 million plus database. Even with > > indexing and what not it takes about 25 seconds to run this query. > (count * > > from table where date > 19000101) Is there a way I could cache the query > so > > that if it is ran in that same way it very quickly returns the full > count. > > I.E. this is a dynamically build query so sometimes it will have a long > > where clause that I may be different than the straight just count > everything > > thing like above. It seems to me that if that query has been ran one > time > > in the same way that query should not have to recount the next time, and > > should just output the count. > > > > Jeremy > > > > > > > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235622 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large count query > caching
On 3/17/06, Jeremy Bunton <[EMAIL PROTECTED]> wrote: > I have a query counting records in a 25 million plus database. Even with > indexing and what not it takes about 25 seconds to run this query. (count * > from table where date > 19000101) Is there a way I could cache the query so > that if it is ran in that same way it very quickly returns the full count. > I.E. this is a dynamically build query so sometimes it will have a long > where clause that I may be different than the straight just count everything > thing like above. It seems to me that if that query has been ran one time > in the same way that query should not have to recount the next time, and > should just output the count. I'm going to assume you've used some sort of profiling (eg Query Analyzer if MS-SQL) to ensure you've got indexes appropriately -- of course hardware/software/config/etc govern speed, but it would be good to make sure you're not using a table scan or other expensive operations in the queries that need the count. You may be able to use some combined indexes to speed things up even more if date is frequently used with other specific fields. You can cache to an application/session/whatever scope variable yourself and set a timer on it. You can use . You'd have to decide how often you need to do the caching depending on how "fresh" the value needs to be. There's a couple more options, depending on your database. You could partition the table into a table where date > 19000101 and one where it's not, then instead of a where clause you can simply count(*), which may be faster depending on the query. In MS-SQL you could create an view WHERE data > 19000101 and index it to virtualize the table and then do your SELECTs against that view (I'm not suggesting just using a normal view -- I'm suggesting what I think MS-SQL calls a "materialized" view). In MySQL you could partion the table into two or more tables and use a MERGE table to combine them when needed. -- John Paul Ashenfelter CTO/Transitionpoint (blog) http://www.ashenfelter.com (email) [EMAIL PROTECTED] ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235620 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large count query > caching
The only problem with this is the query can change due to the dynamic building of the where clause. Could do a cached query that selects everything that could be in the where clause then do QoQ on that for dynamic where clause needs. I'd just be a little fearful of how much would need to be selected for that cached query, seems like it could get rather big if accessing 25 million records. On 3/17/06, Mingo Hagen <[EMAIL PROTECTED]> wrote: > > Hi Jeremy, > > Yes, you can very easily cache queries using the cachedwithin="" > attribute of > > Like so: >cachedwithin="#createTimeSpan( 1, 0, 0, 0 )#"> > SELECT COUNT( * ) FROM myTable WHERE myDate > '1900-01-01' > > > This will cache your query for one day. You could also put the result of > the query into an application variable or something like that, but this > way the query will only be cached if the query text is the same. > > M. > > > Jeremy Bunton wrote: > > I have a query counting records in a 25 million plus database. Even with > > indexing and what not it takes about 25 seconds to run this query. > (count * > > from table where date > 19000101) Is there a way I could cache the query > so > > that if it is ran in that same way it very quickly returns the full > count. > > I.E. this is a dynamically build query so sometimes it will have a long > > where clause that I may be different than the straight just count > everything > > thing like above. It seems to me that if that query has been ran one > time > > in the same way that query should not have to recount the next time, and > > should just output the count. > > > > Jeremy > > > > > > > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235621 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large count query > caching
Hi Jeremy, Yes, you can very easily cache queries using the cachedwithin="" attribute of Like so: SELECT COUNT( * ) FROM myTable WHERE myDate > '1900-01-01' This will cache your query for one day. You could also put the result of the query into an application variable or something like that, but this way the query will only be cached if the query text is the same. M. Jeremy Bunton wrote: > I have a query counting records in a 25 million plus database. Even with > indexing and what not it takes about 25 seconds to run this query. (count * > from table where date > 19000101) Is there a way I could cache the query so > that if it is ran in that same way it very quickly returns the full count. > I.E. this is a dynamically build query so sometimes it will have a long > where clause that I may be different than the straight just count everything > thing like above. It seems to me that if that query has been ran one time > in the same way that query should not have to recount the next time, and > should just output the count. > > Jeremy > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235619 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Large count query > caching
I have a query counting records in a 25 million plus database. Even with indexing and what not it takes about 25 seconds to run this query. (count * from table where date > 19000101) Is there a way I could cache the query so that if it is ran in that same way it very quickly returns the full count. I.E. this is a dynamically build query so sometimes it will have a long where clause that I may be different than the straight just count everything thing like above. It seems to me that if that query has been ran one time in the same way that query should not have to recount the next time, and should just output the count. Jeremy ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235618 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: ot: distinct count query; mssql2k
How about a subquery in the select list: select top 20 field1, COUNT(field1) AS Expr1, (SELECT whatever FROM something WHERE blah...) as TheNewField FROM ... If your DB supports it, this means the text-type column doesn't have to be grouped. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: ot: distinct count query; mssql2k
No doubt there's a SQL way to do it, in which case it'll be better than my suggestion but you could loop through the results of your count query (it wont be a big database call - you only have 20 records to loop through) and run separate queries for each value of field1. Cheers Mike Kear Windsor, NSW, Australia AFP Webworks http://afpwebworks.com .com,.net,.org domains from AUD$20/Year - Original Message - From: alexander sicular <[EMAIL PROTECTED]> Date: Sun, 03 Oct 2004 19:08:29 -0400 Subject: ot: distinct count query; mssql2k To: CF-Talk <[EMAIL PROTECTED]> hi all, i am trying to query a table for most often inserted values and have stumbled into a roadblock. the query i have so far works as expected: select top 20 field1, COUNT(field1) AS Expr1 FROM tbl1 GROUP BY field1 order by expr1 desc the problem is that i would also like to return a second column that just so happens to be of text type. mssql2k will not accept a text type column in the group by clause and i have tried to cast it as a varchar with no luck. any thoughts? tia, alex [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
ot: distinct count query; mssql2k
hi all, i am trying to query a table for most often inserted values and have stumbled into a roadblock. the query i have so far works as expected: select top 20 field1, COUNT(field1) AS Expr1 FROM tbl1 GROUP BY field1 order by expr1 desc the problem is that i would also like to return a second column that just so happens to be of text type. mssql2k will not accept a text type column in the group by clause and i have tried to cast it as a varchar with no luck. any thoughts? tia, alex [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Count(*) Query
SELECT number, count(*) FROM table GROUP BY number > I have a table that has one field that holds numbers 1 thru 9. Can I run a > single query that will give me the count(*) of 1's, count(*) of 2's, etc. or > do I have to run a separate query for each? > > Thanks to all, > > Keith > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Count(*) Query
YOU GUYS ROCK!!! Keith ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Count(*) Query
You're right, I didn't see your query when I wrote this one. -Original Message- From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: Friday, April 04, 2003 9:52 AM To: CF-Talk Subject: RE: Count(*) Query > SELECT Count(NumbersField) AS CountOfField > FROM table1 > GROUP BY NumbersField > > That does it, I tried it. But you only get the Count, so you don't know what it's a count of... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Count(*) Query
> SELECT Count(NumbersField) AS CountOfField > FROM table1 > GROUP BY NumbersField > > That does it, I tried it. But you only get the Count, so you don't know what it's a count of... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Count(*) Query
SELECT Count(NumbersField) AS CountOfField FROM table1 GROUP BY NumbersField That does it, I tried it. Matt Small -Original Message- From: Hawkes, Keith A CIV [mailto:[EMAIL PROTECTED] Sent: Friday, April 04, 2003 7:18 AM To: CF-Talk Subject: Count(*) Query I have a table that has one field that holds numbers 1 thru 9. Can I run a single query that will give me the count(*) of 1's, count(*) of 2's, etc. or do I have to run a separate query for each? Thanks to all, Keith ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Count(*) Query
You could do something like this: SELECT TOP 1 (select count(qty) from items where qty = 1) AS num_1s, (select count(qty) from items where qty = 2) AS num_2s FROM items GROUP BY qty I'm sure there's a more efficient way to do this, but this should work. - Original Message - From: "Hawkes, Keith A CIV" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, April 04, 2003 6:17 AM Subject: Count(*) Query > I have a table that has one field that holds numbers 1 thru 9. Can I run a > single query that will give me the count(*) of 1's, count(*) of 2's, etc. or > do I have to run a separate query for each? > > Thanks to all, > > Keith > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Count(*) Query
> I have a table that has one field that holds numbers 1 thru > 9. Can I run a single query that will give me the count(*) > of 1's, count(*) of 2's, etc. or do I have to run a separate > query for each? Do you want them in one row, or one row per number? The SQL is a lot bigger for one row, but here they are This will give you only 1-9 and one row per number; SELECT number, Count(number) as Counted FROM table GROUP BY number HAVING number BETWEEN 1 AND 9 ORDER BY number This will give you one row with all 9 values; SELECT TOP 1 (SELECT count(number) FROM table WHERE number=1) as Count1, (SELECT count(number) FROM table WHERE number=2) as Count2, (SELECT count(number) FROM table WHERE number=3) as Count3, (SELECT count(number) FROM table WHERE number=4) as Count4, (SELECT count(number) FROM table WHERE number=5) as Count5, (SELECT count(number) FROM table WHERE number=6) as Count6, (SELECT count(number) FROM table WHERE number=7) as Count7, (SELECT count(number) FROM table WHERE number=8) as Count8, (SELECT count(number) FROM table WHERE number=9) as Count9 FROM table HTH ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Count(*) Query
I have a table that has one field that holds numbers 1 thru 9. Can I run a single query that will give me the count(*) of 1's, count(*) of 2's, etc. or do I have to run a separate query for each? Thanks to all, Keith ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL7 count query
On Thu, 9 Nov 2000 13:30:49 +0700, [EMAIL PROTECTED] (Paul Hastings) wrote: >sure you can: > >SELECT >(SELECT COUNT(artist_id) FROM artists) AS artists, >(SELECT COUNT(artworks.artwork_id) FROM artworks) AS artworks, >(SELECT COUNT(news.article_id) FROM news) AS news > >as long as you don't care wether there's any relationship among these >tables... Thanks Paul and Nick... works great! K. __ Kay Smoljak - HTML/ColdFusion Developer - PerthWeb Pty Ltd Internet Solutions for your business! Level 9/105 St George's Tc - Perth - Western Australia Ph: (08) 9226 1366 Fax: (08) 9226 1375 Mobile : 0419 949 007 Visit Perth online! : www.perthweb.com.au Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: SQL7 count query
> :)) You've got result like count(artists)*count(artworks)*count(news)? It > is right. You cannot get result you want in one query. You must run 3 > different queries. sure you can: SELECT (SELECT COUNT(artist_id) FROM artists) AS artists, (SELECT COUNT(artworks.artwork_id) FROM artworks) AS artworks, (SELECT COUNT(news.article_id) FROM news) AS news as long as you don't care wether there's any relationship among these tables... Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: SQL7 count query
Hi Kay, Try getting the counts as sub queries, for example SELECT COUNT(*) AS Artists, (SELECT COUNT(*) FROM Artworks) AS Artworks, (SELECT COUNT(*) FROM News) AS News FROM Artists Hope this helps a little! N\ > From: "Kay Smoljak" <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Thu, 9 Nov 2000 09:31:02 +0800 > To: CF-Talk <[EMAIL PROTECTED]> > Subject: SQL7 count query > > I want to list the total number of items in several tables. I was hoping to > do it in one query, because potentially there could be 10 or more totals and > running that many queries on one page seems silly. Here's the code I tried: > > > select count(artists.artist_id) as artists, > count(artworks.artwork_id) as artworks, > count(news.article_id) as news > from artists, artworks, news > > > > #count.artists# artists listed > #count.artworks# artworks listed > #count.news# news articles > > > But this gives me the total value of all the tables combined for each > variable. What am I doing wrong? > > Thanks, > K. > __ > Kay Smoljak - HTML/ColdFusion Developer - PerthWeb Pty Ltd > Internet Solutions for your business! > > Level 9/105 St George's Tc - Perth - Western Australia > Ph: (08) 9226 1366 Fax: (08) 9226 1375 Mobile : 0419 949 007 > Visit Perth online! : www.perthweb.com.au > > -- > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a > message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: SQL7 count query
> select count(artists.artist_id) as artists, > count(artworks.artwork_id) as artworks, > count(news.article_id) as news > from artists, artworks, news > > But this gives me the total value of all the tables combined for each > variable. What am I doing wrong? :)) You've got result like count(artists)*count(artworks)*count(news)? It is right. You cannot get result you want in one query. You must run 3 different queries. Gennadi Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
SQL7 count query
I want to list the total number of items in several tables. I was hoping to do it in one query, because potentially there could be 10 or more totals and running that many queries on one page seems silly. Here's the code I tried: select count(artists.artist_id) as artists, count(artworks.artwork_id) as artworks, count(news.article_id) as news from artists, artworks, news #count.artists# artists listed #count.artworks# artworks listed #count.news# news articles But this gives me the total value of all the tables combined for each variable. What am I doing wrong? Thanks, K. __ Kay Smoljak - HTML/ColdFusion Developer - PerthWeb Pty Ltd Internet Solutions for your business! Level 9/105 St George's Tc - Perth - Western Australia Ph: (08) 9226 1366 Fax: (08) 9226 1375 Mobile : 0419 949 007 Visit Perth online! : www.perthweb.com.au Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
