Tuning Performance

We intalled a Sybase IQ 12.5 on a HP-UX, we have a IQ
database  of 255 GB, some of the querys are very slow,
please, what are the main aspects that we have to look for
to get a better performance ??.
Thanks.
Jorge
0
Jorge
10/11/2004 7:58:26 PM
sybase.iq 1656 articles. 1 followers. Follow

4 Replies
854 Views

Similar Articles

[PageSpeed] 54

main things (imho):
indexes, optimization of memory usage, iqtc 60% of memory.

The rest need to see more info about your tables, indexes, query plan, type
of querys, HW etc

<Jorge del Mar> wrote in message news:416ae5e2.6071.1681692777@sybase.com...
> We intalled a Sybase IQ 12.5 on a HP-UX, we have a IQ
> database  of 255 GB, some of the querys are very slow,
> please, what are the main aspects that we have to look for
> to get a better performance ??.
> Thanks.
> Jorge


0
Piter
10/12/2004 6:44:44 AM
Thank You Piter.
Sincerely

Jorge
> main things (imho):
> indexes, optimization of memory usage, iqtc 60% of memory.
>
> The rest need to see more info about your tables, indexes,
> query plan, type of querys, HW etc
>
> <Jorge del Mar> wrote in message
> > news:416ae5e2.6071.1681692777@sybase.com... We intalled
> > a Sybase IQ 12.5 on a HP-UX, we have a IQ database  of
> > 255 GB, some of the querys are very slow, please, what
> > are the main aspects that we have to look for to get a
> > better performance ??. Thanks.
> > Jorge
>
>
0
Jorge
10/12/2004 3:03:58 PM
As mentioned, without details it is difficult to know why
things are slow. Sometimes queries are just really big and
slow even on a fast, well tuned system.

But here are a few thoughts on some common problems
(in no particular order and by no means a complete list)

Join keys- A HG (often a LF can work just as well) index
should be on each join column, and columns that will be
joined together should have the same data types.
Integer types (smallint/int/bigint) will usually be the
fastest join key datatypes, and unsigned ints are a bit
faster than signed ints.

Indexes- Index the columns as advised in the admin guide. IQ
likes a lot of indexes. If a HG, make it unique if possible.
If a group of columns has a multicolumn HG index (like you
get with a declared multicolumn primary/foreign key), you
still will likely want a HG on each individual column.

Column definitions- use IQ UNIQUE when creating tables if
possible; in 12.5.0, you can get almost all of the benefits
of putting an IQ UNIQUE on every column by setting the
parameter minimize_storage=ON when you create a table. This
is OFF by default, because it can slow loads a bit, or even
cause a load to fail if there is not enough main cache.
Also, using some thought in defining columns can help;
for example, defining a columns of dates as a timestamp
datatype instead of just as a date wastes some space and
slows queries some.

Queries- Is a query just slow because of how it was written,
like a big correlated subquery or a big cartesian join (join
with no join condition)? Consider rewriting the subquery into
joins, or finding a way to eliminate the cartesian. If you
are returning very large result sets, consider using the
Temp_Extract_Name options.

Hardware- You may be able to fit hundreds of GBytes on
one or two disks, but multiple spindles are needed for
acceptable performance in most cases. IQ tends to like
a lot of cache; make sure you aren't using the tiny default
cache sizes.

Dave

Jorge del Mar wrote:
> We intalled a Sybase IQ 12.5 on a HP-UX, we have a IQ
> database  of 255 GB, some of the querys are very slow,
> please, what are the main aspects that we have to look for
> to get a better performance ??.
> Thanks.
> Jorge
0
David
10/27/2004 3:18:52 PM
Thanks David.

Sincerely

Jorge


> As mentioned, without details it is difficult to know why
> things are slow. Sometimes queries are just really big and
> slow even on a fast, well tuned system.
>
> But here are a few thoughts on some common problems
> (in no particular order and by no means a complete list)
>
> Join keys- A HG (often a LF can work just as well) index
> should be on each join column, and columns that will be
> joined together should have the same data types.
> Integer types (smallint/int/bigint) will usually be the
> fastest join key datatypes, and unsigned ints are a bit
> faster than signed ints.
>
> Indexes- Index the columns as advised in the admin guide.
> IQ likes a lot of indexes. If a HG, make it unique if
> possible. If a group of columns has a multicolumn HG index
> (like you get with a declared multicolumn primary/foreign
> key), you still will likely want a HG on each individual
> column.
>
> Column definitions- use IQ UNIQUE when creating tables if
> possible; in 12.5.0, you can get almost all of the
> benefits of putting an IQ UNIQUE on every column by
> setting the parameter minimize_storage=ON when you create
> a table. This is OFF by default, because it can slow loads
> a bit, or even cause a load to fail if there is not enough
> main cache. Also, using some thought in defining columns
> can help; for example, defining a columns of dates as a
> timestamp datatype instead of just as a date wastes some
> space and slows queries some.
>
> Queries- Is a query just slow because of how it was
> written, like a big correlated subquery or a big cartesian
> join (join with no join condition)? Consider rewriting the
> subquery into joins, or finding a way to eliminate the
> cartesian. If you are returning very large result sets,
> consider using the Temp_Extract_Name options.
>
> Hardware- You may be able to fit hundreds of GBytes on
> one or two disks, but multiple spindles are needed for
> acceptable performance in most cases. IQ tends to like
> a lot of cache; make sure you aren't using the tiny
> default cache sizes.
>
> Dave
>
> Jorge del Mar wrote:
> > We intalled a Sybase IQ 12.5 on a HP-UX, we have a IQ
> > database  of 255 GB, some of the querys are very slow,
> > please, what are the main aspects that we have to look
> > for to get a better performance ??.
> > Thanks.
> > Jorge
0
Jorge
11/8/2004 5:44:14 PM
Reply:

Similar Artilces:

Performance Tuning
This is a multi-part message in MIME format. --------------080804050908060708060306 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 32-bit ASE 12.5 Solaris 8 1) I am running a number of stored procedures ... and have a few problems with the performance. It uses a few temporary tables. The stored procedure calls other stored procedures in a loop. By calling getdate() before and after each stored procedure call ... there was really nothing much that the sps ... do ..... almost everything completes execution in less than second. ...

IQ Performance
I have loaded a copy of a table from an 11.5 ASE server into ASE IQ 12.3.1 which has 11.2 million rows in attempt to speed up users queries. I have created indexes in IQ on the same columns that the ASE 11.5 server has. The response time on a sample query in the SQL server is 10 - 15 seconds (3 seconds if cached) in IQ the response time is 40 - 45 seconds. The database and table and indexes where created in IQ using mostly default values. This is running on a SUN 4500 with 6 gig of memory and about 250 Gig of disk. Any suggestions to beat the 10 second response time in IQ. Th...

Tuning / performance
Hi all, Ok I have now released Bugzilla as our system bugtracking tool, and all is going well. I am running on Win XP virtual server Bugzilla v 2.22 My SQL 5.0.24a-community-nt Apache 2.2.3 But I seem to be hitting some issues. Sometimes users enter requests and they take a long time to respond. (30 secs plus) and their browser (IE and Firefox) returns a page cannot be displayed. I am currently running the default configuration for MySQL and Apache on windows. The bug change/addition is actually stored ok, but the system does not seem to be responding efficiently. ...

Performance tuning
Is there a way to create and analyze some kind of trace files with SQL Anywhere 5.5? I have a program that issues "generated" SQL statements. I'd like to quickly look for those that create full table scans in any of the used tables to see if I can optimize data access. TIA Peter Peter Hiemenz Hilden, near Duesseldorf Germany I wrote something like this a couple months back. It interrogates the trace file that PowerBuilder produces. It's rather proprietary since it's one of the tools that I use when I do Performance & Tuning cons...

Performance Tuning
 (If this is the wrong forum, please advise and I'll repost).I have developed a web site, www.nzgdb.co.nz, using Visual Web Designer 2005 (Express).  It has a large genealogy database (SQL Server 2005), and runs on a new dual-core processor server with 2GB of memory, using SBS 2003 (SP 2).   There are no other applications or web sites running on this server.My design is supposed to be optimized for the two critical activities, page GDB1 which searches the GDB (genealogy database) according to various criteria to return a list of records, and page GDB2 which is invoke...

Performance tuning
Hi, We are using Jconnect 6.0.5 (latest EBF). We found that 'select db_name' was getting called before each and every sql update/insert. We are not sure why it is doing this. We need to prevent this unnecessary db call. Does any one have any idea Thanks Lachu Hi Lachu, This sounds like some JDBC Call might be happening in your application - offheand I don't know exactly what, but something like Connection.getCatalog() I don't think jConnect caches this sort of thing - so it might be the application code makes the call for each command execution. If yo...

performance tuning
Hi, All, I run select * statement from a view on both ASE11.5 and ASE12. The speeds for returning the value are very different. On ASE11.5, it only takes 1min while on ASE12, it takes 30min. ASE12 has bigger data caches for the database and tempdb than what the ASE11.5 has. Both of them have the same indexes. After I run sp_sysmon on ASE12, in the report of data cache management, ASE12 has high cache hit ratio (that is good) and high pool turnover (no good). Does that mean I need to increase the data cache for ASE12? I also noticed that in OS, ASE12 has very tight free mem...

performance tuning
Hi, All, I run select * statement from a view on both ASE11.5 and ASE12. The speeds for returning the value are very different. On ASE11.5, it only takes 1min while on ASE12, it takes 30min. ASE12 has bigger data caches for the database and tempdb than what the ASE11.5 has. Both of them have the same indexes. After I run sp_sysmon on ASE12, in the report of data cache management, ASE12 has high cache hit ratio (that is good) and high pool turnover (no good). Does that mean I need to increase the data cache for ASE12? I also noticed that in OS, ASE12 has very tight free mem...

Performance and Tuning
I have a pb application running on sybase db. It is very slow. We used PFC for developing the application. I would like to know what is the best to way to tune the performance of the application. Any ideas are greatly appreciated . Thanks in advance.. Leo Leo, From my experience, performance problem mostly due to inefficient SQL statement. Check which SQL statement caused the problem and then tune that SQL. Hope this can help. Leo <Spamoff_Leo@yahoo.com> wrote in message news:bn7hXf#LAHA.260@forums.sybase.com... > I have a pb application running on sybase db...

Network performance tuning
A client with a network sniffer on a WAN had these observations about the traffic. This is ASA 6.0.3, tcpip. 1) "the client and server are acknowledging (i.e. ACK) on almost a packet per packet basis. If this can be changed so that acknowledgments are done for groups of packets, say 5, this would help a lot." This is something I've never heard of. Comments? 2) "The packets being formed and sent by both the client and the server are not nearly as big as they can be. If the app can fill up the packets before sending them this would help a lot." Is...

Jaguar Performance Tuning
What tools/techniques are available to do performance tuning Jaguar 3.5? What would be the best way to turn server components? How do you monitor resources being used by Jaguar? - Sam The next Jag 3.5 ebf will have support for OptimizeIt to 'hook' directly into the Jaguar. This will definately help tune components. "sam mccaa" <smccaa@mccaawebster.com> wrote in message news:O9StdOs4$GA.86@forums.sybase.com... > What tools/techniques are available to do performance > tuning Jaguar 3.5? What would be the best way to turn > server component...

Performance Tuning #2
Dear All, Need advice!!! I had attended ASE Performance Tuning course, knew all the sysmon report and update statistic, DOL,optdiag, sp_configure, locks,etc!! BUT, after i come back to my office, i have no idea where to begin. I mean, what shall do to the server? Gather statistics by running the sp_sysmon/sp_monitor/optdiag???? How? Please any advice. Our server had never do any statistic gethering or performance tuning. I will spenind time on our development server before proceed to live server. I really appreciate your help!!! thanks in advance!! On 30 Sep 2003 03:37:21...

performance tuning guidelines ?
Hello ! I did not found any performace tuning chapters in basic docs for Jaguar ? It is very common topic for huge systems that applications servers are to be service. Are there any resources on preformance tuning of Sybase EAS ? sincerely Olek http://www.sybase.com/detail/1,6904,1019504,00.html "Aleksander Grzebyta" <aleksander.grzebytaNO@SPAMtalex.com.pl> wrote in message news:qVFk5xwuCHA.198@forums.sybase.com... > Hello ! > > I did not found any performace tuning chapters in basic docs for Jaguar ? It > is very common topic for ...

Performance Tuning for WAN
We have sites in US and China. I have noticed when I am in China, ConsoleOne tends to connect to the US drives. I have replicas off all the partitions in China so is there a way to force C1 to look locally instead of jumping over the pond? Also, I have just setup a NetWare 6.5 PDC over in China. I have noticed that if I use any account in China, they authenticate quickly. I I use my account which is in the states it takes about 10 minutes. Now I just moved my account to a China context today and now it takes about 30 seconds to login. I am guessing it is trying to authenticate...

Web resources about - Tuning Performance - sybase.iq

Performance - Wikipedia, the free encyclopedia
A performance , in performing arts , generally comprises an event in which a performer or group of performers behave in a particular way for ...

Andrew Bogut puts back issues behind him in return performance against Utah Jazz
Andrew Bogut's third double-double of the NBA season shows he is ready for the Christmas Day clash with the Cleveland Cavaliers.

Sapphire Readies Nitro R9 Fury: Custom Design and Enhanced Performance
... its own custom printed circuit board. Sapphire claims that the new Nitro R9 Fury will feature enhanced durability and slightly higher performance. ...

Ars Technica’s (virtual) Performance Car of the Year
... coming out of the very slow turns 11 and 12. Brakes could be better. 18 more images in gallery 2015 has been the year of the performance car ...

How Twitter used Doze in Android 6.0 Marshmallow to Improve Notification Performance
... Advocate In October, we published a blog post about how Google Cloud Messaging (GCM) works with Doze in Android 6.0 Marshmallow to bring performance ...

How to make your performance review meaningful
Make your performance review meaningful Image by Thinkstock There's a prevailing opinion that performance reviews aren't the best way to evaluate ...

Wingels evaluates personal performance vs Hawks, reflects on year
... 58° Navigation Home Giants A's Sharks Warriors Kings 49ers Raiders Quakes Insiders More Tickets Shop Watch Wingels evaluates personal performance ...

Deadline’s The Contenders: How The Music In ‘Youth’ Became Such A Vital Part Of Michael Caine’s Performance ...
At Deadline’s big awards season day long event last month , The Contenders Presented By Deadline, Youth composer David Lang explained his process ...

Businesses Struggling with Application Performance Issues
Many IT teams still don't have a monitoring solution in place or don't have a solution equipped to detect certain types of performance issues. ...

Sony plans to boost battery performance 40 percent by 2020
Sony is planning to commercialize lithium-sulfur batteries by 2020, and promising increases of up to 40% over conventional lithium-ion architectures. ...

Resources last updated: 12/27/2015 11:54:37 AM