Query Plan Query

We have a sql query that is causing a few issues in terms of
performance as the optimisor is choosing to table scan where
an index will certainly yield significant gains. As proven
when snapshotting the query and adding an index hint
manually.

Unfortunatley since the query is dynamically created by a
3rd party application, we are unable to influence this with
index hints and are dependant on the optimiser.

The table that is table scanning is partitioned (has also
hust been repartitioned to address a slight inbalance across
partitions) and has 7.6M records. It has been recently
reindexed so has a very low level of fragmentation on all
index and data pages. Update stats is also regularly run.

In test, I have removed our large i/o 16K buffer pool from
our data cache and the effect is that the optimiser now uses
the index. However, this is not particular a change I want
to make to production since I am sure the large i/o pool
will be used elsewhere. In addition I have disabled prefetch
on the table and this again influences the optimiser to use
the index. However, I am again reluctant to do this in
production as I am unsure of the wider implications on the
table usage elsewhere in the application.

Does anyone have any suggestions of anything else to
investigate/try or any comments on the 2 options explored
above.
Regards
Gary
0
Gary
7/18/2007 7:51:12 AM
sybase.ase.performance+tuning 2395 articles. 0 followers. Follow

2 Replies
944 Views

Similar Articles

[PageSpeed] 4

I think you are right to be careful with prefetch, on a huge apps with 
tons of sql,
there is often some sql codes that are affected, according my 
experience, and it can have terrible effects if the code is frequently 
used. May be you could try to catch with MDA tables or Monitor 
Historical server all the code accessing this table (and at which rate) 
and see if there are many execs or only a few execs and if there a lot 
of sql queries or not.

May be you could use abstract plan to link a custom execution plan to 
your specific query. It is often used on 3trd party application as it 
impacts only the concerned query.
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs44.htm

Rgds,
Emmanuel

Gary a �crit :
> We have a sql query that is causing a few issues in terms of
> performance as the optimisor is choosing to table scan where
> an index will certainly yield significant gains. As proven
> when snapshotting the query and adding an index hint
> manually.
> 
> Unfortunatley since the query is dynamically created by a
> 3rd party application, we are unable to influence this with
> index hints and are dependant on the optimiser.
> 
> The table that is table scanning is partitioned (has also
> hust been repartitioned to address a slight inbalance across
> partitions) and has 7.6M records. It has been recently
> reindexed so has a very low level of fragmentation on all
> index and data pages. Update stats is also regularly run.
> 
> In test, I have removed our large i/o 16K buffer pool from
> our data cache and the effect is that the optimiser now uses
> the index. However, this is not particular a change I want
> to make to production since I am sure the large i/o pool
> will be used elsewhere. In addition I have disabled prefetch
> on the table and this again influences the optimiser to use
> the index. However, I am again reluctant to do this in
> production as I am unsure of the wider implications on the
> table usage elsewhere in the application.
> 
> Does anyone have any suggestions of anything else to
> investigate/try or any comments on the 2 options explored
> above.
> Regards
> Gary
0
Emmanuel
7/18/2007 6:16:00 PM
I'd want to start with the following:

===========
set showplan on
go
dbcc traceon(3604,302,310)
go
set noexec on
go
<query>
go
===========

Then analyze the 302/310 output to see why the optimizer is choosing to ignore the index.

I'd also suggest running the above with the various changes in place (large IO buffer removed, no prefetch); primary 
objective here being to see what changes in the 302/310 output to see if this shines a light on the issue.


Gary wrote:
> We have a sql query that is causing a few issues in terms of
> performance as the optimisor is choosing to table scan where
> an index will certainly yield significant gains. As proven
> when snapshotting the query and adding an index hint
> manually.
> 
> Unfortunatley since the query is dynamically created by a
> 3rd party application, we are unable to influence this with
> index hints and are dependant on the optimiser.
> 
> The table that is table scanning is partitioned (has also
> hust been repartitioned to address a slight inbalance across
> partitions) and has 7.6M records. It has been recently
> reindexed so has a very low level of fragmentation on all
> index and data pages. Update stats is also regularly run.
> 
> In test, I have removed our large i/o 16K buffer pool from
> our data cache and the effect is that the optimiser now uses
> the index. However, this is not particular a change I want
> to make to production since I am sure the large i/o pool
> will be used elsewhere. In addition I have disabled prefetch
> on the table and this again influences the optimiser to use
> the index. However, I am again reluctant to do this in
> production as I am unsure of the wider implications on the
> table usage elsewhere in the application.
> 
> Does anyone have any suggestions of anything else to
> investigate/try or any comments on the 2 options explored
> above.
> Regards
> Gary
0
Mark
7/18/2007 11:40:26 PM
Reply:

Similar Artilces:

How to Query A Query
I have been trying to build a crosstab report that reports everydate within a selected range across the top and specific row information. 7/1 7/2 7/3 . . . Name Detail1 Detail2 Detail3 Name Detail1 What I get as output is only the dates that contain information in the rows. I can get the information I need by using a query within a query (nested). Can a nested query be done in Informaker 5? What about version 6 or 7? Thanks for your help Convert your SQL to syntax, and you can write anything you want. So if your DB support...

Query a Query
I have a result set in a query. Is it possible to query this query? (no specific SQL dialect, can it be done generically?) Petros -- PETROS GAVRIELIDES wrote: > I have a result set in a query. > Is it possible to query this query? > > (no specific SQL dialect, can it be done generically?) There is a generic answer to your question: Yes, there is a construct called a sub-query that allows you, in 1 SQL statement, to query from a query. But, generically speaking, not all DB engines support this construct. For instance, Firebird only added it just over a y...

How to query a query...
I'm working on converting an Access DB that I created into an ASP.NET application (and attempting to learn ASP at the same time...). One of my queries was very complex and required running a different query first, and then working off of that one. Any suggestions on the best way to do this in ASP? The following is a sample of code I'm using so you can see the type of data-binding I'm using. Thanks for the help! myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" + Server.MapPath("/EquipmentCheckout.mdb")) strS...

Query about Queries
Hello all, As a relatively new user to Sybase I am hoping someone can point me in the right direction. We are getting ready to go live with our database and I am wondering what types of queries I should be running for system performance purposes. If anyone has any suggestions they would be more than appreciated. Thanks in advance, Jerry ...

performing a query within a query
Hey folks, Was wondering (yet again) if there is an easy way of performing a query on another query. For example: I am displaying areas from one table and towns within that area from another table and the output should look like this: Area 1 ---Town A ---Town B I was wondering what is the best way to do this. Thanks for any help. Select AreaInfo,TownInfo FROM Area INNER JOIN town ON Area.AreaID=town.AreaID ORDER BY AreaInfo,TownInfo Then you would need to do the formatting in the Web page.Starting with ASP.NET 2.0? Look at:Programming Microsoft Web Forms My Blo...

Viewing the Query Plan of a SQL Statement
I a trying to view the query plan of an executing SPID. Here is the SQL I am running & running under sa role: declare @batch int declare @context int declare @statement int execute sp_showplan 141, @batch_id= @batch output, @context_id= @context output, @stmt_num=@statement output I plug in my spid id and the results returned state "The query plan for spid '141' is unavailable. Possibly the query has not started or has finished executing." However, I do get a value for @batch _id , that's 212638, but the @context_id is 0, and the @stmt_num is blank....

2 different query plans to same query
This is a multi-part message in MIME format. ---=_forums-1-dub4c742cb9 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have a proc which takes 20 seconds to be executed, but when I execute only the query of the proc (direct by SQL Advantage), its takes less than 1 second. Could someone help me to understand what is happening? The proc create 2 temporaries tables, insert data in those tables and use it in a select joining with others user tables. The proc doesn=92t have any input/output parameter. I got the both ...

Query generating diff query plan
Hi Select statement runs much faster if two variables are commented out from SQL( commented them out with /* */). Query plan generated are different which is causing the performance problem. Any idea why commenting out the two variables is causing change in query plan ? / * OR @sql22_h_CSM01_SCA_ID_1 = '' */ and /* OR @sql23_h_CSM01_SEC_ID_1 = '' */ 1) Query and Plan with variable commented out. ---------------------------------------------------------------------------------------------------------------------------- declare @sql19_h_C...

How to check if a query uses abstract query plan?
We have created abstract query plan for a few complicated queries and loaded them into ap_stdin. We want to find out whether the ap plan has been used when issue the query. How can we verify it? ASE 12.5.0.3 on HPUX. Any comment is appreciated! Hello, I think "set showplan on" before running the query should show if it is using the abstract plan or not: http://infocenter.sybase.com/help/topic/com.sybase.dc20022_1251/html/monitoring/monitoring127.htm Regards, Neal ...

Having trouble querying a query?
I'm trying to transition my reports from Access 2000 into Infomaker 7.0 but I'm having trouble combining two queries together. In Access you had to option of pulling tables or queries as your data, I don't see this option in Infomaker, am I missing something. Thanks from a newbie... IM doesn't have this feature, since it adheres to general Ansi SQL standards. If you show me the Access query, maybe I can give you a workaround. Are you querying an Access DB or a different DB, e.g. Oracle? -- Terry Dykstra (TeamSybase) Please state PB / OS / DB versions in your ...

Query and Sub-Queries
I have a SELECT statement that has two sub queries in it. The 'where' clause in the main query filters a TRADE_DATE field, for example: ...... where TRADE_DT >= '2003/01/01' and TRADE_DT <= '2003/01/31'. Since I only have one date field(TRADE_DATE), I want the sub-query to query where TRADE_DT <= '2003/01/31'. But its not working. I have to hard code the '2003/01/31'. Now I know I can make a dynamic view in VB, but an ideas of how I can do it in SQL? Here's my statement: select T.BROKER_SEQ_NO,B.BROKER_NAME,B.B...

Query of Queried data
I am new to asp.net after working with coldfusion.  I have created a datagrid from a query of a sql database.  I am interested to know how can I create another datagrid which will process a query of the data in the original grid.  So essentually it is a query of a query. Thank you   You want to filter on the result of the first query? The easiest way I can think of, is to put the data in a datatable, then use the select and dataview features of the datatable. Hi,uusquintsYou can filter the datatable in the code-behind and bind the result to the 2nd datagird:...

SQLite query and AS in query
This is my code. {code} q := TSQLQuery.Create(nil); try q.SQLConnection := MainForm.sqlite1; q.SQL.Text := sql; q.Open; finally q.Free; end; {code} And this is my SQL select id as _id, name, note as description from notes q.Fields[0].FieldName is id, it should be _id q.Fields[2].FieldName is note, it should be description Is this normal or am I missing something Grega This is frustrating. I can't work at all. http://pretty-software.com/images/xe4.jpg Edited by: grega loboda on Jun 4, 2013 1:40 AM http://stackoverflow.com/question...

Querying a Queried result...
Hi, is there such a thing as querying a queried result? Example, query 1 produces Result A. Result A's table is stored 'somewhere' to be queried by query 2 which produces Result B If there is, could you kindly direct me to a website with the appropiate article on this topic? Thanks, -Gabian- Yes there is, querying a View is querying a query rewrite. Send me an email the address is in my profile I will send you a Views tutorial I wrote a while back and run a search for query results from View in your BOL(books online) for code samples. Hope this helps. Kind regards, ...

Web resources about - Query Plan Query - sybase.ase.performance+tuning

Query - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Qubole Debuts Presto-As-A-Service, Based On Facebook-Created Open-Source Interactive Query System
Facebook announced last November that it would open-source interactive query system Presto , and now cloud big data platform provider Qubole ...

faroo_p2p: FAROO Search: Spelling correction, Query completion and Instant search http://t.co/TgHECDe5 ...
faroo_p2p: FAROO Search: Spelling correction, Query completion and Instant search http://t.

Query Strings - Quora
Quora is your best source for knowledge.

A Query A Quest A Quotation - Flickr - Photo Sharing!
One of a series of library education posters designed by Peabody Visual Aids in the 1930s and 1940s, which I salvaged from a throw-away pile ...

US to query China on passport maps
The US will raise concerns with China over a new map in Chinese passports which details claims to disputed maritime territory.

Rio Tinto worker sacked after saying 'fk you' to boss over safety query
The Fair Work Commission has answered that burning question: Is it fair to sack someone for saying &quot;*f*ck you&quot; to the boss?

Photos query over student who died in 40m cliff fall
Police are investigating whether a French student who plunged 40 metres off a cliff in the Royal National Park was taking or posing for photographs ...

'Pies query Cloke frees after AFL loss
Collingwood coach Nathan Buckley stresses Travis Cloke's lopsided free-kick count was not the No.1 issue after they lost to Richmond by five ...

Socceroos: Ange Postecoglou gets shirty with Tajik official's 'disrespectful' quit query
Socceroo boss Ange Postecoglou bristled, captain Tim Cahill looked bemused, and the Australian press corps could barely surpress their surprise. ...

Resources last updated: 12/21/2015 1:29:13 PM