Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Distribute processing for very large queries?

8 posts in Windows NT Last posting was on 2000-02-08 20:32:21.0Z
Cristinel Popescu Posted on 2000-02-06 10:19:29.0Z
Message-ID: <389D4AB1.572F324B@telemobil.ro>
Date: Sun, 06 Feb 2000 12:19:29 +0200
From: Cristinel Popescu <Popescu_Cristinel@telemobil.ro>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: AnaMaria Stirbet <AnaMaria.Stirbet@sybase.com.no.spam>
Subject: Distribute processing for very large queries?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 60
NNTP-Posting-Host: pool-110.suntel-lems.ro 194.102.106.110
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2581
Article PK: 1090033

Does know someone how can I distribute processing for very large queries
like this:
"declare @t1 datetime
declare @t2 datetime
declare @i tinyint
declare @j tinyint
declare @k tinyint
declare @l tinyint
declare @m tinyint
declare @n tinyint
select @i=1
select @j=2
select @k=3
select @l=4
select @m=5
select @n=6
select @nr=0
select @t1=(select convert(char(30), getdate(),109))
while (@i<=44)
begin
select @j=@i+1
while (@j<=45)
begin
select @k=@j+1
while (@k<=46)
begin
select @l=@k+1
while (@l<=47)
begin
select @m=@l+1
while (@m<=48)
begin
select @n=@m+1
while (@n<=49)
begin
if ((select count(*) from extr_out
where (n3=@k and n4=@l and n5=@m and n6=@n) or
(n2=@j and n4=@l and n5=@m and n6=@n) or
(n2=@j and n3=@k and n5=@m and n6=@n) or
(n2=@j and n3=@k and n4=@l and n6=@n) or
(n2=@j and n3=@k and n4=@l and n5=@m) or
(n1=@i and n4=@l and n5=@m and n6=@n) or
(n1=@i and n3=@k and n5=@m and n6=@n) or
(n1=@i and n3=@k and n4=@l and n6=@n) or
(n1=@i and n3=@k and n4=@l and n5=@m) or
(n1=@i and n2=@j and n5=@m and n6=@n) or
(n1=@i and n2=@j and n4=@l and n6=@n) or
(n1=@i and n2=@j and n4=@l and n5=@m) or
(n1=@i and n2=@j and n3=@k and n6=@n) or
(n1=@i and n2=@j and n3=@k and n5=@m) or
(n1=@i and n2=@j and n3=@k and n4=@l))=0)
begin
insert toate_extr
select @i,@j,@k,@l,@m,@n
end
select @n=@n+1
end
select @m=@m+1
end
select @l=@l+1
end
select @k=@k+1
end
select @j=@j+1
end
select @i=@i+1
end
select @t2=(select convert(char(30), getdate(),109))
select datediff (ss,@t1,@t2)"

This query perform calculation check if exists combination of four
numbers which is already in one row of extr_out table and if could not
find any, it insert the new combination in toate_extr table.
When I try to run something like this on Intel Pentium II/450Mhz
server with 384Mb RAM in three hour it generate just few ten thousands
of records. All the time was 100% CPU usage and less than 2% usage for
hardisk.
I observe that if run this with SQL Advantage 100% CPU time usage will
be on the client not on the server -- why? That's mean if I split this,
in multiple sub-queries, and run in the same time I will obtain big
improvements?
From my calculation it will take 16 day to finish?
How can I proceed to finish it in one day or less?


Any help will be appreciated.
Thank you,
Cristinel Popescu


Mark A. Parsons Posted on 2000-02-07 07:38:34.0Z
Message-ID: <389E767A.A4D3FA37@compuserve.com>
Date: Mon, 07 Feb 2000 20:38:34 +1300
From: "Mark A. Parsons" <iron_horse@compuserve.com>
Organization: Pegasys Ltd
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Distribute processing for very large queries?
References: <389D4AB1.572F324B@telemobil.ro>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 49
NNTP-Posting-Host: p329.ipa1-n8-16.iconz.net.nz 210.48.25.73
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2577
Article PK: 1090028


> This query perform calculation check if exists combination of four
> numbers which is already in one row of extr_out table and if could not
> find any, it insert the new combination in toate_extr table.
> When I try to run something like this on Intel Pentium II/450Mhz
> server with 384Mb RAM in three hour it generate just few ten thousands
> of records. All the time was 100% CPU usage and less than 2% usage for
> hardisk.
> I observe that if run this with SQL Advantage 100% CPU time usage will
> be on the client not on the server -- why? That's mean if I split this,
> in multiple sub-queries, and run in the same time I will obtain big
> improvements?
> From my calculation it will take 16 day to finish?
> How can I proceed to finish it in one day or less?
>
> Any help will be appreciated.
> Thank you,
> Cristinel Popescu

First off ... your select against extr_out is incurring a table scan
each time you go against it (with all of the or's the optimizer says
'the heck with it, I am going to table scan'; see your showplan output,
too). If this table is of any appreciable size this means a lot of
I/O's which means slow processing time.

Second ... if you want to split this out into several 'parallel'
operations I'd suggest breaking it down from the outer loop in, e.g.,
for each value of 'i' you run a separate process ... process #1 has i=1,
process #2 has i=2, etc.

Third ... I think you need a different way of looking at your problem.
Could you provide the following:

1) the current size (number of rows) of extr_out
2) the current and expected size (number of rows) of toate_extr
3) a better explanation (plus some examples?) of 'legal' sets of numbers
you expect to find in extr_out, or which you plan to put in toate_extr

I'm sure there's a better way to do this ... but it would help to get
some more info ...


--
Mark Parsons
Pegasys Ltd


Cristinel Popescu Posted on 2000-02-07 09:10:34.0Z
Message-ID: <389E8C0A.E1C678BE@telemobil.ro>
Date: Mon, 07 Feb 2000 11:10:34 +0200
From: Cristinel Popescu <Popescu_Cristinel@telemobil.ro>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: "Mark A. Parsons" <iron_horse@compuserve.com>
Subject: Re: Distribute processing for very large queries?
References: <389D4AB1.572F324B@telemobil.ro> <389E767A.A4D3FA37@compuserve.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 78
NNTP-Posting-Host: pool-106.suntel-lems.ro 194.102.106.106
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2575
Article PK: 1090027

Thank you for support . I think it is an interesting problem ...
I submit answers to yours requests -- se down..

"Mark A. Parsons" wrote:

>
> > This query perform calculation check if exists combination of four
> > numbers which is already in one row of extr_out table and if could not
> > find any, it insert the new combination in toate_extr table.
> > When I try to run something like this on Intel Pentium II/450Mhz
> > server with 384Mb RAM in three hour it generate just few ten thousands
> > of records. All the time was 100% CPU usage and less than 2% usage for
> > hardisk.
> > I observe that if run this with SQL Advantage 100% CPU time usage will
> > be on the client not on the server -- why? That's mean if I split this,
> > in multiple sub-queries, and run in the same time I will obtain big
> > improvements?
> > From my calculation it will take 16 day to finish?
> > How can I proceed to finish it in one day or less?
> >
> > Any help will be appreciated.
> > Thank you,
> > Cristinel Popescu
>
> First off ... your select against extr_out is incurring a table scan
> each time you go against it (with all of the or's the optimizer says
> 'the heck with it, I am going to table scan'; see your showplan output,
> too). If this table is of any appreciable size this means a lot of
> I/O's which means slow processing time.
>
> Second ... if you want to split this out into several 'parallel'
> operations I'd suggest breaking it down from the outer loop in, e.g.,
> for each value of 'i' you run a separate process ... process #1 has i=1,
> process #2 has i=2, etc.
>
> Third ... I think you need a different way of looking at your problem.
> Could you provide the following:
>
> 1) the current size (number of rows) of extr_out

334 rows - 4KB data

> 2) the current and expected size (number of rows) of toate_extr

120000 current -- 7,000,000 rows expected

> 3) a better explanation (plus some examples?) of 'legal' sets of numbers
> you expect to find in extr_out, or which you plan to put in toate_extr

sample from extr_out:

"6 11 36 37 43 46
9 24 26 28 34 45
3 5 21 32 34 41
13 21 25 28 39 44
8 10 16 34 47 48
14 16 19 26 38 47
5 13 20 22 23 46
8 13 16 33 46 47
10 11 23 31 33 44
2 10 12 31 45 48
21 25 28 34 45 47
17 25 30 37 38 47
2 6 7 17 30 42
3 17 20 38 44 45
4 5 6 30 36 42
5 7 14 19 35 49
15 22 28 38 41 42
2 4 16 17 18 35
4 34 37 41 44 48
6 7 13 25 29 45
8 14 15 22 34 43
12 14 17 19 36 47
4 6 20 35 43 48"

I want a fast way to generate all combination of 6 distinct numbers from
1 to 49 range and exclude all combination based on 4 numbers from
date_extr rows (ex: not include any combination based on
4 6 20 43 or 4 20 35 48 which you can find in last row on my sample..)

Generated sequence must be in order:

col1 < col2 < col3 < col4 < col5 < col6

and 1 <= col1,col2,col3,col4,col5,col6 <= 49

> I'm sure there's a better way to do this ... but it would help to get
> some more info ...
>



> --
> Mark Parsons
> Pegasys Ltd


Mark A. Parsons Posted on 2000-02-08 20:32:21.0Z
Message-ID: <38A07D55.B4BAA89E@compuserve.com>
Date: Wed, 09 Feb 2000 09:32:21 +1300
From: "Mark A. Parsons" <iron_horse@compuserve.com>
Organization: Pegasys Ltd
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Distribute processing for very large queries?
References: <389D4AB1.572F324B@telemobil.ro> <389E767A.A4D3FA37@compuserve.com> <389E8C0A.E1C678BE@telemobil.ro>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 21
NNTP-Posting-Host: p288.ipa1-n8-16.iconz.net.nz 210.48.25.32
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2562
Article PK: 1090016

Cristinel Popescu wrote:
...snip...

> I want a fast way to generate all combination of 6 distinct numbers from
> 1 to 49 range and exclude all combination based on 4 numbers from
> date_extr rows (ex: not include any combination based on
> 4 6 20 43 or 4 20 35 48 which you can find in last row on my sample..)
>
> Generated sequence must be in order:
>
> col1 < col2 < col3 < col4 < col5 < col6
>
> and 1 <= col1,col2,col3,col4,col5,col6 <= 49

Another request ... assuming this isn't a confidential issue ... could
you explain what *exactly* you're trying to do here ... from a
business/concept point of view? I.e., what do these numbers represent,
how are they to be used, ???

Are you trying to come up with all possible combinations for a lotto
drawing? :-)

--
Mark Parsons
Pegasys Ltd


Cristinel Popescu Posted on 2000-02-07 09:09:57.0Z
Message-ID: <389E8BE5.394BCAAE@telemobil.ro>
Date: Mon, 07 Feb 2000 11:09:57 +0200
From: Cristinel Popescu <Popescu_Cristinel@telemobil.ro>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: "Mark A. Parsons" <iron_horse@compuserve.com>
Subject: Re: Distribute processing for very large queries?
References: <389D4AB1.572F324B@telemobil.ro> <389E767A.A4D3FA37@compuserve.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 78
NNTP-Posting-Host: pool-106.suntel-lems.ro 194.102.106.106
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2576
Article PK: 1090029

Thank you for support . I think it is an intersting problem ...
I submit answers to yours requests -- se down..

"Mark A. Parsons" wrote:

