How can I make my SQL statement smaller so my server doesn't time out?

  • so I have the following problem.

    I have three tables in my postgresql DB.

    They are: package, cartype and packagecartype.

    package refers to a thing that needs to be moved from A to B and has certain requirements for it. For example, one of the requirements is freightlimit, which indicates how big the car has to be that transports a package. So it has minfreight and maxfreight which makes a range, "Car needs to be able to transport at a minimum this much and at a maximum that much, for this package."

    cartype is a table where I save types of cars, that has nothing more than a an id, a minfreight and maxfreight and a name.

    A package can have multiple cartypes, which are saved in the table packagecartype.

    It means you don't have to enter the min/max-freight for the package, instead you can give it one or multiple cartypes to indicate the freight range.

    packagecartype has three columns, it's own id, the packageid and the cartypeid.

    Now I want to build a search function, that make a sql statement and give me all packages within certain parameters.

    One of the fields I want my search to have is minfreight and maxfreight, meaning it will give me back all packages that have min/maxfreight within the given range OR all packages that have cartypes within the given range.

    My SQL statement currently looks like this:

    SELECT * FROM package WHERE
    ((COALESCE(package.maxfreight, (SELECT MAX(ct.maxfreight)
      FROM packagecartype AS pct
      LEFT JOIN cartype AS ct
      ON pct.cartypeid = ct.id
      WHERE pct.cargoid = package.id)) >= $GIVEN_MIN_FREIGHT)
     AND (COALESCE(package.minfreight, (SELECT MIN(ct.minfreight)
      FROM packagecartype AS pct
      LEFT JOIN cartype AS ct
      ON pct.cartypeid = ct.id
      WHERE pct.cargoid = package.id)) <= $GIVEN_MAX_FREIGHT));

    This works fine but it is way too slow. It times out if I use it on a DB with more than a few dozen entries which is just not acceptable. But sadly I am not that good at SQL and don't know how to improve this.

    Any help is GREATLY appreciated!

  • Please provide ddl, sample data and expected output.  Looking at your query, the coalesce function on the where clause isn't good

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • This is a SQL Server forum. You may have more success if you post your questions on a forum dedicated to Postgres.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • I am working with postgresql.

    Example:

    Package Table
    id  | minfreight | maxfreight
    1 |     10    |     150
    2 |   null    |    null
    3 |    300   |    400

    Cartypes
    id | minfreight | maxfreight | name
    1  |  100          | 200            | 'small'
    2  | 201           | 400            | 'large'

    Packagecartype
     id | packageid | cartypeid
    1 | 2 | 1 

    Search input
    minfreight: 10 , maxfreight: 150
    Expected Output:
    packages with id: 1, 2

    Searchinput:
    Cartype: small
    Expected Output:
    packages with id: 1, 2

    Searchinput:
    Cartype: large
    Expected Output:
    packages with id: 3

  • @phil

    I didn't find a proper, active postgresql forum, but if you've got one I'd appreciate a link!

  • a.sophiewirth - Thursday, July 26, 2018 10:24 AM

    @phil

    I didn't find a proper, active postgresql forum, but if you've got one I'd appreciate a link!

    Sorry, but I do not. 
    Unfortunately, not all SQL dialects are the same. The Postgres SQL you posted, for example, does not even parse as valid SQL in SQL Server, making it difficult for people who do not know Postgres to help. That's one of the reasons why finding a dedicated forum is a good idea for you.
    Having said that, I know that there are people here who know Postgres as well as SQL Server, so you may yet be in luck.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • here is what I think the ddl  is that I asked you for, but your search criteria doesn't make any sense.  can you elaborate?  Also, Phil's been on this forum for a long time and has helped alot of people.  I hope that was an honest response and not a smart-ass remark, which was how I interpreted it.


    Drop table if exists #Package
    Drop table if exists #CarType
    Drop table if exists #PackageCarType

    Create table #Package(PackageID int, MinFreight int, MaxFreight int)
    insert into #Package
    Values
    (1,10,150),
    (2,null,null),
    (3,300,400)

    Create table #CarType (CarTypeID int, MinFreight int, MaxFreight int, Name varchar(10))
    insert into #CarType
    Values
    (1,100,200,'Small'),
    (2,201,400,'Large')

    Create table #PackageCarType (Id int, PackageID int, CarTypeID int)
    insert into #PackageCarType values(1,2,1)

    select p.MinFreight PackageMinFreight, p.MaxFreight PackageMaxFreight, pc.PackageID
         ,c.MinFreight CarMinFreight, c.MaxFreight CarMaxFreight, c.Name, c.CarTypeID
    from #Package p
        left join #PackageCarType pc
            on p.PackageID = pc.PackageID
        left join #CarType c
            on c.CarTypeID = pc.CarTypeID

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • a.sophiewirth - Thursday, July 26, 2018 10:24 AM

    @phil

    I didn't find a proper, active postgresql forum, but if you've got one I'd appreciate a link!

    I just did a search on this: Postgres forums
    And there are at least a few with activity in July 2018. It took less than a minute to find these.

    Sue

  • WITH MIN_MAX AS (

      SELECT pct.cargoid AS PACKAGE_ID,
       MIN(ct.minfreight) AS MIN_minfreight,
       MAX(ct.maxfreight) AS MAX_maxfreight
      FROM packagecartype AS pct
      LEFT OUTER JOIN cartype AS ct
       ON pct.cartypeid = ct.id
      GROUP BY pct.cargoid
    )
    SELECT P.*
    FROM package AS P
    INNER JOIN MIN_MAX AS MM
      ON P.id = MM.PACKAGE_ID
    WHERE  COALESCE(P.maxfreight, MM.MAX_maxfreight) >= $GIVEN_MIN_FREIGHT
      AND COALESCE(P.minfreight, MM.MIN_minfreight) <= $GIVEN_MAX_FREIGHT;

  • This solution is untested and might not even solve the problem. It's using what should be generic SQL (except for the variables).
    The lenght of the code is not the reason for the timeout. The execution time (and configuration) is the reason for the timeout. Right now, you're reading 3 whole tables and that might be expensive. Additionally, you're reading 2 of them twice.
    There might be a better option to better profit of indexes, but I'm not sure what's available in Postgres and what's not, nor I'm aware of how that engine works internally.

    SELECT *
    FROM package AS p
    LEFT JOIN (SELECT pct.cargoid, MAX(ct.maxfreight) AS maxfreight, MIN(ct.minfreight) AS minfreight
        FROM packagecartype AS pct
        LEFT JOIN cartype AS ct ON pct.cartypeid = ct.id
        GROUP BY pct.cargoid) AS pc ON pc.cargoid = package.id
    WHERE (p.maxfreight >= $GIVEN_MIN_FREIGHT OR (p.maxfreight IS NULL AND pc.maxfreight >= $GIVEN_MIN_FREIGHT))
    AND (p.minfreight <= $GIVEN_MAX_FREIGHT OR (p.minfreight IS NULL AND pc.minfreight <= $GIVEN_MAX_FREIGHT));

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • @phil:
    Don't worry about it, posting here was a long shot, but let's hope I do get lucky! Thank you for the tips.

    @mike-2: Not really sure in what way asking to follow up on a given tip is a 'smart ass' reply. I didn't see any active, psql specific forums so I asked to be linked to one, seeing as phil suggested I was coming to wrong place by posting here.
    What about my search criteria doesn't make sense to you? I am glad to elaborate.
    Thank you very much for taking the time to type out an answer, but I can absolutely not drop ANY tables, seeing as I am working on an active, LARGE production DB. I will keep this better way to structure things in mind for future needed relations of the sort though. I appreciate it!

     @sue: I didn't. I found a few dead psql forums and the official postgresql community, where I can't post any psql questions as far as I can see though. So if you do have any links for me, I'd appreciate them.

    @steve-2 and @luis: Thank you very much for your help, I'll try the code and come back to you! But I really appreciate you taking the time to type this out!

  • It was just the way I read it.  I apologize and I'm glad it was me who misinterpreted it and not your intent.  As far as dropping tables, those we just temp tables.  On this forum, alot of us will use temp tables in the examples.  They only last as along as the session is open and then are deleted automatically (plus we don't know the names of your tables, so we make examples).  I wouldn't recommend dropping any 'real' tables either.

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • @mike-2: That's alright, I suppose the internet has made us all a little more cynical.
    Ah, I understand, thank you for explaining! Database structures and using them in bigger contexts is still extremely new to me so I understand basically nothing as of yet. 

    @steve-2: I have adapted your code and it seems to be working amazingly well, just the INNER JOIN had to become a LEFT JOIN, Thank you a lot for your help! I am very grateful for it, I was really stuck here.

    @luis, Yeah I think my topic was worded badly, I understand it wasn't really the length of my code that caused the timeout. Your example has been really helpful in figuring out how I can chain JOINTS better together. Thank you!

  • a.sophiewirth - Thursday, July 26, 2018 10:24 AM

    @phil

    I didn't find a proper, active postgresql forum, but if you've got one I'd appreciate a link!

    dba.stackexchange.com would be my recommendation.  Make sure to tag your question as PostgreSQL.

    Here is a reddit discussion of the best PostgreSQL related forums:

    Reddit PostgreSQL forum discussion

  • @sestell: I did indeed post my question to stackexchange as well, though I did not receive as much help as here. To post on reddit never occurred to me though, so I will definitely keep that in mind! Thank you!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply