February 28, 2014 at 7:45 pm
I am working on a project for a company that offers online continuing education classes. The course content is stored in a database table, with one record per "page" of content. Each course can have up to 20-30 pages. The content field is of data type "text".
They want me to write some code that will scour all the courses to find any dead links to external sites.
I am planning on using non-SQL code to actually parse out and then check the URLs, etc. But I need to first generate that list of URLs somehow, and that's what I'd love your feedback on.
Simply running a query like this...
SELECT [content]
FROM
WHERE [content] LIKE '%http%'
...seems like it would just take super long and gobble up resources on the server.
Is there any other alternative? Should I try using some sort of "WAITFOR DELAY" to process only X records at a time?
Thanks in advance for any general feedback/ideas. 🙂
March 5, 2014 at 8:29 am
Have you thought about using a full text index? It might be helpful in identifying records with links. You'd need to be careful with which stemming characters you use to avoid breaking the links up though.
http://technet.microsoft.com/en-us/library/ms345119(v=sql.90).aspx
For parsing the links from the data, I'd be tempted to handle this outside of sql server. You might find that a parsing function built in C# or VB.net and called via CLR performs better.
http://technet.microsoft.com/en-us/library/ms345136(v=sql.90).aspx
http://www.pawlowski.cz/2010/09/string-splitting-t-sql-vs-clr/
March 5, 2014 at 8:53 am
Thanks much for the reply. Unfortunately it's a shared server, so we don't have access to full-text indexing or other special settings.
And yes, I am going to use some non-SQL language to do the actual parsing. Probably PHP or something similar. 🙂 Just need to identify which records contain "http:" first.
March 6, 2014 at 2:57 am
As far as I can see then, you're stuck with one of four options:
like
charindex
patindex
replace
The following shows each and some test results. From the looks of it, there isn't much difference between the first three.
-------------------------------
-- create temporary tally table
-------------------------------
; with e1(n) as (
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all
select 1
)
, e2(n) as (select 1 from e1 a cross join e1 b)
, e4(n) as (select 1 from e2 a cross join e2 b)
select n = isnull(cast((row_number() over (order by (select null))-1) as int),0)
into #Tally
from e4
-- add clustered index to the temporary tally table
alter table #Tally add constraint pk_qt primary key clustered (n)
-- (10000 row(s) affected)
------------------------------------------
-- create temporary table with "documents"
------------------------------------------
create table #Documents (DocId int, DocText varchar(4000))
-- create 10000 "documents"
insert #Documents select n, 'Have you thought about using a full text index? It might be helpful in identifying records with links.
You''d need to be careful with which stemming characters you use to avoid breaking the links up though.
http://technet.microsoft.com/en-us/library/ms345119(v=sql.90).aspx For parsing the links from the data,
I''d be tempted to handle this outside of sql server.
You might find that a parsing function built in C# or VB.net and called via CLR performs better.
http://technet.microsoft.com/en-us/library/ms345136(v=sql.90).aspx
http://www.pawlowski.cz/2010/09/string-splitting-t-sql-vs-clr/
Have you thought about using a full text index? It might be helpful in identifying records with links.
You''d need to be careful with which stemming characters you use to avoid breaking the links up though.
http://technet.microsoft.com/en-us/library/ms345119(v=sql.90).aspx For parsing the links from the data,
I''d be tempted to handle this outside of sql server.
You might find that a parsing function built in C# or VB.net and called via CLR performs better.
http://technet.microsoft.com/en-us/library/ms345136(v=sql.90).aspx
http://www.pawlowski.cz/2010/09/string-splitting-t-sql-vs-clr/
Have you thought about using a full text index? It might be helpful in identifying records with links.
You''d need to be careful with which stemming characters you use to avoid breaking the links up though.
http://technet.microsoft.com/en-us/library/ms345119(v=sql.90).aspx For parsing the links from the data,
I''d be tempted to handle this outside of sql server.
You might find that a parsing function built in C# or VB.net and called via CLR performs better.
http://technet.microsoft.com/en-us/library/ms345136(v=sql.90).aspx
http://www.pawlowski.cz/2010/09/string-splitting-t-sql-vs-clr/
I''d be tempted to handle this outside of sql server.
You might find that a parsing function built in C# or VB.net and called via CLR performs better.
I''d be tempted to handle this outside of sql server.
You might find that a parsing function built in C# or VB.net and called via CLR performs better.
I''d be tempted to handle this outside of sql server.
You might find that a parsing function built in C# or VB.net and called via CLR performs better.
I''d be tempted to handle this outside of sql server.
You might find that a parsing function built in C# or VB.net and called via CLR performs better.'
from #Tally
-- (10000 row(s) affected)
set statistics io on
-- like
select DocId from #Documents where DocText like '%http%'
-- charindex
select DocId from #Documents where charindex('http',DocText) > 0
-- patindex
select DocId from #Documents where patindex('%http%',DocText) > 0
-- substitution
select DocId from #Documents where len(DocText) <> len(replace(DocText,'http',''))
/*
(10000 row(s) affected)
Table '#Documents'. Scan count 1, logical reads 5000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(10000 row(s) affected)
Table '#Documents'. Scan count 1, logical reads 5000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(10000 row(s) affected)
Table '#Documents'. Scan count 1, logical reads 5000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(10000 row(s) affected)
Table '#Documents'. Scan count 1, logical reads 5000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
set statistics io off
------------
-- time test
------------
create table #TestResults (i int, l int, c int, p int, s int)
declare @like int
, @char int
, @pat int
, @sub int
, @i int
, @start datetime
, @end datetime
set @i = 1
while @i <=50
begin
-- like
set @start = getdate()
select DocId from #Documents where DocText like '%http%'
set @end = getdate()
set @like = datediff(ms,@start,@end)
-- charindex
set @start = getdate()
select DocId from #Documents where charindex('http',DocText) > 0
set @end = getdate()
set @char = datediff(ms,@start,@end)
-- patindex
set @start = getdate()
select DocId from #Documents where patindex('%http%',DocText) > 0
set @end = getdate()
set @pat = datediff(ms,@start,@end)
-- substitution
set @start = getdate()
select DocId from #Documents where len(DocText) <> len(replace(DocText,'http',''))
set @end = getdate()
set @sub = datediff(ms,@start,@end)
insert #TestResults select @i, @like, @char, @pat, @sub
set @i = @i+1
end
-- show results
select l=avg(l),c=avg(c),p=avg(p),s=avg(s)
from #TestResults
/*
lcps
5445575485641
*/
Depending on the proportion of documents that have links, I might consider just farming out everything via CLR, regardless of whether a doc contained links or not. You could then just split the input to an array using http: as the delimiter and use the upperbound of the array to determine whether you have multiple array elements (i.e. doc contains links). Of course this relies on you being able to create CLR functions on your server. Although if you can't, you can just do it the other way I guess.
March 6, 2014 at 9:01 am
Thanks, great example of how to compare efficiency of various options! 🙂 Glad to see that the most straightforward ("LIKE") is also basically the fastest.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply