Mindbending Puzzle (with bonus mindbending physics)

Jan 15, 2009 10:45

I have a puzzle at work. My puzzle works like so ( Read more... )

programming, bugs, work

Leave a comment

Comments 13

miusheri January 15 2009, 16:11:06 UTC
And it's not the 11th query that hangs- it hangs on query number 6 pretty consistently.


... )

Reply

t3knomanser January 15 2009, 16:59:44 UTC
Of course, query #2 is doing just fine. It hasn't been replaced yet.

Reply


muneco January 15 2009, 16:37:57 UTC
I remember in SQL 2000 it was possible to execute queries serially, and not quite release the locks from before, thus locking the db (well, the queries anyway. SQL server is pretty robust ( ... )

Reply

t3knomanser January 15 2009, 17:01:55 UTC
The issue is: query #6 works fine. Query #6 only breaks when there are more than 11 queries in the procedure, and only for one location. The query itself is absurdly simple, along the lines of: SELECT SUM(foo) FROM table WHERE location=X and account#=Y and month = 12 and year = 2008- no joins or anything that complex. Not that many records either.

When I run just query 6, it comes back quickly with the right result. When I run queries 1-10, query 6 comes back with a result. When I run queries 1-11, or even 1-5,7-12, it hangs at the sixth one executed, so long as the sixth one in the sequence is hitting Oracle Financials.

I'm trying to figure out how to get a TSql procedure to break things up into different batches, see if that helps. Maybe wrapping up some extra begins and ends or something.

Reply

muneco January 15 2009, 17:32:53 UTC
Is it possible to reorder it, such that 6 gets executed after 7 or before 5? My thought is then, study the crap out of 5... something there might be screwing things up.

How do ALL of them run together (sequentially) at once, when it's just pure sql in sql query analyzer? IE, no dynamic sql?

Reply

t3knomanser January 15 2009, 17:49:52 UTC
If they're not dynamic SQL, they're not running in SQL server. The whole job of my TSQL is to build queries and ship them off to other servers using OPENQUERY.

I see what you're saying though- I'd have to write some code to get it to output the code that it's running; I might do that, but I think I've found a lead.

Reply


kat1031 January 15 2009, 16:49:05 UTC
Have you eliminated any hardware issues? We had something like this happen and it turned out that there was some bad memory in the remote box which would intermittently make things puke.

Reply

t3knomanser January 15 2009, 16:59:13 UTC
It's too consistent for that- it only happens on this location and only when there's a series of queries with 11 or more steps, even when it hasn't executed that far along the path. It's possible, I guess, that it might be that this location ends up pulling enough data to hit that bad block of memory- but it's pulling less data than some and more data than others.

Reply

kat1031 January 15 2009, 17:03:22 UTC
Hmmm.... I guess the only way to test that on your end would be to try 11+ queries that don't hit that box at all.

I know next to nothing about Ora other than that just about anyone I know that works with it spends a lot of time wishing it in to the 11th circle of hell.

Reply


lordindra January 15 2009, 18:56:21 UTC
Never mind my deleted comment, I saw my idea answered in one of the comments. Missed it on first reading.

Reply


canissum January 15 2009, 19:58:01 UTC
Hmmm... I'm a bit late to the game, and my database experience is (painfully rusty). However, does I lookup the bits of the first query, execute it against a remote database, and get a number back. Then I move onto the next query.
mean that you run a query, get back a recordset, use the data, dispose of the recordset (aka complete the transaction), and then run the next query? How many tables are involved? How many other people are accessing those tables in the database at the same time? Is the likelihood of concurrent updates on the table(s) low, medium, or high?

Reply


Leave a comment

Up