Can someone please explain this!!!

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

SQL Server Development (2000)

Can someone please explain this!!!

Author

Topic

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-30 : 11:17:25

The important point is to notice the where clause conditions for touch_user and status_c on all of the select statements. The temp table only contains rows (2448 to be exact) for JON and NP in both situations.

The following returns 1860 rows in 3 seconds:

select * into #cpsptemp from ar.cpspriority where touch_user = 'JON' and status_c = 'NP'

select cpsp.uniqueid_c
from #cpsptemp as cpsp
where cpsp.touch_user = 'JON' and cpsp.status_c = 'NP' and
(not exists ( big multi table select statement here)

No problem with that, now the following is a real puzzler!!!
The following causes tempdb to grow enough to fill a 10 gig harddrive and crash:

select * into #cpsptemp from ar.cpspriority where touch_user = 'JON' and status_c = 'NP'

select cpsp.uniqueid_c
from #cpsptemp as cpsp
where (not exists ( big multi table select statement here)

WHY!!!!!!!!

Thanks in advance

"In theory there is no difference between theory and practice. But in practice there is!"

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-30 : 11:22:55

Sorry, forgot to mention this is SQLServer 7

"In theory there is no difference between theory and practice. But in practice there is!"

Nazim
A custom title

1408 Posts

Posted - 2002-04-30 : 11:27:35

DONT SHOUT!!!!!!!!!!!!!!!

quote:

WHY!!!!!!!!

Coming to your question ,what is the size of your ar.cpspriority table.

How many records does this query returns. and what is error you are getting , can you be more specific? .

i would suggest you to post the whole query including that big select.

select cpsp.uniqueid_c
from #cpsptemp as cpsp
where (not exists ( big multi table select statement here)

--------------------------------------------------------------

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-30 : 11:33:37

Sorry about the shout, frustration level is getting to me, I need a beer

.

The temp table contains 2448 rows, the select from it with the big sub-select returns 1860 rows. cpspriority contains 150k rows.

The error is as stated, tempdb grows to fill the hard drive thus disk full crash.

Here's the full big select:

select cpsp.uniqueid_c
from #cpsptemp as cpsp
where(not exists (select 1
from ar.transmaster as tm,
ar.cpsplan as cps,
ar.psplanmaster as ps,
ar.linkrate as lr,
ar.rateschedule as rs,
ar.rateschedule as rs1,
ar.rates as rates,
ar.staffcred as sc,
ar.foundationservice as fs
where cpsp.transmasterid_c = tm.uniqueid_c
and tm.staffmasterid_c = sc.staffmasterid_c
and tm.foundationid_c = lr.foundationid_c
and tm.foundationid_c = fs.foundationid_c
and lr.ratescheduleid_c = rs.uniqueid_c
and cpsp.cpsplanid_c = cps.uniqueid_c
and cps.psplanmasterid_c = ps.uniqueid_c
and ps.ratescheduleid_c = rs.parentid_c
and rates.ratescheduleid_c = rs.uniqueid_c
and rs.parentid_c = rs1.uniqueid_c
and (tm.activitydate_d >= cps.startdate_d or cps.startdate_d is null)
and (tm.activitydate_d <= cps.enddate_d or cps.enddate_d is null)
and (tm.activitydate_d >= ps.startdate_d or ps.startdate_d is null)
and (tm.activitydate_d <= ps.enddate_d or ps.enddate_d is null)
and (tm.activitydate_d >= rs.startdate_d or rs.startdate_d is null)
and (tm.activitydate_d <= rs.enddate_d or rs.enddate_d is null)
and (tm.activitydate_d >= rs1.startdate_d or rs1.startdate_d is null)
and (tm.activitydate_d <= rs1.enddate_d or rs1.enddate_d is null)
and (tm.activitydate_d >= sc.startdate_d or sc.startdate_d is null)
and (tm.activitydate_d <= sc.enddate_d or sc.enddate_d is null)
and (tm.activitydate_d >= rates.startdate_d or rates.startdate_d is null)
and (tm.activitydate_d <= rates.enddate_d or rates.enddate_d is null)
and ( (tm.calcdurc_n >= rates.sessionmin_n and tm.calcdurc_n <= rates.sessionmax_n and
Upper(rs.minunits_c) = 'MINUTES') or
((tm.calcdurc_n * rs.unitshour_n) >= rates.sessionmin_n and
(tm.calcdurc_n * rs.unitshour_n) <= rates.sessionmax_n and
Upper(rs.minunits_c) = 'UNITS' and Upper(fs.minunits_c) = 'MINUTES') or
(tm.clientduration_n >= rates.sessionmin_n and tm.clientduration_n <= rates.sessionmax_n and
Upper(rs.minunits_c) = 'UNITS' and Upper(fs.minunits_c) = 'UNITS')
)
and (((sc.credential_c = rates.degree_c and sc.credtype_c = 'D') or (rates.degree_c is null))
or ((sc.credential_c = rates.license_c and sc.credtype_c = 'L') or (rates.license_c is null))
or ((sc.credential_c = rates.certification_c and sc.credtype_c = 'C') or (rates.certification_c is null)))))

"In theory there is no difference between theory and practice. But in practice there is!"

Nazim
A custom title

1408 Posts

Posted - 2002-04-30 : 11:41:05

May i suggest something

1)To replace your joins from Where to keyword join's in your select. it should improve on your performance.

eg:

select a from tablename1 t1,table2 t2
where t1.key=t2.key

is better written in this way

select a from tablename1 t1
inner join table2 t2
on t1.key=t2.key

Edited by - Nazim on 04/30/2002 11:51:27

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-30 : 11:46:58

Thanks for the join suggestion, this I know. The problem is this code snippet (the second version) is 3 years old and has taken this opportunity to finally blow up! The first version is something I stumbled across that seems to work while trouble shooting this.

What I don't know is why the second version doesn't work and the first does since the temp table only contains rows for JON and NP why do I still need to have the where clause in the big select include touch_user and status_c?

Thanks

"In theory there is no difference between theory and practice. But in practice there is!"

monkeybite
Posting Yak Master

152 Posts

Posted - 2002-04-30 : 11:48:19

Try using SET ROWCOUNT 10000, then look at the execution plan for your query. Hopefully this will stop the query before it crashes your machine.

The details of the execution plan may show what piece of the query is causing the resultset to return multiple duplicate rows, and thus filling you your tempdb.

-- monkey

Edited by - monkeybite on 04/30/2002 11:54:07

Nazim
A custom title

1408 Posts

Posted - 2002-04-30 : 11:53:54

why dont you run the inner and outer select seperately and have a look at profiler. i always thought Exist works, faster. newayz , its better you work on the inner select code. it looks pathetic to me.

Here are some more suggestion's .

2) Cant you check your activity this way. I dont understand why you are check for isnull?? .
(tm.activitydate_d between cps.startdate_d and cps_.enddate )
and tm.activitydate_d <= cps.enddate_d

3) you can do away with Upper function as Sql server normally doesnt do case sensitive comparision.

