473,555 Members | 2,290 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need more speed for this query :)

Hi,

I found an interesting article on running totals at
http://www.databasejournal.com/featu...le.php/3112381.

I have converted one to postgresql for my banking account:

-- Subtotals on categories and a GrandTotal
select *,
case when a.oid= (select oid from nchecks where a.category
=category order by category desc, oid desc limit 1)
then (select sum(amount)::te xt from nchecks where oid <= a.oid
and a.category=cate gory)
else ' '
end as SubTotal,
case when a.oid = (select oid from nchecks order by category
desc, oid desc limit 1)
then (select sum(amount) from nchecks)::text
else ' '
end as GrandTotal
from nchecks a
order by category ,oid

The account has a 'category' for each transaction and of course an
'amount' for
the transaction ( and some other fields...)

The table does not have any fields defined as keys ( I am using oids
with there being
no chance for overflow...).

With only ~3300 rows the rascal takes its time...

explain begins with...
Sort (cost=626576.75 ..626584.96 rows=3283 width=181).....

Is there any way to get this puppy running a bit faster?

Jerry
Nov 23 '05 #1
2 1490
On Sun, Jun 06, 2004 at 15:36:25 -0700,
Jerry <je*********@ek u.edu> wrote:

-- Subtotals on categories and a GrandTotal
select *,
case when a.oid= (select oid from nchecks where a.category
=category order by category desc, oid desc limit 1)
then (select sum(amount)::te xt from nchecks where oid <= a.oid
and a.category=cate gory)
else ' '
end as SubTotal,
case when a.oid = (select oid from nchecks order by category
desc, oid desc limit 1)
then (select sum(amount) from nchecks)::text
else ' '
end as GrandTotal
from nchecks a
order by category ,oid

The account has a 'category' for each transaction and of course an
'amount' for
the transaction ( and some other fields...)

The table does not have any fields defined as keys ( I am using oids
with there being
no chance for overflow...).


If you don't have an index on (category, oid) the check for new categories
is going to result in O(n^2) steps. And then a sort step will probably be
tacked on at the end.

The summations might not be very efficient either. You might end up with
a table scan for each category if there aren't too many of them.

You might get a better plan doing a normal group by summation for
the subtotals union with a summation over the full table for the
grand total. It is possible that this could be done by one index scan
(assuming an index on category) over the table generating the category
subtotals in order and then merge sorting that with the result of a table
scan to get the grand total. I don't know if the optimizer will do that well
though.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2
On Sun, Jun 06, 2004 at 15:36:25 -0700,
Jerry <je*********@ek u.edu> wrote:

-- Subtotals on categories and a GrandTotal
select *,
case when a.oid= (select oid from nchecks where a.category
=category order by category desc, oid desc limit 1)
then (select sum(amount)::te xt from nchecks where oid <= a.oid
and a.category=cate gory)
else ' '
end as SubTotal,
case when a.oid = (select oid from nchecks order by category
desc, oid desc limit 1)
then (select sum(amount) from nchecks)::text
else ' '
end as GrandTotal
from nchecks a
order by category ,oid

The account has a 'category' for each transaction and of course an
'amount' for
the transaction ( and some other fields...)

The table does not have any fields defined as keys ( I am using oids
with there being
no chance for overflow...).


If you don't have an index on (category, oid) the check for new categories
is going to result in O(n^2) steps. And then a sort step will probably be
tacked on at the end.

The summations might not be very efficient either. You might end up with
a table scan for each category if there aren't too many of them.

You might get a better plan doing a normal group by summation for
the subtotals union with a summation over the full table for the
grand total. It is possible that this could be done by one index scan
(assuming an index on category) over the table generating the category
subtotals in order and then merge sorting that with the result of a table
scan to get the grand total. I don't know if the optimizer will do that well
though.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1840
by: Wouter | last post by:
Hi, I was wandering. Does the query speed also depend on the colums that you don't use in the query (so not in the where and not in the select part). I have a lange unused backup field in every row. I only put the backup information into that field once. But never use it on my live database.
6
9116
by: Chris Foster | last post by:
I am trying to implement a very fast queue using SQL Server. The queue table will contain tens of millions of records. The problem I have is the more records completed, the the slower it gets. I don't want to remove data from the queue because I use the same table to store results. The queue handles concurrent requests. The status...
3
1376
by: Bruce D | last post by:
Here's the breakdown: MySQL 4.0.12 Table: Assignment (indexes are created on proper fields) 419,234 records Table: Finders (indexes are created on proper fields) 5,345,657 records My user wants to be able to do a wild card search on lastname in the finders table. So, I wrote the following query. SELECT Finders.Lastname,...
2
1714
by: Sebastian | last post by:
The following query needs about 2 minutes to complete (finding dupes) on a table of about 10000 addresses. Does anyone have an idea on how to speed this up ? Thanks in advance !!! Sebastian
3
1238
by: Patric | last post by:
Hi I'm having some problem getting the following result. I have 2 tables Drivers -------- DriverID int (PK) DriverName varchar(50)
5
2714
by: JENS CONSER | last post by:
Hello NG, We have a performance problem in using a client server solution based on MS SQL-Server 2000 through a VPN tunnel (via broadband internet connection). The SQL Server is running on a Windows 2003 Server which is configured as VPN server as well. We figured out that not the performance
6
2075
by: lawrence k | last post by:
The following function is way too slow. If anyone has any suggestions about how to speed it up, I'd be grateful for them. We have to call this function 36 times on one page, and I think each time it takes half a second, so it adds up to maybe 18 seconds, which is a lot when you're showing software to a client. The reponse we get is "Why is it...
0
1171
by: hooked888 | last post by:
Hi, I have a database and a query both 100% (with specific index) identical and install and run in my laptop and server but resulting slightly different time frame to complete. Run a query in my laptop 1GB RAM, INTEL 1.66GHz Core 2 took me 1 minute 38 seconds. Run the same query in server 4GB RAM, ZEON 3.0GHz took me 1 minute 36 seconds....
8
6466
by: SaltyBoat | last post by:
Needing to import and parse data from a large PDF file into an Access 2002 table: I start by converted the PDF file to a html file. Then I read this html text file, line by line, into a table using a code loop and an INSERT INTO query. About 800,000 records of raw text. Later, I can then loop through and parse these 800,000 strings into...
4
1566
by: nitinpatel1117 | last post by:
Hi I have a mysql query which seems to execute slowly. I was wondering if there was a way to speed it up a bit. the query is; select count(*) as emailCount from mailing_list where user like 'joel@bloggs.com' and list_id = '47' this table has a primary key called 'email_id' so I was wondering if
0
8060
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7588
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7904
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6176
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5171
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3596
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3575
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
865
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.