Query Syntax: procedure

Showing posts with label procedure. Show all posts

Friday, March 30, 2012

Query/Report processing time

I am having a frustrating problem with a report. It is pulling data from a
stored procedure, and I have tested it through query analyzer and i get
performance times of 4-5 seconds. However, when I select this same sproc
through Reporting Services during report building time, and hit the "!" to
pull the data, punch in the exact same parameters, it is taking upward of
30-40 seconds to pull the same data. What is the deal? It makes no rational
sense.How much data are we talking about? In QA, enable Client Statistics and what are the # of rows
affected and # of bytes sent?
Also, which query designer are you using? Does it have 4 panes and show tables or is it the 2-pane
textbox/grid designer? QA and the designers all use different underlying mechanisms for querying
the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses the SQL Managed provider.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> Well, that is just it. It's NOT during the actual report processing that I
> am having this problem, it is when I am loading the dataset under the "Data"
> tab. When I hit the "!" and enter in my params, the thing is taking forever.
> But when I run the same sproc in Query Analyzer, it is taking 4-5 seconds.
> So, this has nothing to do with actual report processing, it is something it
> is doing when it is grabbing the data that is f'ed up (if you know what I
> mean). Hence, when I deploy this thing (although the report is really
> simple) since it is taking it forever to grab data that should take a few
> seconds, it is taking like 45 seconds to render this report. It is a real
> pain in the ass.
> "Andrew Byrne" wrote:
> > My guess is that in design time you are running the query through the .NET
> > IDE so I presume there is a bigger memory footprint required.
> >
> > That lag experienced is probably due to RS not caching the report between
> > runs, something which was improved in SP1.
> >
> > Again, I would say .NET is involved if you experience lag the first time (in
> > a session) you run a report - the CLR needs to load assemblies, everything is
> > running under IIS etc.
> >
> > Don't get me wrong - I agree that RS could be a lot faster - just merely
> > trying to come up with plausible reasons for the reduction in speed !
> >
> > "Dan Fell" wrote:
> >
> > >
> > > I too suffer from similar problems. I think the problem seems to lie in the
> > > rendering part of the reporting. The queries themselves take relatively
> > > little time but the report can take ages to appear on screen.
> > >
> > > Not only that but it can be quite erratic. There a lag when the Report
> > > Manager's pages and reports are first rendered and then they seem to render
> > > much quicker afterwards. Is this normal?
> > >
> > > DF
> > >
> > > "isamu" wrote:
> > >
> > > > I am having a frustrating problem with a report. It is pulling data from a
> > > > stored procedure, and I have tested it through query analyzer and i get
> > > > performance times of 4-5 seconds. However, when I select this same sproc
> > > > through Reporting Services during report building time, and hit the "!" to
> > > > pull the data, punch in the exact same parameters, it is taking upward of
> > > > 30-40 seconds to pull the same data. What is the deal? It makes no rational
> > > > sense.|||I have experienced the same thing, even though I am designing/running
directly on the server hosting RS.
It seems to be intermittent. I have had a report take 2-3 minutes
everytime. This could go on for hours, even after a reboot. Then, out of
nowhere it takes 3 seconds. FYI: The same query always took about 1
second through query analyzer. This seems to be an RS rendering issue...
Dan
___________________________
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:36026EB9-5AD5-4355-9C15-B7573A5DAB56@.microsoft.com...
> Argh ... well, this is quite odd, after working on this for about 2 hours
> yesterday and even running it a few times today, it appears to have fixed
> itself ... I am not sure, but perhaps this was some sort of Network issue
at
> my client's site. Thanks a lot for your time, and sorry to trouble you.
I
> guess if this issue ever comes up again, have the network guys check their
> latencies.
> "Donovan R. Smith [MSFT]" wrote:
> > How much data are we talking about? In QA, enable Client Statistics and
what are the # of rows
> > affected and # of bytes sent?
> >
> > Also, which query designer are you using? Does it have 4 panes and show
tables or is it the 2-pane
> > textbox/grid designer? QA and the designers all use different
underlying mechanisms for querying
> > the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses
the SQL Managed provider.
> >
> > --
> > Thanks.
> >
> > Donovan R. Smith
> > Software Test Lead
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "isamu" <isamu@.discussions.microsoft.com> wrote in message
> > news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> > > Well, that is just it. It's NOT during the actual report processing
that I
> > > am having this problem, it is when I am loading the dataset under the
"Data"
> > > tab. When I hit the "!" and enter in my params, the thing is taking
forever.
> > > But when I run the same sproc in Query Analyzer, it is taking 4-5
seconds.
> > > So, this has nothing to do with actual report processing, it is
something it
> > > is doing when it is grabbing the data that is f'ed up (if you know
what I
> > > mean). Hence, when I deploy this thing (although the report is really
> > > simple) since it is taking it forever to grab data that should take a
few
> > > seconds, it is taking like 45 seconds to render this report. It is a
real
> > > pain in the ass.
> > >
> > > "Andrew Byrne" wrote:
> > >
> > > > My guess is that in design time you are running the query through
the .NET
> > > > IDE so I presume there is a bigger memory footprint required.
> > > >
> > > > That lag experienced is probably due to RS not caching the report
between
> > > > runs, something which was improved in SP1.
> > > >
> > > > Again, I would say .NET is involved if you experience lag the first
time (in
> > > > a session) you run a report - the CLR needs to load assemblies,
everything is
> > > > running under IIS etc.
> > > >
> > > > Don't get me wrong - I agree that RS could be a lot faster - just
merely
> > > > trying to come up with plausible reasons for the reduction in speed
!
> > > >
> > > > "Dan Fell" wrote:
> > > >
> > > > >
> > > > > I too suffer from similar problems. I think the problem seems to
lie in the
> > > > > rendering part of the reporting. The queries themselves take
relatively
> > > > > little time but the report can take ages to appear on screen.
> > > > >
> > > > > Not only that but it can be quite erratic. There a lag when the
Report
> > > > > Manager's pages and reports are first rendered and then they seem
to render
> > > > > much quicker afterwards. Is this normal?
> > > > >
> > > > > DF
> > > > >
> > > > > "isamu" wrote:
> > > > >
> > > > > > I am having a frustrating problem with a report. It is pulling
data from a
> > > > > > stored procedure, and I have tested it through query analyzer
and i get
> > > > > > performance times of 4-5 seconds. However, when I select this
same sproc
> > > > > > through Reporting Services during report building time, and hit
the "!" to
> > > > > > pull the data, punch in the exact same parameters, it is taking
upward of
> > > > > > 30-40 seconds to pull the same data. What is the deal? It
makes no rational
> > > > > > sense.
> >
> >
> >

Monday, March 26, 2012

Query VS Stored Procedure problem

Hello.

I am having a strange problem with SQL Server 2005. I have written a SELECT query that contains unions, joins and group functions. when the sql query is run using t-sql statements, the query completed execution in about 10-12 seconds. When the same query is written in a stored procedure without making any changes in the SELECT query (only adding a date parameter), it does not generate any result.

I waited for about 1 hour for the stored procedure to give me the result but it did not. Can anyone help me out with this problem?

Thanks in advance.

Raza:

Please provide a listing of your stored procedure.

Dave

|||

Definitely you need to provide the query, but also how much data is involved. Definitely look at the plans of the query (post them here too using set showplan_text on to get the plan) for clues as to what might be happening.

|||The data involved is huge (millions of rows) but regardless the sql statements copied from the proc and written in query window returns result in 5 - 7 seconds and when the same proc is executed it doesnot return any result.

I have MS Sql 2005 64 bit Enterprise Edition with SP1 installed.

here is the query.

SELECT sim.DEALER_CODE, sim.TRANSACTION_STAMP, sid.PRODUCT_CODE, dbo.REFERENCE_PRODUCT_CODES.SALE_PRICE AS UNIT_PRICE, sid.AMOUNT, sid.QUANTITY, sim.INVOICE_NUMBER, dbo.REFERENCE_TRANSACTION_TYPES.DESCRIPTION AS TRANSACTION_TYPE, sim.TRANSACTION_USER, icl.LOCATION_CODE, icl.REGION_NAME, icl.COUNTRY, (CASE WHEN dbo.REFERENCE_DEALER_CODES.DEALER_TYPE = 'I' THEN 'D' WHEN dbo.REFERENCE_DEALER_CODES.DEALER_TYPE = 'N' THEN 'D' ELSE 'E' END) AS DEALER_TYPE, sim.PARAMETER_1 AS ITEM_SERIAL FROM dbo.SALES_INVOICE_DETAIL AS sid INNER JOIN dbo.SALES_INVOICE_MASTER AS sim ON sid.INVOICE_NUMBER = sim.INVOICE_NUMBER INNER JOIN dbo.VIEW_USER_INFORMATION_COUNTRY_LEVEL AS icl ON sim.TRANSACTION_USER = icl.USER_ID INNER JOIN dbo.REFERENCE_TRANSACTION_TYPES ON sim.TRANSACTION_TYPE = dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE INNER JOIN dbo.REFERENCE_PRODUCT_CODES ON sid.PRODUCT_CODE = dbo.REFERENCE_PRODUCT_CODES.PRODUCT_CODE LEFT OUTER JOIN dbo.REFERENCE_DEALER_CODES ON sim.DEALER_CODE = dbo.REFERENCE_DEALER_CODES.DEALER_CODE WHERE (sim.TRANSACTION_STAMP BETWEEN CONVERT(CHAR(10), GETDATE() - 1, 101) AND CONVERT(CHAR(10), GETDATE() - 1, 101) + '

23:59:59') AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Thanks

|||

Some suggestions to identify the problem:

1- Limit the number of rows returned (maybe by adding an extra predicate) and see if the sproc returns any results at all. If the sproc is still hanging, it maybe an urelated issue with the query.

2- If the sproc returns results, try to open a cursor on the original query and print messages after every fetch to verify the query is returning results inside the proc.

Thanks.

|||

What do you mean "doesn't return any result" Do you mean it takes forever, or it returns no rows?

So the procedure is:

create procedure procName
as

Or is there anything else? I don't know why that wouldn't use as good of a plan as an ad hoc query...especially if you recompile the procedure.

|||You are using between to compare a string. This NEVER works.

Try this:

WHERE (sim.TRANSACTION_STAMP BETWEEN CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) AS DATETIME) AND CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) + '

23:59:59') AS DATETIME) AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))|||

obviously probelm is with the date. remove the date from SP and verify.

Can you explain what is your requierment on date field

|||Could you verify whether there any records which satisfies the date condition mentioned in the where clause?|||

Tom Phillips wrote:
You are using between to compare a string. This NEVER works.
Try this:
WHERE (sim.TRANSACTION_STAMP BETWEEN CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) AS DATETIME) AND CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) + ' 23:59:59') AS DATETIME) AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Tom,

That is not true. BETWEEN works with string values, the problem is that it is more difficult to anticipate the results, and a greater reliance upon good indexing. For example, try these two queries:

USE Northwind
GO

SELECT
EmployeeID,
LastName,
FirstName
FROM Employees
WHERE LastName BETWEEN 'a' AND 'f'

SELECT
OrderID,
OrderDate
FROM Orders
WHERE OrderDate BETWEEN cast( convert( char(10), getdate() - 3850, 101 ) AS datetime )
AND ( cast( convert( char(10), getdate() - 3800, 101 ) AS datetime ) + ' 23:59:59' )

|||If the query works as an ad hoc call, but not in a procedure, it is unlikely that there is anything
"wrong" with the query itself. There is something missing that needs to be supplied before we can make a judgment. Maybe a param or something... Or an IF...THEN around the query. We need to see the entire proc...|||

RazaRana wrote:
The data involved is huge (millions of rows) but regardless the sql statements copied from the proc and written in query window returns result in 5 - 7 seconds and when the same proc is executed it doesnot return any result.
...
WHERE (sim.TRANSACTION_STAMP BETWEEN CONVERT(CHAR(10), GETDATE() - 1, 101) AND CONVERT(CHAR(10), GETDATE() - 1, 101) + ' 23:59:59') AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))
Thanks

I don't think that this WHERE clause is correct and will work to return data. I suggest the following alteration:

WHERE ( sim.TRANSACTION_STAMP BETWEEN convert( char(10), getdate() - 1, 101 )
AND convert( char(10), getdate() - 1, 101 ) + ' 23:59:59' )
AND dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE NOT IN ( '2', '7' )
)

|||You are correct, it technically "works". I should have said "NEVER gives the expected results". :)

This is the 3rd time in 3 months I have seen someone trying to do this exact same WHERE clause with BETWEEN a date and 2 strings.|||Hi

Sorry for late reply. The query works perfectly fine and returns results (upto 15,000 rows) in less than 10 secs.

I use the same query in sproc, only the date is passed as a parameter. The sproc takes forever, i waited for 1 hr and 25 minutes and still no results.

The interesting thing is that i killed a few locks created on the tempdb and ran the sproc at midnight and it returned the results in about 10 secs.

Thanx

Friday, March 23, 2012

Query very slow when executed using Reporting Services

Hello,
I have a dataset that uses a stored procedure as the data source. In
the query analyzer, it takes just over 2 seconds to execute. However,
in the execute query mode in the Visual Studio reports designer, the
query takes several minutes.
The report, when run from the server, also takes a very long time to
complete.
There are only 4 parameters, two varchar(2) and two datetime fields.
The particular query I am running returns 12 rows. There are no filters
applied to the report, not that would affect the query in the VS
reports designer.
Does anybody have any ideas on how to improve the performance?
Thank you,
Brian TakitaFirst the good news. You really did a good job with the information you
provided here. The bad news, this is very strange. The most common reasons
for slowness is lots of data returned (but you only have 12 rows), second is
having a filter. The issue with a filter isn't a filter per se but the fact
that all the data is brought over prior to the filter being applied. If you
execute the stored procedure in query analyzer and it returns 12 rows there
then something else is going on.
Do you have a large amount of fields being returned?
How is your data source credential setup? I am wondering if something is
going on with regards to your credentials that is causing the problem.
Do you have another report that uses the same data source? If so, how is its
performance. If not, then try some simple report that uses the same data
source so we can eliminate that as a possible problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112301864.710648.22590@.z14g2000cwz.googlegroups.com...
> Hello,
> I have a dataset that uses a stored procedure as the data source. In
> the query analyzer, it takes just over 2 seconds to execute. However,
> in the execute query mode in the Visual Studio reports designer, the
> query takes several minutes.
> The report, when run from the server, also takes a very long time to
> complete.
> There are only 4 parameters, two varchar(2) and two datetime fields.
> The particular query I am running returns 12 rows. There are no filters
> applied to the report, not that would affect the query in the VS
> reports designer.
> Does anybody have any ideas on how to improve the performance?
> Thank you,
> Brian Takita
>|||Thank you for the prompt reply.
>Do you have a large amount of fields being returned?
Yes. There are 40 fields returned.
>How is your data source credential setup? I am wondering if something
is
>going on with regards to your credentials that is causing the problem.
I'm using integrated security.
>Do you have another report that uses the same data source? If so, how
is its
>performance. If not, then try some simple report that uses the same
data
>source so we can eliminate that as a possible problem.
I tried creating a report using the stored procedure and it is also
slow.|||You can try to pin point what's taking so long. Look in the ReportServer
database; the ExecutionLog
table. It shows what report ran, when, the amount of time for data
retrieval, processing time, and rendering time.
It also shows what render format, render status (fail or success), query
row count and byte size of
the rendered file.
Adrian M.
MCP
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
>>Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
>>How is your data source credential setup? I am wondering if something
> is
>>going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
>>Do you have another report that uses the same data source? If so, how
> is its
>>performance. If not, then try some simple report that uses the same
> data
>>source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Here are the result fields:
TimeDataRetrieval: 255840
TimeProcessing: 45
TimeRendering: 34
Status: rsSuccess
ByteCount: 54070
RowCount: 0
What is strange is when the report is executed on the server, the
RowCount is 0, but when executed from the VS report designer, I get the
correct number of rows, but it is also slow.|||When I try pulling a very similar report with the same parameters as a
pdf file I get the following results:
TimeDataRetrieval: 228716
TimeProcessing: 224082
TimeRendering: 45
Status: rsSuccess
ByteCount: 1872
RountCount: 0|||Is it possible to you to do a test where the stored procedure is returning
fewer fields (for the test just have it do 5 fields for instance) and then
create a report off of that. Let's see if the problem is the number of
fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
> >Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
> >How is your data source credential setup? I am wondering if something
> is
> >going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
> >Do you have another report that uses the same data source? If so, how
> is its
> >performance. If not, then try some simple report that uses the same
> data
> >source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Thank you for your help Bruce. Your heuristics to solve the problem
were invaluable.
I solved the issue but did not find the ultimate source of the problem.
To solve the issue I optimized the stored procedure. I removed an
insert statement that scanned a fairly large table.
My stored procedure is fairly complicated as it uses several table
variables.
It strange that the sp would work fine using the query analyzer but not
using Sql Server RS.
I also tried recreating the sp, making all of the functions called in
the sp inline. There was a test case in the sp that took a long time to
complete.
Maybe the sp caused the server to run into memory constraints?
Thanks again,
Brian Takita|||Problem solved. The ExecutionLog table is something I'll need remember.
Thank you Adrian.|||You might want to turn on SQL profiler and look at the statements that are
generated by Query Analyzer and Reporting Services (through sqlclient).
Sometimes, the acutal execution syntax will be slightly different even when
the results are the same. This is primarily in the way that sqlclient
prepares parameters and executes the stored procedure. This difference may
cause SQL Server not to use a compiled query plan.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112313413.140441.295110@.f14g2000cwb.googlegroups.com...
> Thank you for your help Bruce. Your heuristics to solve the problem
> were invaluable.
> I solved the issue but did not find the ultimate source of the problem.
> To solve the issue I optimized the stored procedure. I removed an
> insert statement that scanned a fairly large table.
> My stored procedure is fairly complicated as it uses several table
> variables.
> It strange that the sp would work fine using the query analyzer but not
> using Sql Server RS.
> I also tried recreating the sp, making all of the functions called in
> the sp inline. There was a test case in the sp that took a long time to
> complete.
> Maybe the sp caused the server to run into memory constraints?
> Thanks again,
> Brian Takita
>

Wednesday, March 21, 2012

Query too slow! Need some performance enhancing tips!

I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow for my
requirements.

Is there any way I can optimize this query. I have thought about
using an indexed view. I haven't done one before, does anyone know if
this would have potential to improve performance or indeed any other
performance enhancing techniques I might try.

SELECT vehicle.vehicle_id
FROM (( [vehicle]
INNER JOIN [vehicle_subj_item_assn] on
vehicle.vehicle_id=[vehicle_subj_item_assn].vehicle_id)
INNER JOIN [subj_item] on
[vehicle_subj_item_assn].subj_item_id=[subj_item].subj_item_id)
INNER JOIN [template_field] on
[subj_item].subj_item_id=[template_field].subj_attr_id
WHERE
([template_field].template_field_id=@.template_field_id) AND
([template_field].template_field_type_id=3) AND
([vehicle_subj_item_assn].subj_item_value_text=@.value) AND
(vehicle.end_dtm IS NOT NULL)

Thanks
GavinOn 1 Dec 2004 06:17:40 -0800, gizmo@.consultant.com wrote:

>I have a stored procedure that queries a database using a Select
>statement with some inner joins and conditions. With over 9 million
>records it takes 1 min 36 sec to complete. This is too slow for my
>requirements.
>Is there any way I can optimize this query. I have thought about
>using an indexed view. I haven't done one before, does anyone know if
>this would have potential to improve performance or indeed any other
>performance enhancing techniques I might try.
>SELECT vehicle.vehicle_id
>FROM (( [vehicle]
>INNER JOIN [vehicle_subj_item_assn] on
>vehicle.vehicle_id=[vehicle_subj_item_assn].vehicle_id)
>INNER JOIN [subj_item] on
>[vehicle_subj_item_assn].subj_item_id=[subj_item].subj_item_id)
>INNER JOIN [template_field] on
>[subj_item].subj_item_id=[template_field].subj_attr_id
>WHERE
>([template_field].template_field_id=@.template_field_id) AND
>([template_field].template_field_type_id=3) AND
>([vehicle_subj_item_assn].subj_item_value_text=@.value) AND
>(vehicle.end_dtm IS NOT NULL)
>Thanks
>Gavin

Hi Gavin,

I don't think you need all those paretheses and brackets. They don't hurt
performance, but they do make the query harder to read.

Also, you never use any column in subj_item. I think you can remove that
table (unless it is ppossible that some value of subj_item_id that does
not exist in subj_item does exist in vehicle_subj_item_assn.subj_item_id
and template_field.subj_attr_id). I don't see any way to improve on the
remaining query:

SELECT vehicle.vehicle_id
FROM vehicle
INNER JOIN vehicle_subj_item_assn
ON vehicle.vehicle_id = vehicle_subj_item_assn.vehicle_id
INNER JOIN template_field
ON vehicle_subj_item_assn.subj_item_id=template_field .subj_attr_id
WHERE template_field.template_field_id = @.template_field_id
AND template_field.template_field_type_id = 3
AND vehicle_subj_item_assn.subj_item_value_text = @.value
AND vehicle.end_dtm IS NOT NULL
(untested)

An other way to optimize this is to look at your indexes. I don't think an
indexed view will do you much good (though you can always try, of course -
remember to test a typical workload, as this specific information will
return faster, but update performance will suffer) - indexed views are
often used for views with aggregations.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(gizmo@.consultant.com) writes:
> I have a stored procedure that queries a database using a Select
> statement with some inner joins and conditions. With over 9 million
> records it takes 1 min 36 sec to complete. This is too slow for my
> requirements.
> Is there any way I can optimize this query. I have thought about
> using an indexed view. I haven't done one before, does anyone know if
> this would have potential to improve performance or indeed any other
> performance enhancing techniques I might try.

You could materialize the query into an indexed view, but I would suspect
that it is an overkill.

You can probably improve performance considerably by reviewing indexes.
96 seconds for 9 millions rows sounds like a table is being scanned
somewhere. However, I cannot do that for you, since you have not submitted
enough with information.

I would suggest that you post the CREATE TABLE and CREATE INDEX statments
for your tables, as well as the approxamite rowcount for the tables.

Also consider Hugo's note about the subj_item table. It may not fill a
function in the query. (Then again, it could serve as an EXISTS condition.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Have you checked out http://www.xprime.com

We've been using their database accelerator for MS SQL with good
results... 5X on many of our stored procs, similar to those you
describe below.

Tony

gizmo@.consultant.com wrote:
> I have a stored procedure that queries a database using a Select
> statement with some inner joins and conditions. With over 9 million
> records it takes 1 min 36 sec to complete. This is too slow for my
> requirements.
> Is there any way I can optimize this query. I have thought about
> using an indexed view. I haven't done one before, does anyone know
if
> this would have potential to improve performance or indeed any other
> performance enhancing techniques I might try.
> SELECT vehicle.vehicle_id
> FROM (( [vehicle]
> INNER JOIN [vehicle_subj_item_assn] on
> vehicle.vehicle_id=[vehicle_subj_item_assn].vehicle_id)
> INNER JOIN [subj_item] on
> [vehicle_subj_item_assn].subj_item_id=[subj_item].subj_item_id)
> INNER JOIN [template_field] on
> [subj_item].subj_item_id=[template_field].subj_attr_id
> WHERE
> ([template_field].template_field_id=@.template_field_id) AND
> ([template_field].template_field_type_id=3) AND
> ([vehicle_subj_item_assn].subj_item_value_text=@.value) AND
> (vehicle.end_dtm IS NOT NULL)
> Thanks
> Gavin

Query too slow

Excuse me in advance fo my little English.

I've got this stored procedure

************************************************** **************************
**********

declare @.Azienda as varchar(3), @.Utente as varchar(20),
@.DataDa as datetime, @.DataA as datetime,
@.AreaDa as varchar(3), @.AreaA as varchar(3),
@.LineaDa as varchar(3), @.LineaA as varchar(3),
@.TipoDa as varchar(3), @.TipoA as varchar(3),
@.FamigliaDa as varchar(3), @.FamigliaA as varchar(3),
@.ProdottoDa as varchar(20), @.ProdottoA as varchar(20),
@.AgenteDa as varchar(4), @.AgenteA as varchar(4),
@.NazioneDa as varchar(50), @.NazioneA as varchar(50),
@.ZonaDa as Varchar(3), @.ZonaA as Varchar(3),
@.ProvinciaDa as varchar(2), @.ProvinciaA as varchar(2),
@.ClienteDa as Varchar(12), @.ClienteA as Varchar(12),
@.DestinDa as varchar (5), @.DestinA as varchar (5),
@.TipoDestinDa as varchar(1), @.TipoDestinA as varchar(1),
@.FlagProdNoTarget as varchar(5),
@.GrAcqDa as varchar(10), @.GrAcqA as varchar(10),
@.TipoCliDa as varchar(3), @.TipoCliA as varchar(3),
@.SettMercDa as varchar(3), @.SettMercA as varchar(3)

Set @.Azienda = '900'
Set @.Utente = 'Eugenio'
Set @.DataDa = '2004-01-01'
Set @.DataA = '2004-01-10'
Set @.AreaDa = 'UNI'
Set @.AreaA = 'UNI'
Set @.LineaDa = ''
Set @.LineaA = 'ZZZ'
Set @.TipoDa = ''
Set @.TipoA = 'ZZZ'
Set @.FamigliaDa = ''
Set @.FamigliaA = 'ZZZ'
Set @.ProdottoDa = ''
Set @.ProdottoA = 'ZZZZZZZZZZZZZZZZZZZZ'
Set @.AgenteDa = ''
Set @.AgenteA = 'ZZZZ'
Set @.NazioneDa = ''
Set @.NazioneA = 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZ'
Set @.ZonaDa = ''
Set @.ZonaA = 'ZZZ'
Set @.ProvinciaDa = ''
Set @.ProvinciaA = 'ZZ'
Set @.ClienteDa = ''
Set @.ClienteA = 'ZZZZZZZZZZZZ'
Set @.DestinDa = ''
Set @.DestinA = 'ZZZZZ'
Set @.TipoDestinDa = ''
Set @.TipoDestinA = 'Z'
Set @.FlagProdNoTarget = 'Vero'
Set @.GrAcqDa = ''
Set @.GrAcqA = 'ZZZZZZZZZZ'
Set @.TipoCliDa = ''
Set @.TipoCliA = 'ZZZ'
Set @.SettMercDa = ''
Set @.SettMercA = 'ZZZ'

Select WSDFR.AreaCommerciale,
WSDFR.Agente,
WSDFR.NazDestin,
WSDFR.ZonaDestin,
WSDFR.ProvDestin,
WSDFR.Cliente,
WSDFR.DescrCliente,
WSDFR.GruppoAcq,
WSDFR.TipoCli,
WSDFR.SettMerc,
WSDFR.CDestin,
WSDFR.DescrDestin,
WSDFR.TipoDestin,
WSDFR.EsclStatis,
WSDFR.EsclTarget,
WSDFR.ValoreNetto,
WSDFR.TpDocum,
WSDFR.VCambioITL,
WSDFR.VCambioEUR,
WSDFR.MeseFatt,
WSDFR.Posizione

From W_St_DocFatt_Righe WSDFR
inner join UniP_Prodotti UPP on
WSDFR.prodotto=UPP.Cod
Where WSDFR.Dtdocum between @.DataDa and @.DataA and
WSDFR.AreaCommerciale between @.AreaDa and @.AreaA and
WSDFR.LineaProdotto between @.LineaDa and @.LineaA and
WSDFR.TipoProdotto between @.TipoDa and @.TipoA and
WSDFR.FamigliaProdotto between @.FamigliaDa and @.FamigliaA and
WSDFR.Prodotto between @.ProdottoDa and @.ProdottoA and
WSDFR.Agente between @.AgenteDa and @.AgenteA

************************************************** **************************
************************

"W_St_DocFatt_Righe" is a view.

This query run on my SQL7 server and it takes about 10 seconds.
This query exists on another SQL7 server and until last week it took about
10 seconds.
The configuration of both servers are same. Only the hardware is different.

Now, on the second server this query takes about 30 minutes to extract the s
ame details, but anybody has changed any details.

If I execute this query without Where, it'll show me the details in 7
seconds.
This query still takes about same time if Where is

Where WSDFR.Dtdocum between @.DataDa and @.DataA and
WSDFR.AreaCommerciale between @.AreaDa and @.AreaA and
WSDFR.LineaProdotto between @.LineaDa and @.LineaA and
--WSDFR.TipoProdotto between @.TipoDa and @.TipoA and
--WSDFR.FamigliaProdotto between @.FamigliaDa and @.FamigliaA and
WSDFR.Prodotto between @.ProdottoDa and @.ProdottoA and
WSDFR.Agente between @.AgenteDa and @.AgenteA

Where WSDFR.Dtdocum between @.DataDa and @.DataA and
WSDFR.AreaCommerciale between @.AreaDa and @.AreaA and
--WSDFR.LineaProdotto between @.LineaDa and @.LineaA and
--WSDFR.TipoProdotto between @.TipoDa and @.TipoA and
WSDFR.FamigliaProdotto between @.FamigliaDa and @.FamigliaA and
WSDFR.Prodotto between @.ProdottoDa and @.ProdottoA and
WSDFR.Agente between @.AgenteDa and @.AgenteA

Where WSDFR.Dtdocum between @.DataDa and @.DataA and
WSDFR.AreaCommerciale between @.AreaDa and @.AreaA and
--WSDFR.LineaProdotto between @.LineaDa and @.LineaA and
--WSDFR.TipoProdotto between @.TipoDa and @.TipoA and
--WSDFR.FamigliaProdotto between @.FamigliaDa and @.FamigliaA and
--WSDFR.Prodotto between @.ProdottoDa and @.ProdottoA and
WSDFR.Agente between @.AgenteDa and @.AgenteA

It is a real puzzle!

What happen?

Is there someone that had such as problems and have the right solution?

Thanks in advance.
Bye
EugenioEugenio (Ciao@.Eugenio.it) writes:
> This query run on my SQL7 server and it takes about 10 seconds.
> This query exists on another SQL7 server and until last week it took about
> 10 seconds.
> The configuration of both servers are same. Only the hardware is
> different.
> Now, on the second server this query takes about 30 minutes to extract
> the s ame details, but anybody has changed any details.
> If I execute this query without Where, it'll show me the details in 7
> seconds.
> This query still takes about same time if Where is

With out knowledge about the underlying tables in the view and their
indexes, I can only answer in general terms.

SQL Server uses a cost-based optimizer. This optimizer evaluates a number
of possible query plans, and estimates which plan will give the best
performance. As basis for its decisions it uses statistics about the
table which holds the distribution of the data in the various columns.
By default these statistics are updated automatically, usually in
conjunction with SQL Server querying the tables.

There are plenty of possibilities for optimizer to go wrong. For instance
if the statistics are somewhat skewed, a small error in the first table
when computing the cost of a certain join order, may be a large error
in the last, and incorrectly lead to the wrong plan. There are also
systematic errors; the optimizer does not know about correlation between
columns, for instance that OrderDate and InvoiceDate tend follow each
other.

Because of this, it can well happen that a query that executed well
yesterday, suddenly executes much slower, because you've execeeded some
threshold which causes SQL Server to pick a bad plan.

The first you should do is to verify that the involved tables have the
same set of indexes in both servers. You could also try UPDATE STATISTICS
WITH FULLSCAN on the problematic server, this may remove inaccuracies in
the statistics. You should also use DBCC SHOWCONTIG on the tables and
observe fragmentation. Use DBCC DBREIDNEX to defragment the tables.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns94F09D378E44FYazorman@.127.0.0.1...
> Eugenio (Ciao@.Eugenio.it) writes:
> > This query run on my SQL7 server and it takes about 10 seconds.
> > This query exists on another SQL7 server and until last week it took
about
> > 10 seconds.
> > The configuration of both servers are same. Only the hardware is
> > different.
> > Now, on the second server this query takes about 30 minutes to extract
> > the s ame details, but anybody has changed any details.
> > If I execute this query without Where, it'll show me the details in 7
> > seconds.
> > This query still takes about same time if Where is
> With out knowledge about the underlying tables in the view and their
> indexes, I can only answer in general terms.
> SQL Server uses a cost-based optimizer. This optimizer evaluates a number
> of possible query plans, and estimates which plan will give the best
> performance. As basis for its decisions it uses statistics about the
> table which holds the distribution of the data in the various columns.
> By default these statistics are updated automatically, usually in
> conjunction with SQL Server querying the tables.
> There are plenty of possibilities for optimizer to go wrong. For instance
> if the statistics are somewhat skewed, a small error in the first table
> when computing the cost of a certain join order, may be a large error
> in the last, and incorrectly lead to the wrong plan. There are also
> systematic errors; the optimizer does not know about correlation between
> columns, for instance that OrderDate and InvoiceDate tend follow each
> other.
> Because of this, it can well happen that a query that executed well
> yesterday, suddenly executes much slower, because you've execeeded some
> threshold which causes SQL Server to pick a bad plan.
> The first you should do is to verify that the involved tables have the
> same set of indexes in both servers. You could also try UPDATE STATISTICS
> WITH FULLSCAN on the problematic server, this may remove inaccuracies in
> the statistics. You should also use DBCC SHOWCONTIG on the tables and
> observe fragmentation. Use DBCC DBREIDNEX to defragment the tables.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks Erland
I'll try it immediatly.
Bye
Eugenio|||>
> With out knowledge about the underlying tables in the view and their
> indexes, I can only answer in general terms.
> SQL Server uses a cost-based optimizer. This optimizer evaluates a number
> of possible query plans, and estimates which plan will give the best
> performance. As basis for its decisions it uses statistics about the
> table which holds the distribution of the data in the various columns.
> By default these statistics are updated automatically, usually in
> conjunction with SQL Server querying the tables.
> There are plenty of possibilities for optimizer to go wrong. For instance
> if the statistics are somewhat skewed, a small error in the first table
> when computing the cost of a certain join order, may be a large error
> in the last, and incorrectly lead to the wrong plan. There are also
> systematic errors; the optimizer does not know about correlation between
> columns, for instance that OrderDate and InvoiceDate tend follow each
> other.
> Because of this, it can well happen that a query that executed well
> yesterday, suddenly executes much slower, because you've execeeded some
> threshold which causes SQL Server to pick a bad plan.
> The first you should do is to verify that the involved tables have the
> same set of indexes in both servers. You could also try UPDATE STATISTICS
> WITH FULLSCAN on the problematic server, this may remove inaccuracies in
> the statistics. You should also use DBCC SHOWCONTIG on the tables and
> observe fragmentation. Use DBCC DBREIDNEX to defragment the tables.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

I did that, but it's changed nothing.
Can you give me others suggestion about this.

Thanks a lot
Eugenio|||Eugenio (Ciao@.Eugenio.it) writes:
> I did that, but it's changed nothing.
> Can you give me others suggestion about this.

No. Not without more information. Please post the following:

o CREATE TABLE and CREATE INDEX statements for the involved tables.
o The output when you run the procedures with SHOW STATISTICS PROFILE ON
on *both* servers. Since the output of the query plan is very wide,
please but this output in an attachment. (In a text file.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 25 May 2004 09:01:31 +0200, Eugenio wrote:

> I did that, but it's changed nothing.
> Can you give me others suggestion about this.

Recompile the view so that the stored plan will take into account the
updated statistics.

Use the Query Plan Optimizer in Query Analyzer to get suggestions about
indexes to add.

Shouldn't all those Set @.var... statments be in a single SELECT statement?

Does it make sense to have a
WHERE ... WSDFR.AreaCommerciale between @.AreaDa and @.AreaA
subclause, when your set statement sets them to the same value? Why not
WHERE ... WSDFR.AreaCommerciale = 'UNI'
?|||"Ross Presser" <rpresser@.imtek.com> ha scritto nel messaggio
news:gr5k7s2nbwbe.gzvmi45ehns7.dlg@.40tude.net...
> On Tue, 25 May 2004 09:01:31 +0200, Eugenio wrote:
> > I did that, but it's changed nothing.
> > Can you give me others suggestion about this.
>
> Recompile the view so that the stored plan will take into account the
> updated statistics.
> Use the Query Plan Optimizer in Query Analyzer to get suggestions about
> indexes to add.

I did that, but it's changed nothing

> Shouldn't all those Set @.var... statments be in a single SELECT statement?
> Does it make sense to have a
> WHERE ... WSDFR.AreaCommerciale between @.AreaDa and @.AreaA
> subclause, when your set statement sets them to the same value? Why not
> WHERE ... WSDFR.AreaCommerciale = 'UNI'
> ?

No, because the first is a part of a stored procedure. It's the query who
doesn't work.
I extracted it to try with Query Analyzer.
An Access programm calls this SP giving a several parameters.

Thanks

Eugenio|||> > I did that, but it's changed nothing.
> > Can you give me others suggestion about this.
> No. Not without more information. Please post the following:
> o CREATE TABLE and CREATE INDEX statements for the involved tables.
> o The output when you run the procedures with SHOW STATISTICS PROFILE ON
> on *both* servers. Since the output of the query plan is very wide,
> please but this output in an attachment. (In a text file.)

Hi, Erland

I send at your mailbox the text files you've asked me, because I can't sent
you a reply with an attachment into the newsgroup. I don't know why, but
it's impossibile.

Thanks
Eugenio|||Eugenio (Ciao@.Eugenio.it) writes:
> I send at your mailbox the text files you've asked me, because I can't
> sent you a reply with an attachment into the newsgroup. I don't know
> why, but it's impossibile.

Got the mail. Since the tables and the plans are complex, I may take a
few days before I get the occasion to look at it. In the mean while,
if you cannot post it as an attachment, you could put it on a web site
and post a URL, in case someone else is interested in taking a stab at it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||..
> Got the mail. Since the tables and the plans are complex, I may take a
> few days before I get the occasion to look at it. In the mean while,
> if you cannot post it as an attachment, you could put it on a web site
> and post a URL, in case someone else is interested in taking a stab at it.

These are the URL where you can find my text files.

Tables and Views structure http://www.unicars.it/eugenio/Tables&Views.sql

Statistic profile of server where the query works
http://www.unicars.it/eugenio/SpeedyStatProf

Statistic profile of server where the query doesn't work
http://www.unicars.it/eugenio/SpeedyStatProf

I had a look at your tables, and I am afraid that your set of tables and
views is far too huge for me to try to dig into it all. But I can share
some observations. Before I go on, I like to stress that there is a limit
on how much help you get in the newsgroups. Also, help here is based on
the idea that you do some of the work yourself. You don't always get the
answer - sometimes you only get the information so that you can find out
on your own. If you are not prepared to investigate on your own, you are
not likely to get your issue sorted out. This does not the least apply in
a complex case like this one.

1) The query you posted was this one (abbreviated)

declare @.Azienda as varchar(3), @.Utente as varchar(20),
@.DataDa as datetime, @.DataA as datetime,
...

Select WSDFR.AreaCommerciale,
WSDFR.Agente,
WSDFR.NazDestin,
...
From W_St_DocFatt_Righe WSDFR
inner join UniP_Prodotti UPP on
WSDFR.prodotto=UPP.Cod
Where WSDFR.Dtdocum between @.DataDa and @.DataA and
WSDFR.AreaCommerciale between @.AreaDa and @.AreaA and
WSDFR.LineaProdotto between @.LineaDa and @.LineaA and
WSDFR.TipoProdotto between @.TipoDa and @.TipoA and
WSDFR.FamigliaProdotto between @.FamigliaDa and @.FamigliaA and
WSDFR.Prodotto between @.ProdottoDa and @.ProdottoA and
WSDFR.Agente between @.AgenteDa and @.AgenteA

It was not clear to me, if this is the actual query you run in
production, or a simplification of the stored procedure, and the
variables are parameters in real life. This has very big importance,
because when SQL Server builds a query plan it uses the values of
the input parameters as a hint, but it is completely blind to the values
of variables and assumes default values. This means that with variables,
you will get the same plan, no matter what you put into the variables.
If you are using parameters, there is a chance that adding WITH RECOMPILE
to the procedure definition that you get the best plan each time.

2) It seems that you are doing some kind of dynamic search where there
the user can specify many different conditions. This is a tricky
case to handle, and often you get best performance with dynamic SQL.
However, dynamic SQL requires the users to have direct permissions
on the involved views and tables, so it may not be an acceptable
solution. In any case, on http://www.sommarskog.se/dyn-search.html,
I have an article which discusses this topic in detail.

3) When looking at your data model, it appears unfinished. There are
some foreign keys defined, but judging from the queries there is for
instance a foreign key from P_FatturaT.Tipodoc to TB_TipologiaDoc.Tipo.
Adding foreign keys may not add performance in itself, but a thorough
study of the data model, may lead to a more precise and effective
data model.

4) The inner view, W_St_DocFatt_TestDoc, has a GROUP BY which only serves
as a DISTINCT. Maybe it is needed, maybe it is not. Since the key
information was incomplete I could not deduce that. But there certainly
is a cost for getting the distinct values, so if you can get than one
out, you may win a lot. Also, thanks to the GROUP BY, you cannot make
this view an indexed view, which could increase the speed of the
query radically.

5) None of the tables has a clustered index. Is this really a concious
decision? In such case, I think you should review it. If you would
change the indexes for the most common search indexes, that could
do wonders to performance, not the least with dynamic SQL.

6) There is a lot of NOLOCK. This may be good for performance, but it
is not good for correctness. If there is indeed traffic going on
in the database when you run your query, you can get incorrect or
inconsistent results back. If you make your view indexed, you should
remove the locking hints, because it's not clear what they would mean.

I also like to repeat what I said in my original posting:

Because of this, it can well happen that a query that executed well
yesterday, suddenly executes much slower, because you've execeeded some
threshold which causes SQL Server to pick a bad plan.

