Query Syntax: stored

Showing posts with label stored. 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.
> >
> >
> >

Query your Stored Procs

Is there a way you can query stored procedures for a string in all of the
stored procs in a database? For example, if you were looking for "LETTERS"
in all of your stored procedures in your Account DB, how would you look for
the string? Please post example if you know.
-Toco-You can search the syscomments which has the source code for all your stored
procedures. A wrapper script can be found at:
http://vyaskn.tripod.com/sql_server...cedure_code.htm
Anith|||SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM AccountDB.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%LETTERS%'
http://www.aspfaq.com/
(Reverse address to reply.)
"Toco" <Toco@.discussions.microsoft.com> wrote in message
news:5A3A9543-40A4-460D-BFDC-09C021991BDD@.microsoft.com...
> Is there a way you can query stored procedures for a string in all of the
> stored procs in a database? For example, if you were looking for
"LETTERS"
> in all of your stored procedures in your Account DB, how would you look
for
> the string? Please post example if you know.
> -Toco-

Query XML type with values from another XML type

Hi - I'm using SQL Server 2005, and I have a view with an XML data type column. I would like to write a stored proc that takes an XML data type as a parameter and return the rows from the view that match the columns in the parameter.

At this point, I'm just trying to get the syntax correct, so I started with some simple queries. Below are the queries:

declare @.params xml

declare @.view xml

set @.params = '<HierarchyTypes>

</HierarchyTypes>'

set @.view = '<HierarchyTypes>

</HierarchyTypes>'

SELECT T.c.query('.') AS result

FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c)

I would like to get the nodes from @.view where @.view.hierarchyTypeId = @.params.hierarchyTypeId. This should be pretty simple, but I'm missing it...

Any thoughts are appreciated!

Thanks,

Phil

It sounds like you are trying to join view and params to get a results set which has one matching node per row. If that is the case, the below query should be doing what you are looking for. If you want to return a single xml fragment, a different technique would have to be used.

In the query below, we iterate over the HierarchyType nodes of both @.view and @.params, project the values of the hierarchyTypeId attributes as integers using the the value() function, verify that they match, and the return the @.param node that matches.

declare @.params xml
declare @.view xml

set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
</HierarchyTypes>'

set @.view = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>'

SELECT T.c.query('.') AS result
FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c),

@.params.nodes('/HierarchyTypes/HierarchyType') P(c)
WHERE T.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')

|||

Hi Todd - this is very close to what I need. The only other wrinkle is that I'm trying to select all of the columns from the view (not just the hierarchyTypeId). The view has an XML column called HierarchyTypes, and I want to use that column in my 'join criteria'. And I'd like to do this in a stored proc.

CREATE PROCEDURE [dbo].[CodeHierarchy_SearchHierarchy]
@.codeTypes xml = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT v.nodeId,
v.parentNodeId,
v.HierarchyTypes
FROM VLinkedCodeHierarchies as v
WHERE /* hierarchyTypes in @.codeTypes are also in v.HierarchyTypes */

At this point, I also require that the return is NOT XML.

Thanks,

Phil

|||

So it sounds like you have a table or a view that has a few relational columns, and 1 xml column. And then you want to join it with an XML fragment.

Is that correct?

If so, then you can modify the query to use CROSS APPLY. In the example I create a test table which has an xml column, fill it with data and then join it with the fragment, projecting the relevant relational columns and xml data. You could then wrap it in a stored procedure or user defined function as needed.

(I CROSS APPLY the vTest table with nodes() function so that I get the nodes for the current row.)

declare @.params xml

drop table vTest
Create table vTest(
id int,
val xml
)

insert into vTest (id, val) values (1,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>')

insert into vTest (id, val) values (2,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="8" otherProp = "1"/>
<HierarchyType hierarchyTypeId="9" otherProp = "2"/>
<HierarchyType hierarchyTypeId="10" otherProp = "3"/>
<HierarchyType hierarchyTypeId="11" otherProp = "4"/>
</HierarchyTypes>')

set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
<HierarchyType hierarchyTypeId="11" />
</HierarchyTypes>'

SELECT vTable.Id,
xVal.c.value('@.hierarchyTypeId', 'int') as hierarchyTypeId,
xVal.c.value('@.otherProp', 'int') as otherProp,
xVal.c.query('.') as MatchingFragment

FROM @.params.nodes('/HierarchyTypes/HierarchyType') P(c),
vTest as vTable CROSS APPLY vTable.Val.nodes('/HierarchyTypes/HierarchyType') xVal(c)
WHERE xVal.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')

If you dont want to Join the parameter fragment and the table, but just want to check that for each row it's xml column has some data in common with the parameter fragment, then you could change the query to use an EXISTS.

|||

Hi Todd - thanks again for the excellent reply. I actually want to make sure that the rows returned have data in common with the XML fragment.

Ideally, I would like to have 3 options:

1) View column has some data from XML fragement

2) View column has all data from XML fragment, but could have more.

3) View column has ONLY data from XML fragement.

I can accomplish #1 above using a SELECT DISTINCT, but I don't think that's the most optimum.

Where are some good resources to educate myself on this?

Thanks!

Phil

|||

Here is where you can find the the basics of XQuery and the T-SQL functions that support it:
http://msdn2.microsoft.com/en-us/library/ms190262.aspx

The current w3c resources:
http://www.w3.org/TR/xquery/

T-SQL Reference:
http://msdn2.microsoft.com/en-us/library/ms189826.aspx

And there are probably a number of tutorials that would discuss the differences of when to use a DISTINCT vs EXISTS vs CROSS APPLY.

Query works in QA, but NOT in SQL Server stored proc?!

Ok...below is a simple query that inserts some records into a temp
table then updates another table using the temp table. It works great
in Query Analyzer, but refuses to save in SQL Servers' stored procedure
area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
I'm banging my head against a wall here, please help! I've tried
placing single and double quotes around fvd_count to no avail...
CREATE PROCEDURE [Update_Counts]
AS
--counts the number of times that distinct doc/poe combo exists
SELECT
doc,
poe,
COUNT(equipment) AS fvd_count <<<<<--ERROR
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND
a.poe = b.poe
GOAre you creating the sp in EM?. Try creating the sp from SQL Query Analyzer.
AMB
"roy.anderson@.gmail.com" wrote:

> Ok...below is a simple query that inserts some records into a temp
> table then updates another table using the temp table. It works great
> in Query Analyzer, but refuses to save in SQL Servers' stored procedure
> area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
> I'm banging my head against a wall here, please help! I've tried
> placing single and double quotes around fvd_count to no avail...
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exists
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count <<<<<--ERROR
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = b.fvd_count
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>|||Did you cut/paste the error message? If so, then you have a typo somewhere
in your code because the error message references field 'fvd_cnt', while the
sp that you show us names it 'fvd_count'.
If that is just a typo in your post...then...another thought is to use
[square brackets] around the field name. It shouldn't be necessary in this
case, but worth a try.
<roy.anderson@.gmail.com> wrote in message
news:1107276839.749893.138630@.c13g2000cwb.googlegroups.com...
> Ok...below is a simple query that inserts some records into a temp
> table then updates another table using the temp table. It works great
> in Query Analyzer, but refuses to save in SQL Servers' stored procedure
> area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
> I'm banging my head against a wall here, please help! I've tried
> placing single and double quotes around fvd_count to no avail...
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exists
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count <<<<<--ERROR
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = b.fvd_count
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>|||I dont think this is the full sproc, i have just tested this in QA & EM
without any error
USE Northwind
GO
CREATE TABLE firstvd (doc varchar(10), poe varchar(10), equipment varchar(10
))
CREATE TABLE lla (doc varchar(10), poe varchar(10), fvd_count int)
GO
CREATE PROCEDURE [Update_Counts]
AS
SELECT doc, poe, COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND a.poe = b.poe
GO
EXEC Update_Counts
GO
DROP TABLE firstvd
DROP TABLE lla
DROP PROCEDURE [Update_Counts]
GO
Instead of using a temp table why not do it in the query
UPDATE a
SET a.fvd_count = (SELECT COUNT(equipment)
FROM firstvd b
WHERE b.doc = a.doc AND b.poe = a.doc)
FROM lla a, firstvd
Andy
"CPK" wrote:

> Did you cut/paste the error message? If so, then you have a typo somewher
e
> in your code because the error message references field 'fvd_cnt', while t
he
> sp that you show us names it 'fvd_count'.
> If that is just a typo in your post...then...another thought is to use
> [square brackets] around the field name. It shouldn't be necessary in thi
s
> case, but worth a try.
> <roy.anderson@.gmail.com> wrote in message
> news:1107276839.749893.138630@.c13g2000cwb.googlegroups.com...
>
>|||Hey all, thanks for the input. I tried using QA to load it in and it
works like that... the first time... but when I call it after that it
ends up producing nothing (called from my asp.net page) or erroring out
in QA or EM.
Tried the square brackets, no go (and yes, it was a typo on my part).
I'll try your query idea next Andy, I don't know what to say regarding
your experiment except to say it just doesn't work in my EM. I did
clarify where the error occurs though. It happens at b.fvd_count below:
CREATE PROCEDURE [Update_Counts]
AS
--counts the number of times that distinct doc/poe combo exi=ADsts
SELECT
doc,
poe,
COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count =3D >>>>>>> b.fvd_count <<<<<<--ERROR HERE
FROM #FVD_Temp b, lla a
WHERE a.doc =3D b.doc AND
a=2Epoe =3D b.poe
GO=20
****************************************
***********************|||Roy
Just try this in QA first, forget about EM its a GUI and should be just
treated that way, i spend 90% of my time using QA
CREATE PROCEDURE [Update_Counts]
AS
SELECT doc, poe, COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND a.poe = b.poe
GO
Oh just one more thing does this field "fvd_count" exist in table lla as we
know it exists in the temp table #FVD_Temp
Andy
"roy.anderson@.gmail.com" wrote:

> Hey all, thanks for the input. I tried using QA to load it in and it
> works like that... the first time... but when I call it after that it
> ends up producing nothing (called from my asp.net page) or erroring out
> in QA or EM.
> Tried the square brackets, no go (and yes, it was a typo on my part).
> I'll try your query idea next Andy, I don't know what to say regarding
> your experiment except to say it just doesn't work in my EM. I did
> clarify where the error occurs though. It happens at b.fvd_count below:
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exi_sts
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = >>>>>>> b.fvd_count <<<<<<--ERROR HERE
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>
> ****************************************
***********************
>|||Yes, the create proc above works in QA and yes, fvd_count exists in
lla...|||Wild guess: Try adding SET NOCOUNT ON in the beginning of your proc code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<roy.anderson@.gmail.com> wrote in message
news:1107286986.218999.296680@.f14g2000cwb.googlegroups.com...
> Yes, the create proc above works in QA and yes, fvd_count exists in
> lla...
>|||Roy
have you sorted this out.
If the CREATE PROC statement returned "The command completed successfully"
then the procedure has been created. But does it execute & do what you
expect'
Andy
"roy.anderson@.gmail.com" wrote:

> Yes, the create proc above works in QA and yes, fvd_count exists in
> lla...
>|||Yes, the query proc works! Thanks much. Additonally, the suggestion to
create it in QA (the original stored proc) was accurate. The SP works
when called from my asp.net page. Even though both ways work (outside
of EM), I'm sticking with the query method because it seems less cpu
intensive.
The weird thing is still that the original SP won't save and the syntax
check fails when I open it (the original SP) in EM. But no matter, at
least I have a working now process now. Thanks everyone for all the
great suggestions!

Wednesday, March 28, 2012

Query with multiple tables Use of JOIN vs WHERE

I have inherited a database, written a few years back and the people who
designed the stored procedures seem to do things differently than I learned
and it seems to work faster, but I cannot figure out why. I check the
execution plans and they appear identical, but the statistics show a HUGE
(to me) difference in reads (147 for the first method and 40 for the second)
. The second method takes about 10 seconds less to return the results. (O
f
course the code is wrapped in a stored procedure - but I was curious about
this "new" way of doing things so I extracted the select statements to do
comparisons).
I'm sure that the explanation is simple and is just an area of SQL coding I
hadn't been exposed to yet.
Any info appreciated,
Nancy
CODE:
It is a simple query to get a count joining 3 tables
I would normally do the query using:
Select count(CODE1)
from Table1
JOIN x_hcfa_cpt
ON
Table1.CLAIMNO = Table2.CLAIM_NUMBER
JOIN x_hcfa_cpt_mas
ON
Table1.TERM_NUMBER = Table3.TERM_NUMBER
where
Table1.TERM_NUMBER = 'asdftcdww'
AND
(Table1.STATUS='false'
or
Table1.STATUS='true')
and
Table1.FLAG='false'
But in the code I inherited they used:
Select count(CODE1)
from Table1, Table2, Table3
where
Table1.X_TERMINAL_NUMBER = 'asdftcdww'
AND
Table1.CLAIMNO = Table2.CLAIM_NUMBER
and
Table1.TERM_NUMBER = Table3.TERM_NUMBER
and
(Table1.STATUS='false'
or
Table1.STATUS='true')
and
Table1.FLAG ='false'> Select count(CODE1)
> from Table1, Table2, Table3
This is non-standard code and should be avoided (especially for outer joins
due to non-conforming behavior, but for inner joins as well). My suggestion
is to re-write the code with JOIN statements, and to avoid ambiguity, I make
it a standard practice to include the type of JOIN, so I would use the INNER
keyword as well (even though it is the default).|||There should be no difference between SQL-92 JOINs (1st statement) and
the older style (2nd statement).
I'm guessing that x_hcfa_cpt and x_hcfa_cpt_mas are Table2 and Table3 in
the first query?
Also, in the first query, the where clause uses Table1.TERM_NUMBER
whereas the 2nd query uses Table1.X_TERMINAL_NUMBER. Could that be the
difference in performance?
Nancy Lytle wrote:

>I have inherited a database, written a few years back and the people who
>designed the stored procedures seem to do things differently than I learned
>and it seems to work faster, but I cannot figure out why. I check the
>execution plans and they appear identical, but the statistics show a HUGE
>(to me) difference in reads (147 for the first method and 40 for the second
)
>. The second method takes about 10 seconds less to return the results. (O
f
>course the code is wrapped in a stored procedure - but I was curious about
>this "new" way of doing things so I extracted the select statements to do
>comparisons).
>I'm sure that the explanation is simple and is just an area of SQL coding I
>hadn't been exposed to yet.
>Any info appreciated,
>Nancy
>CODE:
>It is a simple query to get a count joining 3 tables
>I would normally do the query using:
>Select count(CODE1)
>from Table1
>JOIN Table2
>ON
>Table1.CLAIMNO = Table2.CLAIM_NUMBER
>JOIN Table3
>ON
>Table1.TERM_NUMBER = Table3.TERM_NUMBER
>where
>Table1.TERM_NUMBER = 'asdftcdww'
>AND
> (Table1.STATUS='false'
> or
> Table1.STATUS='true')
>and
> Table1.FLAG='false'
>But in the code I inherited they used:
>Select count(CODE1)
>from Table1, Table2, Table3
>where
>Table1.X_TERMINAL_NUMBER = 'asdftcdww'
>AND
>Table1.CLAIMNO = Table2.CLAIM_NUMBER
>and
>Table1.TERM_NUMBER = Table3.TERM_NUMBER
>and
>(Table1.STATUS='false'
>or
>Table1.STATUS='true')
>and
>Table1.FLAG ='false'
>
>|||On Tue, 13 Sep 2005 16:37:16 -0400, Nancy Lytle wrote:

>I have inherited a database, written a few years back and the people who
>designed the stored procedures seem to do things differently than I learned
>and it seems to work faster, but I cannot figure out why.
(snip)
Hi Nacny,
I'll assume that the different table names and column names are a result
of you renaming some tables and columns when preparing the post, and the
code you actually tested this on didn't have these differences :-)
The difference in the queries are the two different styles of join
notation. In old versions of SQL Server (note that I'm talking real old
here - older than SQL Server 6.5), only the version with the
comma-delimited list of tables is allowed. The more verbose version with
infixed join operators was added later, to adhere to the ANSI standard.
For inner joins, there is absolutely no difference between the two
versions. They are both defined in the ANSI standard, both acccepted by
SQL Server and they will both return the same results. They'll also use
the same execution plan, so that there's no performance difference
either.
For outer joins, things are different. The "old-style notation" (that
uses =* and *= in the WHERE clause to define inner and outer tables) is
ambiguous. It's not defined in the ANSI standard. MS has announced that
it will drop support for =* and *= in a future version. In fact, I
recall reading somewhere that SQL Server 2005 will only accept =* and *=
in the backward compatibility mode.

> I check the
>execution plans and they appear identical, but the statistics show a HUGE
>(to me) difference in reads (147 for the first method and 40 for the second
)
>. The second method takes about 10 seconds less to return the results.
Did you run both tests on an empty cache? I suspect not - and that's
what causes the difference.
Test it like this:
DECLARE @.start datetime
DECLARE @.end datetime
-- Flush all dirty buffers to disk
CHECKPOINT
-- Remove all previously read pages from the data cache
DBCC DROPCLEANBUFFERS
-- Remove all previously compiled execution plans as well
DBCC FREEPROCCACHE
-- Now start the real test
SET @.start = CURRENT_TIMESTAMP
#### ####
#### YOUR QUERY GOES HERE ####
#### ####
SET @.end = CURRENT_TIMESTAMP
SELECT @.start AS StartTime,
@.end AS EndTime,
DATEDIFF(ms, @.Start, @.End) AS "Elapsed (ms)"
If the code that you are testing returns many rows, change the SELECT to
a SELECT ... INTO #temp_table to eliminate the speed of the network and
the display speed of your client from the equation.
I'd be VERY surprised if you still get significant differences if you
test the queries like this. (Small differences are to be expected,
especially if the server you're running this on has other things to do
as well).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo, Trey and Aaron!
I guess there is more sql code I am going to have to change, the SP's (and
they are all named sp_ !) are dotted with uses of comma delimited lists of
tables for joins and usage of *=, not to mention tons of select *'s, and the
sp_ naming convention.
I used Hugo query and that helped me see the real difference between the
two, which is actually very slight and leans toward the use of JOINs.
Thanks again, this is a great group!
Nancy
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1fei1paaf5g8qo9ef509q9cqocdmds466@.
4ax.com...
> On Tue, 13 Sep 2005 16:37:16 -0400, Nancy Lytle wrote:
>
> (snip)
> Hi Nacny,
> I'll assume that the different table names and column names are a result
> of you renaming some tables and columns when preparing the post, and the
> code you actually tested this on didn't have these differences :-)
> The difference in the queries are the two different styles of join
> notation. In old versions of SQL Server (note that I'm talking real old
> here - older than SQL Server 6.5), only the version with the
> comma-delimited list of tables is allowed. The more verbose version with
> infixed join operators was added later, to adhere to the ANSI standard.
> For inner joins, there is absolutely no difference between the two
> versions. They are both defined in the ANSI standard, both acccepted by
> SQL Server and they will both return the same results. They'll also use
> the same execution plan, so that there's no performance difference
> either.
> For outer joins, things are different. The "old-style notation" (that
> uses =* and *= in the WHERE clause to define inner and outer tables) is
> ambiguous. It's not defined in the ANSI standard. MS has announced that
> it will drop support for =* and *= in a future version. In fact, I
> recall reading somewhere that SQL Server 2005 will only accept =* and *=
> in the backward compatibility mode.
>
> Did you run both tests on an empty cache? I suspect not - and that's
> what causes the difference.
> Test it like this:
> DECLARE @.start datetime
> DECLARE @.end datetime
> -- Flush all dirty buffers to disk
> CHECKPOINT
> -- Remove all previously read pages from the data cache
> DBCC DROPCLEANBUFFERS
> -- Remove all previously compiled execution plans as well
> DBCC FREEPROCCACHE
> -- Now start the real test
> SET @.start = CURRENT_TIMESTAMP
> #### ####
> #### YOUR QUERY GOES HERE ####
> #### ####
> SET @.end = CURRENT_TIMESTAMP
> SELECT @.start AS StartTime,
> @.end AS EndTime,
> DATEDIFF(ms, @.Start, @.End) AS "Elapsed (ms)"
> If the code that you are testing returns many rows, change the SELECT to
> a SELECT ... INTO #temp_table to eliminate the speed of the network and
> the display speed of your client from the equation.
> I'd be VERY surprised if you still get significant differences if you
> test the queries like this. (Small differences are to be expected,
> especially if the server you're running this on has other things to do
> as well).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 14 Sep 2005 08:59:40 -0400, Nancy Lytle wrote:

>Thanks, Hugo, Trey and Aaron!
>I guess there is more sql code I am going to have to change, the SP's (and
>they are all named sp_ !) are dotted with uses of comma delimited lists of
>tables for joins and usage of *=, not to mention tons of select *'s, and th
e
>sp_ naming convention.
>I used Hugo query and that helped me see the real difference between the
>two, which is actually very slight and leans toward the use of JOINs.
>Thanks again, this is a great group!
>Nancy
Hi Nancy,
I guess that the "very slight" difference you see falls within the
bounds of statistic inaccuracy. If you repeat the test a few times, you
should see that there really is no difference between the two.
As far as rewriting code, I'd say: find the right path between
religiously rewriting everything (costly, time-consuming, and will
introduce bugs, if only by typo's and copy/paste errors) on the one end,
and leaving working code untouched on the other end.
If you decide to start rewriting where it's needed most, then begin with
the use of =* and *= for outer joins, as they are on the deprecated
feature list. Next should be the sp_ prefix and the use of SELECT *
(both are performance killers in their own ways; both induce a risk of
unexpectedly breaking your code when some change is made elsewhere).
The join syntax for inner joins (i.e. the use of comma-delimited table
list without any =* or *=) should be last on your list, as this is only
a readability improvement (and not all experts would agree that it's an
improvement - there are a few SQL experts who think that the "old style"
join notation is often better, though I'm not one of them).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the suggestions.
I did a complete search and the locations for the *= code is in dynamic sql
written in .asp pages. So, since I am changing the dynamic sql to a stored
procedure, I can get rid of the *= and select * and created proper procedure
naming all at the same time.
I will wait to modify the names of the sp_'s that are already written until
I have a chance to sit down with the other programmers, etc, and we come up
with a plan. My initial thought was to simply recreate the SPs changing
only the name, so we would have essentially 2 sp's that did the same thing,
just one sp_ and one usp_ names. Then we could start cutting over the names
in the code without breaking anything, that couldn't be fixed almost
immediately.
But this is my first time really taking on a task like this, does this sound
like a plan?
Do you have any articles or books to recommend to a new DBA/developer?
Thanks again,
Nancy
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3rvgi15u21emupk0gfqe9pl5dpfk8kgl0s@.
4ax.com...
> On Wed, 14 Sep 2005 08:59:40 -0400, Nancy Lytle wrote:
>
> Hi Nancy,
> I guess that the "very slight" difference you see falls within the
> bounds of statistic inaccuracy. If you repeat the test a few times, you
> should see that there really is no difference between the two.
> As far as rewriting code, I'd say: find the right path between
> religiously rewriting everything (costly, time-consuming, and will
> introduce bugs, if only by typo's and copy/paste errors) on the one end,
> and leaving working code untouched on the other end.
> If you decide to start rewriting where it's needed most, then begin with
> the use of =* and *= for outer joins, as they are on the deprecated
> feature list. Next should be the sp_ prefix and the use of SELECT *
> (both are performance killers in their own ways; both induce a risk of
> unexpectedly breaking your code when some change is made elsewhere).
> The join syntax for inner joins (i.e. the use of comma-delimited table
> list without any =* or *=) should be last on your list, as this is only
> a readability improvement (and not all experts would agree that it's an
> improvement - there are a few SQL experts who think that the "old style"
> join notation is often better, though I'm not one of them).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 15 Sep 2005 09:51:15 -0400, Nancy Lytle wrote:

>Thanks for the suggestions.
>I did a complete search and the locations for the *= code is in dynamic sql
>written in .asp pages. So, since I am changing the dynamic sql to a stored
>procedure, I can get rid of the *= and select * and created proper procedur
e
>naming all at the same time.
Hi Nancy,
Wow, that's a major improvement - getting rid of two major pitfalls at
once!

>I will wait to modify the names of the sp_'s that are already written until
>I have a chance to sit down with the other programmers, etc, and we come up
>with a plan. My initial thought was to simply recreate the SPs changing
>only the name, so we would have essentially 2 sp's that did the same thing,
>just one sp_ and one usp_ names. Then we could start cutting over the name
s
>in the code without breaking anything, that couldn't be fixed almost
>immediately.
>But this is my first time really taking on a task like this, does this soun
d
>like a plan?
Discussing things with the developers is definitely a great idea. As
long as your modifications are invisible to them (such as replacing
dynamic =* crap with non-dynamic OUTER JOINs), you could do you work in
silence (though I'd even recommend communicating your actions in that
case). But if your changes are going toa ffect the developers (and they
will if you intend to eventually remove the badly named stored
procedures), they should be informed, and invited to participate.
But if you are renaming, then I'd just drop the prefix completely. I've
never managed to see the added value of
EXEC usp_MakeMonthlyReport
over
EXEC MakeMonthlyReport

>Do you have any articles or books to recommend to a new DBA/developer?
http://www.aspfaq.com/show.asp?id=2423
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 26, 2012

Query with MAX Date

I have the following situation:
The name of a product can change with time. These changes are stored in a
table with 3 columns: Product_id, Date and ProductName with Product_id and
Date forming the Primary Key.
I want to run a query that returns the product_id and each product's latest
name. For the life of me, I can't get my head rould what such a query would
look like. I can get a query that gives me just the product_id and the Max
of Date with a group by on the Product_id but when I introduce the
ProductName, it returns ALL names.
I have a gut feeling that there may be a subquery involved, or am I barking
up the wrong tree?
Any help much appreciated.
PeteYou can either use a subquery:
SELECT Product_id, ProductName
FROM Your_table t1
WHERE Date = (SELECT MAX(Date) FROM Your_table t2 WHERE t1.Product_id =
t2.Product_id)
or a derived table:
SELECT t1.Product_id, t1.ProductName
FROM Your_table t1
INNER JOIN
(SELECT Product_id, MAX(Date) AS max_date
FROM Your_table
GROUP BY Product_id) t2
ON t1.Product_id = t2.Product_id
AND t1.Date = t2.max_date
Jacco Schalkwijk
SQL Server MVP
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
>I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
> latest
> name. For the life of me, I can't get my head rould what such a query
> would
> look like. I can get a query that gives me just the product_id and the
> Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
> barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||First, please change your column name 'Date' to something more meaningful
and something that doesn't use a reserved word. Also, you should be
consistent in your column naming. Why does Product_id have an underscore,
but ProductName not? Finally, in the future, please post DDL, sample data,
and desired results. See http://www.aspfaq.com/5006
In the meantime, you can try this:
SELECT o.Product_id, i.MaxDate, o.ProductName
FROM Products o
INNER JOIN
(
SELECT Product_id, MaxDate = MAX([Date])
FROM Products
GROUP BY Product_id
) i
ON o.Product_id = i.Product_id
AND o.[Date] = i.MaxDate
http://www.aspfaq.com/
(Reverse address to reply.)
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
> I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
latest
> name. For the life of me, I can't get my head rould what such a query
would
> look like. I can get a query that gives me just the product_id and the
Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||Here is a solution based on guesswork:
SELECT t1.product_id, t1.product_name
FROM tbl t1
WHERE ( SELECT MAX( t2.dtcol )
FROM tbl t2
WHERE t2.product_id = t1.product_id ) = t1.dtcol ;
If this is not what you are looking for, refer to www.aspfaq.com/5006 and
provide required information.
Anith

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 parse out values from one column into different columns

I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.

For (hypothetical) example:

There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)

I need to query these records for a report so it the output is:

userID firstname middleinitial suffix
1 joe j jr
2 bob a sr

What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.Look up CROSSTAB queries in Books Online.select userid,
max(case recordtag when 1 then record end) as firstname,
max(case recordtag when 2 then record end) as middleinitial,
max(case recordtag when 3 then record end) as suffix
from [YourTable]
group by userid|||Thanks for the info. I'll let you know how I do.|||I incorporated the crosstab query into my code and the performance is stellar. Thanks for your help. !!

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....

Friday, March 30, 2012

Query/Report processing time

Query your Stored Procs

Query XML type with values from another XML type

Query works in QA, but NOT in SQL Server stored proc?!

Wednesday, March 28, 2012

Query with multiple tables Use of JOIN vs WHERE

Monday, March 26, 2012

Query with MAX Date

Query VS Stored Procedure problem

Friday, March 23, 2012

Query very slow when executed using Reporting Services

Wednesday, March 21, 2012

Query too slow! Need some performance enhancing tips!

Query too slow

Tuesday, March 20, 2012

query to parse out values from one column into different columns

Query to Oracle via linked server hangs

Query Syntax

Blog Archive

About Me