Nested views

When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?

When you say "4 levels deep" do you mean 4 joins? you might want to test it out. There will be an overhead of creating a temp table, inserting data into it, followed by a SELECT from the temp table. If your report will be run by thousands of users simultaneously you might even see a degradation in performance due to tempdb contention.

|||

By 4 levels deep I mean View1 is a query of 6 tables and several joins, then view2 use view1 with some more tables and joins or calculations etc etc.

Basically the end result in view4 is too complex to write in 1 query or 1 view so you go as far as you can in view1, then expand that result using view2 etc.

So any ideas on performance??

|||

Off the top of the head, since its not a simple query, I cant think of any, so your best bet is to test it out quickly. You can fire up profiler or use SET STATS IO ON and some others like TIME etc and see if there is any improvement/degradation in performance.

Nested Cursors - Me am very bad :(

Hi all,
I have a temp guy who is doing some work for us (seriously, I never wrote the query).
Now this is the scenario (hold tight). So we have a stored procedure as follows...

ALTER PROCEDURE createSegmentPoints AS
DECLARE @.fLat INTEGER
DECLARE @.fLon INTEGER
DECLARE @.segId INTEGER
-- declare cursor to return the from lat and lon for all segments that do not have address point 109.
DECLARE c1 CURSOR FOR (SELECT From_Latitude, From_Longitude, id AS segment_id FROM Segments WHERE SegmentType != 109)
OPEN c1
FETCH NEXT FROM c1 INTO @.fLat, @.fLon, @.segId
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- insert into table the segId, from lat, from lon and returned segment id from function.
INSERT INTO test VALUES (@.segId,@.fLat,@.fLon,dbo.points_test(@.fLat,@.fLon))

FETCH NEXT FROM c1 INTO @.fLat, @.fLon, @.segId
END
CLOSE c1
DEALLOCATE c1

As you can see here I am using a Cursor, which in turn calls a functionwith each row that is processed in the recordset. The function that iscalled is as follows...
ALTER FUNCTION points_test(@.x INTEGER, @.y INTEGER)
RETURNS INTEGER
AS
BEGIN
-- function to find the closed segment point with address point 109 to the segment specified in procedure.
DECLARE @.tempDistance FLOAT(4)
SET @.tempDistance = 1000000
DECLARE @.id, @.seg, lat, lon INTEGER
DECLARE @.distance, @.xd, @.yd FLOAT
DECLARE c1 CURSOR FOR (SELECT from_latitude, from_longitude, id FROM segments WHERE segmenttype = 109)
OPEN c1
FETCH NEXT FROM c1 INTO @.lat, @.lon, @.id
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- calucations to get distance.
SET @.xd = (@.lat-@.x)
SET @.yd = (@.lon-@.y)
SET @.distance = SQRT((@.xd*@.xd) + (@.yd*@.yd))
-- test if you have shortest distance.
IF (@.distance < @.tempDistance)
BEGIN
SET @.tempDistance = @.distance
SET @.seg = @.id
END
FETCH NEXT FROM c1 INTO @.lat,@.lon, @.id
END
CLOSE c1;
DEALLOCATE c1;
RETURN @.seg
END

(This function works out an equation to get the shortest distance fromtwo parameters passed to the function calculated with data from eachrow returned within the cursor)
As you can see here, this function contains ANOTHER cursor!! Ouch. Thefact that their is an SQL query in a function is a killer, but havinganother embedded cursor there is also a killer - this has virtuallykiller the application.
So, how best is it for me to correct this. Should I turn the functioninto a stored procudure? But even if I do this, the nested cursor stillremains. I was thinking maybe to have the SQRT equations within theSELECT expression and then wrapped in a MIN() to maybe get the lowestvalue.
Any ideas would be of great help.
Thanks
Tryst
(1) you could get rid of the cursor in the stored proc by doing something like this:
INSERT INTO test ( col1, col2, col3, col4)
SELECT
From_Latitude,
From_Longitude,
id,
dbo.points_testFrom_LatitudeFrom_Longitude)
FROM
Segments
WHERE
SegmentType != 109
(2) The cursor in the function can be replaced with a table variable. Read up books on line for table varibles (or google) and you can find sample code.
|||OK. Will try your suggested tips.
Thanks
Tryst

need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?
Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
....
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?
|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns>)
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>
|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.
|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:

>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems to
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?

need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
...
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns>)
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:
>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems to
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?

need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
...
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns> )
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if
i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:

>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems t
o
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?