Query optimization

• md.aamir.hussain

  Grasshopper

  Points: 16

  More actions

  January 23, 2010 at 5:13 am

  #229286

  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

  More actions

  January 23, 2010 at 5:44 am

  #1107737

  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

  More actions

  January 23, 2010 at 12:09 pm

  #1107790

  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

  More actions

  January 23, 2010 at 1:53 pm

  #1107819

  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

  More actions

  January 23, 2010 at 2:28 pm

  #1107829

  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: 1004356

  More actions

  January 23, 2010 at 10:31 pm

  #1107877

  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.
  

  ---

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

• G.R.Prithiviraj Kulasingham

  SSCarpal Tunnel

  Points: 4105

  More actions

  January 23, 2010 at 11:38 pm

  #1107882

  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

  More actions

  January 24, 2010 at 4:22 am

  #1107902

  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]