More discussions in SAP SQL Anywhere
Currently Being Moderated
Below 2 sql statements. Statement 1 is finished in 3 seconds. Statement 2 in 785 seconds.The only difference is the line "and service_date &= gt_periods.date_till" in the where clause.What can I do to improve the performance of the second statement? &Table cycle_results has 1555234 records. Both statements return 5 records because there i no data entered after .Statement 1 ( 3 seconds)&all sa_flush_cache();&delete gt_insert into gt_periods(comp_id, farm_id, date_from, date_till, sortno, periodtype) values (, date(''), date(''), 1, 1);&& &&&&&&&&&&& select count(*)&&& from cycle_results join arrival p_id = p_id and arrival.farm_id = cycle_results.farm_id and arrival.animal_id = cycle_results.animal_id, gt_periods& where&&&&&& gt_periods.periodtype = 1 &&&&&&&&&&&&&&&&&&& and service_date &= gt_periods.date_from&&&&&&&&&&&&&&&&&&& and p_id = p_id&&&&&&&&&&&&&&&&&&& and cycle_results.farm_id = gt_periods.farm_id &&&&&&&&&&&&&&&&&&& and gt_periods.sortno = 1&&&&&&&&&&&&&&&&&&& and arrival.arrival_date &= cycle_results.service_&Statement 2 (785 seconds)&all sa_flush_cache();&delete gt_insert into gt_periods(comp_id, farm_id, date_from, date_till, sortno, periodtype) values (, date(''), date(''), 1, 1);&& &&&&&&&&&&& select count(*)&&& from cycle_results join arrival p_id = p_id and arrival.farm_id = cycle_results.farm_id and arrival.animal_id = cycle_results.animal_id, gt_periods& where&&&&&& gt_periods.periodtype = 1 &&&&&&&&&&&&&&&&&&& and service_date &= gt_periods.date_from&&&&&&&&&&&&&&&&&&& and service_date &= gt_periods.date_till&&&&&&&&&&&&&&&&&&& and p_id = p_id&&&&&&&&&&&&&&&&&&& and cycle_results.farm_id = gt_periods.farm_id &&&&&&&&&&&&&&&&&&& and gt_periods.sortno = 1&&&&&&&&&&&&&&&&&&& and arrival.arrival_date &= cycle_results.service_
(in response to Eric Verhorstert)
Currently Being Moderated
I would donwload the latest patch available for SQL Anywhere 12.0.1 and test again (your build number is the GA version). If the performance does not improve, you might attach new query plans with statistics.Thanks
Alert Moderator
(in response to Eric Verhorstert)
Currently Being Moderated
Hi Eric,&To seriously analyze your issue, the information you provided is of limited use.1. The plans don't contain any actual values, just optimizer estimates. These in fact look pretty similar. If the actual run time is that different, something is likely to be wrong with the estimates.2. To get an idea what might be a reasonable access path, table definition and index definitions on your large table are required. Supplement is optimizer statistics (output from DBHist utility).Also your request looks inconsistent.1. The date_till in your example is & date_from, so the result should be empty2. Your description says that the statement including the "and service_date &= gt_periods.date_till" clause runs longer than the one w/out, but the names of the attached plans indicate the opposite.&The general surprise factor would be much smaller if the statement w/ the extra clause is faster or at least not slower than the one w/out. Whether or not it provides an actual restriction, it may tilt index usage etc.Finally, if you're actually restricting with only one row in the join, you'll be way better off by specifying the restrictions as parameters or literals. The join approach hides a lot of valuable information from the optimizer.&- Volker
Alert Moderator高性能嵌入式CPU特殊指令单元的设计与实现_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
高性能嵌入式CPU特殊指令单元的设计与实现
上传于||文档简介
&&嵌​入​式​C​P​U​ ​ ​ ​ ​设​计​与​实​现
阅读已结束，如果下载本文需要使用1下载券
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，查找使用更方便
还剩2页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢}