1

I have two tables:

dbo.allowedURLs (a unique list of all permitted domains)

*** DomainName ***
google.com
facebook.com
etc...

dbo.loggedURLs (a list of URLs processed by a proxy server)

*** Username ***      *** URL ****
fred.smith            www.google.com/something-else-here
joe.black             www.othersite.net/search/query

I need to be able to return a list of all the domain names in the 'allowedURLs' table and a count of how many different users accessed the service. The 'url' field in the 'loggedURLs' table contains the domain name within the full URL string.

Output

*** DomainName ***      *** Count of Username ****
google.com              1

While the query below gives me the results I'm after, I've been having significant performance issues running this and I was just wondering if anyone has advice on a better way to achieve this same outcome?

SELECT        
dbo.allowedURLs.DomainName, 
COUNT(DISTINCT dbo.loggedURLs.username)

FROM dbo.loggedURLs 

INNER JOIN dbo.allowedURLs ON 
dbo.loggedURLs.URL LIKE '%' + dbo.allowedURLs.DomainName + '%'

GROUP BY dbo.allowedURLs.DomainName

Thanks for any advice!

1

Your query will always result in a scan of dbo.loggedURLs because of the leading wildcard in the LIKE statement. There are a couple of possible solutions I can think of.

  1. Add a column to LoggedURLs for a reference to the allowedURL and at the time of insert into LoggedURLs, determine which allowedURL applies, and supply that column (Then Index that column)
  2. If you can come up with an algorithm that will allow you to parse out the allowedURL in a consistent manner, you could create a computed column with the stripped down url, then index that (Example below)

    declare @str varchar(100)
    set @str = 'www.othersite.net/search/query'
    select substring(@str, charindex('www', @str, 1) + 4,(charindex('/', @str, 1)-5)) 
    

This example may not work for all potential situations, but works for the examples given.

0

Here is solution if you are not able to change table schema.

You can first extract raw url by using CROSS APPLY. It makes Hash Match instead of Nester Loop in execution plan. I think this solution is better scaled and helps us avoid Table Spool when Table become bigger.

SELECT DomainName,COUNT(DISTINCT UserName)
FROM dbo.loggedURLs l
CROSS APPLY ( SELECT    [start]=CHARINDEX('www', l.URL)+4,
                        [end]=IIF(  CHARINDEX('/', l.URL,CHARINDEX('www', l.URL)+4)<>0,
                                    CHARINDEX('/', l.URL,CHARINDEX('www', l.URL)+4)-(CHARINDEX('www', l.URL)+4),
                                    LEN(l.URL)
                                    )
            ) X
CROSS APPLY ( SELECT raw_url=SUBSTRING( l.URL,
                                        [start],
                                        [end]
                                        )
            ) Y
JOIN dbo.allowedURLs a ON a.DomainName = Y.raw_url
GROUP BY a.DomainName

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.