Query optimization

  • md.aamir.hussain

    Grasshopper

    Points: 16

    Dear Sir,

    select * from vw_AssetCommonDetail

    This is a view which is taking more time for execution and based on different tables.

    database size is 3GB only.

    Below are the view structure which is based on diffrent table.

    Please help me how to optimize this. what step appropriate step should be taken.

    ALTER VIEW [dbo].[vw_AssetCommonDetail]

    AS

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Blackberry.AssetType), AssetUser

    FROM Blackberry

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = DataCard.AssetType), AssetUser

    FROM DataCard

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Desktop.AssetType), AssetUser

    FROM Desktop

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Firewall.AssetType), AssetUser

    FROM Firewall

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Link.AssetType), AssetUser

    FROM Link

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = MUX.AssetType), AssetUser

    FROM MUX

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Printer.AssetType), AssetUser

    FROM Printer

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Router.AssetType), AssetUser

    FROM Router

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Scanner.AssetType), AssetUser

    FROM Scanner

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Server.AssetType), AssetUser

    FROM Server

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Storage.AssetType), AssetUser

    FROM Storage

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = VCON.AssetType), AssetUser

    FROM VCON

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = VOIP.AssetType), AssetUser

    FROM VOIP

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Monitor.AssetType), AssetUser

    FROM Monitor

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Laptop.AssetType), AssetUser

    FROM Laptop

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle,'' As BaseVendor, Vendor, AssetType, AssetTypeName =

    (SELECT AssetType

    FROM AssetType

    WHERE AssetType.AssetTypeID = Switch.AssetType), AssetUser

    FROM Switch

    Thanks

    Aamir

  • LutzM

    SSC Guru

    Points: 107049

    I would use the UNION operator (or even UNION ALL if possible) inside a CTE to get all values except AssetTypeName and join the CTE to the AssetType table.

    I would also reevaluate my database design to have only one table with all my assets plus one column to define the "source" (e.g. Blackberry, Datacard a.s.o.)....



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • ChrisM@home

    SSC-Insane

    Points: 24260

    ALTER VIEW [dbo].[vw_AssetCommonDetail]

    AS

    SELECT d.AssetID, d.Location, d.Tower, d.[Floor], d.Cubicle, d.BaseVendor, d.Vendor, d.AssetType, d.AssetUser, a.AssetType

    FROM (

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Blackberry

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM DataCard

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Desktop

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Firewall

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Link

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM MUX

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Printer

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Router

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Scanner

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM [Server]

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Storage

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM VCON

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM VOIP

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Monitor

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Laptop

    UNION

    SELECT DISTINCT AssetID, Location,Tower,[Floor],Cubicle,'' As BaseVendor, Vendor, AssetType, AssetUser

    FROM Switch

    ) d

    LEFT JOIN AssetType a ON a.AssetTypeID = d.AssetType

    -- Why do you have separate tables for the entities Blackberry, DataCard, Desktop, Firewall, Link

    -- it makes no more sense than having a separate table for each of your customers.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • ChrisM@home

    SSC-Insane

    Points: 24260

    lmu92 (1/23/2010)


    I would use the UNION operator (or even UNION ALL if possible) inside a CTE to get all values except AssetTypeName and join the CTE to the AssetType table.

    I would also reevaluate my database design to have only one table with all my assets plus one column to define the "source" (e.g. Blackberry, Datacard a.s.o.)....

    Hey Lutz, looks like I took your good advice 😉


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • LutzM

    SSC Guru

    Points: 107049

    lobbymuncher (1/23/2010)


    Hey Lutz, looks like I took your good advice 😉

    ... and you didn't even use a CTE ...

    Nabha's going to like it... 😀



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Jeff Moden

    SSC Guru

    Points: 996865

    I believe I'd use UNION ALL for this. UNION essentially does a distinct which is probably not necessary here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    I also see a DISTINCT clause on all queries.

    You can also try removing them and place only one DISTINCT. I am not sure on the performance, but at times, one single worktable to select the distinct values could be faster than sorting everything separately. I am not sure how it works in your environment, but you need to check it.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • ChrisM@home

    SSC-Insane

    Points: 24260

    Using all or most of the suggestions so far, and including gadget type depending on the source table:

    ALTER VIEW [dbo].[vw_AssetCommonDetail]

    AS

    SELECT DISTINCT d.[Gadget], d.AssetID, d.Location, d.Tower, d.[Floor], d.Cubicle, d.BaseVendor, d.Vendor, d.AssetType, d.AssetUser, a.AssetType

    FROM (

    SELECT CAST('Blackberry' AS VARCHAR(15)) AS [Gadget], AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Blackberry

    UNION ALL SELECT 'DataCard', AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM DataCard

    UNION ALL SELECT 'Desktop', AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Desktop

    UNION ALL SELECT 'Firewall', AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Firewall

    UNION ALL SELECT 'Link', AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Link

    UNION SELECT 'MUX', AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM MUX

    UNION ALL SELECT 'Printer', AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Printer

    UNION ALL SELECT 'Router', AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Router

    UNION ALL SELECT 'Scanner', AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Scanner

    UNION ALL SELECT 'Server', AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM [Server]

    UNION ALL SELECT 'Storage', AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM Storage

    UNION ALL SELECT 'VCON', AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM VCON

    UNION ALL SELECT 'VOIP', AssetID, Location,Tower,[Floor],Cubicle, '' AS BaseVendor, Vendor, AssetType, AssetUser

    FROM VOIP

    UNION ALL SELECT 'Monitor', AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Monitor

    UNION ALL SELECT 'Laptop', AssetID, Location,Tower,[Floor],Cubicle, BaseVendor, Vendor, AssetType, AssetUser

    FROM Laptop

    UNION ALL SELECT 'Switch', AssetID, Location,Tower,[Floor],Cubicle,'' As BaseVendor, Vendor, AssetType, AssetUser

    FROM Switch

    ) d

    LEFT JOIN AssetType a ON a.AssetTypeID = d.AssetType

    Cheers

    ChrisM@home


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

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