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.

Possible to use parallel clause to maximize insert perfromance?

5 posts in General Discussion Last posting was on 2011-04-19 06:52:05.0Z
kennedyc Posted on 2011-04-17 09:28:32.0Z
Sender: 4b6f.4da9c4c1.846930886@sybase.com
From: kennedyc
Newsgroups: sybase.public.ase.general
Subject: Possible to use parallel clause to maximize insert perfromance?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4daab2c0.5ca3.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Apr 2011 02:28:32 -0700
X-Trace: forums-1-dub 1303032512 10.22.241.41 (17 Apr 2011 02:28:32 -0700)
X-Original-Trace: 17 Apr 2011 02:28:32 -0700, 10.22.241.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30114
Article PK: 79344

Hello,

I've written a simple while loop to insert data to a table
(with 8 partition, round robin) in ASE In-Memory db in order
to get some baseline about the insert performance.

But the result showed extremely slow compare to other test
using a multithread program to perform insert.

What I suspect is that the while loop insert is using single
thread for insert and I can enhance its throughput by using
parallel clause. Is my assumption correct or not? Besides,
how can I monitor the query execution if it is using
parallelism or not. Thanks.


"Mark A. Parsons" <iron_horse Posted on 2011-04-17 12:41:34.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Possible to use parallel clause to maximize insert perfromance?
References: <4daab2c0.5ca3.1681692777@sybase.com>
In-Reply-To: <4daab2c0.5ca3.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4daadffe@forums-1-dub>
Date: 17 Apr 2011 05:41:34 -0700
X-Trace: forums-1-dub 1303044094 10.22.241.152 (17 Apr 2011 05:41:34 -0700)
X-Original-Trace: 17 Apr 2011 05:41:34 -0700, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30115
Article PK: 79343

By 'parallelism' I assume you're referring to the dataserver's ability to use certain configuration settings (eg, max
parallel degree, # of worker threads) to run queries in parallel, ie, a single parent spid/session spawns multiple child
spids/sessions. In this case 'parallelism' can only be use for read operations; 'parallelism' cannot be used for write
operations.

As for your tests to determine baseline insert performance ... could you provide more details about your single-session
test case (eg, table/index structure, T-SQL used to perform the inserts, is the insert loop at the client or dataserver
side)? It would also help to get some information about your database layout (eg, <dbname>..sp_helpdb <dbname>).

It may be possible to improve your single-session insert performance through configuration settings, db/table/index
design and/or T-SQL coding.

On 04/17/2011 05:28, kennedyc wrote:
> Hello,
>
> I've written a simple while loop to insert data to a table
> (with 8 partition, round robin) in ASE In-Memory db in order
> to get some baseline about the insert performance.
>
> But the result showed extremely slow compare to other test
> using a multithread program to perform insert.
>
> What I suspect is that the while loop insert is using single
> thread for insert and I can enhance its throughput by using
> parallel clause. Is my assumption correct or not? Besides,
> how can I monitor the query execution if it is using
> parallelism or not. Thanks.


"Mark A. Parsons" <iron_horse Posted on 2011-04-17 12:54:34.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Possible to use parallel clause to maximize insert perfromance?
References: <4daab2c0.5ca3.1681692777@sybase.com> <4daadffe@forums-1-dub>
In-Reply-To: <4daadffe@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4daae30a$1@forums-1-dub>
Date: 17 Apr 2011 05:54:34 -0700
X-Trace: forums-1-dub 1303044874 10.22.241.152 (17 Apr 2011 05:54:34 -0700)
X-Original-Trace: 17 Apr 2011 05:54:34 -0700, vip152.sybase.com
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30117
Article PK: 79346

It may also help to see the output from an sp_sysmon session to see if there are any issues that could be affecting your
test, eg:

==========================
-- on a dataserver with just your test session running

sp_sysmon "begin_sample"
go
<insert_loop>
go
sp_sysmon "end_sample"
go
==========================

I'd suggest running a test that takes (at least) 1-2 minutes to complete in order to get a good idea of overall
performance (ie, a 100-row insert test won't provide much useful info).

Depending on the size of your database and the volume of records being inserted you may need to consider truncating the
table a few times during your looping construct (eg, insert 300K rows, truncate table, insert 300K rows, truncate table,
etc) ... whatever keeps you from filling up the database.

On 04/17/2011 08:41, Mark A. Parsons wrote:
> By 'parallelism' I assume you're referring to the dataserver's ability to use certain configuration settings (eg, max
> parallel degree, # of worker threads) to run queries in parallel, ie, a single parent spid/session spawns multiple child
> spids/sessions. In this case 'parallelism' can only be use for read operations; 'parallelism' cannot be used for write
> operations.
>
> As for your tests to determine baseline insert performance ... could you provide more details about your single-session
> test case (eg, table/index structure, T-SQL used to perform the inserts, is the insert loop at the client or dataserver
> side)? It would also help to get some information about your database layout (eg, <dbname>..sp_helpdb <dbname>).
>
> It may be possible to improve your single-session insert performance through configuration settings, db/table/index
> design and/or T-SQL coding.
>
>
>
> On 04/17/2011 05:28, kennedyc wrote:
>> Hello,
>>
>> I've written a simple while loop to insert data to a table
>> (with 8 partition, round robin) in ASE In-Memory db in order
>> to get some baseline about the insert performance.
>>
>> But the result showed extremely slow compare to other test
>> using a multithread program to perform insert.
>>
>> What I suspect is that the while loop insert is using single
>> thread for insert and I can enhance its throughput by using
>> parallel clause. Is my assumption correct or not? Besides,
>> how can I monitor the query execution if it is using
>> parallelism or not. Thanks.


kennedyc Posted on 2011-04-19 06:52:05.0Z
Sender: 292d.4dac1057.1804289383@sybase.com
From: kennedyc
Newsgroups: sybase.public.ase.general
Subject: Re: Possible to use parallel clause to maximize insert perfromance?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dad3115.4de9.1681692777@sybase.com>
References: <4daae30a$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Apr 2011 23:52:05 -0700
X-Trace: forums-1-dub 1303195925 10.22.241.41 (18 Apr 2011 23:52:05 -0700)
X-Original-Trace: 18 Apr 2011 23:52:05 -0700, 10.22.241.41
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30128
Article PK: 79357

Hi Mark,

Thanks so much for your help. I finally know the slow insert
is caused by the program "interactive SQL".

I was running the while loop on it and it somehow halted
after few hundred thousands of transactions.

Now I'm using isql and the insert figures are reasonable
now. Thanks.

> It may also help to see the output from an sp_sysmon
> session to see if there are any issues that could be
> affecting your test, eg:
>
> ==========================
> -- on a dataserver with just your test session running
>
> sp_sysmon "begin_sample"
> go
> <insert_loop>
> go
> sp_sysmon "end_sample"
> go
> ==========================
>
> I'd suggest running a test that takes (at least) 1-2
> minutes to complete in order to get a good idea of overall
> performance (ie, a 100-row insert test won't provide much
> useful info).
>
> Depending on the size of your database and the volume of
> records being inserted you may need to consider truncating
> the table a few times during your looping construct (eg,
> insert 300K rows, truncate table, insert 300K rows,
> truncate table, etc) ... whatever keeps you from filling
> up the database.
>
>
> On 04/17/2011 08:41, Mark A. Parsons wrote:
> > By 'parallelism' I assume you're referring to the
> dataserver's ability to use certain configuration settings
> > (eg, max parallel degree, # of worker threads) to run
> queries in parallel, ie, a single parent spid/session
> > spawns multiple child spids/sessions. In this case
> 'parallelism' can only be use for read operations;
> > 'parallelism' cannot be used for write operations.
> >
> > As for your tests to determine baseline insert
> performance ... could you provide more details about your
> > single-session test case (eg, table/index structure,
> T-SQL used to perform the inserts, is the insert loop at
> > the client or dataserver side)? It would also help to
> get some information about your database layout (eg,
> <dbname>..sp_helpdb <dbname>). >
> > It may be possible to improve your single-session insert
> > performance through configuration settings,
> db/table/index design and/or T-SQL coding. >
> >
> >
> > On 04/17/2011 05:28, kennedyc wrote:
> >> Hello,
> >>
> >> I've written a simple while loop to insert data to a
> table >> (with 8 partition, round robin) in ASE In-Memory
> db in order >> to get some baseline about the insert
> performance. >>
> >> But the result showed extremely slow compare to other
> test >> using a multithread program to perform insert.
> >>
> >> What I suspect is that the while loop insert is using
> single >> thread for insert and I can enhance its
> throughput by using >> parallel clause. Is my assumption
> correct or not? Besides, >> how can I monitor the query
> execution if it is using >> parallelism or not. Thanks.


Rob V [ Sybase ] Posted on 2011-04-17 13:17:06.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Possible to use parallel clause to maximize insert perfromance?
References: <4daab2c0.5ca3.1681692777@sybase.com>
In-Reply-To: <4daab2c0.5ca3.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4daae852$1@forums-1-dub>
Date: 17 Apr 2011 06:17:06 -0700
X-Trace: forums-1-dub 1303046226 10.22.241.152 (17 Apr 2011 06:17:06 -0700)
X-Original-Trace: 17 Apr 2011 06:17:06 -0700, vip152.sybase.com
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30120
Article PK: 79347


On 17-Apr-2011 11:28, kennedyc wrote:
> Hello,
>
> I've written a simple while loop to insert data to a table
> (with 8 partition, round robin) in ASE In-Memory db in order
> to get some baseline about the insert performance.
>
> But the result showed extremely slow compare to other test
> using a multithread program to perform insert.
>
> What I suspect is that the while loop insert is using single
> thread for insert and I can enhance its throughput by using
> parallel clause. Is my assumption correct or not? Besides,
> how can I monitor the query execution if it is using
> parallelism or not. Thanks.

Sounds logical to me.
Multithreaded will in principle always achieve greater throughput than
single-threaded (assuming the concurrency effects will not be detrimental).
When writing a while-loop in SQL, and executing that, it will all be
serial, since all statements in a session are always executed serially.
Parallelism can only come in for certain types of queries (typically
with partitioned tables).
Individual insert-values statements cannot be parallellized.

Perhaps you were thinking of Sybase IQ with its "begin parallel iq" /
"end parallel iq" construct. ASE doesn't have that.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------