SQL Express Speed Issue

[tbg130]

Hi,

Just wondering if anyone knows if SQL Express processes at the same speed as SQL 2000 or 2005?

My queries seem to take 17 seconds average to find one match out of 2200 records. In the big picture, that seems a bit slow. It is comparing regular expressions to exact values...

Could it be faster to iterate through ALL potential values and match against that (instead of using the regular expression)?

Thanks in advance...

 

[thendrickson]

I suggest you post the query.

Definately sounds like a "bit slow" to me

 

[tbg130]

Fair enough; here is what I have...

SELECT top 1 URL_Expression, IP_Expression, Name, Category, SubCategory1
FROM TABLENAME
WHERE (dbo.find_regular_expression('64.26.154.225', URL_Expression, 0) = 1 or
dbo.find_regular_expression('64.26.154.225', IP_Expression, 0) = 1)

As a sample where the IP number '64.26.154.225' would change.

the dbo.find_regular_expression uses a UDF that I found on the web (which I can't pretend to know anything about as I'm not a real programmer; just trying to get by!)... code below:

CREATE FUNCTION dbo.find_regular_expression
(
@source varchar(5000),
@regexp varchar(1000),
@ignorecase bit = 0
)
RETURNS bit
AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @objMatches integer
DECLARE @objMatch integer
DECLARE @count integer
DECLARE @results bit

EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END




Thanks in advance for any advice or help!

Tyler

 

[thendrickson]

I may be a bit "goofy" today but it looks like you are simply attempting to find records where either URL_expression or IP_expression matches the url you send in.

Is that the case?

if so just do a simple select.

 

[SQLDenis]

while UDF's are fine for select they are terrible in the where clause
A table scan is guaranteed

Also sp_OACreate is very expensive, especially when you call it 2200 times
try rewriting the function or store the IP so that you can do a regular where

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Have you considered using SQLCLR instead
You will still get a table scan even with that but it should be faster than creating and destroying DLL's

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[tbg130]

Thanks SQLDenis,

Sounds like some great ideas; if only I understood them!

I'm not much a programmer so don't know anything about SQLCLR's. For that matter, I also wasn't aware that the DLL's would be created and deleted on the fly...

All in all, any other suggestions, links to pages, or code that could help me out would be much appreciated to get this job done as I'm under a time crunch.

As for thendrickson's comment, I don't think that a 'simple' select can be done any 'simpler' than I have for doing regular expression comparisons can it?

Thanks.

 

[thendrickson]

First, I do little with the web so I may be misunderstanding your problem.

However; Do you actually need to compare regular expressions?

You seem to need to compare IP addresses

How are these URLs already stored in the database as an ip (which is a suggestion sqlDenis made) or as

(for example)

http://www.tek-tips.com/viewthread.cfm?qid=1260958&page=1

if they are stored as an ip address already then you need only a simple select where a = b or c = d You may need to clean up the ips using REPLACE or INSTRING or combination of both depending on existing data so you are comparing apples to apples (assuming the ips are stored as one of the character datatypes which is the most likely scenario)

Not as elegant perhaps but simple and quick.

If you need to resolve the URL to an ip address, things become much more complex of course. I would have to research such a comparison.

 

[tbg130]

thendrickson,

I have to compare an IP address (not in DBase) to all regular expressions that are in DBase. I don't believe that any kind of 'simple' select statement will do therefore need something a little more complexe.

Suggestions?

Thanks.

 

[tbg130]

I'm using this above function to compare an IP to a table of regular expressions; there are 2200 records in the table and each comparison seems to take between 12 and 17 seconds; a bit long in my view.

Any suggestions on what I can do to speed this up?

I don't know CLR as someone suggested, so any tips or code samples would be appreciated.

I'm confident it's not hardware related as it is a decent performing dual xeon machine with ample RAM.

Thanks.

From a guy that's under a tight deadline and doesn't know what he's doing!