CODEWITHSUNDEEP
phpsqlsql-server
ScottC
ScottC

Reputation: 462

How can I speed up these nested SQL queries

When I try these queries it runs pretty slow, since I have a lot of data, but I can't exclude any more from the top query because I need to see first if the second query results in a 0 total for futureAmts AND the on_hand in the first query

$inv = odbc_exec($live,"SELECT * FROM V1ICPR WHERE Account='".$account."'");
while (odbc_fetch_array($inv))
{
    $futureOrds = 0;
    $checkFuture = odbc_exec($live,"SELECT Order_num FROM V1OEMF WHERE Account = '".$account."' AND Status='Open' AND Ship_date > '".date("Ymd")."'");
    while(odbc_fetch_array($checkFuture))
    {
        $getFutureAmt = odbc_exec($live,"SELECT Amount FROM V1OEOO WHERE bol='".odbc_result($checkFuture,'Order_num')."' AND Prod_code='".odbc_result($inv,'Prod_code')."'");            
        while (odbc_fetch_array($getFutureAmt)){

            $futureOrds = $futureOrds + intval(odbc_result($getFutureAmt,'Amount'));

        }
    }

I tried an Inner Join but that query is failing to run altogether, is there a problem with my INNER JOIN query? is there a faster way to code this?

    /*
    $checkFuture = odbc_exec($live,"SELECT V1OEMF.Order_num, V1OEOO.Amount FROM V1OEMF INNER JOIN V1OEOO ON V1OEMF.Order_num = V1OEOO.bol WHERE V1OEMF.Account = '".$account."' AND V1OEMF.Status='Open' AND V1OEMF.Ship_date > '".date("Ymd")."' AND V1OEOO.Prod_code='".odbc_result($inv,'Prod_code')."'")
    while(odbc_fetch_array($checkFuture))
    {
        $futureOrds = $futureOrds + intval(odbc_result($checkFuture,'Amount'));
    }
    */

If I run just one level the first one that just gets the product info, and no infor about future orders, it runs in like 2 seconds, but with these nested queries it can take 30 - 60 + seconds

TABLES:

CREATE TABLE [dbo].[V1ICPR](
[Account] [nvarchar](50) NOT NULL,
[Prod_code] [nvarchar](50) NOT NULL,
[Prod_desc] [nvarchar](50) NOT NULL,
[Whs] [nvarchar](50) NOT NULL,
[On_hand] [nvarchar](50) NOT NULL,
[On_ord] [nvarchar](50) NOT NULL,
[On_hold] [nvarchar](50) NOT NULL,
[Amt_avail] [nvarchar](50) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[V1OEMF](
[Order_num] [nvarchar](50) NOT NULL,
[Status] [nvarchar](50) NOT NULL,
[Account] [nvarchar](50) NOT NULL,
[Warehouse] [nvarchar](50) NOT NULL,
[Weight] [nvarchar](50) NOT NULL,
[Load_stop] [nvarchar](50) NOT NULL,
[Pick_num] [nvarchar](50) NOT NULL,
[Carrier] [nvarchar](50) NOT NULL,
[Carrier_desc] [nvarchar](50) NOT NULL,
[pay_type] [nvarchar](50) NOT NULL,
[EDI] [nvarchar](50) NOT NULL,
[CCode] [nvarchar](50) NOT NULL,
[Ship_date] [nvarchar](50) NOT NULL,
[Order_date] [nvarchar](50) NOT NULL,
[Order_time] [nvarchar](50) NOT NULL,
[Consignee] [nvarchar](50) NOT NULL,
[Cases] [int] NOT NULL,
[Long_ship_date] [nvarchar](50) NOT NULL,
[Delivery_date] [nvarchar](50) NOT NULL,
[Long_del_date] [nvarchar](50) NOT NULL,
[Long_edi_date] [nvarchar](50) NOT NULL,
[Long_entry_date] [nvarchar](50) NOT NULL,
[EDI_time] [nvarchar](50) NOT NULL,
[Appt_num] [nvarchar](50) NOT NULL,
[Cust_ord_num] [nvarchar](50) NOT NULL,
[Cons_ord_num] [nvarchar](50) NOT NULL,
[Ord_weight] [int] NOT NULL,
[Ord_cube] [nvarchar](50) NOT NULL,
[Cube_lbs] [int] NOT NULL,
[Est_pallets] [nvarchar](50) NOT NULL,
[Address_1] [nvarchar](50) NOT NULL,
[Address_2] [nvarchar](50) NOT NULL,
[Address_3] [nvarchar](50) NOT NULL,
[Address_4] [nvarchar](50) NOT NULL,
[Prov] [nvarchar](50) NULL,
[Postal] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[V1OEOO](
[bol] [nvarchar](50) NOT NULL,
[Account] [nvarchar](6) NOT NULL,
[Prod_code] [nvarchar](50) NOT NULL,
[Lot_num] [nvarchar](50) NOT NULL,
[Amount] [nvarchar](50) NOT NULL,
[Prod_desc] [nvarchar](50) NOT NULL
) ON [PRIMARY]

Upvotes: 0

Views: 142

Answers (1)

elixenide
elixenide

Reputation: 44851

This solution is already addressed in the comments, but I am adding it as an answer for the benefit of future readers.

You need to add indexes on each of the columns in your WHERE clauses. This will dramatically increase the speed of each query if, as you say, you have a lot of data. You can do this with an ALTER TABLE command.

Also, use TOP 1 (for MSSQL, LIMIT 1 for MySQL) whenever you expect only one result, especially in the inner loops.

Upvotes: 1

Related Questions