Slow query times through OpenOffice

SilverJester · #1 (**permalink**) 01-05-10, 16:11

First let me start out by saying that I am not the most experienced when it comes to databases. I am currently a junior majoring computer science (who has yet to take any database courses, my first one will be this upcoming semester). I work at a small soccer shop that had a hard drive crash a while back and was never properly backed up, so now I have been given the task of manually going into our database to retrieve information that would normally be provided by our POS program.

So while I have been able to come to a solution by writing a query and running it through PCC. And then copying the contents into a spreadsheet (we use Calc, part of OpenOffice), I'd like to make it simple for other people here that know nothing about programming. The problem is that running the exact same query (see below) takes about 30mins when run from OpenOffice (which is using OODBC), and only about 5 seconds when run through PCC. I have no Idea why this...I tried googling and came up with the possiblitiy of poorly written query, but as I said I've never taken a formal class so I don't if that is the case.

My query:

Code:

SELECT Cell.ItemNumber "Item Number", Items.AverageCost "Store Cost", ( IF(Grid.SizeName_1 = '', 0, 1) + IF(Grid.SizeName_2 = '', 0, 1) + IF(Grid.SizeName_3 = '', 0, 1) + IF(Grid.SizeName_4 = '', 0, 1) + IF(Grid.SizeName_5 = '', 0, 1) + IF(Grid.SizeName_6 = '', 0, 1) + IF(Grid.SizeName_7 = '', 0, 1) + IF(Grid.SizeName_8 = '', 0, 1) + IF(Grid.SizeName_9= '', 0, 1) + IF(Grid.SizeName_10 = '', 0, 1) + IF(Grid.SizeName_11 = '', 0, 1) + IF(Grid.SizeName_12 = '', 0, 1) + IF(Grid.SizeName_13 = '', 0, 1) + IF(Grid.SizeName_14 = '', 0, 1) + IF(Grid.SizeName_15 = '', 0, 1) + IF(Grid.SizeName_16 = '', 0, 1) + IF(Grid.SizeName_17 = '', 0, 1) + IF(Grid.SizeName_18 = '', 0, 1) + IF(Grid.SizeName_19 = '', 0, 1) + IF(Grid.SizeName_20 = '', 0, 1) + IF(Grid.SizeName_21 = '', 0, 1) + IF(Grid.SizeName_22 = '', 0, 1) + IF(Grid.SizeName_23 = '', 0, 1) + IF(Grid.SizeName_24 = '', 0, 1) + IF(Grid.SizeName_25 = '', 0, 1) + IF(Grid.SizeName_26 = '', 0, 1) + IF(Grid.SizeName_27 = '', 0, 1) + IF(Grid.SizeName_28 = '', 0, 1) + IF(Grid.SizeName_29 = '', 0, 1) + IF(Grid.SizeName_30 = '', 0, 1) + IF(Grid.SizeName_31 = '', 0, 1) + IF(Grid.SizeName_32 = '', 0, 1) + IF(Grid.SizeName_33 = '', 0, 1) + IF(Grid.SizeName_34 = '', 0, 1) + IF(Grid.SizeName_35 = '', 0, 1) + IF(Grid.SizeName_36 = '', 0, 1) + IF(Grid.SizeName_37 = '', 0, 1) + IF(Grid.SizeName_38 = '', 0, 1) + IF(Grid.SizeName_39 = '', 0, 1) + IF(Grid.SizeName_40 = '', 0, 1) + IF(Grid.SizeName_41 = '', 0, 1) + IF(Grid.SizeName_42 = '', 0, 1) + IF(Grid.SizeName_43 = '', 0, 1) + IF(Grid.SizeName_44 = '', 0, 1) + IF(Grid.SizeName_45 = '', 0, 1) + IF(Grid.SizeName_46 = '', 0, 1) + IF(Grid.SizeName_47 = '', 0, 1) + IF(Grid.SizeName_48 = '', 0, 1) + IF(Grid.SizeName_49 = '', 0, 1) + IF(Grid.SizeName_50 = '', 0, 1) ) "Number of Sizes", SUM(Cell.QtyOnHand_1), SUM(Cell.QtyOnHand_2), SUM(Cell.QtyOnHand_3), SUM(Cell.QtyOnHand_4), SUM(Cell.QtyOnHand_5), SUM(Cell.QtyOnHand_6), SUM(Cell.QtyOnHand_7), SUM(Cell.QtyOnHand_8), SUM(Cell.QtyOnHand_9), SUM(Cell.QtyOnHand_10), SUM(Cell.QtyOnHand_11), SUM(Cell.QtyOnHand_12), SUM(Cell.QtyOnHand_13), SUM(Cell.QtyOnHand_14), SUM(Cell.QtyOnHand_15), SUM(Cell.QtyOnHand_16), SUM(Cell.QtyOnHand_17), SUM(Cell.QtyOnHand_18), SUM(Cell.QtyOnHand_19), SUM(Cell.QtyOnHand_20), SUM(Cell.QtyOnHand_21), SUM(Cell.QtyOnHand_22), SUM(Cell.QtyOnHand_23), SUM(Cell.QtyOnHand_24), SUM(Cell.QtyOnHand_25), SUM(Cell.QtyOnHand_26), SUM(Cell.QtyOnHand_27), SUM(Cell.QtyOnHand_28), SUM(Cell.QtyOnHand_29), SUM(Cell.QtyOnHand_30), SUM(Cell.QtyOnHand_31), SUM(Cell.QtyOnHand_32), SUM(Cell.QtyOnHand_33), SUM(Cell.QtyOnHand_34), SUM(Cell.QtyOnHand_35), SUM(Cell.QtyOnHand_36), SUM(Cell.QtyOnHand_37), SUM(Cell.QtyOnHand_38), SUM(Cell.QtyOnHand_39), SUM(Cell.QtyOnHand_40), SUM(Cell.QtyOnHand_41), SUM(Cell.QtyOnHand_42), SUM(Cell.QtyOnHand_43), SUM(Cell.QtyOnHand_44), SUM(Cell.QtyOnHand_45), SUM(Cell.QtyOnHand_46), SUM(Cell.QtyOnHand_47), SUM(Cell.QtyOnHand_48), SUM(Cell.QtyOnHand_49), SUM(Cell.QtyOnHand_50)
	FROM "Cell", "Items", "Grid"
	WHERE (Cell.ItemNumber = Items.ItemNumber and Cell.ItemNumber = Grid.ItemNumber) and Cell.Type = 'C' and Cell.Location = 'SB' AND Grid.Type = 'G'
	GROUP BY Cell.Itemnumber, AverageCost, SizeName_1, SizeName_2, SizeName_3, SizeName_4, SizeName_5, SizeName_6, SizeName_7, SizeName_8, SizeName_9, SizeName_10, SizeName_11, SizeName_12, SizeName_13, SizeName_14, SizeName_15, SizeName_16, SizeName_17, SizeName_18, SizeName_19, SizeName_20, SizeName_21, SizeName_22, SizeName_23, SizeName_24, SizeName_25, SizeName_26, SizeName_27, SizeName_28, SizeName_29, SizeName_30, SizeName_31, SizeName_32, SizeName_33, SizeName_34, SizeName_35, SizeName_36, SizeName_37, SizeName_38, SizeName_39, SizeName_40, SizeName_41, SizeName_42, SizeName_43, SizeName_44, SizeName_45, SizeName_46, SizeName_47, SizeName_48, SizeName_49, SizeName_50