>
> > This query perform calculation check if exists combination of four
> > numbers which is already in one row of extr_out table and if could not
> > find any, it insert the new combination in toate_extr table.
> > When I try to run something like this on Intel Pentium II/450Mhz
> > server with 384Mb RAM in three hour it generate just few ten thousands
> > of records. All the time was 100% CPU usage and less than 2% usage for
> > hardisk.
> > I observe that if run this with SQL Advantage 100% CPU time usage will
> > be on the client not on the server -- why? That's mean if I split this,
> > in multiple sub-queries, and run in the same time I will obtain big
> > improvements?
> > From my calculation it will take 16 day to finish?
> > How can I proceed to finish it in one day or less?
> >
> > Any help will be appreciated.
> > Thank you,
> > Cristinel Popescu
>
> First off ... your select against extr_out is incurring a table scan
> each time you go against it (with all of the or's the optimizer says
> 'the heck with it, I am going to table scan'; see your showplan output,
> too). If this table is of any appreciable size this means a lot of
> I/O's which means slow processing time.
>
> Second ... if you want to split this out into several 'parallel'
> operations I'd suggest breaking it down from the outer loop in, e.g.,
> for each value of 'i' you run a separate process ... process #1 has i=1,
> process #2 has i=2, etc.
>
> Third ... I think you need a different way of looking at your problem.
> Could you provide the following:
>
> 1) the current size (number of rows) of extr_out

334 rows - 4KB data

> 2) the current and expected size (number of rows) of toate_extr

120000 current -- 7,000,000 rows expected

> 3) a better explanation (plus some examples?) of 'legal' sets of numbers
> you expect to find in extr_out, or which you plan to put in toate_extr

sample from extr_out:

"6 11 36 37 43 46
9 24 26 28 34 45
3 5 21 32 34 41
13 21 25 28 39 44
8 10 16 34 47 48
14 16 19 26 38 47
5 13 20 22 23 46
8 13 16 33 46 47
10 11 23 31 33 44
2 10 12 31 45 48
21 25 28 34 45 47
17 25 30 37 38 47
2 6 7 17 30 42
3 17 20 38 44 45
4 5 6 30 36 42
5 7 14 19 35 49
15 22 28 38 41 42
2 4 16 17 18 35
4 34 37 41 44 48
6 7 13 25 29 45
8 14 15 22 34 43
12 14 17 19 36 47
4 6 20 35 43 48"

I want a fast way to generate all combination of 6 distinct numbers from
1 to 49 range and exclude all combination based on 4 numbers from
date_extr rows (ex: not include any combination based on
4 6 20 43 or 4 20 35 48 which you can find in last row on my sample..)

Generated sequence must be in order:

col1 < col2 < col3 < col4 < col5 < col6

and 1 <= col1,col2,col3,col4,col5,col6 <= 49

> I'm sure there's a better way to do this ... but it would help to get
> some more info ...
>



> --
> Mark Parsons
> Pegasys Ltd


Jason Froebe Posted on 2000-02-06 13:44:04.0Z
From: "Jason Froebe" <jfroebe@sybase.com>
References: <389D4AB1.572F324B@telemobil.ro>
Subject: Re: Distribute processing for very large queries?
Date: Sun, 6 Feb 2000 07:44:04 -0600
Lines: 110
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <JFBcXiKc$GA.306@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 158.159.8.17
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2580
Article PK: 1090032

Hi Cristinel,

I see you're using local variables as sargs (search arguments). These will
by default cause the optimizer to assume that 33% of the table will be
returned. You don't mention which version of ASE you're on or if indexes
are involved. Run showplans and 302 trace flag to find out what is
happening.

Take a look at the Performance & Tuning Guide. Specifically local
variables, indexes, and showplan output.

--

Jason Froebe
Product Support Engineer (PSE)
Sybase, Inc.

