Best performance Row by row loop (SQL2K)

Question

Best performance Row by row loop (SQL2K)

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Please create the test table I used, with the insert I used, in the database you are testing, and run your copy of the code on that. See if it finds the other rows besides just the one with the char(31) in it. Or have you already done that?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

agh100 (1/21/2009)
Hi GSquared,
I have been sick for some days now, so I haven't been able to try your script until now.. And thanks very much, it works splendidly on SQL 2005 not on 2000 since "coalesce" doesn't exists. But never mind... I will just import a copy of my database to 2005, and run the script on it...
Thanks very very much....
Kind regards,
Anders

Heh... you better check again... COALESCE works just fine in SQL Server 2000. If you don't think so, look it up in the 2k BOL... it's there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

agh100 Ten Centuries Points: 1139 More actions · Answer 3

Hi GSquared,

I have run the code on the Test1 table you sendt.. And i can see that it contains the char values.. But it only returns char31....

For the "fun of it" I tried changing the value of @char31 to :

SELECT @char31 = char(35)

Now the code will only find char(35)'s... For some reason the query does not work with the interval :

' like ''%[' + @Char0 + '-' + @Char31 + ']%'''

Perhaps I should just make it with a lot of OR clauses... like ''%[' + @Char0 + ']%''' or like ''%[' +@char1 + ']%''' ..... and so forth...

Sorry for posting the thread wrong in the first place, I didn't notice it... But now I am using SQL 2005...

Mvh

Anders

agh100 Ten Centuries Points: 1139 More actions · Answer 4

This is what the query returns, if I "print @sql" instead of executing it...

select * from dbo.Test1 where isnull([Col1], '')+isnull([Col2], '')+isnull([Col3], '') like '%-]%';

Kind regards,

Anders

GSquared SSC Guru Points: 260824 More actions · Answer 5

Aha! It's losing the first bracket in the query, per the print results. Try changing that to "[[", see if it gets what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

agh100 Ten Centuries Points: 1139 More actions · Answer 6

It wasn't... 🙁 ....

I am still pretty sure, that for some reason, the interval @char0 - @char31 is not correctly interpreted for some reason... It will still only "look" at the last variable @char31... (or the last one entered)...

' like ''%[' + @Char0 + '-' + @Char1 + ']%'''

shows me only char(1) values

' like ''%[' + @Char0 + '-' + @Char2 + ']%'''

shows me only char(2) values

I don't understand how you can make it work when I cannot...

agh100 Ten Centuries Points: 1139 More actions · Answer 7

Could it have anything to do with the version of SQL server I am using??!

For test purposes I am using SQL Express 2005 9.0.1399...

Kind regards,

Anders

GSquared SSC Guru Points: 260824 More actions · Answer 8

Try this:

create table #T (

ID int identity primary key,

Col1 char(10));

insert into #T (Col1)

select '1' union all

select 'a';

select *

from #T

where Col1 like '%[a-z]%';

See what that gives you.

If it's not working for you, it's more likely an issue with collation than with server version.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

agh100 Ten Centuries Points: 1139 More actions · Answer 9

It works...

I got everything to work now... Strangely enough, it will not return values from 0-31 but it works from 1-31...

So if i change the code to:

declare @Char0 char(1), @Char31 char(1), @SQL nvarchar(max);

select @Char0 = char(1), @Char31 = char(31);

It works... I don't understand, cause I was convinced that I had tested it... But I guess I didnt.. Cause it works 😉

Sorry for taking so much of your time Gsquared, you have been very helpful and patience with me... Thanks...

Kind regards,

Anders

GSquared SSC Guru Points: 260824 More actions · Answer 10

Glad I could help.

I can understand why char(0) wouldn't work, since it's basically the lack of a character.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

CHAR(0) is the NULL character... remember what happens with nulls... same thing here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)