Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    11

    Unanswered: Slow query times through OpenOffice

    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

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Dec 2009
    Posts
    11
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •