Performance Tuning - MDX Query

Hi,

I have some performance troubles with queries of the type

MEDIAN(existing(DimensionX.Attributehierarchie.Attribute.Members),MeasureA/MeasureB)

[also min,max,stddev,variance, avg, etc....]

or similar with different functions... whereas this attriute has 50.000 to 100.000 Members and the related measures are of all types of aggregate functions (sum, count, distinct count, lastnonempty, ..)

While we have some of this measrues on the columns and analyze be an other hierarchy of the DimensionX on Rows it is incredible slow (up to minutes)

Do you have any ideas how to tune?

Best Regards, HANNES

It's hard to say... I assume you've done all the obvious things like partitioned your cube appropriately and made sure your queries are hitting aggregations?

Chris

|||

As you said - partitiond by time ==> the queries are more or less on leave because the set is leave of this dimension - the lastnonempty forces the time dimension to leaves and therefore are only two other dimensions with possible aggregates...

The only thing I could think of is cache (Subcube) where I do a lot of this statements... Is cache already supported? - does it work at processing time only for the partition i want to cache?

Best Regards, HANNES

|||

Well, it could be worth building the necessary aggregation (manually if that's the only way possible) even if there are only two other dimensions. The Cache statement isn't supported and I don't recommend you use it, but some form of cache warming could be useful because the results of your calculated members should be cached after the first time you've run your query.

What are you seeing in Profiler when you run a query? Are you seeing a bulk evaluation taking place, as Mosha describes in various blog entries (eg http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx)? Looking again at your original example, it might also be worth creating a hidden calculated measure defined as Measures.A/Measures.B, setting its Non_Empty_Behavior property as Mosha describes here:
http://sqlblog.com/blogs/mosha/archive/2007/03/06/averages-ratios-division-by-zero-and-non-empty-behavior.aspx
and then altering your existing expression to be Median(existing MyAttribute.Members, Measures.NewCalc) to see if that makes a difference.

Chris

|||

Thanks for the input.

the hidden meausre - we used this approach already.

we have thought about some relational changes and redesign of the cube (to load more aggregated data) - maybe with this we could reach aceptable performance - we will see.

Thanks, HANNES

|||Can you give a full MDX query for exmaple?|||

Thats an more representive example, but we are on the way to redesign the cube...

with member [Measures].[X] as [Measures].[Betriebskosten Original]/[Measures].[Immobilien Anzahl], visible=0, non_empty_behavior=[Measures].[Betriebskosten Original] member [Measures].[Median] asMedian(existing(Immobilien.Immobilie.Immobilie.Members),[Measures].[X]) select {[Measures].[X],[Measures].[Median]} oncolumns, { [Immobilien].[Geographie].[Alle Immobilien nach Geographie], [Immobilien].[Geographie].[Land].&[AUT], [Immobilien].[Geographie].[Land].&[AUT].children } onrows from [Angebotsanalyse] where [Zeit].[Zeit nach Monaten].[Monat].&[2007-02]

The Backgrounds:

Betriebskosten Orginal: LastNonEmpty|||

we have redesigned the cube to include additional measuregroups with the data relationaly pepared for month, quater, week and year level and useing on the measure groups the sum agg function. We do then a calc meausre dependent on the time level we use the appropriate physical member.

The cube become really complex because of this but we get out the results and performace we need.

HANNES

sql

Performance Tuning - MDX Query

Hi,

I have some performance troubles with queries of the type

MEDIAN(existing(DimensionX.Attributehierarchie.Attribute.Members),MeasureA/MeasureB)

[also min,max,stddev,variance, avg, etc....]

or similar with different functions... whereas this attriute has 50.000 to 100.000 Members and the related measures are of all types of aggregate functions (sum, count, distinct count, lastnonempty, ..)

While we have some of this measrues on the columns and analyze be an other hierarchy of the DimensionX on Rows it is incredible slow (up to minutes)

Do you have any ideas how to tune?

Best Regards, HANNES

It's hard to say... I assume you've done all the obvious things like partitioned your cube appropriately and made sure your queries are hitting aggregations?

Chris

|||

As you said - partitiond by time ==> the queries are more or less on leave because the set is leave of this dimension - the lastnonempty forces the time dimension to leaves and therefore are only two other dimensions with possible aggregates...

The only thing I could think of is cache (Subcube) where I do a lot of this statements... Is cache already supported? - does it work at processing time only for the partition i want to cache?

Best Regards, HANNES

|||

Well, it could be worth building the necessary aggregation (manually if that's the only way possible) even if there are only two other dimensions. The Cache statement isn't supported and I don't recommend you use it, but some form of cache warming could be useful because the results of your calculated members should be cached after the first time you've run your query.

What are you seeing in Profiler when you run a query? Are you seeing a bulk evaluation taking place, as Mosha describes in various blog entries (eg http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx)? Looking again at your original example, it might also be worth creating a hidden calculated measure defined as Measures.A/Measures.B, setting its Non_Empty_Behavior property as Mosha describes here:
http://sqlblog.com/blogs/mosha/archive/2007/03/06/averages-ratios-division-by-zero-and-non-empty-behavior.aspx
and then altering your existing expression to be Median(existing MyAttribute.Members, Measures.NewCalc) to see if that makes a difference.

Chris

|||

Thanks for the input.

the hidden meausre - we used this approach already.

we have thought about some relational changes and redesign of the cube (to load more aggregated data) - maybe with this we could reach aceptable performance - we will see.

Thanks, HANNES

|||Can you give a full MDX query for exmaple?|||

Thats an more representive example, but we are on the way to redesign the cube...

with

member [Measures].[X] as [Measures].[Betriebskosten Original]/[Measures].[Immobilien Anzahl], visible=0, non_empty_behavior=[Measures].[Betriebskosten Original]

member [Measures].[Median] as Median(existing(Immobilien.Immobilie.Immobilie.Members),[Measures].[X])

select

{[Measures].[X],[Measures].[Median]} on columns,

{

[Immobilien].[Geographie].[Alle Immobilien nach Geographie],

[Immobilien].[Geographie].[Land].&[AUT],

[Immobilien].[Geographie].[Land].&[AUT].children

} on rows

from

[Angebotsanalyse]

where

[Zeit].[Zeit nach Monaten].[Monat].&[2007-02]

The Backgrounds:

Betriebskosten Orginal: LastNonEmpty|||

we have redesigned the cube to include additional measuregroups with the data relationaly pepared for month, quater, week and year level and useing on the measure groups the sum agg function. We do then a calc meausre dependent on the time level we use the appropriate physical member.

The cube become really complex because of this but we get out the results and performace we need.

HANNES

performance statistics

Hello, I want to use a command line utility to run queries and gather
performance statistics on them. I would like to dump my query results
to nul because all I really care about is the running time. Does anyone
know how to get this using osql? The -p option prints to stdout along
with the query.
Something similar to the db2batch -o r 0
Thanks,
Jen
--
Posted via http://dbforums.comTry:
OSQL -E -S Myserver -i"MyScriptFile.sql" -o NUL
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"moxie" <member44687@.dbforums.com> wrote in message
news:3495957.1066436974@.dbforums.com...
> Hello, I want to use a command line utility to run queries and gather
> performance statistics on them. I would like to dump my query results
> to nul because all I really care about is the running time. Does
anyone
> know how to get this using osql? The -p option prints to stdout along
> with the query.
>
> Something similar to the db2batch -o r 0
>
> Thanks,
> Jen
>
> --
> Posted via http://dbforums.com|||This seams like a resonable approach, but -o nul dumps all of the output
including the performance statistics from -p. I want to keep the
performance statistics.
I guess I want something like setting rowcount 1, but this seems to only
process the query as a top 1 query and stops query processing when the
first tuple is output, especially when it is a projection.
Thanks,
Jen
Posted via http://dbforums.com|||If you simply want to ignore the query output, you might consider
selecting the results into variables like the example below. This will
provide OSQL metrics that don't include resultset processing
DECLARE @.MyData int
SELECT @.MyData = MyData FROM MyTable
There may be better ways to gather performance metrics, though. Can you
elaborate on your objectives?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"moxie" <member44687@.dbforums.com> wrote in message
news:3503080.1066686834@.dbforums.com...
> This seams like a resonable approach, but -o nul dumps all of the
output
> including the performance statistics from -p. I want to keep the
> performance statistics.
>
> I guess I want something like setting rowcount 1, but this seems to
only
> process the query as a top 1 query and stops query processing when the
> first tuple is output, especially when it is a projection.
>
> Thanks,
> Jen
>
> --
> Posted via http://dbforums.com

Performance question: View vs. Table

Hi all, I am new to the forum~

Suppose I have multiple tables, T1, T2, T3. I will use SELECT queries and apply AVG() and STDEV() on each of their columns, and average their results.

I can do this in two ways: one is to apply my SELECT multiple (3) times, then divide it by 3 in this case.

Another way is I create a View that UNIONS all T1 T2 T3, and apply AVG() and STDEV() on each columns.

Which solution is better? I mean, from a performance point of view. This is just a simplified version of my problem, and I would like to know what is the performance of using one (View) over the other (Table)... Does using View instead of table give me any performance overhead? Thanks everyone...They are the same solution. whether you use a select statment or a veiw you will be pulling data off the drives (or cache) to produce the answer. The view has the advantage of being pr-optimized. Views are generally used for this type of thing as it hides all the aggregation.

Don't know if this helped, Books Online has some interesting info look up views-SQL Server, overview and follow the hyper link to Scenarios for Using Views.sql

Performance question: Indexes on separate file group or dimension table on separate file?

Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.
Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>
|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>
|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic

Performance question: Indexes on separate file group or dimension table on separate fi

Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--sql

Performance question for begginers

Hi,
On an SQL 2000, which of the following queries will have better performance:
select * from Phones
where ContactID in (select ID From Contacts where ClientID = 14)
Or
select * from Phones
inner join Contacts on Phones.ContactID = Contacts.ID
inner join Clients on Contacts.ClientID = Clients.ID
where ClientID = 14
As far as I know the second is better, but my own tests prove otherwise...
Thanks,gwenda wrote:
> Hi,
> On an SQL 2000, which of the following queries will have better
> performance:
> select * from Phones
> where ContactID in (select ID From Contacts where ClientID = 14)
> Or
> select * from Phones
> inner join Contacts on Phones.ContactID = Contacts.ID
> inner join Clients on Contacts.ClientID = Clients.ID
> where ClientID = 14
> As far as I know the second is better, but my own tests prove
> otherwise...
> Thanks,
It depends. First no "SELECT *". It's bad practice, a performance hog, a
network hog, and makes performance tuning impossible. The columns you
select have a lot to do with the overall execution plan SQL Server uses.
In the second example, you're selecting all columns in all three tables.
Re-run the queries with the columns you really want back and report the
results. Also post your indexes, keys, and table DDL.
David Gugick
Imceda Software
www.imceda.com|||SQL Server will try to generate the most efficient execution plan possible
based on your query. Given 2 different queries that are semantically
identical, it is entirely possible that SQL Server will generate the same
efficient execution plan even though the queries are expressed differently.
You'll then get the same performance.
As David pointed out, your queries are semantically different so you're
comparing apples and oranges. The example below shows identical execution
plans with different but semantically identical queries:
CREATE TABLE Contacts
(
ID int NOT NULL
CONSTRAINT PK_Contacts PRIMARY KEY,
ClientID int NOT NULL
)
GO
CREATE INDEX Index1 ON Contacts(ClientID)
CREATE TABLE Phones
(
PhoneID int NOT NULL
CONSTRAINT PK_Phones PRIMARY KEY,
ContactID int NOT NULL
CONSTRAINT FK_Phones_Contacts FOREIGN KEY
REFERENCES Contacts(ID),
)
GO
SELECT p.*
FROM Phones p
WHERE ContactID IN (SELECT ID FROM Contacts WHERE ClientID = 14)
GO
SELECT p.*
FROM Phones p
WHERE EXISTS
(
SELECT *
FROM Contacts c
WHERE c.ID = p.ContactID AND
c.ClientID = 14
)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"gwenda" <nina@.community.nospam> wrote in message
news:5199EF86-020D-4475-92F2-E7B64EDCA2C0@.microsoft.com...
> Hi,
> On an SQL 2000, which of the following queries will have better
> performance:
> select * from Phones
> where ContactID in (select ID From Contacts where ClientID = 14)
> Or
> select * from Phones
> inner join Contacts on Phones.ContactID = Contacts.ID
> inner join Clients on Contacts.ClientID = Clients.ID
> where ClientID = 14
> As far as I know the second is better, but my own tests prove otherwise...
> Thanks,
>|||These are two totally different queries. The second involves three tables,
returning columns from all three, while the first one only returns data from
one table and only touches two tables.) If you have good indexes on the
clientId and contactId the first one will be very fast.
The second should be pretty fast, but it is totally about indexes and how
everthing is structured.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"gwenda" <nina@.community.nospam> wrote in message
news:5199EF86-020D-4475-92F2-E7B64EDCA2C0@.microsoft.com...
> Hi,
> On an SQL 2000, which of the following queries will have better
> performance:
> select * from Phones
> where ContactID in (select ID From Contacts where ClientID = 14)
> Or
> select * from Phones
> inner join Contacts on Phones.ContactID = Contacts.ID
> inner join Clients on Contacts.ClientID = Clients.ID
> where ClientID = 14
> As far as I know the second is better, but my own tests prove otherwise...
> Thanks,
>|||Hi,
Thank you all for this helpful responses and sorry for not providing all the
needed data. So:
Phones:
CREATE TABLE [Phones] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ContactID] [int] NOT NULL ,
[Type] [tinyint] NOT NULL ,
[Number] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Remarks] [nvarchar] (150) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Phones] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Phones_Contacts] FOREIGN KEY
(
[ContactID]
) REFERENCES [Contacts] (
[ID]
)
) ON [PRIMARY]
GO
Contacts:
CREATE TABLE [Contacts] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryCode] [tinyint] NULL ,
[Name] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[JobTitle] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Address] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Email] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[IsEmergency] [bit] NULL ,
[LastUpdated] [datetime] NULL ,
[Remarks] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[ClientID] [int] NULL ,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Contacts_Clients] FOREIGN KEY
(
[ClientID]
) REFERENCES [Clients] (
[ID]
)
) ON [PRIMARY]
GO
Clients:
CREATE TABLE [Clients] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (150) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
The two "fixed" queries are:
select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
from Phones p
where ContactID in (select ID From Contacts where ClientID = 14)
OR
select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
from Phones
inner join Contacts on Phones.ContactID = Contacts.ID
inner join Clients on Contacts.ClientID = Clients.ID
where ClientID = 14
The purpose is getting the information from phones in the best performance.
Hope that will help you to help me.
Thanks again,
Gwenda|||The performance of each approach depends on the relative table size (and
number of distinct ID's that "select ID From Contacts where ClientID =
14" produces). If there are just a few distinct ID's for ClientID 14,
then the first approach could be faster. If not, then I would expect the
second approach to be faster. So it really depends. You would have to
test it for yourself.
You could also try the following approach and see how fast that is.
select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
from Phones p
where EXISTS (select 1 From Contacts where ClientID = 14 AND
ID=p.ContactID)
BTW: based on the clustered index that already exists on Contacts(ID),
this query would benefit from a nonclustered index on
Contacts(ClientID).
HTH,
Gert-Jan
gwenda wrote:
> Hi,
> Thank you all for this helpful responses and sorry for not providing all t
he
> needed data. So:
> Phones:
> CREATE TABLE [Phones] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ContactID] [int] NOT NULL ,
> [Type] [tinyint] NOT NULL ,
> [Number] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Remarks] [nvarchar] (150) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Phones] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Phones_Contacts] FOREIGN KEY
> (
> [ContactID]
> ) REFERENCES [Contacts] (
> [ID]
> )
> ) ON [PRIMARY]
> GO
> Contacts:
> CREATE TABLE [Contacts] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryCode] [tinyint] NULL ,
> [Name] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
> [JobTitle] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
> [Address] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Email] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
> [IsEmergency] [bit] NULL ,
> [LastUpdated] [datetime] NULL ,
> [Remarks] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [ClientID] [int] NULL ,
> CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Contacts_Clients] FOREIGN KEY
> (
> [ClientID]
> ) REFERENCES [Clients] (
> [ID]
> )
> ) ON [PRIMARY]
> GO
> Clients:
> CREATE TABLE [Clients] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (150) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Description] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> The two "fixed" queries are:
> select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
> from Phones p
> where ContactID in (select ID From Contacts where ClientID = 14)
> OR
> select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
> from Phones
> inner join Contacts on Phones.ContactID = Contacts.ID
> inner join Clients on Contacts.ClientID = Clients.ID
> where ClientID = 14
> The purpose is getting the information from phones in the best performance
.
> Hope that will help you to help me.
> Thanks again,
> Gwenda|||Thanks :)
"Gert-Jan Strik" wrote:

