[This reply is migrated from our old forums. The original author name has been removed]
Re: app periodically stalls after executing sql for about a minute....
Roger
I turned on the +Make Result Sets Read-Only,+ ran a few queries, some at the same time -- no issues found.
Roger Bjärevall
said
almost 11 years ago
[This reply is migrated from our old forums.]
Re: app periodically stalls after executing sql for about a minute....
Hi Alex,
What is the effect if you in Tool Properties and in the SQL Commander / Result Sets category, check "Make Result Sets Read-Only" and then re-run the query?
Regards
Roger
a
anonymous
said
almost 11 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: app periodically stalls after executing sql for about a minute....
Hans
Unlikely...... I do not recall anything running at that time before I selected the text and hit F9 (execute).... But I will try to wath it now..
What are those more than frequent +select user_name+..
BTW, I also had a few floating sql windows open.. just fyi
Hans Bergsten
said
almost 11 years ago
[This reply is migrated from our old forums.]
Re: app periodically stalls after executing sql for about a minute....
Hi Alex,
Ah, I see now that two separate SQL Commander tabs were used. Is it possible that you hit Execute in the SQL Commander with the script that got stuck while the SQL Commander for the large query was still processing the result?
Best Regards,
Hans
a
anonymous
said
almost 11 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: app periodically stalls after executing sql for about a minute....
Hans, no, i did not have them both in the same tab.. Those were two separate sql tabs..
And yes, now that you pointed out the "select user_name" calls, i cleared the Debug window, and executed the statement below..
------------------------- Product Information ------------------------
Product: DbVisualizer Pro 9.1.6
Build: #2117 (2014/01/26 21:20)
Java VM: Java HotSpot(TM) Client VM
Java Version: 1.6.0_65
Java Vendor: Sun Microsystems Inc.
OS Name: Windows 7
OS Arch: x86
OS Version: 6.1
------------------------- Connected Databases ------------------------
Database Type: Sybase ASE
Major Version: 15
Minor Version: 7
Profile: sybase-ase
Connection Message:
Adaptive Server Enterprise
Adaptive Server Enterprise/15.7.0/EBF 20809 SMP ESD#04 /P/x86_64/Enterprise Linux/ase157x/3245/64-bit/FBO/Fri Jan 4 05:11:12 2013
jConnect (TM) for JDBC (TM)
jConnect (TM) for JDBC(TM)/7.07 ESD #4 (Build 26792)/P/EBF20144/JDK 1.6.0/jdbcmain/OPT/Thu Jun 7 23:57:39 PDT 2012
--------------------------- Debug Printouts --------------------------
13:41:26 [DEBUG pool-3-thread-224 C.?] RootConnection: SybConnection.createStatement()
13:41:26 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.isValid] Executing connection validation statement: SELECT 1
13:41:26 [DEBUG pool-3-thread-224 B.?] RootConnection: SybStatement.executeQuery("SELECT 1")
13:41:26 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:41:26 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.isValid] Executing connection validation statement: SELECT 1
13:41:26 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("SELECT 1")
13:41:26 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:41:26 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:41:26 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:41:26 [DEBUG ExecutorRunner-pool-2-thread-132 J.execute] Executing...
13:41:26 [DEBUG ExecutorRunner-pool-2-thread-132 J.setCurrentCatalog] DbConnection='SYBAMR_ETMA_DEV_DS01' Catalog='tss_ds_dev2' Schema='null' NewCatalog='tss_ds_dev2'
13:41:26 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:41:26 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:41:26 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:41:26 [DEBUG ExecutorRunner-pool-2-thread-132 J.setCurrentSchema] DbConnection='SYBAMR_ETMA_DEV_DS01' Catalog='null' Schema='dbo' NewSchema='dbo'
13:41:26 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:41:26 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.execute("select
TrdDateNum = tdc.TrdDateNum
,Insatrument = im.Instrument
,BookID = im.BookID
,ProductID = 'SNGLST'
,GTR_SHARES = sum(gtr.SHARES)
,GTR_GRSCMM = sum(gtr.COMM + gtr.OTC)
,GTR_NETCMM = sum ( (gtr.COMM+gtr.OTC) *
case gtr.rec_type
when 'I' then IsNull(r.Inst,p.Inst)
when 'B' then IsNull(r.BCC,p.BCC)
when 'C' then IsNull(r.Citation,p.Citation)
when 'M' then IsNull(r.IAD,p.IAD)
when 'R' then IsNull(r.Retail,p.Retail)
end)
,creDate = getdate()
from GatorSumNegRate gtr
inner join cnr_RevenueAccts ra
on ra.ap_acct = isnull(gtr.revenue_account,'{null}')
and ra.status = 'A'
inner join gtr_sales_channel sc
on sc.sales_channel_cd = gtr.sales_channel
and sc.sales_channel_touch = 'H'
inner join iTB_TradeDateCal tdc (index idx0)
on tdc.ExchID = 'US'
and tdc.YearNum = datepart(year, gtr.trade_date)
and tdc.TradeDate = gtr.trade_date
inner join cemr_CurBudgetStatsInstrMap im (index BSIM_LOAD_PK)
on im.YearNum = datepart(year, gtr.trade_date)
and im.TrdDateNum = tdc.TrdDateNum
and im.Instrument = gtr.symbol
inner join cemr_CurBudgetStatsScope bs
on bs.YearNum = datepart(year, gtr.trade_date)
and bs.TrdDateNum = tdc.TrdDateNum
and bs.UntCode = 'D'
and bs.BookID = im.BookID
left join nasd_apps_PTS_Rates p
on p.RptType = 'CNR_REG'
and p.ProductID = 1
and (p.EffDate <= gtr.trade_date and gtr.trade_date <= isnull(p.EndDate, gtr.trade_date))
left join nasd_apps_IPSPTS_Rates r
on r.ProductID = 1
and r.cust_rollup_id = gtr.cust_rollup_id
where
gtr.trade_date >= '01/01/2014'
and (gtr.group_type = 'UC' OR gtr.group_type = 'NC')
and gtr.bus_product = 'SNGLST'
and isnull(gtr.product_code,'{null}') not in ('EQTYSPDR','EQTYMSCI')
group by tdc.TrdDateNum, im.Instrument, im.BookID")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:00 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:00 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
13:42:00 [DEBUG ExecutorRunner-pool-2-thread-132 F.?] Edit not allowed: More than one table is referenced in the FROM clause
13:42:01 [DEBUG ExecutorRunner-pool-2-thread-132 AbstractFacade.getColumn] executing "select user_name()"
13:42:01 [DEBUG pool-3-thread-224 C.?] DefaultEditor2191: SybConnection.createStatement()
13:42:01 [DEBUG pool-3-thread-224 B.?] DefaultEditor2191: SybStatement.executeQuery("select user_name()")
Hans Bergsten
said
almost 11 years ago
[This reply is migrated from our old forums.]
Re: app periodically stalls after executing sql for about a minute....
Hi Alex,
Thanks for the log. It's still not obvious what's join on, because the log just shows that execution of a simple query takes a long time and that should not freeze the GUI.
One thing that looks odd is the large number of executions of "select user_name()" after execution of the large SELECT starting at 09:19:32. DbVisualizer uses this query to get the current schema before and after executing a script. Do you always get this many "select user_name()" when you execute this large SELECT?
Execution of the script that gets stuck for 1 min 20 sec starts at 09:19:34, just one second after the execution of the large SELECT ends. It looks like you are executing it in the same SQL Commander tab as you used for the large SELECT. Is that true? If so, how did you manage to replace the large SELECT with the script in less than a second?
Best Regards,
Hans
a
anonymous
said
almost 11 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: app periodically stalls after executing sql for about a minute....
[This reply is migrated from our old forums. The original author name has been removed]
Re: app periodically stalls after executing sql for about a minute....
Hans
I will post the complete debug output the next time the issue occurs.
Thanks.
Hans Bergsten
said
almost 11 years ago
[This reply is migrated from our old forums.]
Re: app periodically stalls after executing sql for about a minute....
Hi Alex,
One more thing. Can you please run this again with debugging enabled and post the complete debug output from the execution. I can't match the logs you have posted so far to exactly what I get when running a test. It looks like you may have cut off parts of the log.
Best Regards,
Hans
Hans Bergsten
said
almost 11 years ago
[This reply is migrated from our old forums.]
Re: app periodically stalls after executing sql for about a minute....
Hi Alex,
Please try with the jTDS driver bundled with DbVisualizer.
Best Regards,
Hans
a
anonymous
said
almost 11 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: app periodically stalls after executing sql for about a minute....
Hans
I am not doing anything esle other than waiting for the app to unfreeze...
I am using jConnect-7_0 jconn4.jar driver
Hans Bergsten
said
almost 11 years ago
[This reply is migrated from our old forums.]
Re: app periodically stalls after executing sql for about a minute....
Hi Alex,
Okay, so then it was not related to the bug we fixed in 9.1.6 as I had assumed. Once again the log shows that it is the getColumns() call that actually takes 8 seconds.
Are you doing something else while you execute the statement in the SQL Commander, such as navigating the tree or opening Object View tabs for database objects?
Are using the latest supported driver for the database version? Have you tested with the jTDS driver (unclear which driver you use)?
Best Regards,
Hans
a
anonymous
said
almost 11 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: app periodically stalls after executing sql for about a minute....
Hans
The behavior persists: (Table presently has 11,275 rows and 53 columns...) this time is was stuck for 9 seconds before executing after i hit F9 (execute shortcut)
*11:51:23* [DEBUG pool-3-thread-42 E.?] DefaultEditor14: SybDatabaseMetaData.getColumns("tss_ds_dev2", "dbo", "cemr_TrdBudgetStats", "%")
11:51:*31* [DEBUG ExecutorRunner-pool-2-thread-29 AbstractFacade.getColumn] executing "select user_name()"
11:51:31 [DEBUG pool-3-thread-42 C.?] DefaultEditor14: SybConnection.createStatement()
11:51:31 [DEBUG pool-3-thread-42 B.?] DefaultEditor14: SybStatement.executeQuery("select user_name()")
Hans Bergsten
said
almost 11 years ago
[This reply is migrated from our old forums.]
Re: app periodically stalls after executing sql for about a minute....
Hi Alex,
It sounds like it may be the issue I mentioned ail be fixed in the next maintenance version that is causing this. It ail be released soon, so please upgrade when it becomes available and let us know if it solved the problem.
Best Regards,
Hans
a
anonymous
said
almost 11 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: app periodically stalls after executing sql for about a minute....
Hi. By 'unresponsive' i mean that the app does not respond to any mouse clicks or keystrokes. It freezes, and only after 40-60secons it starts to display the execution progress bar...
anonymous