→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Slim_D
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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%'

View solution in original post

5 REPLIES 5
Esben_D
Lansweeper Employee
Lansweeper Employee
Correct, the report builder just kinda does what it wants. It should not cause any issues though.
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
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
Engaged Sweeper II
Merci beaucoup pour aide !

Thanks a lot !
RCorbeil
Honored Sweeper II
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%'