企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

12
返回列表 发新帖
楼主: fs111

ChoreQuit Function

[复制链接]

73

主题

375

帖子

530

积分

高级会员

Rank: 4

积分
530
QQ
发表于 2014-6-29 20:59:23 | 显示全部楼层
dutchaussie wrote:I thought of that and I did chuck the SQL in SQL server.
The result: returning lines after 9 seconds and completely run after 30 seconds. Now I am not a SQL expert by all means (the contrary really, I have been using it for 2 months now) but to me sounds reasonable.
If you are using Oracle as the source for your TI process you may need to modify some settings on your TM1 server. Check out this link http://forums.olapforums.com/viewtopic.php?f=3&t=3557, especially the post about the SQLFetchScroll setting.
回复 支持 反对

使用道具 举报

75

主题

385

帖子

554

积分

高级会员

Rank: 4

积分
554
QQ
发表于 2014-6-29 21:18:20 | 显示全部楼层
Hi Tomok,

Thank you for your reply.
As mentioned in the post before we use SQL server as far as I understand that topic you were referring to is all about Oracle?
回复 支持 反对

使用道具 举报

82

主题

368

帖子

553

积分

高级会员

Rank: 4

积分
553
QQ
发表于 2014-6-29 21:35:39 | 显示全部楼层
dutchaussie wrote:As mentioned in the post before we use SQL server as far as I understand that topic you were referring to is all about Oracle?

That setting isn't just for Oracle, it;s just that Oracle is the only back-end database I've ever had to use it for. If you are having a problem with an SQL query timing out on your server that doesn't time out when you run it with another client front-end then it is probably worth a try. If it doesn't work you can always just revert back to the original setting.
回复 支持 反对

使用道具 举报

81

主题

411

帖子

598

积分

高级会员

Rank: 4

积分
598
QQ
发表于 2014-6-29 21:41:01 | 显示全部楼层
Not sure if you had any luck figuring out what is killing your TI routine.

Some things you can try.
Look in the message log and see if it's complaining in there. (right click on your server name in the server explorer).  Post anything you think might be related.
The ODBC connection has a logging facility, try turning that on and see if you get anything useful back.
Put some ASCIIOutput statements in your TI script so you can see how far the TI get's before it freezes.
Check the message logging on the SQL side, you'll probably need the SQL DBA for this.
Check that there's no activty on the SQL side that could block or put a lock on the table your reading from.
More of a tip this one, make sure your ODBC connection is using a dedicated log in.  I've had problems in the past with a shared account where other applications or users are using the same log-in as my TIs, if they put the password in wrong a few times, that locks the account which breaks your ODBC, cue much head scartching.

Might be worth posting all of your TI script maybe someone will spot an issue.

That's all I have I think, HTH

Cheers,
回复 支持 反对

使用道具 举报

77

主题

392

帖子

558

积分

高级会员

Rank: 4

积分
558
QQ
发表于 2014-6-29 21:45:31 | 显示全部楼层
Hi Steve,

Thanks for your suggestions.
I found out what is causing it, well almost. I def know it is  something with the SQL. As far as I am concerned it is a very basic SQL statement with one left join. It works fine in SQL server but TM1 doesnt like it at all!

Now the hard part is why TM1 doesnt like this particular SQL statement and that will take a bit more investigation from my end I guess. I have a workaround for now but it seems odd to me TM1 cannot handle this kind of queries.

I will take your suggestions on board and see what I can do.

Thanks again.

Cheers Niels
回复 支持 反对

使用道具 举报

77

主题

397

帖子

570

积分

高级会员

Rank: 4

积分
570
QQ
发表于 2014-6-29 22:03:37 | 显示全部楼层
If you can, remove the JOIN from your SQL by building a view that does all that lifting for you. Then all you have to do is SELECT ALL FROM YourView. I have had the same issue happen to me before so I just baked it into a view. Problem solved.
回复 支持 反对

使用道具 举报

66

主题

378

帖子

540

积分

高级会员

Rank: 4

积分
540
QQ
发表于 2014-6-29 22:12:27 | 显示全部楼层
Hi Tomok,

Thanks for that. I did get the same advise from someone else.
Another option I have heard is to index the actual column that joins the two tables.

Will try both and see which one works best.

Cheers NIels
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2019-6-27 09:55 , Processed in 0.177005 second(s), 8 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表