Querying a Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Querying a Query

Author

Topic

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 2008-07-11 : 11:30:20

Coming from a FoxPro background, I use to regularly do the following:

Select * from Sometable into cursor curTemp

Select * from curTemp

The cursor was entirely in memory and I could chain a number of such queries together instead of writing one complex query.

Is there a similiar way to do this in SQL Server?

Thanks,

Greg

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-07-11 : 12:17:27

quote:
Originally posted by gregoryagu

Select * from Sometable into cursor curTemp
Select * from curTemp

Having no Foxpro exposure, I'm not sure. But wouldn't these two essentially do the same thing?

Terry

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-07-11 : 12:23:37

Yes there is but why would be a better question.
What exactly are you trying to accomplish

select * into #tmp from MyTable where MyPk < 100
select * from #tmp
drop table #tmp

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 2008-07-11 : 12:33:13

Sorry for the incomplete data. More exactly, here is what I need to accomplish.

I am working with an application that uses a temp table as part of the stored query. This part of the query is quite slow, and everything I have read says not to use temp tables as they are slow.

Here is the line as extracted out of the Profiler:

SELECT * INTO #tmp_forceorder FROM ( SELECT O.objectid, X.val FROM Objects O JOIN ObjectItemXRef X ON X.objectid = O.objectid AND X.[type] = @itemtype AND O.[type] = @objecttype
AND ( @subtype IS NULL OR O.subtype = @subtype ) ) S

It is very very slow and I am looking for a way to rewrite this without the use of the temp table.

Greg

contrari4n
Starting Member

27 Posts

Posted - 2008-07-11 : 12:52:52

My immediate thought is that it is probably not the temp table causing the problem, but the query itself.

Have you looked at the execution plan?
Are there indexes on objectid on each table?
How selective is O.[type] and X.[type]. With only a few distinct values it may not use an index even if one existed.
Any index on O.subtype will not be used because of the way it is combined in the OR statement (see http://www.sql-server-pro.com/or-condition-performance.html)

Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 2008-07-11 : 13:19:38

Yes, I have looked at the execution plan. (see below)

Yes, there is a clustered index on objectid on each table.

O.[type] and X.[type] has only a few distinct values.

The execution plan says that 81% of the cost is coming from a Clustered Index Scan with the predicate being "[ProgressionData].[dbo].[ObjectItemXRef].[type] as [X].[type]=[@itemtype]"

There is currently no index on O.subtype. (Should there be?)

Greg

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 2008-07-11 : 14:47:02

I did some more testing, and indeed it is not the temp table factor that is slowing this query as originally thought.

So we can consider this solved and call Thread.Abort.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources