Professional Web Applications Themes

performance in query... - Microsoft SQL / MS SQL Server

Hello, someone knows o could help me with this query please: I attachment the scripting and the end I have this tables: Table_Settings --------- IDCust-InCity-OutCity-Prefix-InMask-OutMask ------------------------------------------- 41-Boston-Vancouver-NULL-Usa-Ca 41-Mty-Bgt-0528-Mex-Col 41-Mty-Bgt-0525-Mex-Ecu 41-Houston-NULL-NULL-Usa-NA 41-Quito-Ontario-NULL-Ecu-Ca Table_InCity --------- IDInCity-DesInCity 1-Boston 2-Mty 3-Houston 4-Quito Table_OutCity --------- IDOutCity-DesOutCity 1-Vancouver 2-Bgt 3-Ontario 4-Miami 5-Texas 6-Cincinati Table_Universe ------------ IDInCity-IDOutCity-Code-Balance 1-1-345789-40 1-1-879658-25 1-1-null-10 2-2-05281234-80 2-2-05286789-65 2-2-05257412-30 2-2-05255896-14 3-4-null-45 3-4-981258-45 3-5-3668745-24 3-6-1168745-12 4-3-748978-79 4-3-null-48 4-3-549874-23 I need to build a query which read data from Table_Settings, Table_InCity, Table_OutCity and Table_Universe in order to get this result: IDCust-InMask-OutMask-TotalBalance ---------------------------------- 41-Usa-Ca-65 41-Mex-Col-145 41-Mex-Ecu-44 41-Usa-NA-126 41-Ecu-Ca-150 I have this query, but the results expected ...

  1. #1

    Default performance in query...

    Hello, someone knows o could help me
    with this query please:

    I attachment the scripting and the end

    I have this tables:

    Table_Settings
    ---------
    IDCust-InCity-OutCity-Prefix-InMask-OutMask
    -------------------------------------------
    41-Boston-Vancouver-NULL-Usa-Ca
    41-Mty-Bgt-0528-Mex-Col
    41-Mty-Bgt-0525-Mex-Ecu
    41-Houston-NULL-NULL-Usa-NA
    41-Quito-Ontario-NULL-Ecu-Ca



    Table_InCity
    ---------
    IDInCity-DesInCity
    1-Boston
    2-Mty
    3-Houston
    4-Quito


    Table_OutCity
    ---------
    IDOutCity-DesOutCity
    1-Vancouver
    2-Bgt
    3-Ontario
    4-Miami
    5-Texas
    6-Cincinati


    Table_Universe
    ------------
    IDInCity-IDOutCity-Code-Balance
    1-1-345789-40
    1-1-879658-25
    1-1-null-10
    2-2-05281234-80
    2-2-05286789-65
    2-2-05257412-30
    2-2-05255896-14
    3-4-null-45
    3-4-981258-45
    3-5-3668745-24
    3-6-1168745-12
    4-3-748978-79
    4-3-null-48
    4-3-549874-23

    I need to build a query which read data from
    Table_Settings, Table_InCity, Table_OutCity and
    Table_Universe in order to get this result:

    IDCust-InMask-OutMask-TotalBalance
    ----------------------------------
    41-Usa-Ca-65
    41-Mex-Col-145
    41-Mex-Ecu-44
    41-Usa-NA-126
    41-Ecu-Ca-150


    I have this query, but the results expected are not
    correct, this is:

    Create view viewUniverse
    AS
    Select I.IDInCity AS IDInCity, O.IDOutCity AS IDOutCity,
    C.InCity AS InCity, C.OutCity AS OutCity, C.Prefix AS
    Prefix, C.InMask AS InMask, C.OutMask AS OutMask
    from Table_Settings AS C
    join Table_InCity AS I on ( C.InCity = I.DesInCity)
    join Table_OutCity AS O on ( C.OutCity = O.DesOutCity )
    WHERE C.IDCust = '41'


    Select *from viewUniverse

    SELECT C.InMask, C.OutMask, SUM(U.Balance) AS Balance
    FROM viewUniverse AS C
    JOIN Table_Universe AS U
    ON C.IDInCity = U.IDInCity
    AND C.IDOutCity = U.IDOutCity
    AND U.Code LIKE COALESCE(C.Prefix,'')+'%'
    GROUP BY C.InMask, C.OutMask


    InMask-OutMask-Balance
    ----------------------
    Ecu-Ca-102
    Mex-Col-145
    Mex-Ecu-44
    Usa-Ca-65


    What can I do in order to fix above query ???

    Any help is greatly appreciated.


    ** Scripting **

    if exists (select * from sysobjects where id = object_id
    (N'[dbo].[Table_InCity]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [dbo].[Table_InCity]
    GO

    if exists (select * from sysobjects where id = object_id
    (N'[dbo].[Table_OutCity]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [dbo].[Table_OutCity]
    GO

    if exists (select * from sysobjects where id = object_id
    (N'[dbo].[Table_Settings]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [dbo].[Table_Settings]
    GO

    if exists (select * from sysobjects where id = object_id
    (N'[dbo].[Table_Universe]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [dbo].[Table_Universe]
    GO

    CREATE TABLE [dbo].[Table_InCity] (
    [IDInCity] [int] NULL ,
    [DesInCity] [varchar] (50) NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Table_OutCity] (
    [IDOutCity] [int] NULL ,
    [DesOutCity] [varchar] (50) NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Table_Settings] (
    [IDCust] [int] NULL ,
    [InCity] [varchar] (50) NULL ,
    [OutCity] [varchar] (50) NULL ,
    [Prefix] [varchar] (50) NULL ,
    [InMask] [varchar] (50) NULL ,
    [OutMask] [varchar] (50) NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Table_Universe] (
    [IDInCity] [int] NULL ,
    [IDOutCity] [int] NULL ,
    [Code] [varchar] (50) NULL ,
    [Balance] [int] NULL
    ) ON [PRIMARY]
    GO


    ** Data **

    Insert
    Into Table_Settings
    (IDCust,InCity,OutCity,Prefix,InMask,OutMask)
    values('41','Boston','Vancouver','NULL','Usa','Ca' );

    Insert
    Into Table_Settings
    (IDCust,InCity,OutCity,Prefix,InMask,OutMask)
    values('41','Mty','Bgt','0528','Mex','Col');

    Insert
    Into Table_Settings
    (IDCust,InCity,OutCity,Prefix,InMask,OutMask)
    values('41','Mty','Bgt','0525','Mex','Ecu');

    Insert
    Into Table_Settings
    (IDCust,InCity,OutCity,Prefix,InMask,OutMask)
    values('41','Houston','NULL','NULL','Usa','NA');

    Insert
    Into Table_Settings
    (IDCust,InCity,OutCity,Prefix,InMask,OutMask)
    values('41','Quito','Ontario','NULL','Ecu','Ca');


    Insert
    Into Table_InCity(IDInCity,DesInCity)
    values('1','Boston');

    Insert
    Into Table_InCity(IDInCity,DesInCity)
    values('2','Mty');

    Insert
    Into Table_InCity(IDInCity,DesInCity)
    values('3','Houston');

    Insert
    Into Table_InCity(IDInCity,DesInCity)
    values('4','Quito');


    Insert
    Into Table_OutCity(IDOutCity,DesOutCity)
    values('1','Vancouver');

    Insert
    Into Table_OutCity(IDOutCity,DesOutCity)
    values('2','Bgt');

    Insert
    Into Table_OutCity(IDOutCity,DesOutCity)
    values('3','Ontario');

    Insert
    Into Table_OutCity(IDOutCity,DesOutCity)
    values('4','Miami');

    Insert
    Into Table_OutCity(IDOutCity,DesOutCity)
    values('5','Texas');

    Insert
    Into Table_OutCity(IDOutCity,DesOutCity)
    values('6','Cincinati');


    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('1','1','345789','40');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('1','1','879658','25');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('1','1','null','10');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('2','2','05281234','80');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('2','2','05286789','65');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('2','2','05257412','30');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('2','2','05255896','14');

    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('3','4','null','45');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('3','4','981258','45');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('3','5','3668745','24');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('3','6','1168745','12');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('4','3','748978','79');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('4','3','null','48');
    Insert Into Table_Universe
    (IDInCity,IDOutCity,Code,Balance) values
    ('4','3','549874','23');

    lubiel Guest

  2. #2

    Default Re: performance in query...

    Hello Lubiel !

    Nice Scipting, good work, it is perfectly to script the tables, that we can
    easier try to help you.

    SELECT IDCUST, INMask, OUTMask, Balance
    FROM (SELECT Table_Settings.IDCust, Table_Settings.InMask,
    Table_Settings.OutMask, Table_InCity.IDInCity,
    Table_OutCity.IDOutCity
    FROM Table_Settings INNER JOIN
    Table_OutCity ON
    Table_Settings.OutCity = Table_OutCity.DesOutCity INNER
    JOIN
    Table_InCity ON
    Table_Settings.InCity = Table_InCity.DesInCity)
    DRVTBL INNER JOIN
    Table_Universe ON
    DRVTBL.IDINCity = Table_universe.IDINCity AND
    DRVTBL.IDOUTCity = Table_universe.IDOUTCity

    Try this. Though there is no PK in the Table_universe i couldn´t identify
    which balance was needed to populate, so
    14 rows are given back (instead of four). Ok ?

    ----------- -------------------------------------------------- -------------
    ------------------------------------- -----------
    41 Usa Ca
    40
    41 Usa Ca
    25
    41 Usa Ca
    10
    41 Mex Col
    80
    41 Mex Ecu
    80
    41 Mex Col
    65
    41 Mex Ecu
    65
    41 Mex Col
    30
    41 Mex Ecu
    30
    41 Mex Col
    14
    41 Mex Ecu
    14
    41 Ecu Ca
    79
    41 Ecu Ca
    48
    41 Ecu Ca
    23

    (14 row(s) affected)


    Jens Süßmeyer. 


    Jens Guest

  3. #3

    Default Re: performance in query...

    OK, Thanks for you answer, but the result
    is not correct, not expected for me request.

    I remember you:

    I need to build a query which read data from
    Table_Settings, Table_InCity, Table_OutCity and
    Table_Universe in order to get this result:

    IDCust-InMask-OutMask-TotalBalance
    ----------------------------------
    41-Usa-Ca-75
    41-Mex-Col-145
    41-Mex-Ecu-44
    41-Usa-NA-126
    41-Ecu-Ca-150

    I dont have PK in the Table_universe, but i need to
    get the balance like is show above.

    Any help is greatly appreciated.


     
    tables, that we can 
    Table_OutCity.DesOutCity INNER 
    couldn´t identify 
    ---- ------------- 
    Usa Ca 
    Usa Ca 
    Usa Ca 
    Mex Col 
    Mex Ecu 
    Mex Col 
    Mex Ecu 
    Mex Col 
    Mex Ecu 
    Mex Col 
    Mex Ecu 
    Ecu Ca 
    Ecu Ca 
    Ecu Ca 
    >
    >
    >.
    >[/ref]
    lubiel Guest

Similar Threads

  1. SQL Query Performance
    By DanCasper in forum Coldfusion Database Access
    Replies: 4
    Last Post: November 21st, 06:34 PM
  2. Simple query, very low performance
    By Bernhard Kornberger in forum MySQL
    Replies: 3
    Last Post: July 6th, 12:27 PM
  3. Query performance problem
    By Phil in forum PostgreSQL / PGSQL
    Replies: 8
    Last Post: March 19th, 10:46 PM
  4. query performance SE vs IDS
    By Jack in forum Informix
    Replies: 9
    Last Post: January 2nd, 01:59 PM
  5. Query Performance: Using DECODE() instead of IN()
    By Paul in forum Oracle Server
    Replies: 3
    Last Post: October 20th, 09:09 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