on 07-03-2015 12:32 PM
Hi Experts,
As Sybase is column oriented database, I am trying to analyze the following 2 scenarios -
We have a database where the system is collecting data every minutes and the amount of data in tables is huge.
Looking at some way to create a query to help gain better performance.
Can you help me understand which of the following query can be best suited for optimum performance where data is huge in tables -
1. A table can have many number of columns, but of this the user is interested to see data only from certain columns.
Creating bespoke view where we only fetch few columns from table.
create view select col1, col2 from table
2. We could create a new user with just select on columns they wish to see on tables using
grant select col1,col2 on table to dcqueryuser
Appreciate any response. Thanks.
As IQ is column-oriented it will only access the columns you specify in the query, whether directly - 'select col1, col2....' - or defined in a view - 'create view as select col1, col2....' and using 'select * from view'.
Either way you will get the same performance as the view is not compiled but evaluated at runtime.
Unless you are wanting to restrict a user's access to certain columns for security reasons (which can be done by defining a view) you only need to reference the columns you want in a query.
You will not improve performance by only granting select on certain columns.
What you will want to do is ensure you are always running 'commit' before and after every query and load/insert to ensure versioning is reduced and queries are running on the most recent data. For performance, use the LOAD TABLE command to load large amounts of data and try avoid row-by-row inserts. Otherwise, investigate the RLV capability.
Chris
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Chris, Thanks for your response.
So, is it correct to say that using select statement as follows
select col1,col2 from table
is going to give me same performance as
create view col1, col2 from table.
The major reason for us to experience performance issues is only to fetch/view data from large database. Any better solution here as user does not really need all this information from database.
Yes. Both will only access the columns asked for at runtime and run the same. IQ will not read any other columns from the underlying table to satisfy the request.
Depending on what else you expect to do with the columns (aggregation, ordering, grouping, search arguments) you may want to look into which indexes you may or may not want to add to the table as well. IQ does not have only B-tree indexes like other DBMS engines.
The indexes are the storage and access mechanisms of IQ. The row is not stored at all, only the columns as indexes. IQ may use more than 1 index on a column to satisfy a query. Also a column can have more than 1 index as well.
Index creation and maintenance is part of the DML process. You do not have to maintain indexes or statistics separately. When the table DML is complete, the indexes are up-to-date. Dropping and adding indexes separately or during maintenance cycles is not necessary. Don't worry about storage either. The actual storage of the data with indexes will be less than the raw data size.
This will go a long way to help the query performance you are concerned with.
Have a look at Tayeb's response and also review the indexing guidelines at:
Indexing - SAP IQ Performance and Tuning - SAP Library
IQ also contains an index advisor to suggest which indexes could be useful to add. The index advisor can list the suggested indexes and also show the suggestions in the HTML query plan output.
Indexing Tips - SAP IQ Performance and Tuning - SAP Library
Chris
Now that I know I should reconsider the query.
I am trying to run a query plan.
Is there any way to get this reviewed as I am struggling to understand the output of query plan.
I am using following as guidelines
Hi Vrushali,
A couple of points to consider when dealing with large tables:
1- Good indexing strategy. See Mark Mumy guidelines here :
http://scn.sap.com/community/iq/blog/2013/06/26/sap-sybase-iq-indexes-and-indexing-techniques
2- Splitting tables on columns having high cardinality then merge them using Union ALL
3- Using Hash partitions (requires IQ16 and IQ_VLDBMGMT license)
4- In the query text, specify only the columns needed.
Regards,
Tayeb.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.