Toad World® Forums

Multi threading and handling of Heavy queries

Hi!

I have two issues which are very related so I present both here.

If I run a heavy Query Toad gets unresponcive and some times freezes on the task bar without ability to activate the window.

With Oracle queries this is helped out somewhat because of the row fetch limitation but on Sybase this setting does not seem to be aknowledged?

Is the Toad Data Point Application not multi threaded?

It should be possible to run other tasks in TDP in parallell!

Arne

Found a way to solve the problem with the application beeing “frozen” on the taskbar:

Right click the app. and then pick the “Toad Data Point” window.

(May be a generic windows solution so sorry for the ignorance).

The Sybase issue still seems to remain an issue.

This isn't really a multi-threading issue. Oracle offers a lot more features to pause a query and leave the query open. Most other databases including Sybase don't really pause as they leave a lock on the table. You can extend the row limit to Sybase by going to Options | general | and chosing the Extend to editor row limit. If you use this then you are warned about the table lock.

On the "frozen" aspect, this should never happen. You mentioned that a long running query causes this? What action did you request/perform just before the freeze? Did you happen to sort a column? This is one area that might do this.

I have found canceling a query on a Sybase database to be useless as the cancel command is not processed until after the query has completed (then it returns nothing but takes just as long as letting it run to completion). Be sure the query is what you want before running it or make sure it includes limited data when testing as it is painful to run a large query by mistake on Sybase. This may depend on which version/flavor of Sybase you are using. The one I used to query for Facets was like that (Sybase ASE).

Run Set Rowcount 1000 before your query will limit the rows returned for all future queries on that session. Set Rowcount 0 will return it to normal. Newer versions of Sybase I believe incorporated the top statement (which is much better because it only applies to that query).

Sorting may have been the cause at one point in time, yes.

Yes, sorting is a hard area to handle. When we retrieve more than 2500 rows we serialize to disk and swap the info to the grid as a user scrolls. Works excellent for millions and millions of rows. But if you click to sort on a column of the grid we have to bring all of the rows into memory first and then sort and write back to disk. this takes time when involving many rows. We did some optimization on this awhile back but we could only do so much. Best to let your query do the sorting on the server.