Professional Web Applications Themes

how to optimize this simple query/proc - Microsoft SQL / MS SQL Server

Hi Everyone-- Please help. Do you have any idea how to optimize (AKA speed up) this query? select distinct line_type, line_item, uom_cd from order_mgmt.dbo.job_sheet_other where line_type != '' and line_item != '' and uom_cd != '' order by line_type, line_item, uom_cd asc Note that this query is actually in a stored proc. Below is the SQL for the stored proc and the table itself. (Note-- I cannot make ANY schema changes.) Any hints or suggestions are appreciated. Thank you. --Mark This is the stored proc.... /****** Object: Stored Procedure dbo.win_unbilled_sales_detail_get_linetype_lineite m_uomcd Script Date: 08/14/2003 09:55:33 ******/ if exists (select * ...

  1. #1

    Default how to optimize this simple query/proc



    Hi Everyone--

    Please help.

    Do you have any idea how to optimize (AKA speed up) this query?



    select distinct line_type, line_item, uom_cd
    from order_mgmt.dbo.job_sheet_other
    where line_type != ''
    and
    line_item != ''
    and
    uom_cd != ''
    order by line_type, line_item, uom_cd asc


    Note that this query is actually in a stored proc.

    Below is the SQL for the stored proc and the table itself.

    (Note-- I cannot make ANY schema changes.)

    Any hints or suggestions are appreciated.

    Thank you.

    --Mark










    This is the stored proc....



    /****** Object: Stored Procedure
    dbo.win_unbilled_sales_detail_get_linetype_lineite m_uomcd Script Date:
    08/14/2003 09:55:33 ******/
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[win_unbilled_sales_detail_get_linetype_lineitem_uo mcd]')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure
    [dbo].[win_unbilled_sales_detail_get_linetype_lineitem_uo mcd]
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    /****** Object: Stored Procedure
    dbo.win_unbilled_sales_detail_get_linetype_lineite m_uomcd Script Date:
    08/14/2003 09:55:33 ******/

    create procedure win_unbilled_sales_detail_get_linetype_lineitem_uo mcd
    as

    select distinct line_type, line_item, uom_cd
    from order_mgmt.dbo.job_sheet_other
    where line_type != ''
    and
    line_item != ''
    and
    uom_cd != ''
    order by line_type, line_item, uom_cd asc
    if error <> 0
    begin
    return error
    end
    else
    begin
    return -1
    end

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO




    This is the table......




    /****** Object: Table [dbo].[job_sheet_other] Script Date: 08/14/2003
    09:51:28 ******/
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[job_sheet_other]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [dbo].[job_sheet_other]
    GO

    /****** Object: Table [dbo].[job_sheet_other] Script Date: 08/14/2003
    09:51:29 ******/
    CREATE TABLE [dbo].[job_sheet_other] (
    [sls_order_no] [varchar] (10) NOT NULL ,
    [Job_execution_dt] [datetime] NOT NULL ,
    [grp_id] [smallint] NOT NULL ,
    [line_no] [smallint] NOT NULL ,
    [line_type] [varchar] (4) NOT NULL ,
    [line_item] [char] (15) NOT NULL ,
    [uom_cd] [varchar] (4) NULL ,
    [Item_qty] [money] NULL ,
    [vhcle_no] [char] (8) NULL ,
    [branch] [varchar] (3) NULL ,
    [xtrct_status] [char] (1) NOT NULL ,
    [subcntrctd] [varchar] (1) NOT NULL ,
    [actl_qty] [money] NULL ,
    [actl_uom] [varchar] (4) NULL ,
    [po_no] [char] (10) NULL ,
    [po_line_no] [smallint] NULL ,
    [quote_no] [char] (10) NULL ,
    [billable_indcr] [varchar] (1) NOT NULL ,
    [mtrl_left_onsite_indcr] [varchar] (1) NOT NULL ,
    [job_dt] [datetime] NULL ,
    [mnfstd_co_cd] [varchar] (10) NULL ,
    [mnfstd_cnt] [smallint] NULL ,
    [mdfctn_usrnam] [varchar] (16) NULL ,
    [mdfctn_dt] [datetime] NULL ,
    [creatn_dt] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO


    Mark Guest

  2. #2

    Default Re: how to optimize this simple query/proc

    Put an index on (line_type, line_item, uom_cd) and change the query to:

    select distinct line_type, line_item, uom_cd
    from order_mgmt.dbo.job_sheet_other
    where line_type > ''
    and
    line_item > ''
    and
    uom_cd > ''
    order by line_type, line_item, uom_cd asc


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Mark Kamoski" <com> wrote in message news:#LF#phx.gbl...


    Hi Everyone--

    Please help.

    Do you have any idea how to optimize (AKA speed up) this query?



    select distinct line_type, line_item, uom_cd
    from order_mgmt.dbo.job_sheet_other
    where line_type != ''
    and
    line_item != ''
    and
    uom_cd != ''
    order by line_type, line_item, uom_cd asc


    Note that this query is actually in a stored proc.

    Below is the SQL for the stored proc and the table itself.

    (Note-- I cannot make ANY schema changes.)

    Any hints or suggestions are appreciated.

    Thank you.

    --Mark










    This is the stored proc....



    /****** Object: Stored Procedure
    dbo.win_unbilled_sales_detail_get_linetype_lineite m_uomcd Script Date:
    08/14/2003 09:55:33 ******/
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[win_unbilled_sales_detail_get_linetype_lineitem_uo mcd]')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure
    [dbo].[win_unbilled_sales_detail_get_linetype_lineitem_uo mcd]
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    /****** Object: Stored Procedure
    dbo.win_unbilled_sales_detail_get_linetype_lineite m_uomcd Script Date:
    08/14/2003 09:55:33 ******/

    create procedure win_unbilled_sales_detail_get_linetype_lineitem_uo mcd
    as

    select distinct line_type, line_item, uom_cd
    from order_mgmt.dbo.job_sheet_other
    where line_type != ''
    and
    line_item != ''
    and
    uom_cd != ''
    order by line_type, line_item, uom_cd asc
    if error <> 0
    begin
    return error
    end
    else
    begin
    return -1
    end

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO




    This is the table......




    /****** Object: Table [dbo].[job_sheet_other] Script Date: 08/14/2003
    09:51:28 ******/
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[job_sheet_other]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [dbo].[job_sheet_other]
    GO

    /****** Object: Table [dbo].[job_sheet_other] Script Date: 08/14/2003
    09:51:29 ******/
    CREATE TABLE [dbo].[job_sheet_other] (
    [sls_order_no] [varchar] (10) NOT NULL ,
    [Job_execution_dt] [datetime] NOT NULL ,
    [grp_id] [smallint] NOT NULL ,
    [line_no] [smallint] NOT NULL ,
    [line_type] [varchar] (4) NOT NULL ,
    [line_item] [char] (15) NOT NULL ,
    [uom_cd] [varchar] (4) NULL ,
    [Item_qty] [money] NULL ,
    [vhcle_no] [char] (8) NULL ,
    [branch] [varchar] (3) NULL ,
    [xtrct_status] [char] (1) NOT NULL ,
    [subcntrctd] [varchar] (1) NOT NULL ,
    [actl_qty] [money] NULL ,
    [actl_uom] [varchar] (4) NULL ,
    [po_no] [char] (10) NULL ,
    [po_line_no] [smallint] NULL ,
    [quote_no] [char] (10) NULL ,
    [billable_indcr] [varchar] (1) NOT NULL ,
    [mtrl_left_onsite_indcr] [varchar] (1) NOT NULL ,
    [job_dt] [datetime] NULL ,
    [mnfstd_co_cd] [varchar] (10) NULL ,
    [mnfstd_cnt] [smallint] NULL ,
    [mdfctn_usrnam] [varchar] (16) NULL ,
    [mdfctn_dt] [datetime] NULL ,
    [creatn_dt] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO



    Tom Guest

  3. #3

    Default Re: how to optimize this simple query/proc

    If you can't make schema changes, then you are lost. Currently there are
    no indexes on the table. In fact, there even aren't any keys!! Any query
    will have to do a full table scan.

    The improvement I can suggest (which will not influence performance in
    any way), is to drop the proprietary != syntax, and replace it with <>
    (so it should be where line_type <> '')

    Gert-Jan


    Mark Kamoski wrote: 
    Gert-Jan Guest

Similar Threads

  1. Optimize Query
    By umuayo in forum Coldfusion Database Access
    Replies: 7
    Last Post: April 14th, 03:58 PM
  2. Help optimize query
    By anilcool@gmail.com in forum MySQL
    Replies: 4
    Last Post: June 28th, 06:48 PM
  3. Optimize this query...
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 13th, 04:55 PM
  4. Simple stored proc in query question
    By Bob Castleman in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 04:20 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139