I repeat this, because given the very complex nature of your query,
there are very many ways for the optimizer to go astray.

I hope these observations should give you some input for your
further investigations.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Many thanks for so detailed answer.

Regards
Eugenio

Tuesday, March 20, 2012

Query to Oracle via linked server hangs

Basic description:

We have developed a solution that sends data from SQL Server to an Oracle server as the result of a stored procedure called by a job that runs every minute. While this development worked fine in our test environment, after moving it to production it ran successfully the first minute, but the second minute the stored procedure hung, and the process could not be killed. In order to stop the process I had to stop both the SQL Agent and the MSDTC services.

Our SQL Server box:
SQL Server 2000 Standard Edition SP4
Windows 2003 Server R2 SP1

Our Oracle box:
Test: Oracle 9.2.0.6
Production: Oracle 9.2.0.4

To setup the SQL box, I did the following:
1) Install Oracle Client Tools version 10.2.0.1
2) Restart Server
3) Modify the registry as follows:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI] "OracleXaLib"="oraclient10.dll" "OracleSqlLib"="orasql10.dll" "OracleOciLib"="oci.dll"
4) Modified the PATH variable so that all references to SQL Server appear in front of Oracle path references
5) Added the linked server via sp_addlinkedserver '<tns name>','Oracle','MSDAORA','<tns name>'
6) Added linked server logins via sp_addlinkedsrvlogin '<tns name>','False','<SQL user>','<Oracle User name>','<password on oracle>'
7) Changed the registry for MSDTC to match this:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC]
"AllowOnlySecureRpcCalls"=dword:00000000
"FallbackToUnsecureRPCIfNecessary"=dword:00000001
"TurnOffRpcSecurity"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\Security]
"NetworkDtcAccess"=dword:00000001
"NetworkDtcAccessAdmin"=dword:00000001
"NetworkDtcAccessClients"=dword:00000001
"NetworkDtcAccessTransactions"=dword:00000001
"NetworkDtcAccessTip"=dword:00000001
"XaTransactions"=dword:00000001
"DomainControllerState"=dword:00000000
"AccountName"="NT Authority\\NetworkService"
"NetworkDtcAccessOutbound"=dword:00000001
"NetworkDtcAccessInbound"=dword:00000001