Other information that might be relevant:
- PervasiveSQL 2000i
- Query returns about 3000 records

mirtheil · #2 (**permalink**) 01-05-10, 19:10

If the exact same query runs fast in PCC and slow in OpenOffice, the problem may be in the way OpenOffice is working.

One thing you can do to optimize the query is to make sure the restrictions (WHERE clause) are optimized. Specifically, you would need to check how many records match each restriction. If, for example, the "Grid.Type = 'G'" restriction returns the fewest records, place it at the front of the WHERE clause. Something like:

Code:

where
Grid.Type = 'G' AND (Cell.ItemNumber = Items.ItemNumber and Cell.ItemNumber = Grid.ItemNumber) and Cell.Type = 'C' and Cell.Location = 'SB'

Now, I'm not saying that will solve the problem and you'll need to do some testing (counting records returned for each restiction... use SELECT COUNT(*) FROM <table> WHERE <restriction> type queries), but it should help you make sure you're getting the data the fastest you can.

SilverJester · #3 (**permalink**) 01-07-10, 15:36

Thanks for the reply. Yes it is the exact same query (copied and pasted directly from PCC into OpenOffice). So I guess then I'll have to look into what would cause OpenOffice to preform so slowly.
And I'll take a look into the Where clauses to see if I can gain any speed there, what you said makes sense (although I didn't take that into account when writing the query).

Internet Services

Web Development

Digital Marketing

Consumer Tech