Slow Query - Regular Expression

[tbg130]

Hi,

I'm trying to do a query on a database that will find me the owner of an IP address I've stored. I have IP addresses and hostnames; and the corresponding Regular Expressions. My query is taking about 7 seconds per lookup and I have hundreds of lookups; any tips to speed this up would be great.

First off, it just tries to identify if it is an IP address or a hostname with the regular expression... by using IF statements, will choose the corresponding SELECT statement to use...

if (dbo.find_regular_expression('hostnameoripaddress, '^([0-9]|[1-9][0-9]|1[0-9][0-9]|2[-4][0-9]|25[0-5])\.([0-9]|[1-9][0-9]|1[0-9][0-9]|2[-4][0-9]|25[0-5])\.([0-9]|[1-9][0-9]|1[0-9][0-9]|2[-4][0-9]|25[0-5])\.([0-9]|[1-9][0-9]|1[0-9][0-9]|2[-4][0-9]|25[0-5])', 0) = 1)
SELECT top 1 *
FROM sampledata
where (ip_low != '0.0.0.0' and dbo.ipstringtoint('hostnameoripaddress') between dbo.ipstringtoint(ip_low) and dbo.ipstringtoint(ip_high))

else
select top 1 *
from sampledata
where (ip_low != '0.0.0.0' and dbo.ipstringtoint('hostnameoripaddress') between dbo.ipstringtoint(ip_low) and dbo.ipstringtoint(ip_high))

dbo.ipstringtoint converts the IP string such as 192.168.1.1 to its corresponding integer value such as 425897585.

dbo.find_regular_expression is a UDF I found on the Web; I suspect this may be slowing things up so suggestions on how to compare against regular expression or a better function would be great.

The database has no null values; that is why there is '0.0.0.0' as default value in ip_low or ip_high if only hostname and hostname regular expression are known.

Thanks in advance.

 

[gmmastros]

The regular expression you are using probably works really well for validating an IP Address. Assuming all your IP Addresses are already valid, you don't have to work so hard to determine if you have an IP address or a host name.

To get the records with an actual IP Address, you should be able to use:

Select *
From SampleData
Where HostNameOrIpAddress Like '%[0-9.]%'

Also... it looks like the queries on both sides of the 'else' are the same. Is that intentional?

If you can re-write this query to NOT use functions, then your performance will increase.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tbg130]

Thanks; the code I copied was not 'as actual'. You are right; they are not suppose to be the same (and they aren't) on both sides of the If statement. The second one should read...

ELSE
select top 1 * from sampledata where dbo.find_regular_expression('%VALUE%', url_expression,0) = 1

The problem is that the code is searching for results that match information from a web log file; in other words, in the log file, some are resolved to hostnames and some are left as IP adresses. This is why I require the 'if' statement to identify if it is an ip address or a hostname before doing a search against the database.

IF it is an IP address, I look for a record where the IP address fits between the IP_Low and IP_High in my databse.

IF it is a hostname, I look for regular expressions in my databse that match the hostname.

More ideas or comments?

 

[bborissov]

Like George said if you are sure that you IP addresses are already valid you could use something like:

IF (ISNUMERIC(LEFT(hostnameoripaddress, CHARINDEX('.',hostnameoripaddress)-1) = 1)
    Select *
    From   SampleData
    Where  HostNameOrIpAddress Like '%[0-9.]%'
ELSE
    select top 1 *
           from sampledata
            where url_expression LIKE '%VALUE%'

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.