> The performance of each approach depends on the relative table size (and
> number of distinct ID's that "select ID From Contacts where ClientID =
> 14" produces). If there are just a few distinct ID's for ClientID 14,
> then the first approach could be faster. If not, then I would expect the
> second approach to be faster. So it really depends. You would have to
> test it for yourself.
> You could also try the following approach and see how fast that is.
> select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
> from Phones p
> where EXISTS (select 1 From Contacts where ClientID = 14 AND
> ID=p.ContactID)
> BTW: based on the clustered index that already exists on Contacts(ID),
> this query would benefit from a nonclustered index on
> Contacts(ClientID).
> HTH,
> Gert-Jan
> gwenda wrote:
>

Performance question

Hello Everybody,
There is an unexplained yet situation with
performance/time to return query results on 3 queries.
Queries must be identical and are executed from SQL S 2000
via Linked server to an Oracle database.
End table is the same (events).
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'FLORIDA'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'POWER2'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'APCEPKS'
Last query always takes ~5 sec to return results (
to 'APCEPKS'); there are much more records than others.
First and second queries take more than a minute!!!
The order of execution doesn't matter.
Execution plan shows no differences.
My question is:
What could cause performance difference like this?
Any suggestions are highly appreciated!
Thx,
DobbyAdd these commands after each query and see how does it affect ?
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>|||Basically, you are submitting the queries to an Oracle database, via
SQL-Server. So I would look at the Oracle side. If you submit the
queries directly on the Oracle database, do they behave the same?
Gert-Jan
Dobromir Rizov wrote:
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby|||One thing I can think of -- not familiar with Oracle data/index structure --
is that Servername is non-cluster indexed, and most of the records for
Servername = 'APCEPKS' are located in a small range of data pages, whereas
the records for the other two servername values are spread wide.
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>|||Same all queries executed directly in Oracle take less
than a second to return result.
Dobromir
>--Original Message--
>Basically, you are submitting the queries to an Oracle
database, via
>SQL-Server. So I would look at the Oracle side. If you
submit the
>queries directly on the Oracle database, do they behave
the same?
>Gert-Jan
>
>Dobromir Rizov wrote:
>> Hello Everybody,
>> There is an unexplained yet situation with
>> performance/time to return query results on 3 queries.
>> Queries must be identical and are executed from SQL S
2000
>> via Linked server to an Oracle database.
>> End table is the same (events).
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'FLORIDA'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'POWER2'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'APCEPKS'
>> Last query always takes ~5 sec to return results (
>> to 'APCEPKS'); there are much more records than others.
>> First and second queries take more than a minute!!!
>> The order of execution doesn't matter.
>> Execution plan shows no differences.
>> My question is:
>> What could cause performance difference like this?
>> Any suggestions are highly appreciated!
>> Thx,
>> Dobby
>.
>|||In that case, I would consider moving to an OPENQUERY syntax:
SET QUOTED_IDENTIFIER OFF
SELECT MaxID FROM OPENQUERY(PHDT,
"SELECT MAX(ID) AS MaxID
FROM PHDT..PS_USER.EVENTS
WHERE SERVERNAME='FLORIDA'
")
I realize however, that this may not meet your requirement...
Gert-Jan
Dobromir Rizov wrote:
> Same all queries executed directly in Oracle take less
> than a second to return result.
> Dobromir
> >--Original Message--
> >Basically, you are submitting the queries to an Oracle
> database, via
> >SQL-Server. So I would look at the Oracle side. If you
> submit the
> >queries directly on the Oracle database, do they behave
> the same?
> >
> >Gert-Jan
> >
> >
> >Dobromir Rizov wrote:
> >>
> >> Hello Everybody,
> >>
> >> There is an unexplained yet situation with
> >> performance/time to return query results on 3 queries.
> >>
> >> Queries must be identical and are executed from SQL S
> 2000
> >> via Linked server to an Oracle database.
> >> End table is the same (events).
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'FLORIDA'
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'POWER2'
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'APCEPKS'
> >>
> >> Last query always takes ~5 sec to return results (
> >> to 'APCEPKS'); there are much more records than others.
> >>
> >> First and second queries take more than a minute!!!
> >>
> >> The order of execution doesn't matter.
> >>
> >> Execution plan shows no differences.
> >>
> >> My question is:
> >>
> >> What could cause performance difference like this?
> >>
> >> Any suggestions are highly appreciated!
> >>
> >> Thx,
> >>
> >> Dobby
> >.
> >|||The execution plan shows:
Row count 1 on the shortest query and full table scans on
others.
My understanding is that indexes are used only (but
always) with the query on 'APCEPKS', but never with any
other queries.
What makes queries to differ?
Is there a way to force a query to use a particular index?
Thx,
Dobby
>--Original Message--
>Hello Everybody,
>There is an unexplained yet situation with
>performance/time to return query results on 3 queries.
>Queries must be identical and are executed from SQL S
2000
>via Linked server to an Oracle database.
>End table is the same (events).
>
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'FLORIDA'
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'POWER2'
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'APCEPKS'
>Last query always takes ~5 sec to return results (
>to 'APCEPKS'); there are much more records than others.
>First and second queries take more than a minute!!!
>The order of execution doesn't matter.
>Execution plan shows no differences.
>My question is:
>What could cause performance difference like this?
>Any suggestions are highly appreciated!
>Thx,
>Dobby
>
>
>.
>|||Directly in SQL*Plus queries retutn instant results.
There are indexes in place.
Dobby
>--Original Message--
>Basically, you are submitting the queries to an Oracle
database, via
>SQL-Server. So I would look at the Oracle side. If you
submit the
>queries directly on the Oracle database, do they behave
the same?
>Gert-Jan
>
>Dobromir Rizov wrote:
>> Hello Everybody,
>> There is an unexplained yet situation with
>> performance/time to return query results on 3 queries.
>> Queries must be identical and are executed from SQL S
2000
>> via Linked server to an Oracle database.
>> End table is the same (events).
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'FLORIDA'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'POWER2'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'APCEPKS'
>> Last query always takes ~5 sec to return results (
>> to 'APCEPKS'); there are much more records than others.
>> First and second queries take more than a minute!!!
>> The order of execution doesn't matter.
>> Execution plan shows no differences.
>> My question is:
>> What could cause performance difference like this?
>> Any suggestions are highly appreciated!
>> Thx,
>> Dobby
>.
>sql

Performance question

Hello Everybody,
There is an unexplained yet situation with
performance/time to return query results on 3 queries.
Queries must be identical and are executed from SQL S 2000
via Linked server to an Oracle database.
End table is the same (events).
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'FLORIDA'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'POWER2'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'APCEPKS'
Last query always takes ~5 sec to return results (
to 'APCEPKS'); there are much more records than others.
First and second queries take more than a minute!!!
The order of execution doesn't matter.
Execution plan shows no differences.
My question is:
What could cause performance difference like this?
Any suggestions are highly appreciated!
Thx,
DobbyAdd these commands after each query and see how does it affect ?
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Basically, you are submitting the queries to an Oracle database, via
SQL-Server. So I would look at the Oracle side. If you submit the
queries directly on the Oracle database, do they behave the same?
Gert-Jan
Dobromir Rizov wrote:

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby

---

|||One thing I can think of -- not familiar with Oracle data/index structure --
is that Servername is non-cluster indexed, and most of the records for
Servername = 'APCEPKS' are located in a small range of data pages, whereas
the records for the other two servername values are spread wide.
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Same all queries executed directly in Oracle take less
than a second to return result.
Dobromir

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

|||In that case, I would consider moving to an OPENQUERY syntax:
SET QUOTED_IDENTIFIER OFF
SELECT MaxID FROM OPENQUERY(PHDT,
"SELECT MAX(ID) AS MaxID
FROM PHDT..PS_USER.EVENTS
WHERE SERVERNAME='FLORIDA'
")
I realize however, that this may not meet your requirement...
Gert-Jan
Dobromir Rizov wrote:[QUOTE]
> Same all queries executed directly in Oracle take less
> than a second to return result.
> Dobromir
>
> database, via
> submit the
> the same?
> 2000
> SERVERNAME
> SERVERNAME
> SERVERNAME|||Directly in SQL*Plus queries retutn instant results.
There are indexes in place.
Dobby

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

sql

Performance Problems Question

a lot of our users get Sql Server timeout errors from what appear to be
pretty basic queries run from our web app. the server should be well
equipped hardware wise to deal with server load (dual proc xeon with nearly
4 gb ram raid 5). concurrent user connections average around 300; it handles
an average of 4 transactions a second. running the queries themselves via QA
in some cases takes an extremely long time. otehr times, they run in a
timely fashion.
I notice on perfmon a something that raised an eyebrow - the database is
spread over 2 filegroups, primary is about 4.5GB, secondary index filegroup
is about 2.3 GB. but we are getting very high disk queue values - averaging
50 over an hour or so.
While I know that as general rule of thum the threshold for disk queue is 2
* physical drives, 2 of the drives are hardly used. One drive contains only
backups, the second contains Diskeeper files, the 3rd is the OS drive. In
theory, only 2 of the drives are being written to during normal database
usage.
My question - is THIS normal (can I rule out the queue as a bottleneck),
given that we arent separating the filegroups over the RAID?I notice the system gets tweaked during Page Reads... probably indexes then?
"Ick" <nospamplease> wrote in message
news:uFZMAsphDHA.4088@.tk2msftngp13.phx.gbl...
> a lot of our users get Sql Server timeout errors from what appear to be
> pretty basic queries run from our web app. the server should be well
> equipped hardware wise to deal with server load (dual proc xeon with
nearly
> 4 gb ram raid 5). concurrent user connections average around 300; it
handles
> an average of 4 transactions a second. running the queries themselves via
QA
> in some cases takes an extremely long time. otehr times, they run in a
> timely fashion.
> I notice on perfmon a something that raised an eyebrow - the database is
> spread over 2 filegroups, primary is about 4.5GB, secondary index
filegroup
> is about 2.3 GB. but we are getting very high disk queue values -
averaging
> 50 over an hour or so.
> While I know that as general rule of thum the threshold for disk queue is
2
> * physical drives, 2 of the drives are hardly used. One drive contains
only
> backups, the second contains Diskeeper files, the 3rd is the OS drive. In
> theory, only 2 of the drives are being written to during normal database
> usage.
> My question - is THIS normal (can I rule out the queue as a bottleneck),
> given that we arent separating the filegroups over the RAID?
>
>

Performance problem: SSAS 2005 + ProClarity #2 (Update)

Hi,

I have updated my earlier post on performance (Thomas was helping me),

had some updates, thoughts and queries.

Please do check that.

here is the link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=929316&SiteID=1

apologies for this post, but I am not sure if the older ones that get updated are seen. if you want me to open another post for it , please do tell me.

thanks a lot.

Regards

I have posted an answere on your original post.

Regards

Thomas Ivarsson

Performance of parameterized queries

I've come up with a query which allows me to perform fairly dynamic
queries depending on which parameters are set when a stored procedure
is called, but I'm not sure if what I'm doing is a performance no-no.
Here is an example:
CREATE PROCEDURE p_TradesGetForProcessing
@.tradeID int = null,
@.underlyingSymbol varchar(20) = null,
@.startTradeDate datetime = null,
@.endTradeDate datetime = null,
@.expirationDate datetime = null
AS
SELECT TradePL.TradeID, TradeID.OptionID,
FROM OptionTradeViewForSelectingTradesToProce
ss
WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
In essence, this allows me to pass in some or all of the filter
parameters. I was hoping that this would be fairly optimal in that the
optimizer has a change to compile the query. What I don't know is if
there is going to be a huge amount of overhead and I'm better off doing
it some other way.Whether some other method will be performant can be determined only by
testing the various methods. However, for a variety options, see:
http://www.sommarskog.se/dyn-search.html
Anith|||bpeikes (ben@.peikes.com) writes:
> I've come up with a query which allows me to perform fairly dynamic
> queries depending on which parameters are set when a stored procedure
> is called, but I'm not sure if what I'm doing is a performance no-no.
> Here is an example:
> CREATE PROCEDURE p_TradesGetForProcessing
> @.tradeID int = null,
> @.underlyingSymbol varchar(20) = null,
> @.startTradeDate datetime = null,
> @.endTradeDate datetime = null,
> @.expirationDate datetime = null
> AS
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
> and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
> In essence, this allows me to pass in some or all of the filter
> parameters. I was hoping that this would be fairly optimal in that the
> optimizer has a change to compile the query. What I don't know is if
> there is going to be a huge amount of overhead and I'm better off doing
> it some other way.
In SQL 2000, the optimizer have no idea of the values, and will arrange
for a one-size-fits all plan. Which in this case is mostly like to
be a table scan, because of the >= conditions. Had you only had =
conditions and all columns had been indexed, it's possible that SQL
Server would pick index concatenation.
For SQL 2005 you can add the hint OPTION (RECOMPILE) to force statement
recompilation - SQL Server will then look at the actual values and use
them for the plan.
I have a longer article on the topic of dynamic searches on my web
site: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx|||bpeikes wrote:
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol =
> @.underlyingSymbol) and (@.startTradeDate is NULL or TradeDay >=
> @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
You're not likely to get index optimization using this method. The only
way to tell for sure is to check the execution plans for each version of
the query, but I'm fairly sure you'll see a lot of index scans (assuming
indexes are in place). What you might want to do is have this procedure
call other procedures depending on the parameters passed. I don't know
if you want to allow all NULL values passed in. If so, the query could
do away with the entire WHERE clause. Once you determine the valid
versions of the query, you can validate the parameters in the main proc
and then design the necessary indexes to support those queries.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
That is a performance no-no, you'll not get good if any index usage on that
query because of the IS NULL and OR stuff.
For search stuff, like below, you should look at using either a ton of IF
ELSE statements or preferably dynamic parameterised SQL...
Build the SQL only for the parameters that are passed, if tradeID is the
only parameter passed then do this...
SET @.nsql = '
select...
from ..
where TradeID = @.tradeID'
exec sp_executesql @.nsql,
N'@.tradeID int',
@.tradeID
The execution plan will be cached and is reusable so it isn't compiled each
time.
Make sure you use parameters as i've done rather than hard code values and
you won't have a problem with injection.
The one draw back is that you will need to permission access to the base
tables/views used in the dynamic SQL rather than execute permission on the
proc; you can get round this problem of security using application roles in
your application.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"bpeikes" <ben@.peikes.com> wrote in message
news:1131574058.424530.205360@.g43g2000cwa.googlegroups.com...
> I've come up with a query which allows me to perform fairly dynamic
> queries depending on which parameters are set when a stored procedure
> is called, but I'm not sure if what I'm doing is a performance no-no.
> Here is an example:
> CREATE PROCEDURE p_TradesGetForProcessing
> @.tradeID int = null,
> @.underlyingSymbol varchar(20) = null,
> @.startTradeDate datetime = null,
> @.endTradeDate datetime = null,
> @.expirationDate datetime = null
> AS
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
> and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
> In essence, this allows me to pass in some or all of the filter
> parameters. I was hoping that this would be fairly optimal in that the
> optimizer has a change to compile the query. What I don't know is if
> there is going to be a huge amount of overhead and I'm better off doing
> it some other way.
>|||Tony Rogerson wrote:
> Hi,
> That is a performance no-no, you'll not get good if any index usage
> on that query because of the IS NULL and OR stuff.
> For search stuff, like below, you should look at using either a ton
> of IF ELSE statements or preferably dynamic parameterised SQL...
> Build the SQL only for the parameters that are passed, if tradeID is
> the only parameter passed then do this...
> SET @.nsql = '
> select...
> from ..
> where TradeID = @.tradeID'
> exec sp_executesql @.nsql,
> N'@.tradeID int',
> @.tradeID
>
To add to what Tony has described, you can use dynamic SQL and send in
all parameters, regardless of whether those parameters exist in the SQL
statement. Assuming your database can support dynamic SQL, you could use
something like the following even if the SQL statement only contains
@.Param1:
Exec sp_executesql @.nvcSql, N'@.Param1 INT, @.Param2 INT, @.Param3 INT',
@.Param1, @.Param2, @.Param3
David Gugick
Quest Software
www.imceda.com
www.quest.com

Performance of Nested Views

In Our application there is a method which contain a series ( around 7 ) of
functions ( we can think it as sql queries).
Earlier we use to create one table for each fuction out put and use it as
the input to next function. ( we use simple recovery model)
Things were working at that time , but to improve performance ( reduce I/O )
we decided to create views as the ouput of each fuction and use it as the
input of next funtion and so on . At last we create a table from the last
view created.Actually we only need this table( out put of whole method ) for
future use.
But when we try to create the table from the last view, it just take hours
and hours to complete(?) .( never completed even after 60 hours).
Earlier in the table creation approach entire method use to complete in 16
hours.
I'm not sure how sql sever resolve the query when we use the last view .Is
there any way we can see how optimizer convert whole thing into a query.
What could be the reason for this method not working.?Is there any reason
nested views performance degrade when level increases?
Any help really appreciated .
Thanks
Binu
Below the queries I use.
table T123456_2004_JAN has got 5,000,000 records.
CREATE VIEW [dbo].[S1233769547736706426] AS SELECT [S3895777261424671822].*
FROM T123456_2004_JAN
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547767957187] AS SELECT [S1233769547736706426].*
,dbo . SC_GREATEST_CHAR ( [F39] , [F40] , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
DEFAULT , DEFAULT , DEFAULT ) [PM00461] FROM DBO.[S1233769547736706426]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547824202597] AS SELECT [A].* , CASE WHEN
[A].[PM00458] IS NULL THEN CONVERT ( NUMERIC ( 15 ) ,NULL ) ELSE ( SELECT
COUNT ( * ) + 1 [RANK] FROM DBO.[S1233769547767957187] [B] WHERE ( (
[B].[PM00458] > [A].[PM00458] ) OR ( [B].[PM00458] = [A].[PM00458] AND
[B].[PM00460] > [A].[PM00460] ) ) AND [B].[F39] = [A].[F39] ) END [PM00462]
FROM DBO.[S1233769547767957187] [A]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547879852187] AS SELECT [A].* , ( SELECT { fn
IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S1233769547824202597] [B]
WHERE [A].[F39] = [B].[F39] AND ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) } > {
fn IFNULL ( [B].[F40] ,CHAR(0) ) } OR ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) }
= { fn IFNULL ( [B].[F40] ,CHAR(0) ) } AND
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPT YVALUEHANDLE(dbo.EMPTYVALU
EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
[A].[F42] ) + [A].[F39] )<=
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPT YVALUEHANDLE(dbo.EMPTYVALU
EHANDLE( CONVERT ( VARCHAR ,[B].[F41] , 101 ) + [B].[F40] ) + [B].[F1] ) +
[B].[F42] ) + [B].[F39] )) ) ) [PM00463] FROM DBO.[S1233769547824202597] [A]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547912175445] AS SELECT [S1233769547879852187].*
,( CASE WHEN '2004-05-01' > [F41] THEN ( CASE WHEN { fn TIMESTAMPDIFF (
SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATEPART
( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAMPDIFF (
SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } ELSE { fn TIMESTAMPDIFF (
SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } - 1 END ) END ) ELSE -1 * ( CASE WHEN
{ fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } = 0 THEN 0 ELSE
( CASE WHEN DATEPART ( DD ,'2004-05-01' ) <= DATEPART ( DD ,[F41] ) THEN {
fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } ELSE { fn
TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } - 1 END ) END ) END )
[PM00464] FROM DBO.[S1233769547879852187]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547N1268698629] AS SELECT
[S1233769547912175445].* , { fn ROUND ( 110 * [PM00464] ,10 ) } [PM00465]
FROM DBO.[S1233769547912175445]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547N1213049039] AS SELECT
[S1233769547N1268698629].* , { fn ROUND ( ( CASE WHEN [PM00457] = 1 THEN
[PM00465] ELSE [PM00463] END ) ,10 ) } [PM00466] FROM
DBO.[S1233769547N1268698629]
--Earlier below statement is an -- select into statement ....
CREATE VIEW [dbo].[S1233769547N1177270046] AS SELECT
[S1233769547N1213049039].* , CASE WHEN [PM00464] = 0 THEN CONVERT ( NUMERIC
( 20 ) ,NULL ) ELSE { fn ROUND ( [PM00463] / [PM00464] ,0 ) } END [PM00467]
FROM DBO.[S1233769547N1213049039]
Final Statement to Create the Table: -- ( this never complete after 60
hours)
SELECT [F1] , [F39] , [F40] , [F41] , [F42] , [PM00464] [D192] , [PM00459]
[D185] , [PM00457] [D186] , [PM00467] [D198] , [PM00461] [D193] ,
[FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [FG00456_004] [D188_4] ,
[FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [FG00456_017] [D188_17]
, [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [FG00456_006] [D188_6] ,
[FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [FG00456_008] [D188_8] ,
[FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG00456_023] [D188_23] ,
[FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG00456_015] [D188_15] ,
[FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG00456_018] [D188_18] ,
[FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [FG00456_016] [D188_16]
, [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D184] , [PM00460] [D187] ,
[PM00466] [D167] INTO DBO.[S389577726N118115039] FROM
DBO.[S1233769547N1177270046]
Below the estimated execution Plan:
|--Table Insert(OBJECT[S1387535081666676858]),
SET[S1387535081666676858].[D167]=[Expr1022],
[S1387535081666676858].[D187]=[S138753508748329346].[PM00460],
[S1387535081666676858].[D184]=[S138753508748329346].[PM00458],
[S1387535081666676858].[D1]=[Expr1018],
[S1387535081666676858].[D166]=[Expr1021],
[S1387535081666676858].[D188_16]=[S138753508748329346].[FG00456_016],
[S1387535081666676858].[D188_12]=[S138753508748329346].[FG00456_012],
[S1387535081666676858].[D188_22]=[S138753508748329346].[FG00456_022],
[S1387535081666676858].[D188_18]=[S138753508748329346].[FG00456_018],
[S1387535081666676858].[D188_20]=[S138753508748329346].[FG00456_020],
[S1387535081666676858].[D188_1]=[S138753508748329346].[FG00456_001],
[S1387535081666676858].[D188_15]=[S138753508748329346].[FG00456_015],
[S1387535081666676858].[D188_11]=[S138753508748329346].[FG00456_011],
[S1387535081666676858].[D188_7]=[S138753508748329346].[FG00456_007],
[S1387535081666676858].[D188_23]=[S138753508748329346].[FG00456_023],
[S1387535081666676858].[D188_5]=[S138753508748329346].[FG00456_005],
[S1387535081666676858].[D188_9]=[S138753508748329346].[FG00456_009],
[S1387535081666676858].[D188_8]=[S138753508748329346].[FG00456_008],
[S1387535081666676858].[D188_24]=[S138753508748329346].[FG00456_024],
[S1387535081666676858].[D188_14]=[S138753508748329346].[FG00456_014],
[S1387535081666676858].[D188_6]=[S138753508748329346].[FG00456_006],
[S1387535081666676858].[D188_2]=[S138753508748329346].[FG00456_002],
[S1387535081666676858].[D188_3]=[S138753508748329346].[FG00456_003],
[S1387535081666676858].[D188_17]=[S138753508748329346].[FG00456_017],
[S1387535081666676858].[D188_21]=[S138753508748329346].[FG00456_021],
[S1387535081666676858].[D188_10]=[S138753508748329346].[FG00456_010],
[S1387535081666676858].[D188_4]=[S138753508748329346].[FG00456_004],
[S1387535081666676858].[D188_13]=[S138753508748329346].[FG00456_013],
[S1387535081666676858].[D188_19]=[S138753508748329346].[FG00456_019],
[S1387535081666676858].[D198]=[Expr1020],
[S1387535081666676858].[D186]=[S138753508748329346].[PM00457],
[S1387535081666676858].[D192]=[Expr1019],
[S1387535081666676858].[F41]=[S138753508748329346].[F41],
[S1387535081666676858].[F1]=[S138753508748329346].[F1],
[S1387535081666676858].[F39]=[S138753508748329346].[F39],
[S1387535081666676858].[F40]=[S138753508748329346].[F40],
[S1387535081666676858].[F42]=[S138753508748329346].[F42],
[S1387535081666676858].[D185]=[S138753508748329346].[PM00459],
[S1387535081666676858].[D193]=[Expr1002]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE[Expr1019]=If ('May 1 2004
12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
[S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
(datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), [Expr1020]=If (If
('May 1 2004 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
[S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
(datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))=0) then NULL else
round([Expr1018]/Convert(If ('May 1 2004
12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
[S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
(datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))), 0, NULL),
[Expr1021]=round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')=0) then 0 else If (datepart(day,
[S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
(datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1) else
( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL),
[Expr1022]=round(If (Convert([S138753508748329346].[PM00457])=1) then
Convert(round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
12:00AM')=0) then 0 else If (datepart(day,
[S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
(datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1) else
( -1*If (datediff(month, 'May 1 2004 12:00AM',
[S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL)) else
[Expr1018], 10, NULL)))
|--Table Spool
|--Compute
Scalar(DEFINE[Expr1018]=isnull([Expr1016], 0.000)))
|--Nested Loops(Inner Join, OUTER
REFERENCES[S138753508748329346].[F42], [S138753508748329346].[F41],
[S138753508748329346].[F40], [S138753508748329346].[F1],
[S138753508748329346].[F39]))
|--Compute
Scalar(DEFINE[Expr1002]=[dbo].[SC_GREATEST_CHAR](Convert([S138753508748329
346].[F39]), Convert([S138753508748329346].[F40]), DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)))
| |--Nested Loops(Inner Join,
WHERE[S138753508748329346].[PM00458]=NULL)OUTER
REFERENCES[S138753508748329346].[F39], [S138753508748329346].[PM00460],
[S138753508748329346].[PM00458]))
| |--Table
Scan(OBJECT[SynComp_MOP].[dbo].[S138753508748329346]))
| |--Row Count Spool
| |--Constant Scan
|--Hash Match(Cache,
HASH[S138753508748329346].[F42], [S138753508748329346].[F41],
[S138753508748329346].[F40], [S138753508748329346].[F1],
[S138753508748329346].[F39], Convert(char(0)), Convert(char(0))),
RESIDUAL((((([S138753508748329346].[F42]=[S138753508748329346].[F42] AND
[S138753508748329346].[F41]=[S138753508748329346].[F41]) AND
[S138753508748329346].[F40]=[S138753508748329346].[F40]) AND
[S138753508748329346].[F1]=[S138753508748329346].[F1]) AND
[S138753508748329346].[F39]=[S138753508748329346].[F39]) AND
[ConstExpr1073]=Convert(char(0))) AND [ConstExpr1074]=Convert(char(0))))
|--Compute Scalar(DEFINE[Expr1016]=If
([Expr1151]=0) then NULL else [Expr1152]))
|--Stream
Aggregate(DEFINE[Expr1151]=COUNT_BIG([S138753508748329346].[PM00458]),
[Expr1152]=SUM([S138753508748329346].[PM00458])))
|--Nested Loops(Inner Join,
WHERE[S138753508748329346].[PM00458]=NULL)OUTER
REFERENCES[S138753508748329346].[F39], [S138753508748329346].[PM00460],
[S138753508748329346].[PM00458]))
|--Filter(WHEREisnull([S138753508748329346].[F40],
Convert(char(0)))>[S138753508748329346].[F40] OR
(isnull([S138753508748329346].[F40],
Convert(char(0)))=[S138753508748329346].[F40] AND
[dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMP
TYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S1387535087
48329346].[F41])+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+
[S138753508748329346].[F42]))+[S138753508748329346].[F39]))<=[dbo].[EMPTYVAL
UEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](
Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S138753508748329346].[F41]
)+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+[S1387535087483
29346].[F42]))+[S138753508748329346].[F39])))))
| |--Index
Spool(SEEK[S138753508748329346].[F39]=[S138753508748329346].[F39]))
| |--Table
Scan(OBJECT[SynComp_MOP].[dbo].[S138753508748329346]))
|--Row Count Spool
|--Constant Scan
User Defined Functions can be very bad for performance. Have you tried to
replace the functions with straight SQL? From the names of the functions I
assume that they are rather straightforward.
Jacco Schalkwijk
SQL Server MVP
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:%23A4n9FraEHA.4048@.TK2MSFTNGP10.phx.gbl...
> In Our application there is a method which contain a series ( around 7 )
of
> functions ( we can think it as sql queries).
> Earlier we use to create one table for each fuction out put and use it as
> the input to next function. ( we use simple recovery model)
> Things were working at that time , but to improve performance ( reduce
I/O )
> we decided to create views as the ouput of each fuction and use it as the
> input of next funtion and so on . At last we create a table from the last
> view created.Actually we only need this table( out put of whole method )
for
> future use.
> But when we try to create the table from the last view, it just take
hours
> and hours to complete(?) .( never completed even after 60 hours).
> Earlier in the table creation approach entire method use to complete in 16
> hours.
> I'm not sure how sql sever resolve the query when we use the last view .Is
> there any way we can see how optimizer convert whole thing into a query.
> What could be the reason for this method not working.?Is there any reason
> nested views performance degrade when level increases?
> Any help really appreciated .
> Thanks
> Binu
> Below the queries I use.
> table T123456_2004_JAN has got 5,000,000 records.
> CREATE VIEW [dbo].[S1233769547736706426] AS SELECT
[S3895777261424671822].*
> FROM T123456_2004_JAN
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547767957187] AS SELECT
[S1233769547736706426].*
> ,dbo . SC_GREATEST_CHAR ( [F39] , [F40] , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT ) [PM00461] FROM DBO.[S1233769547736706426]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547824202597] AS SELECT [A].* , CASE WHEN
> [A].[PM00458] IS NULL THEN CONVERT ( NUMERIC ( 15 ) ,NULL ) ELSE ( SELECT
> COUNT ( * ) + 1 [RANK] FROM DBO.[S1233769547767957187] [B] WHERE ( (
> [B].[PM00458] > [A].[PM00458] ) OR ( [B].[PM00458] = [A].[PM00458] AND
> [B].[PM00460] > [A].[PM00460] ) ) AND [B].[F39] = [A].[F39] ) END
[PM00462]
> FROM DBO.[S1233769547767957187] [A]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547879852187] AS SELECT [A].* , ( SELECT { fn
> IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S1233769547824202597] [B]
> WHERE [A].[F39] = [B].[F39] AND ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) } > {
> fn IFNULL ( [B].[F40] ,CHAR(0) ) } OR ( { fn IFNULL ( [A].[F40]
,CHAR(0) ) }
> = { fn IFNULL ( [B].[F40] ,CHAR(0) ) } AND
>
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPT YVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
> [A].[F42] ) + [A].[F39] )<=
>
dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPT YVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[B].[F41] , 101 ) + [B].[F40] ) + [B].[F1] ) +
> [B].[F42] ) + [B].[F39] )) ) ) [PM00463] FROM DBO.[S1233769547824202597]
[A]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547912175445] AS SELECT
[S1233769547879852187].*
> ,( CASE WHEN '2004-05-01' > [F41] THEN ( CASE WHEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATEPART

> ( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } ELSE { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } - 1 END ) END ) ELSE -1 * ( CASE
WHEN
> { fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } = 0 THEN 0 ELSE
> ( CASE WHEN DATEPART ( DD ,'2004-05-01' ) <= DATEPART ( DD ,[F41] ) THEN {
> fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } ELSE { fn
> TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } - 1 END ) END )
END )
> [PM00464] FROM DBO.[S1233769547879852187]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1268698629] AS SELECT
> [S1233769547912175445].* , { fn ROUND ( 110 * [PM00464] ,10 ) } [PM00465]
> FROM DBO.[S1233769547912175445]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1213049039] AS SELECT
> [S1233769547N1268698629].* , { fn ROUND ( ( CASE WHEN [PM00457] = 1 THEN
> [PM00465] ELSE [PM00463] END ) ,10 ) } [PM00466] FROM
> DBO.[S1233769547N1268698629]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1177270046] AS SELECT
> [S1233769547N1213049039].* , CASE WHEN [PM00464] = 0 THEN CONVERT (
NUMERIC
> ( 20 ) ,NULL ) ELSE { fn ROUND ( [PM00463] / [PM00464] ,0 ) } END
[PM00467]
> FROM DBO.[S1233769547N1213049039]
>
> Final Statement to Create the Table: -- ( this never complete after 60
> hours)
> SELECT [F1] , [F39] , [F40] , [F41] , [F42] , [PM00464] [D192] , [PM00459]
> [D185] , [PM00457] [D186] , [PM00467] [D198] , [PM00461] [D193] ,
> [FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [FG00456_004] [D188_4]
,
> [FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [FG00456_017]
[D188_17]
> , [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [FG00456_006] [D188_6]
,
> [FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [FG00456_008] [D188_8]
,
> [FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG00456_023] [D188_23]
,
> [FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG00456_015] [D188_15]
,
> [FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG00456_018] [D188_18]
,
> [FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [FG00456_016]
[D188_16]
> , [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D184] , [PM00460] [D187]
,
> [PM00466] [D167] INTO DBO.[S389577726N118115039] FROM
> DBO.[S1233769547N1177270046]
>
>
> Below the estimated execution Plan:
> |--Table Insert(OBJECT[S1387535081666676858]),
> SET[S1387535081666676858].[D167]=[Expr1022],
> [S1387535081666676858].[D187]=[S138753508748329346].[PM00460],
> [S1387535081666676858].[D184]=[S138753508748329346].[PM00458],
> [S1387535081666676858].[D1]=[Expr1018],
> [S1387535081666676858].[D166]=[Expr1021],
> [S1387535081666676858].[D188_16]=[S138753508748329346].[FG00456_016],
> [S1387535081666676858].[D188_12]=[S138753508748329346].[FG00456_012],
> [S1387535081666676858].[D188_22]=[S138753508748329346].[FG00456_022],
> [S1387535081666676858].[D188_18]=[S138753508748329346].[FG00456_018],
> [S1387535081666676858].[D188_20]=[S138753508748329346].[FG00456_020],
> [S1387535081666676858].[D188_1]=[S138753508748329346].[FG00456_001],
> [S1387535081666676858].[D188_15]=[S138753508748329346].[FG00456_015],
> [S1387535081666676858].[D188_11]=[S138753508748329346].[FG00456_011],
> [S1387535081666676858].[D188_7]=[S138753508748329346].[FG00456_007],
> [S1387535081666676858].[D188_23]=[S138753508748329346].[FG00456_023],
> [S1387535081666676858].[D188_5]=[S138753508748329346].[FG00456_005],
> [S1387535081666676858].[D188_9]=[S138753508748329346].[FG00456_009],
> [S1387535081666676858].[D188_8]=[S138753508748329346].[FG00456_008],
> [S1387535081666676858].[D188_24]=[S138753508748329346].[FG00456_024],
> [S1387535081666676858].[D188_14]=[S138753508748329346].[FG00456_014],
> [S1387535081666676858].[D188_6]=[S138753508748329346].[FG00456_006],
> [S1387535081666676858].[D188_2]=[S138753508748329346].[FG00456_002],
> [S1387535081666676858].[D188_3]=[S138753508748329346].[FG00456_003],
> [S1387535081666676858].[D188_17]=[S138753508748329346].[FG00456_017],
> [S1387535081666676858].[D188_21]=[S138753508748329346].[FG00456_021],
> [S1387535081666676858].[D188_10]=[S138753508748329346].[FG00456_010],
> [S1387535081666676858].[D188_4]=[S138753508748329346].[FG00456_004],
> [S1387535081666676858].[D188_13]=[S138753508748329346].[FG00456_013],
> [S1387535081666676858].[D188_19]=[S138753508748329346].[FG00456_019],
> [S1387535081666676858].[D198]=[Expr1020],
> [S1387535081666676858].[D186]=[S138753508748329346].[PM00457],
> [S1387535081666676858].[D192]=[Expr1019],
> [S1387535081666676858].[F41]=[S138753508748329346].[F41],
> [S1387535081666676858].[F1]=[S138753508748329346].[F1],
> [S1387535081666676858].[F39]=[S138753508748329346].[F39],
> [S1387535081666676858].[F40]=[S138753508748329346].[F40],
> [S1387535081666676858].[F42]=[S138753508748329346].[F42],
> [S1387535081666676858].[D185]=[S138753508748329346].[PM00459],
> [S1387535081666676858].[D193]=[Expr1002]))
> |--Top(ROWCOUNT est 0)
> |--Compute Scalar(DEFINE[Expr1019]=If ('May 1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), [Expr1020]=If (If
> ('May 1 2004 12:00AM'>[S138753508748329346].[F41]) then If
(datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))=0) then NULL else
> round([Expr1018]/Convert(If ('May 1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))), 0, NULL),
> [Expr1021]=round(110*If ('May 1 2004
12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1)
else
> ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL),
> [Expr1022]=round(If (Convert([S138753508748329346].[PM00457])=1) then
> Convert(round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1)
else
> ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then
datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL)) else
> [Expr1018], 10, NULL)))
> |--Table Spool
> |--Compute
> Scalar(DEFINE[Expr1018]=isnull([Expr1016], 0.000)))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES[S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39]))
> |--Compute
>
Scalar(DEFINE[Expr1002]=[dbo].[SC_GREATEST_CHAR](Convert([S138753508748329
> 346].[F39]), Convert([S138753508748329346].[F40]), DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)))
> | |--Nested Loops(Inner Join,
> WHERE[S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES[S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
> | |--Table
> Scan(OBJECT[SynComp_MOP].[dbo].[S138753508748329346]))
> | |--Row Count Spool
> | |--Constant Scan
> |--Hash Match(Cache,
> HASH[S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39], Convert(char(0)), Convert(char(0))),
> RESIDUAL((((([S138753508748329346].[F42]=[S138753508748329346].[F42] AND
> [S138753508748329346].[F41]=[S138753508748329346].[F41]) AND
> [S138753508748329346].[F40]=[S138753508748329346].[F40]) AND
> [S138753508748329346].[F1]=[S138753508748329346].[F1]) AND
> [S138753508748329346].[F39]=[S138753508748329346].[F39]) AND
> [ConstExpr1073]=Convert(char(0))) AND [ConstExpr1074]=Convert(char(0))))
> |--Compute
Scalar(DEFINE[Expr1016]=If
> ([Expr1151]=0) then NULL else [Expr1152]))
> |--Stream
> Aggregate(DEFINE[Expr1151]=COUNT_BIG([S138753508748329346].[PM00458]),
> [Expr1152]=SUM([S138753508748329346].[PM00458])))
> |--Nested Loops(Inner Join,
> WHERE[S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES[S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
> |--Filter(WHEREisnull([S138753508748329346].[F40],
> Convert(char(0)))>[S138753508748329346].[F40] OR
> (isnull([S138753508748329346].[F40],
> Convert(char(0)))=[S138753508748329346].[F40] AND
>
[dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMP
>
TYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S1387535087
>
48329346].[F41])+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+
>
[S138753508748329346].[F42]))+[S138753508748329346].[F39]))<=[dbo].[EMPTYVAL
>
UEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](
>
Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S138753508748329346].[F41]
> )+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+[S13875350874
83
> 29346].[F42]))+[S138753508748329346].[F39])))))
> | |--Index
> Spool(SEEK[S138753508748329346].[F39]=[S138753508748329346].[F39]))
> | |--Table
> Scan(OBJECT[SynComp_MOP].[dbo].[S138753508748329346]))
> |--Row Count Spool
> |--Constant Scan
>
|||The latest copy of the SQL Server Newsletter from http://www.sqlmag.com contains a good discussion on enstec views and what to be wary of when using them. I received my newsletter (via email) about 12 hours ago. I don't have it handy though unfortunately
but you should be able to find it by going to the website.
Regards
Jamie
"Abraham" wrote:

> In Our application there is a method which contain a series ( around 7 ) of
> functions ( we can think it as sql queries).
> Earlier we use to create one table for each fuction out put and use it as
> the input to next function. ( we use simple recovery model)
> Things were working at that time , but to improve performance ( reduce I/O )
> we decided to create views as the ouput of each fuction and use it as the
> input of next funtion and so on . At last we create a table from the last
> view created.Actually we only need this table( out put of whole method ) for
> future use.
> But when we try to create the table from the last view, it just take hours
> and hours to complete(?) .( never completed even after 60 hours).
> Earlier in the table creation approach entire method use to complete in 16
> hours.
> I'm not sure how sql sever resolve the query when we use the last view .Is
> there any way we can see how optimizer convert whole thing into a query.
> What could be the reason for this method not working.?Is there any reason
> nested views performance degrade when level increases?
> Any help really appreciated .
> Thanks
> Binu
> Below the queries I use.
> table T123456_2004_JAN has got 5,000,000 records.
> CREATE VIEW [dbo].[S1233769547736706426] AS SELECT [S3895777261424671822].*
> FROM T123456_2004_JAN
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547767957187] AS SELECT [S1233769547736706426].*
> ,dbo . SC_GREATEST_CHAR ( [F39] , [F40] , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT ,
> DEFAULT , DEFAULT , DEFAULT ) [PM00461] FROM DBO.[S1233769547736706426]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547824202597] AS SELECT [A].* , CASE WHEN
> [A].[PM00458] IS NULL THEN CONVERT ( NUMERIC ( 15 ) ,NULL ) ELSE ( SELECT
> COUNT ( * ) + 1 [RANK] FROM DBO.[S1233769547767957187] [B] WHERE ( (
> [B].[PM00458] > [A].[PM00458] ) OR ( [B].[PM00458] = [A].[PM00458] AND
> [B].[PM00460] > [A].[PM00460] ) ) AND [B].[F39] = [A].[F39] ) END [PM00462]
> FROM DBO.[S1233769547767957187] [A]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547879852187] AS SELECT [A].* , ( SELECT { fn
> IFNULL ( SUM ( [B].[PM00458] ) ,0 ) } FROM DBO.[S1233769547824202597] [B]
> WHERE [A].[F39] = [B].[F39] AND ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) } > {
> fn IFNULL ( [B].[F40] ,CHAR(0) ) } OR ( { fn IFNULL ( [A].[F40] ,CHAR(0) ) }
> = { fn IFNULL ( [B].[F40] ,CHAR(0) ) } AND
> dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPT YVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[A].[F41] , 101 ) + [A].[F40] ) + [A].[F1] ) +
> [A].[F42] ) + [A].[F39] )<=
> dbo.EMPTYVALUEHANDLE(dbo.EMPTYVALUEHANDLE(dbo.EMPT YVALUEHANDLE(dbo.EMPTYVALU
> EHANDLE( CONVERT ( VARCHAR ,[B].[F41] , 101 ) + [B].[F40] ) + [B].[F1] ) +
> [B].[F42] ) + [B].[F39] )) ) ) [PM00463] FROM DBO.[S1233769547824202597] [A]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547912175445] AS SELECT [S1233769547879852187].*
> ,( CASE WHEN '2004-05-01' > [F41] THEN ( CASE WHEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } = 0 THEN 0 ELSE ( CASE WHEN DATEPART
> ( DD ,[F41] ) <= DATEPART ( DD ,'2004-05-01' ) THEN { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } ELSE { fn TIMESTAMPDIFF (
> SQL_TSI_MONTH,[F41] ,'2004-05-01' ) } - 1 END ) END ) ELSE -1 * ( CASE WHEN
> { fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } = 0 THEN 0 ELSE
> ( CASE WHEN DATEPART ( DD ,'2004-05-01' ) <= DATEPART ( DD ,[F41] ) THEN {
> fn TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } ELSE { fn
> TIMESTAMPDIFF ( SQL_TSI_MONTH,'2004-05-01' ,[F41] ) } - 1 END ) END ) END )
> [PM00464] FROM DBO.[S1233769547879852187]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1268698629] AS SELECT
> [S1233769547912175445].* , { fn ROUND ( 110 * [PM00464] ,10 ) } [PM00465]
> FROM DBO.[S1233769547912175445]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1213049039] AS SELECT
> [S1233769547N1268698629].* , { fn ROUND ( ( CASE WHEN [PM00457] = 1 THEN
> [PM00465] ELSE [PM00463] END ) ,10 ) } [PM00466] FROM
> DBO.[S1233769547N1268698629]
> --Earlier below statement is an -- select into statement ....
> CREATE VIEW [dbo].[S1233769547N1177270046] AS SELECT
> [S1233769547N1213049039].* , CASE WHEN [PM00464] = 0 THEN CONVERT ( NUMERIC
> ( 20 ) ,NULL ) ELSE { fn ROUND ( [PM00463] / [PM00464] ,0 ) } END [PM00467]
> FROM DBO.[S1233769547N1213049039]
>
> Final Statement to Create the Table: -- ( this never complete after 60
> hours)
> SELECT [F1] , [F39] , [F40] , [F41] , [F42] , [PM00464] [D192] , [PM00459]
> [D185] , [PM00457] [D186] , [PM00467] [D198] , [PM00461] [D193] ,
> [FG00456_019] [D188_19] , [FG00456_013] [D188_13] , [FG00456_004] [D188_4] ,
> [FG00456_010] [D188_10] , [FG00456_021] [D188_21] , [FG00456_017] [D188_17]
> , [FG00456_003] [D188_3] , [FG00456_002] [D188_2] , [FG00456_006] [D188_6] ,
> [FG00456_014] [D188_14] , [FG00456_024] [D188_24] , [FG00456_008] [D188_8] ,
> [FG00456_009] [D188_9] , [FG00456_005] [D188_5] , [FG00456_023] [D188_23] ,
> [FG00456_007] [D188_7] , [FG00456_011] [D188_11] , [FG00456_015] [D188_15] ,
> [FG00456_001] [D188_1] , [FG00456_020] [D188_20] , [FG00456_018] [D188_18] ,
> [FG00456_022] [D188_22] , [FG00456_012] [D188_12] , [FG00456_016] [D188_16]
> , [PM00465] [D166] , [PM00463] [D1] , [PM00458] [D184] , [PM00460] [D187] ,
> [PM00466] [D167] INTO DBO.[S389577726N118115039] FROM
> DBO.[S1233769547N1177270046]
>
>
> Below the estimated execution Plan:
> |--Table Insert(OBJECT[S1387535081666676858]),
> SET[S1387535081666676858].[D167]=[Expr1022],
> [S1387535081666676858].[D187]=[S138753508748329346].[PM00460],
> [S1387535081666676858].[D184]=[S138753508748329346].[PM00458],
> [S1387535081666676858].[D1]=[Expr1018],
> [S1387535081666676858].[D166]=[Expr1021],
> [S1387535081666676858].[D188_16]=[S138753508748329346].[FG00456_016],
> [S1387535081666676858].[D188_12]=[S138753508748329346].[FG00456_012],
> [S1387535081666676858].[D188_22]=[S138753508748329346].[FG00456_022],
> [S1387535081666676858].[D188_18]=[S138753508748329346].[FG00456_018],
> [S1387535081666676858].[D188_20]=[S138753508748329346].[FG00456_020],
> [S1387535081666676858].[D188_1]=[S138753508748329346].[FG00456_001],
> [S1387535081666676858].[D188_15]=[S138753508748329346].[FG00456_015],
> [S1387535081666676858].[D188_11]=[S138753508748329346].[FG00456_011],
> [S1387535081666676858].[D188_7]=[S138753508748329346].[FG00456_007],
> [S1387535081666676858].[D188_23]=[S138753508748329346].[FG00456_023],
> [S1387535081666676858].[D188_5]=[S138753508748329346].[FG00456_005],
> [S1387535081666676858].[D188_9]=[S138753508748329346].[FG00456_009],
> [S1387535081666676858].[D188_8]=[S138753508748329346].[FG00456_008],
> [S1387535081666676858].[D188_24]=[S138753508748329346].[FG00456_024],
> [S1387535081666676858].[D188_14]=[S138753508748329346].[FG00456_014],
> [S1387535081666676858].[D188_6]=[S138753508748329346].[FG00456_006],
> [S1387535081666676858].[D188_2]=[S138753508748329346].[FG00456_002],
> [S1387535081666676858].[D188_3]=[S138753508748329346].[FG00456_003],
> [S1387535081666676858].[D188_17]=[S138753508748329346].[FG00456_017],
> [S1387535081666676858].[D188_21]=[S138753508748329346].[FG00456_021],
> [S1387535081666676858].[D188_10]=[S138753508748329346].[FG00456_010],
> [S1387535081666676858].[D188_4]=[S138753508748329346].[FG00456_004],
> [S1387535081666676858].[D188_13]=[S138753508748329346].[FG00456_013],
> [S1387535081666676858].[D188_19]=[S138753508748329346].[FG00456_019],
> [S1387535081666676858].[D198]=[Expr1020],
> [S1387535081666676858].[D186]=[S138753508748329346].[PM00457],
> [S1387535081666676858].[D192]=[Expr1019],
> [S1387535081666676858].[F41]=[S138753508748329346].[F41],
> [S1387535081666676858].[F1]=[S138753508748329346].[F1],
> [S1387535081666676858].[F39]=[S138753508748329346].[F39],
> [S1387535081666676858].[F40]=[S138753508748329346].[F40],
> [S1387535081666676858].[F42]=[S138753508748329346].[F42],
> [S1387535081666676858].[D185]=[S138753508748329346].[PM00459],
> [S1387535081666676858].[D193]=[Expr1002]))
> |--Top(ROWCOUNT est 0)
> |--Compute Scalar(DEFINE[Expr1019]=If ('May 1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), [Expr1020]=If (If
> ('May 1 2004 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))=0) then NULL else
> round([Expr1018]/Convert(If ('May 1 2004
> 12:00AM'>[S138753508748329346].[F41]) then If (datediff(month,
> [S138753508748329346].[F41], 'May 1 2004 12:00AM')=0) then 0 else If
> (datepart(day, [S138753508748329346].[F41])<=datepart(day, 'May 1 2004
> 12:00AM')) then datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM') else (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')-1) else ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1))), 0, NULL),
> [Expr1021]=round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1) else
> ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL),
> [Expr1022]=round(If (Convert([S138753508748329346].[PM00457])=1) then
> Convert(round(110*If ('May 1 2004 12:00AM'>[S138753508748329346].[F41])
> then If (datediff(month, [S138753508748329346].[F41], 'May 1 2004
> 12:00AM')=0) then 0 else If (datepart(day,
> [S138753508748329346].[F41])<=datepart(day, 'May 1 2004 12:00AM')) then
> datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM') else
> (datediff(month, [S138753508748329346].[F41], 'May 1 2004 12:00AM')-1) else
> ( -1*If (datediff(month, 'May 1 2004 12:00AM',
> [S138753508748329346].[F41])=0) then 0 else If (datepart(day, 'May 1 2004
> 12:00AM')<=datepart(day, [S138753508748329346].[F41])) then datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41]) else (datediff(month,
> 'May 1 2004 12:00AM', [S138753508748329346].[F41])-1)), 10, NULL)) else
> [Expr1018], 10, NULL)))
> |--Table Spool
> |--Compute
> Scalar(DEFINE[Expr1018]=isnull([Expr1016], 0.000)))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES[S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39]))
> |--Compute
> Scalar(DEFINE[Expr1002]=[dbo].[SC_GREATEST_CHAR](Convert([S138753508748329
> 346].[F39]), Convert([S138753508748329346].[F40]), DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
> DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)))
> | |--Nested Loops(Inner Join,
> WHERE[S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES[S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
> | |--Table
> Scan(OBJECT[SynComp_MOP].[dbo].[S138753508748329346]))
> | |--Row Count Spool
> | |--Constant Scan
> |--Hash Match(Cache,
> HASH[S138753508748329346].[F42], [S138753508748329346].[F41],
> [S138753508748329346].[F40], [S138753508748329346].[F1],
> [S138753508748329346].[F39], Convert(char(0)), Convert(char(0))),
> RESIDUAL((((([S138753508748329346].[F42]=[S138753508748329346].[F42] AND
> [S138753508748329346].[F41]=[S138753508748329346].[F41]) AND
> [S138753508748329346].[F40]=[S138753508748329346].[F40]) AND
> [S138753508748329346].[F1]=[S138753508748329346].[F1]) AND
> [S138753508748329346].[F39]=[S138753508748329346].[F39]) AND
> [ConstExpr1073]=Convert(char(0))) AND [ConstExpr1074]=Convert(char(0))))
> |--Compute Scalar(DEFINE[Expr1016]=If
> ([Expr1151]=0) then NULL else [Expr1152]))
> |--Stream
> Aggregate(DEFINE[Expr1151]=COUNT_BIG([S138753508748329346].[PM00458]),
> [Expr1152]=SUM([S138753508748329346].[PM00458])))
> |--Nested Loops(Inner Join,
> WHERE[S138753508748329346].[PM00458]=NULL)OUTER
> REFERENCES[S138753508748329346].[F39], [S138753508748329346].[PM00460],
> [S138753508748329346].[PM00458]))
> |--Filter(WHEREisnull([S138753508748329346].[F40],
> Convert(char(0)))>[S138753508748329346].[F40] OR
> (isnull([S138753508748329346].[F40],
> Convert(char(0)))=[S138753508748329346].[F40] AND
> [dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMP
> TYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S1387535087
> 48329346].[F41])+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+
> [S138753508748329346].[F42]))+[S138753508748329346].[F39]))<=[dbo].[EMPTYVAL
> UEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](Convert([dbo].[EMPTYVALUEHANDLE](
> Convert([dbo].[EMPTYVALUEHANDLE](Convert(Convert([S138753508748329346].[F41]
> )+[S138753508748329346].[F40]))+[S138753508748329346].[F1]))+[S1387535087483
> 29346].[F42]))+[S138753508748329346].[F39])))))
> | |--Index
> Spool(SEEK[S138753508748329346].[F39]=[S138753508748329346].[F39]))
> | |--Table
> Scan(OBJECT[SynComp_MOP].[dbo].[S138753508748329346]))
> |--Row Count Spool
> |--Constant Scan
>
>