Prev: Syntax question in stored procedure
Next: freetext problem
From: Jeff on 13 Oct 2009 18:39
Can't. I am using The ROW_NUMBER() , RANK() and DENSE_RANK (0
function. I lose that information if I query for a Speecfic item.
THatr why I have been doing the iteration but they take a while since
I am running many simulations


A difficult problem

On Tue, 13 Oct 2009 12:19:22 +0100, "Paul"
<paulriley(a)novareconsulting.com> wrote:

>Insert into a temporary table with the addition of an identity column. Then
>use this table as the basis for your query the identity columnh will
>identify record/row position.
>
>There are numerous examples of doing this on the web for Paging large
>amounts of data.
>
>
>
><Jeff(a)google.com> wrote in message
>news:5d05d5dh7mehsgn45l8af672vbeufienlf(a)4ax.com...
>>I have a rather large database that I am datamining. I am doing
>> numerous SQL sorts to determine which methodolgy works best. I would
>> like to determine the new "record position" of a particular record in
>> the group. Currently I am doing it programtically by itering through
>> the records till I find the particular record. This can be time
>> consuming given the size of the database
>>
>> Is there a better method?
>>
>

From: --CELKO-- on 13 Oct 2009 19:57
>> I have a rather large database that I am data mining. <<

How many petabytes? SQL Server is not in that game; you ought be
using Teradata, KX, WX2, etc.

>> I am doing numerous SQL sorts to determine which methodology works best..<<

methodology is the study of methods. so this sentence is strange at
best. Sorting is a file system concept; SQL is based on sets, which
have no ordering.

>> I would like to determine the new "record position" of a particular record [sic] in the group. <<

THE group? What does that mean? Since SQL uses rows and not records
and has no concept of a position, this is getting worse.

>> Currently I am doing it programmatic by iterating [no such concept in declarative languages] through the records [sic] till I  find the particular record [sic] .  <

Records and iteration are procedural language and file system
concepts; they are not part of SQL. We use a KEY to locate a ROW.

There are ordinal functions (ROW_NUMBER(), RANK() and DENSE_RANK())
that sort of like what you might be trying to ask. They are based on
von Neuman's definition of ordinal numbers.

From: --CELKO-- on 13 Oct 2009 19:58
"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

If you don't know anything about RDBMS, then get a copy of the
simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

From: Tony Rogerson on 14 Oct 2009 00:35
> How many petabytes? SQL Server is not in that game; you ought be
> using Teradata, KX, WX2, etc.
>

Absolutely - but GBytes and TBytes SQL Server is in that game as we have
told you before.

Thankfully at the moment there are only a fraction of a percent of companies
requiring data mining on Petabytes of data - if you are doing any real work
in the real world you would know that.

But do tell us; as you are giving opinion on SQL Server's data mining
abilities can you --celko-- tell us what real world experience you have of
that feature of the product - for instance, have you even installed it to
review it or like usual are you just going on your double decade old opinion
on SQL Server?

--ROGGIE--

"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:0b464be0-be1c-43b2-81b7-061a01d0fd75(a)a21g2000yqc.googlegroups.com...
>>> I have a rather large database that I am data mining. <<
>
> How many petabytes? SQL Server is not in that game; you ought be
> using Teradata, KX, WX2, etc.
>
>>> I am doing numerous SQL sorts to determine which methodology works
>>> best.<<
>
> methodology is the study of methods. so this sentence is strange at
> best. Sorting is a file system concept; SQL is based on sets, which
> have no ordering.
>
>>> I would like to determine the new "record position" of a particular
>>> record [sic] in the group. <<
>
> THE group? What does that mean? Since SQL uses rows and not records
> and has no concept of a position, this is getting worse.
>
>>> Currently I am doing it programmatic by iterating [no such concept in
>>> declarative languages] through the records [sic] till I find the
>>> particular record [sic] . <
>
> Records and iteration are procedural language and file system
> concepts; they are not part of SQL. We use a KEY to locate a ROW.
>
> There are ordinal functions (ROW_NUMBER(), RANK() and DENSE_RANK())
> that sort of like what you might be trying to ask. They are based on
> von Neuman's definition of ordinal numbers.
>
From: Jeff on 14 Oct 2009 07:55
It is not the database size (half a million) but the number of
simulations (650,000). Using the current SQL commands (run in Vb.net)
takes about 12 seconds per simulation. By my estimates it will take
over 90 days to run the simulation so I am trying to optimize the
code. Once the data is ORDERed by multiple criteria I need to find
the row_number value for about 100 records. The only way I have been
doing it is with a ExecuteReader and iterating through the list. This
take up a lot of time. If I use a WHERE command on the list I lose
the ROW_NUMBER values.

Any thoughts?




On Tue, 13 Oct 2009 16:57:40 -0700 (PDT), --CELKO--
<jcelko212(a)earthlink.net> wrote:

>>> I have a rather large database that I am data mining. <<
>
>How many petabytes? SQL Server is not in that game; you ought be
>using Teradata, KX, WX2, etc.
>
>>> I am doing numerous SQL sorts to determine which methodology works best.<<
>
>methodology is the study of methods. so this sentence is strange at
>best. Sorting is a file system concept; SQL is based on sets, which
>have no ordering.
>
>>> I would like to determine the new "record position" of a particular record [sic] in the group. <<
>
>THE group? What does that mean? Since SQL uses rows and not records
>and has no concept of a position, this is getting worse.
>
>>> Currently I am doing it programmatic by iterating [no such concept in declarative languages] through the records [sic] till I �find the particular record [sic] . �<
>
> Records and iteration are procedural language and file system
>concepts; they are not part of SQL. We use a KEY to locate a ROW.
>
>There are ordinal functions (ROW_NUMBER(), RANK() and DENSE_RANK())
>that sort of like what you might be trying to ask. They are based on
>von Neuman's definition of ordinal numbers.

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Syntax question in stored procedure
Next: freetext problem