"Cristinel Popescu" <Popescu_Cristinel@telemobil.ro> wrote in message
news:389D4AB1.572F324B@telemobil.ro...
> Does know someone how can I distribute processing for very large queries
> like this:
> "declare @t1 datetime
> declare @t2 datetime
> declare @i tinyint
> declare @j tinyint
> declare @k tinyint
> declare @l tinyint
> declare @m tinyint
> declare @n tinyint
> select @i=1
> select @j=2
> select @k=3
> select @l=4
> select @m=5
> select @n=6
> select @nr=0
> select @t1=(select convert(char(30), getdate(),109))
> while (@i<=44)
> begin
> select @j=@i+1
> while (@j<=45)
> begin
> select @k=@j+1
> while (@k<=46)
> begin
> select @l=@k+1
> while (@l<=47)
> begin
> select @m=@l+1
> while (@m<=48)
> begin
> select @n=@m+1
> while (@n<=49)
> begin
> if ((select count(*) from extr_out
> where (n3=@k and n4=@l and n5=@m and n6=@n) or
> (n2=@j and n4=@l and n5=@m and n6=@n) or
> (n2=@j and n3=@k and n5=@m and n6=@n) or
> (n2=@j and n3=@k and n4=@l and n6=@n) or
> (n2=@j and n3=@k and n4=@l and n5=@m) or
> (n1=@i and n4=@l and n5=@m and n6=@n) or
> (n1=@i and n3=@k and n5=@m and n6=@n) or
> (n1=@i and n3=@k and n4=@l and n6=@n) or
> (n1=@i and n3=@k and n4=@l and n5=@m) or
> (n1=@i and n2=@j and n5=@m and n6=@n) or
> (n1=@i and n2=@j and n4=@l and n6=@n) or
> (n1=@i and n2=@j and n4=@l and n5=@m) or
> (n1=@i and n2=@j and n3=@k and n6=@n) or
> (n1=@i and n2=@j and n3=@k and n5=@m) or
> (n1=@i and n2=@j and n3=@k and n4=@l))=0)
> begin
> insert toate_extr
> select @i,@j,@k,@l,@m,@n
> end
> select @n=@n+1
> end
> select @m=@m+1
> end
> select @l=@l+1
> end
> select @k=@k+1
> end
> select @j=@j+1
> end
> select @i=@i+1
> end
> select @t2=(select convert(char(30), getdate(),109))
> select datediff (ss,@t1,@t2)"
>
> This query perform calculation check if exists combination of four
> numbers which is already in one row of extr_out table and if could not
> find any, it insert the new combination in toate_extr table.
> When I try to run something like this on Intel Pentium II/450Mhz
> server with 384Mb RAM in three hour it generate just few ten thousands
> of records. All the time was 100% CPU usage and less than 2% usage for
> hardisk.
> I observe that if run this with SQL Advantage 100% CPU time usage will
> be on the client not on the server -- why? That's mean if I split this,
> in multiple sub-queries, and run in the same time I will obtain big
> improvements?
> From my calculation it will take 16 day to finish?
> How can I proceed to finish it in one day or less?
>
>
> Any help will be appreciated.
> Thank you,
> Cristinel Popescu


Cristinel Popescu Posted on 2000-02-06 16:54:06.0Z
Message-ID: <389DA72E.337B176B@telemobil.ro>
Date: Sun, 06 Feb 2000 18:54:06 +0200
From: Cristinel Popescu <Popescu_Cristinel@telemobil.ro>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Jason Froebe <jfroebe@sybase.com>
Subject: Re: Distribute processing for very large queries?
References: <389D4AB1.572F324B@telemobil.ro> <JFBcXiKc$GA.306@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 190
NNTP-Posting-Host: pool-110.suntel-lems.ro 194.102.106.110
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2578
Article PK: 1090031

Run showplans and 302 trace flag look like this:
(table extr_out have 334 row and 4KB data without indexes)

"QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is DBCC.
QUERY PLAN FOR STATEMENT 2 (at line 2).
STEP 1
The type of query is DBCC.
QUERY PLAN FOR STATEMENT 3 (at line 3).
STEP 1
The type of query is SET OPTION ON.
QUERY PLAN FOR STATEMENT 4 (at line 4).
STEP 1
The type of query is SET OPTION ON.
QUERY PLAN FOR STATEMENT 5 (at line 7).
STEP 1
The type of query is DECLARE.
QUERY PLAN FOR STATEMENT 6 (at line 16).
STEP 1
The type of query is SELECT.
QUERY PLAN FOR STATEMENT 7 (at line 17).
STEP 1
The type of query is SELECT.
QUERY PLAN FOR STATEMENT 8 (at line 18).
STEP 1
The type of query is SELECT.
QUERY PLAN FOR STATEMENT 9 (at line 19).
STEP 1
The type of query is SELECT.
QUERY PLAN FOR STATEMENT 10 (at line 20).
STEP 1
The type of query is SELECT.
QUERY PLAN FOR STATEMENT 11 (at line 21).
STEP 1
The type of query is SELECT.
QUERY PLAN FOR STATEMENT 12 (at line 22).
STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 13 (at line 23).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped ONCE AGGREGATE.

STEP 2
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 14 (at line 24).

STEP 1
The type of query is COND.

QUERY PLAN FOR STATEMENT 15 (at line 26).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 16 (at line 27).

STEP 1
The type of query is COND.

QUERY PLAN FOR STATEMENT 17 (at line 29).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 18 (at line 30).

STEP 1
The type of query is COND.

QUERY PLAN FOR STATEMENT 19 (at line 32).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 20 (at line 33).

STEP 1
The type of query is COND.

QUERY PLAN FOR STATEMENT 21 (at line 35).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 22 (at line 36).

STEP 1
The type of query is COND.

QUERY PLAN FOR STATEMENT 23 (at line 38).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 24 (at line 39).

STEP 1
The type of query is COND.

QUERY PLAN FOR STATEMENT 25 (at line 41).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.


FROM TABLE
extr_out
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


STEP 2
The type of query is COND.

QUERY PLAN FOR STATEMENT 26 (at line 58).

STEP 1
The type of query is INSERT.
The update mode is direct.
TO TABLE
toate_extr

QUERY PLAN FOR STATEMENT 27 (at line 61).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 28 (at line 63).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 29 (at line 65).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 30 (at line 67).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 31 (at line 69).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 32 (at line 71).

STEP 1
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 33 (at line 73).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped ONCE AGGREGATE.

STEP 2
The type of query is SELECT.

QUERY PLAN FOR STATEMENT 34 (at line 74).

STEP 1
The type of query is SELECT.


"
Any hint please?
Thank you,
Cristi