HTH

--------------------------------------------------------------

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-30 : 11:59:37

Thanks Nazim, I appreciate the comments, but you're missing the point.
The big select works fine as long a it includes the touch_user and status_c conditions on the temp table in the where clause. What is baffling is since the temp table was created with this condition why does it make a difference if it's included or not in the big select? The temp table only contains rows for that touch_user and status_c. Adding the condition to the where in the big select is redundant.

Thanks

"In theory there is no difference between theory and practice. But in practice there is!"

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-30 : 12:13:02

The SELECT INTO doesn't create any indexes on your temp table. You could explicitly create the table and a clustered index and that might improve the query.




create table #cpsptemp (
   uniqueid_c int 
   , transmasterid_c  int
   , cpsplanid_c int
)
go

CREATE  /*UNIQUE*/  CLUSTERED  INDEX [#ClusIdx_cpsptemp] 
ON #cpsptemp(
   transmasterid_c
   , cpsplanid_c
/* or just on transmasterid_c
   transmasterid_c
*/
/* or just on cpsplanid_c
   cpsplanid_c
*/
/* or something else
   <<column1>>, <<column2>>, ...
*/
)
GO

insert #cpsptemp (uniqueid_c, transmasterid_c, cpsplanid_c)
select uniqueid_c, transmasterid_c, cpsplanid_c 
from ar.cpspriority 
where touch_user = 'JON' and status_c = 'NP' 


select cpsp.uniqueid_c 
from #cpsptemp as cpsp 
where (not exists ( big multi table select statement here) 


drop table #cpsptemp

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-30 : 12:22:34

Thanks MuffinMan, again I appreciate the response, but it doesn't address the problem. See my last response to Nazim. I know the temp table doesn't have an index. Yes, Nazim and you are both correct, rewriting the select and creating an index would improve performance. The question remains, why does the where clause on touch_user and status_c make a difference if it is included or not since the temp table only contains rows for that specific user and status? Including it makes the select work fine, not including it causes the select to fill tempdb to the capacity of the hard drive, when with or without it the temp table is returning the same number of rows for the query?????

Thanks

quote:

The SELECT INTO doesn't create any indexes on your temp table. You could explicitly create the table and a clustered index and that might improve the query.




create table #cpsptemp (
   uniqueid_c int 
   , transmasterid_c  int
   , cpsplanid_c int
)
go

