May 27, 2011 at 3:40 pm
I have a query that pull sql information from Discovery database.
My question is for the query below in bold, is it a correct syntax?
when it runs in ssms, it didn't show any error.
And if I add same criteteria in where statement, it returns different rows of records.
SELECT DISTINCT
c.sysMachine
,ct.ClientType
,c.Description
,ctd.[defOS] + ' '+ ctd.[defOSType] +' ' +ctd.[OSServicePack] AS [OS]
,ctd.Location
,s.[Mfg] AS [Manufacturer]
,s.[Name] AS [Product]
,s.[Model] [Version/Edition]
,e.Version [Build]
,s.defFrequency AS [Usage Frequency]
,s.aClass AS [Product Type]
,s.[ID]
,r.Model
,r.Filename
,f.uSoftwarefile
,f.SRDFile
,f.SoftwareEmbedded
--,s.ProdID
FROM [Discovery].[dbo].[sysSoftware] s
LEFT JOIN [Discovery].[dbo].Client c ON c.Client = s.ID
LEFT JOIN [Discovery].[dbo].qryClientType ct on c.ClientType = ct.ClientTypeID
LEFT JOIN [Discovery].[dbo].qryClientDetail ctd ON c.Client = ctd.ID
LEFT JOIN V_ReportComputerDetailsSoftware r ON r.Client = c.Client
AND r.ManufacturerName = s.Mfg AND r.Product = s.Name AND r.VersionNumber = s.Model
AND LOWER(r.Filename) IN('sqlservr.exe', 'reportingservicesservice.exe', 'msdtssrvr.exe', 'msmdsrv.exe') LEFT JOIN SoftwareAud a ON a.Client = c.Client AND a.Software = r.Software
LEFT JOIN SoftwareFile sf ON a.SoftwareFile = sf.SoftwareFile
LEFT JOIN SRDFileUnique f ON f.uSoftwarefile = sf.SoftwareFile AND a.SRDFile = f.SRDFile
LEFT JOIN SoftwareEmbedded e ON e.SoftwareEmbedded = f.SoftwareEmbedded
WHERE
LOWER(Name)= 'sql server' --Like 'SQL%'
AND LOWER(Mfg) like 'microsoft%'
AND LOWER(ct.ClientType) LIKE '%server%'
AND (LOWER(s.Model) LIKE '%integration services'
OR LOWER(s.Model) LIKE '%integration services (%'
OR LOWER(s.Model) LIKE '%reporting services'
OR LOWER(s.Model) LIKE '%reporting services (%'
OR LOWER(s.Model) LIKE '% edition%'
OR LOWER(s.Model) LIKE '%analysis services'
OR LOWER(s.Model) LIKE '%analysis services (%')
AND LOWER(s.Model) NOT LIKE ('%express%')
ORDER BY sysMachine
May 28, 2011 at 2:14 am
If I understand the question correctly, you're asking the difference between this:
LEFT JOIN V_ReportComputerDetailsSoftware r ON r.Client = c.Client
AND r.ManufacturerName = s.Mfg AND r.Product = s.Name AND r.VersionNumber = s.Model
AND LOWER(r.Filename) IN
('sqlservr.exe', 'reportingservicesservice.exe', 'msdtssrvr.exe', 'msmdsrv.exe')
...and this:
LEFT JOIN V_ReportComputerDetailsSoftware r ON r.Client = c.Client
AND r.ManufacturerName = s.Mfg AND r.Product = s.Name AND r.VersionNumber = s.Model
[...]
WHERE LOWER(r.Filename) IN
('sqlservr.exe', 'reportingservicesservice.exe', 'msdtssrvr.exe', 'msmdsrv.exe')
In the first code block, where all of the criteria for V_ReportComputerDetailsSoftware is specified in the ON clause of the LEFT JOIN, the request is to only match those rows to the main query set where theFileName value is in the IN list. If there are no matching rows, the the columns selected from the view will be NULL in the query output.
In the second code block, the conditions have been moved to the WHERE clause, which means filter out rows *after* all of the joining is complete. In other words, any of the rows in the resultset with an r.FileName value that is NULL or not in the specified list will be removed from the output. It produces the same result as INNER JOINing the table (view?) instead of LEFT JOINing it.
To get the two resultsets to match, change the WHERE clause to:
LEFT JOIN V_ReportComputerDetailsSoftware r ON r.Client = c.Client
AND r.ManufacturerName = s.Mfg AND r.Product = s.Name AND r.VersionNumber = s.Model
[...]
-- modify the WHERE clause to test for either NULL or a specific value for r.Filename
WHERE IsNull(LOWER(r.Filename), 'sqlservr.exe') IN
('sqlservr.exe', 'reportingservicesservice.exe', 'msdtssrvr.exe', 'msmdsrv.exe')
...although you will realize better performance by leaving the condition in the ON clause of the LEFT JOIN.
Side note: are you running in case-sensative mode? If not, ditch all the LOWER() functions, because you're preventing the use of indexes.
-Eddie
Eddie Wuerch
MCM: SQL
May 28, 2011 at 4:15 am
Piling on for just a moment, but you need to know this, all those functions running in your columns is going to prevent any indexes on those tables from being used properly. This pattern of querying the database (and I'll bet all you queries look like this) is extremely problematic. You need to remove those functions or your system will suffer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 28, 2011 at 9:51 am
Thank you both.
This is a database and query someone else setup before - that uses case sensitive collations. (I don't know why, it's a database for network system managment).
We seldom query backend database, seldom to run the query,so we can ignore the performance issue for now.
Thanks Eddie for the explanation about the difference of the two locations of criteria. That's exactly I'm looking for.
I never saw the first case, I only see before when using left join, using on to match keys, but never know you can use a criteria like a in statement follow that, is there somewhere for example in MSDN library I can see more examples or definitions of that?
Thanks
May 28, 2011 at 10:24 am
Try not using the NOT Operator as in AND LOWER(s.Model) NOT LIKE ('%express%')in the Discovery.dbo.sysSoftware Table or any other table.
That will force a table scan.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply