Which is more efficient?

Question

Post reply

Which is more efficient?

Fishbarnriots

SSCrazy

Points: 2568
More actions
January 7, 2009 at 4:12 am

#73425

Hi,
I'm trying to optimise a stored proc, just to shave milli seconds where possible.
Currently it uses example 1, would example 2 be more efficient, or is there a better way of achieving the same result
--Where Column1 is a varchar(14)
Example 1
Declare @xxx VARCHAR(14)
set @xxx = '12345'
SET @xxx = LEFT(@xxx + '00000000000000', 14)
SELECT Column2
FROM Table1
WHERE LEFT(column1 + '00000000000000', 14) = @xxx)
Example 2
Declare @xxx VARCHAR(14)
set @xxx = '12345'
SET @xxx = @xxx + REPLICATE('0', 14 - DATALENGTH(@xxx))
SELECT Column2
FROM Table1
WHERE Column1 + REPLICATE('0', 14 - DATALENGTH(Column1)) = @xxx)
Any suggestions gratefully received.

Viewing 8 posts - 1 through 7 (of 7 total)

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

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 1

Neither one of them!

If you have '123000' in Column1 which is translated to '12300000000000'

and you search for '123' you will get a match, because '123' is also translated into '12300000000000' !

Use option 3

Declare @xxx VARCHAR(14)

set @xxx = '12345'

SELECT Column2

FROM Table1

WHERE column1 = @xxx

or, if you are into pattern searches, use option 4

Declare @xxx VARCHAR(14)

set @xxx = '12345'

SELECT Column2

FROM Table1

WHERE column1 LIKE @xxx + '%'

N 56°04'39.16"
E 12°55'05.25"

Fishbarnriots SSCrazy Points: 2568 More actions · Answer 2

Good suggestions, but column1 may contain

1005

10054603700000

10055

10055000100000

10055000200000

10055000300000

etc

So a search for 1005 could return multiple records rather than just the one, all rather frustrating, it would have been nice if the data was added in the correct format in the first instance.

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 3

See option 4 if you want to do pattern searches.

In your case, return all records starting with '1005..'

N 56°04'39.16"
E 12°55'05.25"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Fishbarnriots (1/7/2009)
Good suggestions, but column1 may contain
1005
10054603700000
10055
10055000100000
10055000200000
10055000300000
etc
So a search for 1005 could return multiple records rather than just the one, all rather frustrating, it would have been nice if the data was added in the correct format in the first instance.

Can I suggest that you set this up as test data per the link in my sig? It would give us all a level playing field. It's worth doing too because there are several approaches to this - some will perform really well and some will still be running when you come back in to work next monday - like the original...

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

The best possible ways that i could handle this is...

1. Convert the value to 14 digit before inserting data to the table. By doing this, I just avoided the conversion and query the table using simple where.

2. Create a computed column and then compare this column instead of the original column.

3. Option 1 of your post

--Ramesh

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Fishbarnriots (1/7/2009)
Good suggestions, but column1 may contain
1005
10054603700000
10055
10055000100000
10055000200000
10055000300000
etc
So a search for 1005 could return multiple records rather than just the one, all rather frustrating, it would have been nice if the data was added in the correct format in the first instance.

It doesn't matter at all if the query returns 1 row or 100 rows, so long as it's fast. You can select from the result using the right-padding thing.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Fishbarnriots SSCrazy Points: 2568 More actions · Answer 7

Thanks for the replies, looking at the underlying data there is a fundamental flaw in the logic being applied. I think I will have to pass this one back to the developer.