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 2005 Forums
 Transact-SQL (2005)
 Help Optimizing a query

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-06-21 : 15:03:36
Hello, I was wondering if I could get a hand getting this query a little more optimized... I'm hoping you SQL gods could help me avoid the I/O's from having to dump the data into a temp table, remove duplicate phones, and the tally the left over results.

I can’t remove the dupes from the table I'm running the select from, because the table has multiple records per house (i.e. one for the wife, one for the husband)

any help or advice would be greatly appreciated.

Thanks!


SELECT o.state, o.phone,uid into ##work FROM   dbo.mytable o WHERE  1 = 1 
	AND o.phone<>'' 
	AND o.mortgage_amount >= 100 
	and o.home_owner in ( '3','4' ) 
	and (1=2 OR o.state in ( 'AZ' ))
	and name_type='p'
	and o.do_not_call<>'y'
	and o.name_type='p'
go

DELETE T1 FROM ##work AS T1 JOIN (SELECT PHONE,[MAX_MYRECORDID]=MAX(UID) FROM ##work
GROUP BY PHONE) AS T2 ON T2.PHONE=T1.PHONE AND T2.MAX_MYRECORDID<>T1.UID
go

select state,COUNT(state) from ##work group by state order by state

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-06-21 : 15:20:21
select state, count(*)
from (
SELECT state, row_number() over (partition by phone order by uid desc) as recid
FROM dbo.mytable
WHERE phone<>''
AND mortgage_amount >= 100
and home_owner in ( '3','4' )
and (o.state in ( 'AZ' ))
and name_type='p'
and do_not_call<>'y'
and name_type='p'
) as f
where recid = 1
group by state
order by state

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-06-21 : 18:17:09
do you think that'll help much? i'm running quries like this, with 200+ selects (fields) on about 223million rows all day... i thing cutting back on i/o's would be a good start in cleaning up the quries...
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2009-06-21 : 19:50:09
Heh... hell ya! It get's rid of the dupes on the fly.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-06-21 : 19:58:43
LOL that was the plan, i guess after i implement this, I'll just have to take a look at everything, and see what's bogging it down next.

Thanks a lot guys!
Go to Top of Page

timmy
Master Smack Fu Yak Hacker

1242 Posts
Posted - 2009-06-21 : 20:15:14
Another thing to look at is to remove the 'SELECT blah INTO ##temptable' and declare the temp table explicitly before you start

SELECT <stuff> INTO ##temptable can cause locking on tempdb system tables and could cause you problems. If nothing else it's lazy....
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-06-21 : 20:31:14
i'm a nub over here, so do you mean

create the table, then insert into it?
i know with the way PESO helped me, i wont need to drop into tables any more, But, i do still use that every now and then so knowing the best practice is always a good thing.

is that what you mean though? create table, then insert into it, rather then just select into?
Go to Top of Page

timmy
Master Smack Fu Yak Hacker

1242 Posts
Posted - 2009-06-21 : 20:40:53
Yes - that's what I meant.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-06-21 : 21:47:01
well thank you sir for that, i do appreciate it, i'll start weeding it out of my normal code... every little bit helps right?
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2009-06-21 : 22:13:58
quote:
Originally posted by timmy

SELECT <stuff> INTO ##temptable can cause locking on tempdb system tables and could cause you problems. If nothing else it's lazy....


Heh... Absolutely NOT true. That's a myth left over from the very early days of SQL Server. Don't take my word for it, though (that's how myths get started ). Instead, please read the following Microsoft article...

[url]http://support.microsoft.com/kb/153441/EN-US/[/url]

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2009-06-21 : 22:16:32
quote:
Originally posted by albertkohl

i'm a nub over here, so do you mean

create the table, then insert into it?
i know with the way PESO helped me, i wont need to drop into tables any more, But, i do still use that every now and then so knowing the best practice is always a good thing.

is that what you mean though? create table, then insert into it, rather then just select into?


Then read the article at the link I posted above. It's a myth that SELECT/INTO causes problems and it hasn't been true since before SQL Server 6.5 sp1. The use of SELECT/INTO is an outstanding tool to aid in performance driven code especially where Temp tables are involved.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-06-21 : 22:27:07
well that's good to know, thanks jeff, i'll check out the link at the office tomorrow.

Appreciate all the input guys... thanks!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-06-22 : 02:50:52
You can query the new DMV views for more information about locks.
SQL Server enginge has to put locks on all involved tables during the SELECT INTO part to keep consistency.

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2009-06-22 : 07:47:04
quote:
Originally posted by Peso

You can query the new DMV views for more information about locks.
SQL Server enginge has to put locks on all involved tables during the SELECT INTO part to keep consistency.

E 12°55'05.63"
N 56°04'39.26"


Yeah, but those locks don't cause the problem with TempDB that everyone thinks. TempDB is NOT frozen during the SELECT/INTO. I'll try to remember to post the code that proves it tonight. I'm on my way to work.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-06-22 : 07:50:33
I meant the tables referenced in the query, not the TempDB itself.
Sorry to not been clearer about that.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2009-06-22 : 10:48:27
quote:
Originally posted by Peso

I meant the tables referenced in the query, not the TempDB itself.
Sorry to not been clearer about that.


E 12°55'05.63"
N 56°04'39.26"


Oh... ok. Sorry about that. I just want people to absolutely understand that the systems tables will not be locked in such a fashion as to render them useless during the duration of the SELECT/INTO.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-06-22 : 11:56:58
so your saying for example, that if i use the select into method, mytable can have locking on it while it copies the data? that's good to know, because i use it all the time.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2009-06-22 : 12:09:47
The point is that you can control the locking and that it won't turn into "blocking".

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-07-29 : 02:01:55
quote:
Originally posted by Peso

select state, count(*)
from (
SELECT state, row_number() over (partition by phone order by uid desc) as recid
FROM dbo.mytable
WHERE phone<>''
AND mortgage_amount >= 100
and home_owner in ( '3','4' )
and (o.state in ( 'AZ' ))
and name_type='p'
and do_not_call<>'y'
and name_type='p'
) as f
where recid = 1
group by state
order by state

E 12°55'05.63"
N 56°04'39.26"


Okay, so quick question. how can i do this samething, but with two levels, first by phone (as above) and then another one by email.

Any Ideas?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-29 : 02:47:37
You can add another "level" to the PARTITION BY clause.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2009-07-29 : 02:54:12
would you give me an example? for example, i've done it by address, like:
(partiton by address, zip order by uid desc) as recid and that worked,

but i would assume if i did email like:
(partition by phone,email order by uid desc) as recid
would only unique records where the phone and the email are unique,
not where 100% phones are unique, AND 100% emails are unique.

Follow?
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site