CREATE  /*UNIQUE*/  CLUSTERED  INDEX [#ClusIdx_cpsptemp] 
ON #cpsptemp(
   transmasterid_c
   , cpsplanid_c
/* or just on transmasterid_c
   transmasterid_c
*/
/* or just on cpsplanid_c
   cpsplanid_c
*/
/* or something else
   <<column1>>, <<column2>>, ...
*/
)
GO

insert #cpsptemp (uniqueid_c, transmasterid_c, cpsplanid_c)
select uniqueid_c, transmasterid_c, cpsplanid_c 
from ar.cpspriority 
where touch_user = 'JON' and status_c = 'NP' 


select cpsp.uniqueid_c 
from #cpsptemp as cpsp 
where (not exists ( big multi table select statement here) 


drop table #cpsptemp

"In theory there is no difference between theory and practice. But in practice there is!"

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-30 : 12:31:15

Well, perhaps when you include the touch_user and status_c in the query, the optimizer uses an index on the table (ar.cpspriority) that helps it execute the query more efficiently?

When you include the touch_user and status_c in the WHERE clause, does the query plan use an index from ar.cpspriority?

If you DO NOT include the WHERE, does the plan show TABLE SCAN's?

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-30 : 12:52:50

The problem is not with the select using cpspriority. The problem is with the big select that uses the temp table. The temp table is created from cpspriority and only contains rows for touch_user JON and status_c of NP. The big select is then executed using the temp table. The big select works fine if the where clause includes the same touch_user and status_c condtions that were used to create the temp table in the first place it fails big time if those conditions are not included. The big select does not access cpspriority. It uses the temp table created from cpspriority that only has JON and NP in it. Why does it matter if the where clause on the big select limits the temp table again by what is already in the temp table? Does this explain the problem any better?

quote:

Well, perhaps when you include the touch_user and status_c in the query, the optimizer uses an index on the table (ar.cpspriority) that helps it execute the query more efficiently?

When you include the touch_user and status_c in the WHERE clause, does the query plan use an index from ar.cpspriority?

If you DO NOT include the WHERE, does the plan show TABLE SCAN's?

"In theory there is no difference between theory and practice. But in practice there is!"

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-30 : 13:01:53

You have explained the the problem in fine detail.

What does the query plan tell you?

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-30 : 16:05:49

Sorry, just got back from pounding my head against a wall

As expected, given the vastly different results, the query plans are very different. I could only compare the estimated plans since I can't get the bad query to run (tempdb fills drive before any rows are returned). The question still remains, why? Why does adding a where clause that is essentially equivalent to "where 1 = 1 and ..." cause the query plan and subsequent select to work fine and not have a redundant where clause added cause the query plan and subsequent select to return a ridiculously large result set?

Oh well, I guess it's just one of those "why ask why" deals. It just scares the h_ll out of me that something this seemingly simple can cause such a huge problem

.

How many of you would insert into a temp table using a simple where clause, and then turn around and use that same where clause when selecting from the temp table? Essentially:

select into #temp from sometable where a = b

then

select from #temp where a = b

Kinda redundant isn't it? See my point? I see lots of beer in my future

quote:

You have explained the the problem in fine detail.

What does the query plan tell you?

"In theory there is no difference between theory and practice. But in practice there is!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-30 : 16:56:36

Correlated subqueries can very easily cause table scans, but I don't think that's the real problem here (not all of it anyway) A SELECT should not affect tempdb like that unless the optimizer is trying to build a temporary working table to put results into. The WHERE clause with the temp table reference might allow it to recognize that it does not need to build this working table. I really think it's something like this because you are joining an ungodly number of tables together (and you ABSOLUTELY MUST rewrite this to use ANSI JOIN syntax if you ever want this to optimize in the future) Not to mention that you can change this:

and (tm.activitydate_d >= cps.startdate_d or cps.startdate_d is null)
and (tm.activitydate_d <= cps.enddate_d or cps.enddate_d is null)

to this:

AND tm.activitydate_d BETWEEN IsNull(cps.startdate_d, tm.activitydate_d) AND IsNull(cps.enddate_d, tm.activitydate_d)

I would also recommend that you consider building a view from the other tables that are used in the query, then joining the temp table to the view instead. It may not optimize any better, but it will certainly make the code a lot easier to manage. You may also be able to get a query plan out of it and look to see if there are any table scans or worktable creation going on.

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-04-30 : 17:11:37

Thanks for the response Rob.
Believe me, I know it needs to be rewritten

. Unfortunately refactoring this stuff is so far away on my "to do" list I can't even see it from here using the Hubble. I think you are on the right track though. It has to be the optimizer trying to build temp tables when the redundant where clause is not included. It's just so damned irritating to have to add a where clause that selects all of the rows from a table...

Thanks again.

"In theory there is no difference between theory and practice. But in practice there is!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources