cancel
Showing results for 
Search instead for 
Did you mean: 

how to setup a query plan in effective at any time for SP or SQL query?

Former Member
0 Kudos

I have a SP which include a group by SQL statement. It retrieve data from a couple of tables which are over 1G size,

When I run this SP at first time, it take more than 5 minutes to get the result. then I run it again and again, Finally, it become very quick, I can get the result within second.

Not sure why. I guess it is because of query plan.

How to make it running at first time to get result within second? How to force a better best query plan in effective at first time to run the query?

If the engine has better plan in memory, could it be lost at some point? because I have the complain from end user said some times it is fast, sometime it is very slow.

How to resolve this problem?

Accepted Solutions (1)

Accepted Solutions (1)

kevin_sherlock
Contributor
0 Kudos

Kent,

It all depends on several factors.  Most obvious is if parameters are passed to the procedure that affect which or how many rows are evaluated and returned by the query.  More likely is it's simply a matter of the first execution having to get the data directly from disk using physical io and storing those rows in data cache.  The second execution comes along and finds all of the data in cache which is obviously much faster than disk io.  A stored procedure's query plan is stored in procedure cache and is most likely reused, so doubtful if the query plan is changed between executions.

Try using some "set" commands to illustrate this for you:

set statistics io on

go

exec myproc

go

Former Member
0 Kudos

Thanks, Kevin. turn statistics on and got the result like:

Total actual I/O cost for this command: 0.

Total writes for this command: 0

......

Table: mytab1 scan count 8560, logical reads: (regular=45886 apf=0 total=45886), physical reads: (regular=100 apf=34 total=134), apf IOs used=34

Table: mytab2 scan count 4, logical reads: (regular=8808 apf=7 total=8815), physical reads: (regular=1938 apf=312 total=2250), apf IOs used=300
Table: invent scan count 10139, logical reads: (regular=31167 apf=0 total=31167), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: mytab3 scan count 10139, logical reads: (regular=10139 apf=0 total=10139), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: Worktable1  scan count 5, logical reads: (regular=18041 apf=0 total=18041), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: Worktable2  scan count 0, logical reads: (regular=103 apf=0 total=103), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Total actual I/O cost for this command: 0.

Total writes for this command: 0

.....

Worktable1, Worktable2 does not exist in database, possible db engine created.  you are right, Possibly the result got from cache. QUestion is: how to allow users to get result as quick as possible even the user run the app to get the result at first time?

former_member188958
Active Contributor
0 Kudos

Hi Kent,

There are two main causes for longer first exections.

The first main cause is compilation time.  If there is no query plan for the procedure in procedure cache, the ASE optimizer has to compile a new plan from the query tree stored in sysprocedures.  This usually isn't an issue with short/simple stuff, but for complex queries involving many possible join orders, etc., compilation can take some time. To avoid this, you can capture/create an Abstract Query Plan and write the plan into the stored procedure source.   If the procedure is not run frequently enough (in comparision with other activity), it's plan can be aged out of cache, requiring recompilation again (unless an abstract plan was used).

The second main cause is uncached data. What you can do here is have some script run at startup that reads in what you think will be the most frequently used data to prime the cache.  After that, the cache will just manage itself to keep the most recently used data in memory.

If there are particularly hot tables that you want to keep cached, you can create named caches and bind those tables to the named cache so they will stay resident in memory.

-bret

kevin_sherlock
Contributor
0 Kudos

Yes, the worktables are created by the execution engine at runtime to store temporary results during query processing.  They are created for a variety of reasons.  One of those reasons is called reformatting, which can be a performance problem with your query.

You can create a named (dedicated) data cache for those tables if this is a concern.  Then, when you start ASE, you have to have a script execute your stored procedure so the data is brought to that cache for subsequent proc calls.

This isn't really the normal process though to solve your problem.  I would start with your query itself, and the query plan information.  It may be very possible to reduce your total io (whether they are cache hits, or misses) so that execution times are acceptable no matter the state of the cache.

Post your query, each table's ddl (exec sp_help <tablename>), and the query plan (set showplan on).

My other recommendation to you based on your other questions in the forum are to read the comprehensive Performance and Tuning manuals that address many of these topics in great detail.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc20020.1570/html/basics/title.htm

kevin_sherlock
Contributor
0 Kudos

Table Name                Scans    LReads    LR/Scan    PReads

-----------------    ----------   -------   --------  --------

Worktable1                    5     18041    3608.20         0

Worktable2                    0       103     103.00         0

invent                    10139     31167       3.07         0

mytab1                     8560     45886       5.36       134

mytab2                        4      8815    2203.75      2250

mytab3                    10139     10139       1.00         0

{TOTALS}                  28847    114151       3.96      2384

Above is a summary of your io for the tables (and a total).  If you look at the logical reads per scan (LR/Scan), you can tell that mytab3 looks like a lookup table, as is invent and both are indexed well.   If we had to guess at a join order it would be mytab2 -> mytab1 -> invent/mytab3 (in either order).  The presence of 2 worktables, and the high reads per scan of worktable1 suggest to me that it might be a reformatting of mytab2. 

In any case, it would be interesting to see the query, table definitions, and query plan (and possibly some table statisitics later if needed) as I requested above to see if we can do anything to improve the query plan.

Former Member
0 Kudos

thanks, kevin. Here is the pesudo query( I modify table name as business rule from my company). you are right, mytab3 is a lookup table.

Select d.stock,i.description,c.categoryname,
Round(IsNull(Sum(d.qty),0),2) AS qty,
.....
From mytab1 d,mytab2 s,invent i,mytab3 c       
Where
d.stock != 'param1'
And d.id1 = s.id1    --id1: univarchar(11)        
And i.code = c.code   --code:univarchar(2)         
And d.stock = i.stock  --stock: univarchar(12)           
And i.code2 = d.code2  --code2: univarchar(2)
And d.code2 = 'param2'
And s.id2 = 'param3'   --id2: univarchar(6)

Group By  c.categoryname,d.stock,i.description


Order By d.stock

here is the query plan when run this query:

The command completed with no results returned

QUERY PLAN FOR STATEMENT 1 (at line 1).
Executed in parallel by coordinating process and 4 worker processes.


    STEP 1
        The type of query is SELECT (into Worktable1).
        GROUP BY
        Evaluate Grouped SUM OR AVERAGE AGGREGATE.
        Evaluate Grouped SUM OR AVERAGE AGGREGATE.
        Evaluate Grouped SUM OR AVERAGE AGGREGATE.
        Executed in parallel by coordinating process and 4 worker processes.

        FROM TABLE
            mytab2
            s
        Nested iteration.
        Index : ind_mytab2 _id2
        Forward scan.
        Positioning by key.
        Keys are:
            id2  ASC
        Executed in parallel with a 4-way hash scan.
        Using I/O Size 16 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            mytab1
            d
        Nested iteration.
        Index : ind_det_inv
        Forward scan.
        Positioning by key.
        Keys are:
            id1  ASC
        Using I/O Size 16 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            invent
            i
        Nested iteration.
        Using Clustered Index.
        Index : invent_pk
        Forward scan.
        Positioning by key.
        Keys are:
            stock  ASC
            code2  ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            mytab3
            c
        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.
        TO TABLE
            Worktable1.

        Parallel work table merge.

    STEP 2
        The type of query is INSERT.
        The update mode is direct.
        Executed by coordinating process.
        Worktable2 created, in allpages locking mode, for ORDER BY.

        FROM TABLE
            Worktable1.
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 8 Kbytes for data pages.
        With MRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable2.

    STEP 3
        The type of query is SELECT.
        Executed by coordinating process.
        This step involves sorting.

        FROM TABLE
            Worktable2.
        Using GETSORTED
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 8 Kbytes for data pages.
        With MRU Buffer Replacement Strategy for data pages.

Total estimated I/O cost for statement 1 (at line 1): 1409882.

The sort for Worktable2 is done in Serial

kevin_sherlock
Contributor
0 Kudos

One way to improve (possibly only slightly) this query is to either add the column "code2" to the  ind_det_inv index on mytab1, or create a separate index on mytab1 on (code2,id1)  or  (id1,code2) depending on the selectivity of "code2" values.

Former Member
0 Kudos

Thank you Kevin. id1 is for master-detail relationship between Mytab1(detail) and Mytab2(master).

codes is kind of thing like department.  on mytab1, it already have index on id1(ind_det_inv index) nad index on code2(looks like not used by query plan). So if I create a new index on id1 and code2, which column order should be better,  (id1, code2) or (code2, id1)?

byt the way, id1 is not null.  code2 is nullable.

kevin_sherlock
Contributor
0 Kudos

I don't know because I don't know what your data looks like.  Try both and see which one reduces your total logical io.  Try some testing to see for yourself and let us know if either works very well.  As I said, no guarantees that it will significantly help you or not.   Best bet my be some combination of useful indexes, and a caching strategy that has already been suggested by a few of us on this thread.

Answers (0)