Jason Froebe wrote:
>
> Hi Cristinel,
>
> I see you're using local variables as sargs (search arguments). These will
> by default cause the optimizer to assume that 33% of the table will be
> returned. You don't mention which version of ASE you're on or if indexes
> are involved. Run showplans and 302 trace flag to find out what is
> happening.
>
> Take a look at the Performance & Tuning Guide. Specifically local
> variables, indexes, and showplan output.
>
> --
>
> Jason Froebe
> Product Support Engineer (PSE)
> Sybase, Inc.
>
> "Cristinel Popescu" <Popescu_Cristinel@telemobil.ro> wrote in message
> news:389D4AB1.572F324B@telemobil.ro...
> > Does know someone how can I distribute processing for very large queries
> > like this:
> > "declare @t1 datetime
> > declare @t2 datetime
> > declare @i tinyint
> > declare @j tinyint
> > declare @k tinyint
> > declare @l tinyint
> > declare @m tinyint
> > declare @n tinyint
> > select @i=1
> > select @j=2
> > select @k=3
> > select @l=4
> > select @m=5
> > select @n=6
> > select @nr=0
> > select @t1=(select convert(char(30), getdate(),109))
> > while (@i<=44)
> > begin
> > select @j=@i+1
> > while (@j<=45)
> > begin
> > select @k=@j+1
> > while (@k<=46)
> > begin
> > select @l=@k+1
> > while (@l<=47)
> > begin
> > select @m=@l+1
> > while (@m<=48)
> > begin
> > select @n=@m+1
> > while (@n<=49)
> > begin
> > if ((select count(*) from extr_out
> > where (n3=@k and n4=@l and n5=@m and n6=@n) or
> > (n2=@j and n4=@l and n5=@m and n6=@n) or
> > (n2=@j and n3=@k and n5=@m and n6=@n) or
> > (n2=@j and n3=@k and n4=@l and n6=@n) or
> > (n2=@j and n3=@k and n4=@l and n5=@m) or
> > (n1=@i and n4=@l and n5=@m and n6=@n) or
> > (n1=@i and n3=@k and n5=@m and n6=@n) or
> > (n1=@i and n3=@k and n4=@l and n6=@n) or
> > (n1=@i and n3=@k and n4=@l and n5=@m) or
> > (n1=@i and n2=@j and n5=@m and n6=@n) or
> > (n1=@i and n2=@j and n4=@l and n6=@n) or
> > (n1=@i and n2=@j and n4=@l and n5=@m) or
> > (n1=@i and n2=@j and n3=@k and n6=@n) or
> > (n1=@i and n2=@j and n3=@k and n5=@m) or
> > (n1=@i and n2=@j and n3=@k and n4=@l))=0)
> > begin
> > insert toate_extr
> > select @i,@j,@k,@l,@m,@n
> > end
> > select @n=@n+1
> > end
> > select @m=@m+1
> > end
> > select @l=@l+1
> > end
> > select @k=@k+1
> > end
> > select @j=@j+1
> > end
> > select @i=@i+1
> > end
> > select @t2=(select convert(char(30), getdate(),109))
> > select datediff (ss,@t1,@t2)"
> >
> > This query perform calculation check if exists combination of four
> > numbers which is already in one row of extr_out table and if could not
> > find any, it insert the new combination in toate_extr table.
> > When I try to run something like this on Intel Pentium II/450Mhz
> > server with 384Mb RAM in three hour it generate just few ten thousands
> > of records. All the time was 100% CPU usage and less than 2% usage for
> > hardisk.
> > I observe that if run this with SQL Advantage 100% CPU time usage will
> > be on the client not on the server -- why? That's mean if I split this,
> > in multiple sub-queries, and run in the same time I will obtain big
> > improvements?
> > From my calculation it will take 16 day to finish?
> > How can I proceed to finish it in one day or less?
> >
> >
> > Any help will be appreciated.
> > Thank you,
> > Cristinel Popescu


"Carl C. Federl" <cfederl Posted on 2000-02-06 13:53:46.0Z
Subject: Re: Distribute processing for very large queries?
From: "Carl C. Federl" <cfederl@mediaone.net_removethis>
Reply-to: cfederl@mediaone.net_removethis
References: <389D4AB1.572F324B@telemobil.ro>
Date: Sun, 6 Feb 2000 13:53:46 GMT
X-Newsreader: News Rover 5.3.0 (http://www.NewsRover.com)
Message-ID: <Kf2LwmKc$GA.184@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
Lines: 22
NNTP-Posting-Host: ro02-24-29-238-209.ce.mediaone.net 24.29.238.209
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2579
Article PK: 1090034

I calculated the number of times that the inner most logic will be executed
as 7,256,313,856 (44*44*44*44*44*44)
At 100 Checks per second, this works out to be 72,563,138 seconds to run the
entire logic.
At 86,400 seconds per day, this works out to approx 840 days to run this
algorithm (or over 2 year)

On 6-Feb-2000, Cristinel Popescu <Popescu_Cristinel@telemobil.ro> wrote:
>
three hour it generate just few ten thousands of records.
>

This seems fine: 3 hours is 10,800 seconds and you do not state how may
rows already exist in the table.

Do these calculation appear correct ?

If so, to peform these calculations in a week, you would need 120 CPUs (840
days / 7 ) and need to pass initial values for @i thru @n
to partition the logic appopriately.

This looks like a job for a super computer, a Pentium 450 just won't do the
job.