how to write this in a better way

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to write this in a better way

Author

Topic

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-08-05 : 12:39:58

Select col1,col2,col3 from table1
where col3 like '%test%'
where col1] >= CONVERT(CHAR(10), getDATE() -1, 101)
AND (col1) < CONVERT(CHAR(10), getDATE() , 101)

this is to get data for yesterday

and col3 is nvarchar(4000)
and table1 is like almost 2gb

it takes a while is there a better way of doing this

Thanks,

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 12:59:40

quote:
Originally posted by schinni

where col3 like '%test%'

What's that for?

It'll incur a table scan everytime....

Brett

8-)

SELECT POST=NewId()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-05 : 13:01:00

This should help you out:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27941[/url]

Check out Arnold's and Jeff's replies.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 13:09:59

Try this...although I'm still not to keen on it...

At least you'll only scan 1 days worth of data..


SELECT	  col1
	, col2
	, col3 
  FROM (
	SELECT	  col1
		, col2
		, col3 
	  FROM 	  table1
	 WHERE col1 >= CONVERT(CHAR(10), getDATE() -1, 101)
	   AND col1 < CONVERT(CHAR(10), getDATE() , 101)
) AS XXX
WHERE col3 LIKE '%test%'

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 13:11:51

quote:
Originally posted by tduggan

This should help you out:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27941[/url]

Check out Arnold's and Jeff's replies.

Tara

I was looking for that......The Frib-Miester!

Brett

8-)

SELECT POST=NewId()

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-08-05 : 13:12:13

my sp is sp_Search @str

Select col1,col2,col3 from table1
where col3 like '@str'
where col1] >= CONVERT(CHAR(10), getDATE() -1, 101)
AND (col1) < CONVERT(CHAR(10), getDATE() , 101)

i am seeing if i can replace like with some other thing so that
it makes faster

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 13:27:30

It's not the like, it's the leading wildcard...

If you need to do that, then

What I am suggesting is to do it in 2 steps first get the data for that day..then scan that...

Otherwise you'll be scanning every day (every row) in the table...

And to plagerize Jeff's code (usually a good thing to do)the date predicate would look like below...but your bigger problem is the LIKE '%something%'.

If you can't los the preceding %, try what's below and let me know.


DECLARE @date datetime

SELECT @date = DATEADD(d,-1,GetDate())

SELECT	  col1
	, col2
	, col3 
  FROM (
	SELECT	  col1
		, col2
		, col3 
	  FROM 	  table1
	 WHERE col1 >= @date and col1 < dateadd(dd,1,@date)
) AS XXX
WHERE col3 LIKE '%test%'

Brett

8-)

SELECT POST=NewId()

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-05 : 13:47:24

I'm not clear on whether col1 is VARCHAR or DATETIME in this problem. I've seen CONVERT used to retrieve the date-only part of GETDATE and then it's implicitly converted to DATETIME in the conditional part of the query.

If col1 is DATETIME:

SELECT col1, col2, col3

FROM Table1

WHERE DATEDIFF(dd, col1, GETDATE()) = 1

isn't this a better approach??

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 14:17:09

Sam,

Check out the thread Tara posted....

When ever you use a function on a column in a predicate, it won't be able to use an index and cause a scan.

That's called a Stage 2 predicate or non sargable, and should be avoided...

esp here, where's s/he's got 2gb table...

the like '%something%' will also cause a scan.

That's why I choose the least of 2 evils...

I don't know how many rows equate to a day...but it's still better than scanning everything.

Brett

8-)

SELECT POST=NewId()

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-05 : 15:29:33

I had believed intrinsic functions would be smarter than forcing a SCAN, so I tried it.

Now I'll be rewriting some procs..

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources