OLAP and/or data mining?

Hello,

If I wrote the next ebay (yes I know, yawn-snore) and I had a database
with 5 million auction items in it, what would be a really good
strategy to get a search done very quickly? Would it involve
something called OLAP and/or "data mining"? The only technology I am
familiar with is simply SQL Server databases with stored procedures.
I think I'd be guessing correctly and say that this technology simply
wouldn't be fast enough *on it's own* to do super fast queries against
massive amounts of data.

Any insights would be of great interest. Thanks.

-Frameworker."Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> Hello,
> If I wrote the next ebay (yes I know, yawn-snore) and I had a database
> with 5 million auction items in it, what would be a really good
> strategy to get a search done very quickly? Would it involve
> something called OLAP and/or "data mining"? The only technology I am
> familiar with is simply SQL Server databases with stored procedures.
> I think I'd be guessing correctly and say that this technology simply
> wouldn't be fast enough *on it's own* to do super fast queries against
> massive amounts of data.

OLAP is exactly what you'd want.

Data mining may be useful post-auction to see who bought what, what things
need more promotion etc.

I don't know why you don't think SQL Server wouldn't be fast enough for a
small setup like 5 million auctions.

I have a database with 14 million rows that's probably as complex as an
auction database and most queries are a couple of seconds or less. (some
are longer because of some fairly complex floating point math that has to be
done along the way.)

I have another database that handles at least that many inserts per day w/o
a major problem. And this is all on 3+ y.o. equipment.

Give me a quad Xeon MP box and the right disk subsystem and this thing could
scream.

> Any insights would be of great interest. Thanks.
> -Frameworker.|||"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:fCNFb.56190$Ug6.2898@.twister.nyroc.rr.com...
> "Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
> news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> > Hello,
> > If I wrote the next ebay (yes I know, yawn-snore) and I had a database
> > with 5 million auction items in it, what would be a really good
> > strategy to get a search done very quickly? Would it involve
> > something called OLAP and/or "data mining"? The only technology I am
> > familiar with is simply SQL Server databases with stored procedures.

Using stored procedures as component building blocks
you can create all forms of data mining and analytical
processing applications.

> > I think I'd be guessing correctly and say that this technology simply
> > wouldn't be fast enough *on it's own* to do super fast queries against
> > massive amounts of data.
> OLAP is exactly what you'd want.
> Data mining may be useful post-auction to see who bought what, what things
> need more promotion etc.
> I don't know why you don't think SQL Server wouldn't be fast enough for a
> small setup like 5 million auctions.

Seconded.

If you write your procs in the right manner, and maintain
the data integrity in your database then you should have
null performance problems.

> I have a database with 14 million rows that's probably as complex as an
> auction database and most queries are a couple of seconds or less. (some
> are longer because of some fairly complex floating point math that has to
be
> done along the way.)
> I have another database that handles at least that many inserts per day
w/o
> a major problem. And this is all on 3+ y.o. equipment.
> Give me a quad Xeon MP box and the right disk subsystem and this thing
could
> scream.
>
> > Any insights would be of great interest. Thanks.
> > -Frameworker.|||Hi Greg,

I have one follow up question please:

I'd like to incorporate OLAP in to my design. Do I go ahead and
create a sensibly normalised relational database design, with many
dozens of stored procedures to query the tables in "the usual manner",
*then* add on OLAP as a kind of "bolt on", or does OLAP technology
require a totally different database/stored procedures design strategy
right from the word go?

Thanks!

- Frameworker.

"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<fCNFb.56190$Ug6.2898@.twister.nyroc.rr.com>...
> "Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
> news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> > Hello,
> > If I wrote the next ebay (yes I know, yawn-snore) and I had a database
> > with 5 million auction items in it, what would be a really good
> > strategy to get a search done very quickly? Would it involve
> > something called OLAP and/or "data mining"? The only technology I am
> > familiar with is simply SQL Server databases with stored procedures.
> > I think I'd be guessing correctly and say that this technology simply
> > wouldn't be fast enough *on it's own* to do super fast queries against
> > massive amounts of data.
> OLAP is exactly what you'd want.
> Data mining may be useful post-auction to see who bought what, what things
> need more promotion etc.
> I don't know why you don't think SQL Server wouldn't be fast enough for a
> small setup like 5 million auctions.
> I have a database with 14 million rows that's probably as complex as an
> auction database and most queries are a couple of seconds or less. (some
> are longer because of some fairly complex floating point math that has to be
> done along the way.)
> I have another database that handles at least that many inserts per day w/o
> a major problem. And this is all on 3+ y.o. equipment.
> Give me a quad Xeon MP box and the right disk subsystem and this thing could
> scream.
>
> > Any insights would be of great interest. Thanks.
> > -Frameworker.|||I'm a speed freak! If I can get a query to run .25 seconds faster
using OLAP technology, I'm all for it. :o)

"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message news:<f3WFb.62838$aT.6089@.news-server.bigpond.net.au>...
> "Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
> news:fCNFb.56190$Ug6.2898@.twister.nyroc.rr.com...
> > "Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
> > news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> > > Hello,
> > > > If I wrote the next ebay (yes I know, yawn-snore) and I had a database
> > > with 5 million auction items in it, what would be a really good
> > > strategy to get a search done very quickly? Would it involve
> > > something called OLAP and/or "data mining"? The only technology I am
> > > familiar with is simply SQL Server databases with stored procedures.
>
> Using stored procedures as component building blocks
> you can create all forms of data mining and analytical
> processing applications.
>
> > > I think I'd be guessing correctly and say that this technology simply
> > > wouldn't be fast enough *on it's own* to do super fast queries against
> > > massive amounts of data.
> > OLAP is exactly what you'd want.
> > Data mining may be useful post-auction to see who bought what, what things
> > need more promotion etc.
> > I don't know why you don't think SQL Server wouldn't be fast enough for a
> > small setup like 5 million auctions.
>
> Seconded.
> If you write your procs in the right manner, and maintain
> the data integrity in your database then you should have
> null performance problems.
>
> > I have a database with 14 million rows that's probably as complex as an
> > auction database and most queries are a couple of seconds or less. (some
> > are longer because of some fairly complex floating point math that has to
> be
> > done along the way.)
> > I have another database that handles at least that many inserts per day
> w/o
> > a major problem. And this is all on 3+ y.o. equipment.
> > Give me a quad Xeon MP box and the right disk subsystem and this thing
> could
> > scream.
> > > > Any insights would be of great interest. Thanks.
> > > > -Frameworker.|||"Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
news:f109ac80.0312240543.63e644ac@.posting.google.c om...
> I'm a speed freak! If I can get a query to run .25 seconds faster
> using OLAP technology, I'm all for it. :o)

I think you completely misunderstand what OLAP is or does. (And I mispoke
below (forgive me, I was tired). You want an OLTP system, not an OLAP.

OLAP and getting better performance are orthogonal to each other. i.e.
using OLAP is not a way to increase performance.

>
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:<f3WFb.62838$aT.6089@.news-server.bigpond.net.au>...
> > "Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
> > news:fCNFb.56190$Ug6.2898@.twister.nyroc.rr.com...
> > > > "Framework fan" <tempframeworkfan@.hotmail.com> wrote in message
> > > news:f109ac80.0312221359.2269c2f0@.posting.google.c om...
> > > > Hello,
> > > > > > If I wrote the next ebay (yes I know, yawn-snore) and I had a
database
> > > > with 5 million auction items in it, what would be a really good
> > > > strategy to get a search done very quickly? Would it involve
> > > > something called OLAP and/or "data mining"? The only technology I
am
> > > > familiar with is simply SQL Server databases with stored procedures.
> > Using stored procedures as component building blocks
> > you can create all forms of data mining and analytical
> > processing applications.
> > > > I think I'd be guessing correctly and say that this technology
simply
> > > > wouldn't be fast enough *on it's own* to do super fast queries
against
> > > > massive amounts of data.
> > > > OLAP is exactly what you'd want.
> > > > Data mining may be useful post-auction to see who bought what, what
things
> > > need more promotion etc.
> > > > I don't know why you don't think SQL Server wouldn't be fast enough
for a
> > > small setup like 5 million auctions.
> > Seconded.
> > If you write your procs in the right manner, and maintain
> > the data integrity in your database then you should have
> > null performance problems.
> > > I have a database with 14 million rows that's probably as complex as
an
> > > auction database and most queries are a couple of seconds or less.
(some
> > > are longer because of some fairly complex floating point math that has
to
> > be
> > > done along the way.)
> > > > I have another database that handles at least that many inserts per
day
> > w/o
> > > a major problem. And this is all on 3+ y.o. equipment.
> > > > Give me a quad Xeon MP box and the right disk subsystem and this thing
> > could
> > > scream.
> > > > > > > > Any insights would be of great interest. Thanks.
> > > > > > -Frameworker.

Odd query plan for view

I have a SQL 2000 table containing 2 million rows of Trade data. Here
are some of the columns:

[TradeId] INT IDENTITY(1,1) -- PK, non-clustered
[LoadDate] DATETIME -- clustered index
[TradeDate] DATETIME -- non-clustered index
[Symbol] VARCHAR(10)
[Account] VARCHAR(10)
[Position] INT
etc..

I have a view which performs a join against a security master table (to
gather more security data). The purpose of the view is to return all
the rows where [TradeDate] is within the last trading days.

The query against the view takes over around 30 minutes. When I view
the query plan, it is not using the index on the [TradeDate] column but
is instead using the clustered index on the [LoadDate] column... The
odd thing is, the [LoadDate] column is not used anywhere in the view!

For testing purposes, I decided to do a straight SELECT against the
table (minus the joins) and that one ALSO uses the clustered index scan
against a column not referenced anywhere in the query.

There is a reason why I have not posted my WHERE clause until now. The
reason is that I am doing what I think is a very inefficient clause:

WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())

The function calculates the proper trade date based on the specified
date (in this case, the current date). It is my understanding that the
function will be called for all rows. (Which COULD explain the
performance issue...)

However, this view has been around for ages and never before caused any
sort of problems. The issue actually started the day after I had to
recreate the table. (I had to recreate the table because some columns
where added and others where renamed.)

On a side note, if I replace the WHERE clause with a hard-coded date
(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine but
STILL uses the clustered index on the [LoadDate] column.(JayCallas@.hotmail.com) writes:
> The query against the view takes over around 30 minutes. When I view
> the query plan, it is not using the index on the [TradeDate] column but
> is instead using the clustered index on the [LoadDate] column... The
> odd thing is, the [LoadDate] column is not used anywhere in the view!

But "Clustered index scan" is just the same as "Table Scan". So it is
not very strange. No non-clustered index was good, so it scans the
index.

> There is a reason why I have not posted my WHERE clause until now. The
> reason is that I am doing what I think is a very inefficient clause:
> WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())
> However, this view has been around for ages and never before caused any
> sort of problems. The issue actually started the day after I had to
> recreate the table. (I had to recreate the table because some columns
> where added and others where renamed.)

Statistics change, and old plan was not good any more. Yes, the above
is a problematic condition. Don't you read this newsgroup? :-) I
answered a very similar question last night.

You know something about the data that the optimizer does not. It
sees:

WHERE TradeDate > <UnknownValue
It estimates that it will hit 30% of the rows, a standard assumption.
And for 30% hit-rate a non-clustered index will be more expensive
than scanning the table.

This may be the place for an index hint See also the thread
"ranged datetime predicates & cardinality estimates" from yeaterday.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland for responding. As usual your posts are very helpful.

So, a Clustered Index Scan is as bad as a Table Scan? In this case,
what is it actually checking? The LoadDate is not even used in the
query so I am not sure what it is scanning for. Is it just using the
scan to look up the rest of the row?

As I already knew that the particular WHERE clause was bad, was I just
lucky all this time that the response time was good? Maybe because
there had not been any changes to the view or table in ages? Or maybe
because the plan was determined when there was a lot less data in the
table? Do query plans survive server reboots or restarting SQL?

Will take a look at the index hint any see how it goes.|||(JayCallas@.hotmail.com) writes:
> So, a Clustered Index Scan is as bad as a Table Scan? In this case,
> what is it actually checking? The LoadDate is not even used in the
> query so I am not sure what it is scanning for. Is it just using the
> scan to look up the rest of the row?

Let's say that you need to look up Michael Richardson in the telephone
book. Of course you open the book on R and quickly find him. You are seeking
the clustered index.

But say now that you are looking for someone whose first name is Jake,
and that he lives on Smallstreet, and you really need to find him. What
do you do? You read the phone book from start to end, that is you scan
the clustred index. The LastName, which is the key in the index is not
part of the search, but that is irrelevant.

> As I already knew that the particular WHERE clause was bad, was I just
> lucky all this time that the response time was good?

Bad is a little too strong a word. Problematic is more accurate.

The problem with a non-clustered index, is that if you get many hits,
and you for every hit you need to access the data page, you will do more
reads that you do, if you just scan the table from left to right.

>Do query plans survive server reboots or restarting SQL?

No. The plan is in cache only, and could also disappear during run-time,
if the plan is aged out.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The hint worked. Thanks.

Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.
This seems to suggest parameter sniffing:
http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=en&ie=UTF-8&oe=UTF-8&
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegro ups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.
|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegro ups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.
|||Some resources:
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032276813&CountryCode=US
[url]http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032270016&EventC ategory=3&culture=en-US&CountryCode=US[/url]
http://download.microsoft.com/download/4/1/f/41f09116-19b3-40fe-9d54-d1e9b7af9e82/REAL_Lifecycle_Partitioning.doc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegro ups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.

Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.This seems to suggest parameter sniffing:
http://groups.google.ca/groups/sear...
=UTF-8&
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegroups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/20...procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/sear...fing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegroups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/20...es.html

The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/sear...fing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.|||Some resources:
http://msevents.microsoft.com/CUI/W...&CountryCode=US
http://msevents.microsoft.com/CUI/W...&CountryCode=US
http://download.microsoft.com/downl...artitioning.doc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegroups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.

Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.This seems to suggest parameter sniffing:
http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=en&ie=UTF-8&oe=UTF-8&
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegroups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegroups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.|||Some resources:
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032276813&CountryCode=US
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032270016&EventCategory=3&culture=en-US&CountryCode=US
http://download.microsoft.com/download/4/1/f/41f09116-19b3-40fe-9d54-d1e9b7af9e82/REAL_Lifecycle_Partitioning.doc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegroups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.