8) Stopped and restarted services in the following order:
1) MSDTC Stop
2) SQL Server Stop
3) MSDTC Start
4) SQL Server Start

The stored procedure:
In a single transaction, the stored procedure compares a production table against a logging table. If a record exists in the production table that is not in the logging table, a record is inserted into logging table, and a record sent to Oracle via an INSERT INTO OPENQUERY('INSTANCE','SELECT Column1, column2, column3,... FROM SCHEMA.TABLE')
SELECT column1,column2,column FROM SQLTable

This stored procedure has worked just fine for us in test, to either the test or production Oracle boxes, but it now fails, and hangs, in production to either the test or production Oracle boxes.

Additionally, I can run the following query via Query Analyzer from our test box to both the test Oracle and production Oracle and it runs successfully (this is NOT used in our stored procedure code, but is presented here as an indication that I think there is something wrong with the settings on our production SQL box):

SELECT * FROM OPENQUERY('INSTANCE','SELECT * FROM SCHEMA.TABLE')

When I run this same query via Query Analyzer on our Production SQL box, to either the test Oracle or production Oracle, it hangs, and I have to kill the process, and restart the MSDTC service.
Other queries that hang are:
SELECT * FROM SERVER..SCHEMA.TABLE

Additionally, I noticed that when I used this method to kill the process I would see errors like the following in the Application Event Log on the SQL box:

The XA Transaction Manager attempted to perform recovery with the XA resource manager. The XA resource manager reported that recovery was unsuccessful. DSN = MTxOCI.Dll.

Since I figured this was an aborted transaction still residing in the MSDTC log file, I would stop the MSDTC service, delete the MSDTC log file, reset the MSDTC log, and then restart the MSDTC service in order to prevent this error from occurring.

Not ALL queries from the production SQL box to production and test Oracle boxes fail. I can get results returned for this query:

SELECT COLUMN1, COLUMN2 FROM SERVER..SCHEMA.TABLE

I've been scouring the internet for about a week now, and I've run out of ideas on what to check on the production SQL box. Any suggestions would be greatly appreciated.

Tim

Dismayed by the lack of comment on my problem, I did some additional research into what is happening on my machines. I setup the Sysinternals utility ProcMon to capture file access when I run the query, both on Production and on Test. The biggest different I noticed was that, on Production where the queries are hanging, there's ALOT of activity by DLLHOST.exe opening oracle dlls. No such activity occurs on Test. In fact on Test, DLLHOST.exe isn't running at all.

So, I did some searching on Oracle's metalink, and Note 333327.1 (which points to KB 833388) suggests that DLLHOST.exe indicates that I've somehow configured my Oracle Provider for OLEDB to run Out of Process, and suggests modifying the registry key for the Oracle provider, HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers\OraOLEDB.Oracle\ and set AllowInProcess=1

A couple things about this puzzle me. First, the Providers folder in the registry on my Test box (again, where the queries work fine) does not contain any entries whatsoever. On Production, I've got a bunch of folders in the Providers key, but none for OraOLEDB.Oracle. And why would it even matter, since I've configured my linked server to use MSDAORA? The MSDAORA key contains AllowInProcess=1 on Production.

Am I looking down the wrong path? Any suggestions?
|||

Did some more work on this today, and answered some of my own questions, but not the most important one.

I was able to find my Providers key on Test. Since we have multiple SQL Server instances on Test, it was under an instance folder at HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\Providers\

I added keys for \OraOLEDB.Oracle\ to both Production and Test, and made sure to set AllowInProcess=1 in the registry. I restarted SQL Server on both Production and Test. The queries, and subsequently the development, both worked fine in Test. On Production, I ran one of my trouble queries, SELECT * FROM LinkServer..Schema.Table, against the test Oracle instance, and I got results!

But when I ran it a second time, the query hung.

I tried the same test against the production Oracle instance. The query ran successfully the first time, but now all subsequent running of the query just hangs.

Back to the drawing board.

|||Worked on this again yesterday. I coordinated with our Oracle guy to see if he could watch what was happening on his end. As we tested, I saw that my SELECT * queries actually could complete today! Sure, they took over a minute, when from test they took less than a second, but it's progress, of a sort.

I setup ProcMon again to catch file and registry key activity again on the Production SQL Server box that has the long running queries. I verified that the linked queries to Oracle are now running In Process (no longer using DLLHOST.exe). But, more interesting, I spotted a number of Buffer Overflows occurring. I thought I was on to something, but then I saw this blog entry:
http://blogs.technet.com/markrussinovich/archive/2005/05/17/buffer-overflows.aspx
So, it may be nothing, and I'm back to the drawing board again.
|||

A resolution! The network/infrastructure folks looked at the problem after I asked them to check out the NIC on the server with the Production SQL, and it turns out that the ports on the Cisco router that the server was plugged into was configured in such a manner that was resulting in lots of dropped packets and packet collisions. After changing the Cisco router setting for those ports, my queries run as expected from my SQL Server production box.

I learned alot during this problem, though....