Insert Into #temp table

Question

Insert Into #temp table

twahl0630

Valued Member

Points: 61
More actions
January 3, 2013 at 1:32 pm

#268170

Hello all
need to find a faster way to do this select into I'm using or ststatementso filter on multiple columns
any ideas
select m.RecordId,m.NumberForSearch into #dump
from table1 m join table2tablem.RecordId = a.RecordId
where RecordId = 3212
and a.AlphaNumeric2 <> '2012' or a.AlphaNumeric5 <> '2012' or a.AlphaNumeric7 <> '2012' or a.AlphaNumeric9 <> '2012'
Thanks
Fyi modify some thing for privacy issues

Viewing 13 posts - 1 through 12 (of 12 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

twahl0630 (1/3/2013)
Hello all
need to find a faster way to do this select into I'm using or ststatementso filter on multiple columns
any ideas
select m.RecordId,m.NumberForSearch into #dump
from table1 m join table2tablem.RecordId = a.RecordId
where RecordId = 3212
and a.AlphaNumeric2 <> '2012' or a.AlphaNumeric5 <> '2012' or a.AlphaNumeric7 <> '2012' or a.AlphaNumeric9 <> '2012'
Thanks
Fyi modify some thing for privacy issues

Your where clause is the issue. What you have is a whole series of nonSARGable predicates.

What about something like this?

where RecordId = 3212

and

(

a.AlphaNumeric2 < '2012'

or a.AlphaNumeric2 > '2012'

or a.AlphaNumeric5 < '2012'

or a.AlphaNumeric5 > '2012'

or a.AlphaNumeric7 < '2012'

or a.AlphaNumeric7 > '2012'

or a.AlphaNumeric9 < '2012'

or a.AlphaNumeric9 > '2012'

)

If that doesn't help much you will need quite a bit more information. Take a look at this article for what to post for performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 2

twahl0630 (1/3/2013)
Hello all
need to find a faster way to do this select into I'm using or ststatementso filter on multiple columns
any ideas
select m.RecordId,m.NumberForSearch into #dump
from table1 m join table2tablem.RecordId = a.RecordId
where RecordId = 3212
and a.AlphaNumeric2 <> '2012' or a.AlphaNumeric5 <> '2012' or a.AlphaNumeric7 <> '2012' or a.AlphaNumeric9 <> '2012'
Thanks
Fyi modify some thing for privacy issues

Low hanging fruit would be to get rid of those OR's and UNION ALL a separate SELECT for each one. Also if there is any chance of converting the inequality to an equality, do that. Also check your indexing.

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

Lowell SSC Guru Points: 323506 More actions · Answer 3

Roland Alexander STL (1/3/2013)

twahl0630 (1/3/2013)
Low hanging fruit would be to get rid of those OR's and UNION ALL a separate SELECT for each one. Also if there is any chance of converting the inequality to an equality, do that. Also check your indexing.

exactly what i was thinking;

something like this would be the model:

get some indexes in place, and then the UNION Roland was suggesting:

CREATE INDEX IX_table1_AlphaNumeric2 ON table1(AlphaNumeric2)

CREATE INDEX IX_table1_AlphaNumeric5 ON table1(AlphaNumeric5)

CREATE INDEX IX_table1_AlphaNumeric7 ON table1(AlphaNumeric7)

CREATE INDEX IX_table1_AlphaNumeric9 ON table1(AlphaNumeric9)

select m.RecordId,m.NumberForSearch into #dump

from table1 m join table2 a on m.RecordId = a.RecordId

where RecordId = 3212 AND a.AlphaNumeric2 <> '2012'

UNION

select m.RecordId,m.NumberForSearch

from table1 m join table2 a on m.RecordId = a.RecordId

where RecordId = 3212 AND a.AlphaNumeric5 <> '2012'

UNION

select m.RecordId,m.NumberForSearch

from table1 m join table2 a on m.RecordId = a.RecordId

where RecordId = 3212 AND a.AlphaNumeric7 <> '2012'

UNION

select m.RecordId,m.NumberForSearch

from table1 m join table2 a on m.RecordId = a.RecordId

where RecordId = 3212 AND a.AlphaNumeric9 <> '2012'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 4

Is this simpler?

;with cte as

(

select m.RecordId,m.NumberForSearch,

a.AlphaNumeric2,a.AlphaNumeric5,a.AlphaNumeric7,a.AlphaNumeric9

from table1 m join table2 a on m.RecordId = a.RecordId

where RecordId = 3212

)

select RecordId,NumberForSearch into #dump

from cte where AlphaNumeric2 <> '2012'

UNION

select RecordId,NumberForSearch

from cte where AlphaNumeric5 <> '2012'

UNION

select RecordId,NumberForSearch

from cte where AlphaNumeric7 <> '2012'

UNION

select RecordId,NumberForSearch

from cte where AlphaNumeric9 <> '2012'

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 5

What about this:

where RecordId = 3212

and 1 in

(select case when AlphaNumeric2 = '2012' then 0 else 1 end union

select case when AlphaNumeric5 = '2012' then 0 else 1 end union

select case when AlphaNumeric7 = '2012' then 0 else 1 end union

select case when AlphaNumeric9 = '2012' then 0 else 1 end)

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

Maybe combine them into a single select?

where RecordId = 3212

and

(select case when AlphaNumeric2 = '2012' then 0 else 1 end +

case when AlphaNumeric5 = '2012' then 0 else 1 end +

case when AlphaNumeric7 = '2012' then 0 else 1 end +

case when AlphaNumeric9 = '2012' then 0 else 1 end) >= 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 7

Only in some case also:

;with cte as

(

select m.RecordId,m.NumberForSearch,

a.AlphaNumeric2,a.AlphaNumeric5,a.AlphaNumeric7,a.AlphaNumeric9

from table1 m join table2 a on m.RecordId = a.RecordId

where RecordId = 3212

)

select RecordId,NumberForSearch into #dump from cte

except

select RecordId,NumberForSearch from cte

where AlphaNumeric2 + AlphaNumeric5 + AlphaNumeric7 + AlphaNumeric9 = '2012201220122012'

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 8

where RecordId = 3212

and not(replace(AlphaNumeric2 + AlphaNumeric5 + AlphaNumeric7 + AlphaNumeric9, '2012', '') = '')

Sean Lange SSC Guru Points: 286573 More actions · Answer 9

zombieisdead2020 (1/3/2013)
where RecordId = 3212
and not(replace(AlphaNumeric2 + AlphaNumeric5 + AlphaNumeric7 + AlphaNumeric9, '2012', '') = '')

Get rid of the NOT to keep is ARGable.

where RecordId = 3212

and (replace(AlphaNumeric2 + AlphaNumeric5 + AlphaNumeric7 + AlphaNumeric9, '2012', '') > '')

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 10

Thank you Sean Lange.

😀

select m.RecordId,m.NumberForSearch into #dump

from table1 m join table2 a on m.RecordId = a.RecordId

cross apply (

select 1

from (

values(a.AlphaNumeric2),

(a.AlphaNumeric5),

(a.AlphaNumeric7),

(a.AlphaNumeric9)

)D(value)

where value = '2012'

group by value

having count(value) < 4

)D(v)

where RecordId = 3212

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 11

where RecordId = 3212

and

(

'2012' > ANY

(select AlphaNumeric2 union all

select AlphaNumeric5 union all

select AlphaNumeric7 union all

select AlphaNumeric9)

or '2012' < ANY

(select AlphaNumeric2 union all

select AlphaNumeric5 union all

select AlphaNumeric7 union all

select AlphaNumeric9)

)

twahl0630 Valued Member Points: 61 More actions · Answer 12

Thank all the union work the best. this saved me from doing a loop on those columns. my first idea was use an array setup to check. but i think a snail would move data faster then the loop.