Hey guys, I have a SQL Server database on my computer. I use openquery to pull data from an Oracle server. For some reason if I run an openquery that is too big it just hangs and never finishes. Five hours is the longest it will run and finish. For example, if I pulled data for past 3 months, it will take 4 hours. If I pulled data for the past 4 months, it will just appear to keep running and running indefinitely. I let it run for 72 hours once. I am trying to fix this problem, and I am not sure if it is on my end with SQL Server, or it is Oracle. I am not even sure if it is more hardware or software related. Any insight would be helpful, thanks.
![[H]ard|Forum](/styles/hardforum/xenforo/logo_dark.png){kind=logo}
Navigation
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
More options
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
SQL Server hangs... or is it Oracle?
- Thread starter coder_t2
- Start date
Ok, I might not understand the full functionality of SSIS, but it appears I can't use it in a stored procedure. This is a deal breaker for me since I have a C# program call stored procedures in my SQL Server database automatically to pull data and run reports. Interesting enough, if I run two openqueries at once, it hangs. However, my C# app called a stored procedure with openqueries in it and it worked fine, even though the openqueries in management studio were still "running".
This is all new information, and, consequently, have several places that issues could reside in. How about laying out what's in place, describe the stored procs a little (and which database they reside in), and why certain decisions were made on technical approaches.
EDIT: Also... If you ran the stored proc from SQL Server Management Studio (or TOAD, depending on which database the stored proc resides in), how long would it take to process?
EDIT: Also... If you ran the stored proc from SQL Server Management Studio (or TOAD, depending on which database the stored proc resides in), how long would it take to process?
Last edited:
The stored procedure is in the SQL Server DB. For the most part it is just a openquery. I use the parameters of the stored procedure to change the daterange.
Execution time between the running it in SQL Server Management Studio and my app is pretty negligible. It is shorter through the app, but it has the same issue where it hangs if the query is pulling too much data. If you want to get specific, if I run an openquery from Jan 09 to Sept 09, it takes 45 min. If I run from Jan 07 to Sept 09, it hangs.
Does that answer your questions? I am actually doing a test as I type this seeing if the openquery SQL will finish if I run it through TOAD. Obviously without the openquery function since I am directly connected to the Oracle DB. I will let you know how it turns out.
Execution time between the running it in SQL Server Management Studio and my app is pretty negligible. It is shorter through the app, but it has the same issue where it hangs if the query is pulling too much data. If you want to get specific, if I run an openquery from Jan 09 to Sept 09, it takes 45 min. If I run from Jan 07 to Sept 09, it hangs.
Does that answer your questions? I am actually doing a test as I type this seeing if the openquery SQL will finish if I run it through TOAD. Obviously without the openquery function since I am directly connected to the Oracle DB. I will let you know how it turns out.
Your answer partially answers my question. It tells me that you're getting consistent results when calling the stored proc alone (within SSMS) or the C# console app.
To get more into the data... How many rows get returned when the query works? How many rows would be returned when the query fails? How many tables are we talking about in the query? What kind of maintenance and indexing is happening on both the Oracle and SQL Server databases?
Part of SSIS is data transferring and execution order/branching. A simple SSIS package could stream records directly from Oracle to SQL Server, even managing any table/column reference or datatype changes along the way. It's fairly intuitive.
Your query is already built (in the stored proc), so the majority of the work is already done. The SQL Server "Books Online" or a Google search can get you going with a SSIS package in a short time.
To get more into the data... How many rows get returned when the query works? How many rows would be returned when the query fails? How many tables are we talking about in the query? What kind of maintenance and indexing is happening on both the Oracle and SQL Server databases?
Part of SSIS is data transferring and execution order/branching. A simple SSIS package could stream records directly from Oracle to SQL Server, even managing any table/column reference or datatype changes along the way. It's fairly intuitive.
Your query is already built (in the stored proc), so the majority of the work is already done. The SQL Server "Books Online" or a Google search can get you going with a SSIS package in a short time.
Why is it that you're using a stored procedure? Is this a job that you repeatedly execute? When you execute it again, do you execute it over the same date ranges, or do you execute it for new date ranges to bring different data back?
Why doesn't your C# app connect directly to Oracle?
You mention that you're selecting date from a range from another table. How many rows are in that table, total? Does that table have an index that satisfies the Oracle query processor so that you're not doing a table scan to find the dates your query should return?
The questions PTNL are asking about the Oracle database are important. Is that database being actively updated or maintained? If so, then odds are you're hitting a locking problem. That is, someone's working on the table, but you're trying to lock it to get your data back. What do the Oracle tools tell you about the state of your query when it "hangs" ?
What is it that your stored procedure does, anyway? You say that it selects data from the remote Oracle database using openrowset. What does it do with the data it retrieves? Does it loop over that data with a cursor? Is it doing a sort? Is it inserting the data to the local SQL Server? Is it filtering it in any way? Maybe it would be best if you showed the code for the SQL Server.
What do the SQL Server management tools tell you about the state of your stored procedure when it appears to be "hung"?
Why doesn't your C# app connect directly to Oracle?
You mention that you're selecting date from a range from another table. How many rows are in that table, total? Does that table have an index that satisfies the Oracle query processor so that you're not doing a table scan to find the dates your query should return?
The questions PTNL are asking about the Oracle database are important. Is that database being actively updated or maintained? If so, then odds are you're hitting a locking problem. That is, someone's working on the table, but you're trying to lock it to get your data back. What do the Oracle tools tell you about the state of your query when it "hangs" ?
What is it that your stored procedure does, anyway? You say that it selects data from the remote Oracle database using openrowset. What does it do with the data it retrieves? Does it loop over that data with a cursor? Is it doing a sort? Is it inserting the data to the local SQL Server? Is it filtering it in any way? Maybe it would be best if you showed the code for the SQL Server.
What do the SQL Server management tools tell you about the state of your stored procedure when it appears to be "hung"?
Last edited:
35To get more into the data... How many rows get returned when the query works?
0. It just hangs so it must be cancelled.How many rows would be returned when the query fails?
3How many tables are we talking about in the query?
All fields that are qualified on are indexed. It is doing a count of orders though. Which is probably why it takes so long. Thats millions and millions of rows. The Oracle database is maintained. The SQL Server database is my own personal thing. I use it to create local copies of the data so I can manipulate as I please. Much quicker this way, and I don't have to worry about polluting an actual production database with my junk.What kind of maintenance and indexing is happening on both the Oracle and SQL Server databases?
My previous post answers some of your questions, but I answered some more in this post as well.
1. Why is it that you're using a stored procedure? Is this a job that you repeatedly execute? When you execute it again, do you execute it over the same date ranges, or do you execute it for new date ranges to bring different data back?
I do use it repeatedly with the dates changes. Usually its a small range like a day or two. However, sometimes I need to do an adhoc report and run it for a longer period, such as 2 years.
2.Why doesn't your C# app connect directly to Oracle?
Because I create a local table on SQL Server. Which is then imported into excel via the C# app, and it formats the data into a pretty report. I also do it because I can manipulate the data anyway I please without affecting production data.
3. You mention that you're selecting date from a range from another table. How many rows are in that table, total? Does that table have an index that satisfies the Oracle query processor so that you're not doing a table scan to find the dates your query should return?
I only qualify on indexed fields. The main table is huge. Millions and millions of rows. I am qualifying on date and product. Daterange is 2 years. Only 4 products. But they total about 5 million orders in that timespan.
4. If so, then odds are you're hitting a locking problem. That is, someone's working on the table, but you're trying to lock it to get your data back. What do the Oracle tools tell you about the state of your query when it "hangs" ?
I highly doubt it is locking from someone else accessing it. Only 4 or 5 people use it, and not nearly as much as I do. Plus I run this at night, so no one would be accessing. I don't really have any software with Oracle tools to check the query. I use a freeware program called SQL Tools to connect to it directly.
5. What is it that your stored procedure does, anyway? You say that it selects data from the remote Oracle database using openrowset. What does it do with the data it retrieves? Does it loop over that data with a cursor? Is it doing a sort? Is it inserting the data to the local SQL Server? Is it filtering it in any way? Maybe it would be best if you showed the code for the SQL Server.
I'll post the code in the next post.
6. What do the SQL Server management tools tell you about the state of your stored procedure when it appears to be "hung"?
It says its running. But it can't be. I let it run for 72 hours once.
1. Why is it that you're using a stored procedure? Is this a job that you repeatedly execute? When you execute it again, do you execute it over the same date ranges, or do you execute it for new date ranges to bring different data back?
I do use it repeatedly with the dates changes. Usually its a small range like a day or two. However, sometimes I need to do an adhoc report and run it for a longer period, such as 2 years.
2.Why doesn't your C# app connect directly to Oracle?
Because I create a local table on SQL Server. Which is then imported into excel via the C# app, and it formats the data into a pretty report. I also do it because I can manipulate the data anyway I please without affecting production data.
3. You mention that you're selecting date from a range from another table. How many rows are in that table, total? Does that table have an index that satisfies the Oracle query processor so that you're not doing a table scan to find the dates your query should return?
I only qualify on indexed fields. The main table is huge. Millions and millions of rows. I am qualifying on date and product. Daterange is 2 years. Only 4 products. But they total about 5 million orders in that timespan.
4. If so, then odds are you're hitting a locking problem. That is, someone's working on the table, but you're trying to lock it to get your data back. What do the Oracle tools tell you about the state of your query when it "hangs" ?
I highly doubt it is locking from someone else accessing it. Only 4 or 5 people use it, and not nearly as much as I do. Plus I run this at night, so no one would be accessing. I don't really have any software with Oracle tools to check the query. I use a freeware program called SQL Tools to connect to it directly.
5. What is it that your stored procedure does, anyway? You say that it selects data from the remote Oracle database using openrowset. What does it do with the data it retrieves? Does it loop over that data with a cursor? Is it doing a sort? Is it inserting the data to the local SQL Server? Is it filtering it in any way? Maybe it would be best if you showed the code for the SQL Server.
I'll post the code in the next post.
6. What do the SQL Server management tools tell you about the state of your stored procedure when it appears to be "hung"?
It says its running. But it can't be. I let it run for 72 hours once.
Here is the query. I ran this directly on the Oracle database and it eventually finished. It took less than 8 hours. I think the issue is that the query is going over so much data that the SQL Server must eventually time-out or something. But it just never cancels the query.
select *
into dbo.adhocTemp
from openquery(myoracle, '
select a13.QTR,
a12.PRODUCT_NAME,
sum(a11.Orders) Orders
from COMN.T_DATA a11
join COMN.T_PRODUCT_WK a12
on (a11.PRODUCT_ID = a12. PRODUCT_ID )
join COMN.T_DISP_DATE a13
on (a11.DATE_OF_SVC = a13.DATE_OF_SVC and
a11.MONTH = a13.DISP_MONTH_ID)
where (a12.PRODUCT_NAME in (''CYMBALTA'', ''GABAPENTIN'', ''LYRICA'', ''SAVELLA'')
and a11.MONTH between 200701 and 200909)
group by a13. QTR,
a12.PRODUCT_NAME
')
select *
into dbo.adhocTemp
from openquery(myoracle, '
select a13.QTR,
a12.PRODUCT_NAME,
sum(a11.Orders) Orders
from COMN.T_DATA a11
join COMN.T_PRODUCT_WK a12
on (a11.PRODUCT_ID = a12. PRODUCT_ID )
join COMN.T_DISP_DATE a13
on (a11.DATE_OF_SVC = a13.DATE_OF_SVC and
a11.MONTH = a13.DISP_MONTH_ID)
where (a12.PRODUCT_NAME in (''CYMBALTA'', ''GABAPENTIN'', ''LYRICA'', ''SAVELLA'')
and a11.MONTH between 200701 and 200909)
group by a13. QTR,
a12.PRODUCT_NAME
')
If that query took eight hours when you ran it directly on your Oracle server, and it only returns 35 rows, the problem is obviously that you've badly designed the physical database on the Oracle server. You're missing some indexes, or you're using incredibly slow storage, or both. You could try adjusting the time out periods for your openrowset connection, but at that point, you're just treating symptoms when there's a very bad underlying issue on the Oracle system.
I didn't design the Oracle system, I just access it's data. It only returns 35 rows because it is grouped. It is actually going through and counting each individual order. The sum(a11.Orders) Orders line should actually read sum(a11.Orders_Cnt) Orders. Not sure how that got messed up in the copying. But essentially this column is a 0, 1. A 1 representing a filled order, a 0 for a cancelled order. There are about 300 million orders filled within that time period. about 5 million are for the products I have filtered. Is 8 hours really slow for that? And I am not sure if it even took 8 hours. I ran the query at the end of the day, went home. And checked it before I went to bed. I was disconnected from the server from a timeout, so I couldn't see a completion time.
Edit: The server that is used is a Sun Sunfire server. It is like 4-5 years old I believe.
Edit: The server that is used is a Sun Sunfire server. It is like 4-5 years old I believe.
Yes, I can see that it's counting each individual order.
You're unsure of your own numbers, but it's all I have to go on. There are 28,800 seconds in eight hours. If you're reading 300 million rows in eight hours, you're only processing (that is, reading and filtering) 10,416 rows per second. That's pretty slow. If you know your row size, you can multiply it by 10,416 to figure out how many bytes per second your server is handling. Your server should easily process 10 megabytes per second; is your row size 960 bytes?
Ideally, you're only looking at the 5 million rows, though, because the index will satisfy the filter. Then, the rates are even worse; 5 million records in eight hours means you're only processing 173 per second, which is miserable.
It doesn't matter who designed the Oracle system; that's not relevant. The problem is that it's either poorly designed or implemented, badly maintained, overwhelmed by its data volume, or some combination of the above. You're getting inadequate performance from it, and you need to fix that.
You're unsure of your own numbers, but it's all I have to go on. There are 28,800 seconds in eight hours. If you're reading 300 million rows in eight hours, you're only processing (that is, reading and filtering) 10,416 rows per second. That's pretty slow. If you know your row size, you can multiply it by 10,416 to figure out how many bytes per second your server is handling. Your server should easily process 10 megabytes per second; is your row size 960 bytes?
Ideally, you're only looking at the 5 million rows, though, because the index will satisfy the filter. Then, the rates are even worse; 5 million records in eight hours means you're only processing 173 per second, which is miserable.
It doesn't matter who designed the Oracle system; that's not relevant. The problem is that it's either poorly designed or implemented, badly maintained, overwhelmed by its data volume, or some combination of the above. You're getting inadequate performance from it, and you need to fix that.
It doesn't matter who designed the Oracle system; that's not relevant. The problem is that it's either poorly designed or implemented, badly maintained, overwhelmed by its data volume, or some combination of the above. You're getting inadequate performance from it, and you need to fix that.
Your probably right. Only thing is I don't have the access or ability to fix it. I am not the DBA. But I see what your getting at. I also noticed that it takes exponentially longer as I increase the time period. So 3 months takes about 5 minutes. 9 months takes about 45min. Is this normal?
The row size for the main table is like 130 different fields, majority of which are varchar. So it's big. I wouldn't be surprised if that is why its so slow.
Not trying to make a pompous "Captain Obvious" statement here, but yes, the more rows that get processed the longer the processing takes. But as mikeblas pointed out, the timeframe needed is horrendous and the query's scalability and efficiency needs to be looked at.Your probably right. Only thing is I don't have the access or ability to fix it. I am not the DBA. But I see what your getting at. I also noticed that it takes exponentially longer as I increase the time period. So 3 months takes about 5 minutes. 9 months takes about 45min. Is this normal?
Open up discussion with the DBA group, and see what maintenance is going on from a table performance level. See what kind of metrics they can provide, and location(s) of bottlenecks.
If this database is part of an OOB solution (ie: vendor product), then get them involved. Act as a facilitator of communication between you (the business owner), the DBA group (performance and maintenance owner), and the vendor (database design owner) to help push this through to resolution. Though don't expect any deep analysis from the vendor to be part of their regular support (this query and side application sounds like something completely from scratch, right?)
And on a related thought... Is your query the only one with bottlenecks, or does this happen on other stored procs or executed queries? One place you could look is other stored procs or queries used in report generation; something that aggregates a large group of data. See how long those queries take, and the execution path involved. This would also require partnering with the DBA group.
The tell is that it takes exponentially longer. That means the amount of work is being multiplied by something to result in an exponentially increasing amount of time. If it was a linear increase, then you'd know the indexes are doing their jobs.
Indeed, you'll have to talk to the DBA or the owner of that database (or that machine, or whatever). They should be willing to help you. If they're not, then your best bet will be either to do the query a month at a time, or to copy the all raw data to your own servers and start working on it there, where you can control the hardware, the indexing implementation, and the volume yourself.
While the latter approach can lead to success, I don't think it's something a successful employee would do. Working with people is a part of the job.
Indeed, you'll have to talk to the DBA or the owner of that database (or that machine, or whatever). They should be willing to help you. If they're not, then your best bet will be either to do the query a month at a time, or to copy the all raw data to your own servers and start working on it there, where you can control the hardware, the indexing implementation, and the volume yourself.
While the latter approach can lead to success, I don't think it's something a successful employee would do. Working with people is a part of the job.
Cool thanks for the replies. I'm going to start a discussion with the DBA and the vendor that maintains the hardware. The DBA was the designer of the datawarehouse. But I'll do what I have been doing and that is just doing queries in smaller timeframes. This is part of the reason why its really nice to be have my own local database to work with.