Presentation de rapport

[Slim_D]

Bonjour vous trouverez ci apres une syntaxe d un rapport
je souhaite savoir si il existe une possibilité d'optimiser ma syntaxe

merci de votre aide

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblADComputers.OU,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssets.Lastseen,
  tblADComputers.Description,
  tblAssetCustom.Custom1
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
    tblAssets.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%tours%'
  And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
  (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%blois%' And
  tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
  (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Orleans%' And
  tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
  (tsysOS.OSname Not Like '%Win 10%' And
  tblADComputers.OU Like '%chatellerault%' And tblAssetCustom.State = 1 And
  tblComputersystem.Domainrole < 2) Or
  (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%poitiers%' And
  tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
  (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%nogent%' And
  tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
  (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%chateaudun%'
  And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
  (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%le mans%' And
  tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2) Or
  (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Chartres%' And
  tblAssetCustom.State = 1 And tblAssetCustom.State = 1 And
  tblComputersystem.Domainrole < 2)
Order By tblAssets.Domain,
  tblAssets.AssetName

[Esben_D]

Correct, the report builder just kinda does what it wants. It should not cause any issues though.

[RCorbeil]

Unless someone at LANSweeper chimes in to tell us otherwise, as best I know you can't preserve code formatting. Because the LANSweeper report designer/editor has to accommodate both typing of code and drag-and-drop query building, it can't make any assumptions and ends up re-parsing the query, reformatting it as it sees fit.

[Slim_D]

Sorry ! i've another question. How to preserve the indentation afeter paste sql code in the editor

example

when i pas this

Where
      tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
  And tsysOS.OSname Not Like '%Win 10%'
  And (   (tblADComputers.OU Like '%tours%')
       Or (tblADComputers.OU Like '%blois%')
       Or (tblADComputers.OU Like '%Orleans%')
       Or (tblADComputers.OU Like '%chatellerault%')
       Or (tblADComputers.OU Like '%poitiers%')
       Or (tblADComputers.OU Like '%nogent%')
       Or (tblADComputers.OU Like '%chateaudun%')
       Or (tblADComputers.OU Like '%le mans%')
       Or (tblADComputers.OU Like '%Chartres%')
      )

it will be that in lansweeper

Where tsysOS.OSname Not Like '%Win 10%' And ((tblADComputers.OU Like '%tours%')
    Or (tblADComputers.OU Like '%blois%') Or (tblADComputers.OU Like
      '%Orleans%') Or (tblADComputers.OU Like '%chatellerault%') Or
    (tblADComputers.OU Like '%poitiers%') Or (tblADComputers.OU Like '%nogent%')
    Or (tblADComputers.OU Like '%chateaudun%') Or
    (tblADComputers.OU Like '%le mans%') Or
    (tblADComputers.OU Like '%Chartres%')) And tblAssetCustom.State = 1 And
  tblComputersystem.Domainrole < 2

thank you in advance

SlimD

[Slim_D]

Merci beaucoup pour aide !

Thanks a lot !

[RCorbeil · Accepted solution]

SQL Server will probably optimize the query behind the scenes anyway, but were it me, I would tidy up the WHERE clause.

Where
     (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%tours%'         And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
  Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%blois%'         And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
  Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Orleans%'       And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
  Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%chatellerault%' And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
  Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%poitiers%'      And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
  Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%nogent%'        And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
  Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%chateaudun%'    And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
  Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%le mans%'       And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)
  Or (tsysOS.OSname Not Like '%Win 10%' And tblADComputers.OU Like '%Chartres%'      And tblAssetCustom.State = 1 And tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2)

Pull the redundant elements out so that the conditions are only evaluated once:

Where
  tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
  And tsysOS.OSname Not Like '%Win 10%'
  And (   (tblADComputers.OU Like '%tours%')
       Or (tblADComputers.OU Like '%blois%')
       Or (tblADComputers.OU Like '%Orleans%')
       Or (tblADComputers.OU Like '%chatellerault%')
       Or (tblADComputers.OU Like '%poitiers%')
       Or (tblADComputers.OU Like '%nogent%')
       Or (tblADComputers.OU Like '%chateaudun%')
       Or (tblADComputers.OU Like '%le mans%')
       Or (tblADComputers.OU Like '%Chartres%')
      )

If you want to get serious about optimization, you would want to change the OSName comparison. Given the values of tsysOS.OSname

OSname
-----------
Not scanned
NT 3.51
NT 4
Win 10
Win 2000
Win 2000 S
Win 2003
Win 2003 R2
Win 2008
Win 2008 R2
Win 2012
Win 2012 R2
Win 2016
Win 2019
Win 7
Win 7 RC
Win 8
Win 8.1
Win Home
Win Vista
Win XP

replacing

  And tsysOS.OSname Not Like '%Win 10%'

with

  And tsysOS.OSname <> 'Win 10'

would be more efficient. If you want to anticipate Microsoft adding to the "Win 10" name, maybe

  And tsysOS.OSname Not Like 'Win 10%'

We're talking milliseconds at most, but if you're wanting to look at this as a general optimization exercise, it can be a good habit to form.

Fast: exact match: OSname = 'Win 10'
Mid:  starts with: OSname LIKE 'Win 10%'
Mid:  ends with  : OSname LIKE '%Win 10'
Slow: contains   : OSname LIKE '%Win 10%'