Solved

optimizer is not using the correct query plan

Posted on 2006-07-20
4
726 Views
Last Modified: 2011-10-03
HI,
I have a problem with a query that is not using the correct query plan and this is causing the stored proc to get stuck at a particular point.  I have the same stored procedure on 2 systems, production and uat. When I look at the query plan in uat it is correct. However when the same stored procedure is executed in production the query plan is not correct. There is a join that is not executed in the right order. What could be causing this ? Co-incidentally another error appeared in my logs at the same time this problem occured:

 Err Description: [SYBASE][OLE DB Sybase provider]Sybase does not allow more than one active statement when retrieving results without a cursor.

May not be related though but i'm guessing it is.
0
Comment
Question by:colechr
4 Comments
 
LVL 3

Accepted Solution

by:
knel1234 earned 500 total points
ID: 17145313
Hi,

With the information that you have supplied, I would try the following.
First, I would verify that the indexes are the same on this table for both environments.
This happens more than most of us would like to admit.  Second, I would run update stats.  Maybe your statistics are "out of wack".  Third, it looks like you are comparing a test and production environment.  If so, people often trim down their data to save money on disk space.  Please make sure that the cardinality of the fields in the index that you expected to be used are similar in both environments.  Lastly, you might want to drop and recreate the SP.  Just to make sure you are utilizing the correct query plan.  These should get you started.  If you could provide results and/or an indication of which of these you have attempted, that would be great.  Either way, I am sure a positive answer is coming your way.


cheers
knel  
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 17159198
Knel's points are all excellent. Unless your UAT database was dumped and loaded from production, you don't know if anything is the same in the two environments.

Rowcounts, indexes, optimiser statistics, even data fragmentation issues can all be reasons for the optimiser "doing the right thing" by picking a different query plan. I'd also suggest checking that the procedure itself is the same on both sides!!! (I had that once and it drove me crazy before I thought to check!)

Rowcounts are easy to check. Cardinality is a bit more difficult ("select count(distinct COLUMN)) ...") but could shed some useful light.

Note that running update stats will simply refresh the stats that are already there. So (say) if someone ran "update statistics [TABLE]" in one database, and "update index statistics [TABLE] using 200 values", just running "update statistics" will refresh those stats. ie. it's still possible for stats to be very different after an "update statistics" on both sides. If you really want stats to be identical, then perhaps do a "delete statistics" first. (Can you tell I've also had this happen to me?? 8-) ) Don't forget at a minimum you need to do an sp_recompile on the table after changing any stats...

Fragmentation is actually very important to the optimiser. I've seen a difference of just 0.05 in a DPCR (data page cluster ratio) cause a different join order. If you're running ASE 12.5.x of a recent vintage you have a function "derived_stats" which can give you this and other fragmentation stats, else just use "optdiag" (from the command line, like "bcp") and compare the various ratios it gives on the columns in the table. If this is an issue, you'd fix it with either "reorg rebuild" (if the table is locked datapages or datarows), or drop & recreate clustered index (if not).

Good luck!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sybase sql anywhere unload database 3 803
Best Fit Equation for a Line 5 831
SQL Query 2 325
How do I get Sybase ASE to throw an error that Java can catch? 4 511
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Note: This is the third blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   We’ve been talki…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now