Performance tuning for a stored procedure

I have an SP that is big, huge, 700-800 lines.

I am not an expert but I need to figure out every possible way that
I can improve the performance speed of this SP.

In the next couple of weeks I will work on preparing SQL statements
that will create the tables, insert sample record and run the SP.
I would hope people will look at my SP and give me any hints on how
I can better write the SP.

In the meantime, after looking at the SP briefly, my first observations are:

1- use SET NOCOUNT ON
2- avoid using UNION statements
3- use WITH (NOLOCK) with each SELECT statement
4- avoid using NESTED Select statements
5- use #temp tables
6- avoid renaming tables in SELECT statements, for example SELECT * FROM
tblClients C

Am i correct to consider the above 6 points as valid things in terms of
causing
performance problems?

I would appreciate any comments/help

Thank you very muchHi Serge,

On Thu, 9 Sep 2004 00:13:31 -0400, serge wrote:

>I have an SP that is big, huge, 700-800 lines.
>I am not an expert but I need to figure out every possible way that
>I can improve the performance speed of this SP.
>In the next couple of weeks I will work on preparing SQL statements
>that will create the tables, insert sample record and run the SP.
>I would hope people will look at my SP and give me any hints on how
>I can better write the SP.
>In the meantime, after looking at the SP briefly, my first observations are:
>1- use SET NOCOUNT ON

This will not affect performance, but it is good practice. Many clients
choke on the numerous "(n row(s) affected)" messages returned by an SP
without this setting.

>2- avoid using UNION statements

Depends. Sometimes, a UNION can be quicker than the alternative. I've seen
cases where a query with a complicated WHERE clause that was evaluated
with a table scan was rewritten as two (or more) queries with UNION; each
of those queries was resolved with an index and the UNION version ran a
lot quicker.

You can gain performance by using UNION ALL instead of UNION where
possible. Sometimes, this can be made possible by extending the WHERE
clause of one of the queries in the UNION (though you have to be carefull
that the cost of this extension doesn't outweigh the gain of UNION ALL
over UNION!).

Run the following examples and check execution plans and statistics on I/O
and time used for execution:

USE pubs
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_id LIKE '2%'
OR au_lname = 'Ringer'
GO
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_id LIKE '2%'
UNION
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_lname = 'Ringer'
GO
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_id LIKE '2%'
UNION ALL
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_lname = 'Ringer'
AND au_id NOT LIKE '2%'
GO

>3- use WITH (NOLOCK) with each SELECT statement

This can gain you some performance (especially if concurrency is high on
your database), but you run the risk of getting dirty reads. If that risk
is acceptable, go ahead. If you don't know what a dirty read is, then
don't use this option.

>4- avoid using NESTED Select statements

Again: depends. If you can safely and easily replace the nested select (or
subquery, as it's usually called) with other code, do so, then test both
versions to see if performance actually has improved (if often won't
improve, as SQL Server's optimizer already uses the same execution plan).

Similar, if you can safely replace a correlated subquery with a
non-correlated, do so and test both versions.

But if removing the subquery means that you have to code lots more SQL, it
might hurt performance instead of improving it. And if you can gain some
performance by replacing an intuitive subquery with a contrived and hard
to understand query, then you might want to reconsider if you really value
performance higher than maintainability. One day, you will find youself
staring at that query, wondering what the %$# that ^%#$&%# query is
supposed to do.

>5- use #temp tables

At the risk of repeating myself: depends. If you find the same subquery
used over and over in the procedure, it MIGHT help performance if you
execute that subquery into a #temp table and use that for the rest of the
execution. It MIGHT also help further to index the temp table. But, again,
it might also hurt performance - creating the temp table and storing the
data induces some overhead as well and if you're not careful, you might be
faced with numerous recompilationms of the stored procedure that wouldn't
be needed without the temp table.

If you use a temp table to break a complicated query down in steps, you
have a good chance of degrading performance. In one complicated query, the
optimizer may choose an execution plan that you would never think of but
that's faster than the obvious way to execute it; if you dictate the steps
by executing them seperateely with a temp table for intermediate results,
you take a lot of options from the optimizer. Of course, there is also the
consideration of maintainability and readability of your code, so you
might choose to accept the performance degradation, just so that you will
understand your code when (not if!!) you (or someone else) have to get
back to it later.

>6- avoid renaming tables in SELECT statements, for example SELECT * FROM
>tblClients C

I've never heard that using a table alias (as this is called) would hurt
performance. If you have any evidence of this, please point me to it. I
would be highly surprised.

In fact, using an alias is absolutely needed when you use the same table
more than once in a query and when you use derived table; in all other
cases (except for single-table queries or very short table names) I'd also
heartily recommend using an alias. Do choose a mnemonic alias, not just a
random one or two letter combination!

>Am i correct to consider the above 6 points as valid things in terms of
>causing
>performance problems?

See above. And you might also want to take a look at this site:
http://www.sql-server-performance.com/

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||As always, Hugo has already given an excellent response. Just some
additions here...

> 3- use WITH (NOLOCK) with each SELECT statement

Don't use this, unless there is a specific performance problem with
respect to the statement in question. And even then you should only use
it after you have assessed whether the risks are acceptable.

> 6- avoid renaming tables in SELECT statements, for example SELECT * FROM
> tblClients C

Nonsense.

> 2- avoid using UNION statements

This is not a goal. Your goal should be to write statements that are
easy to read and understand and are effective. It starts there. In
general, you should not use GROUP BY, DISTINCT or UNION (without ALL) if
you don't need it. But if you do need it, then go for it, SQL-Server is
optimized for their use.

> 4- avoid using NESTED Select statements

Start with a set orientation in mind. That way you will automatically
avoid most unnecessary nested selects. The ones that remain are probably
your best choice.

> 5- use #temp tables

Don't use intermediate tables unless you have to. If you have to then
#temp tables are usually better than creating a permanent table and
dropping it at the end.

Gert-Jan
--
(Please reply only to the newsgroup)|||serge (sergea@.nospam.ehmail.com) writes:
> I have an SP that is big, huge, 700-800 lines.

Hey, you should see some of our procedures. We have one that is 3000 lines
long!

But, OK, 700 lines is no small size for a stored procedure.

Hugo and Gert-Jan has already pointed out weakness in your observations,
but I like to make some supplemental comments.

> 2- avoid using UNION statements

This is a bad rule. Sometimes UNION may be the wrong solution. Sometimes
it is the right. Even if we are talking from a performance perspective.

> 3- use WITH (NOLOCK) with each SELECT statement

Only do this, if there are unavioadble table scans, and you are really
are experience contention problems - *and* if you can accept that the
results are not consistent.

> 4- avoid using NESTED Select statements

Again, not a very good rule. But it depends a little on what you mean.
Say that you have:

SELECT a, b, (SELECT SUM(c) FROM B WHERE B.col = A.col) = c
FROM A
WHERE col2 BETWEEN 12 AND 19

My observation is that a rewrite using a derived table often gives better
performance:

SELECT A.a, A.b, B.c
FROM A
JOIN (SELECT c = SUM(c), col FROM B GROUP BY col) AS B ON A.col = B.col
WHERE col2 BETWEEN 12 AND 19

This may look expensive if B is large and there are only a handful of
values between 12 and 19 in A. But the above is only a logical description
of the query. The optimizer may recast computation order, as long as the
result is the same, and often does with a very good result.

The same thing applies to update queries:

UPDATE A
SET c = (SELECT SUM(c) FROM B WHERE A.col = B.col)
WHERE col2 BETWEEN 12 AND 19

While the above is ANSI-compliant, this is usually more effective:

UPDATE A
SET c = B.c
FROM A
JOIN (SELECT c = SUM(c), col FROM B GROUP BY col) AS B ON A.col = B.col
WHERE col2 BETWEEN 12 AND 19

You must benchmark all such changes. It may not always be the best thing
to do.

> 5- use #temp tables

This is a very complex topic. Yes, it can sometimes be a good thing to
save intermediate results in a temp table. But temp tables can also
cause performance problems, since if you fill up a temp table, SQL Server
may opt to recompile the procedure. And recompiling a 700 line stored
procedure can easily take a few seconds. This can be evaded, by using
table variables instead. Table variabels never causes recompilations.
But then again, you may want those recompilations, because it can slash
the execution time of the procedure from three hours to two minutes.

Here is a real-life story about a procedure optimization that I did
some time ago. The code originated from a stored procedure that I had
written in 1997 for MS SQL 6.0, and used a couple of temp tables on
which a bunch of operations were performed. At a customer site, this
procedure took too long time. I tried a lot of tricks in the book,
but few gave any effect.

Eventually, I replaced the most of the temp-table manipulation with a
50+ line SELECT statement which performed a FULL JOIN of three table
sources, whereof at least one was a derived table. (And a three-way
full join requires at least one derived table in itself, because you
have to full-join two by two.)

So why I did not write it this way in 1997? Well, at that time SQL Server
did not have FULL JOIN or derived tables. Also, the requirements of the
original procedure was different, and more complex that the current one.

> 6- avoid renaming tables in SELECT statements, for example SELECT * FROM
> tblClients C

No, use aliases. The impact on execution on performance is neglible, but
the impact on developer performance should not be ignored.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>>1- use SET NOCOUNT ON
> This will not affect performance, but it is good practice. Many clients
> choke on the numerous "(n row(s) affected)" messages returned by an SP
> without this setting.

From the SQL 2000 BOL:

<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_set-set_3ed0.htm">
For stored procedures that contain several statements that do not return
much actual data, this can provide a significant performance boost because
network traffic is greatly reduced.
</Excerpt
--
Hope this helps.

Dan Guzman
SQL Server MVP|||On Fri, 10 Sep 2004 11:30:32 GMT, Dan Guzman wrote:

>>>1- use SET NOCOUNT ON
>>
>> This will not affect performance, but it is good practice. Many clients
>> choke on the numerous "(n row(s) affected)" messages returned by an SP
>> without this setting.
>
>From the SQL 2000 BOL:
><Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_set-set_3ed0.htm">
>For stored procedures that contain several statements that do not return
>much actual data, this can provide a significant performance boost because
>network traffic is greatly reduced.
></Excerpt
Hi Dan,

Yes, you're right. My bad. Thanks for pointing out my error!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you all for your responses.

A lot of new things for me in your answers, I'll have to read them many
times
and take the time to fully understand them.

Thanks again.|||> 3- use WITH (NOLOCK) with each SELECT statement

This post is for everyone who gave me answers about the WITH (NOLOCK).

I've been using WITH (NOLOCK) in my own applications for a few years.
And i do know about Dirty Reads. It's basically the SELECT statement will
not
wait for a transaction with a lock to finish before getting the data. For
example,
if a transaction is at the stage of modifying a column from 'USA' to
'Canada',
the SELECT statement will fetch me USA even though the record
is locked and in the process of having the column's value being changed to
Canada.

Am i correct with my explanation?

Now, i have an SP with 300 lines with a lot tables, joins, left joins and
one or
a few unions.

If i run this SP with parameters in the SQL Query Analyzer, it takes 1
second
to return 11 rows. However, the front-end application which is ASP based,
when calls this SP, it takes 23-25 seconds for the ASP page to load!

If i use WITH (NOLOCK) on all the tables, the ASP page loads in 4-5
seconds!

I am planning on understanding this SP and all other SPs and find all the
ways possible to better write it in order to improve the code and have
better performance results. This will be a slow process.

Until then, if my explanation of WITH (NOLOCK) is valid and that there
is only 1 side-effect, then maybe we can temporarily use WITH (NOLOCK)
for the short term.

As always, I appreciate any feedback.

Thank you very much|||Hi Serge,

On Tue, 14 Sep 2004 00:23:04 -0400, serge wrote:

>I've been using WITH (NOLOCK) in my own applications for a few years.
>And i do know about Dirty Reads. It's basically the SELECT statement will
>not
>wait for a transaction with a lock to finish before getting the data. For
>example,
>if a transaction is at the stage of modifying a column from 'USA' to
>'Canada',
>the SELECT statement will fetch me USA even though the record
>is locked and in the process of having the column's value being changed to
>Canada.
>Am i correct with my explanation?

Only partially. It depends on how "far" the processing of the other
transaction has gone. If the update has already performed but the
transaction is not yet finished, the SELECT statement with nolock hint
will return the value 'Canada'.

This can cause quite unexpected side effects when the other transaction
later has to be rolled back - you'll have read a value that logically
never even existed in the database. It might even be that the rollback
occured BECAUSE the value 'Canada' violates a business rule - it's
inserted in the table first, then the trigger starts that checks the
business rules and a rollback is initiated if the business rules were
violated. Normal locking behaviour ensures that nobody ever sees the
"illegal" value 'Canada', as this row is locked until the transaction
finishes; performing dirty read means that you run the risk of returning
this "illegal" value.

>If i run this SP with parameters in the SQL Query Analyzer, it takes 1
>second
>to return 11 rows. However, the front-end application which is ASP based,
>when calls this SP, it takes 23-25 seconds for the ASP page to load!
>If i use WITH (NOLOCK) on all the tables, the ASP page loads in 4-5
>seconds!

If the SP executes in 1 second from QA, then you should be able to see
comparable performance from any other client. I suspect that this
difference is caused by something in the ASP code. As I have never used
ASP myself, I'll leave it to others to comment on this.

Your solution to use dirty reads looks more like a workaround than like a
fix. I hope someone more ASP-savvy then me can help you find the real
cause of the delay.

And I suggest you think very long and very hard about what harm might be
caused if your SP reads and uses data changed by an unfinished transaction
that might even be rolled back.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,

Your reply was very informative for me.

I liked your explanation of the "illegal" value. It's true, i didn't think
about Dirty Reads like you explained, where the value is actually 'Canada'
but that it hasn't been finalized and the record had been committed.

As far ASP having a problem, yes it has a serious problem the ASP page.
When i run the profiler, bunch of SPs get executed THREE straight times!!!
This i find very bad and I'll have to investigate why the repeat (i guess
the page
is being refreshed 3 times thus triggering all SPs to be executed three
times).

And yes I am considering WITH (NOLOCK) as a workaround for now and
not a fix.

I'll keep investigating.

Thank you very much Hugo.

> >I've been using WITH (NOLOCK) in my own applications for a few years.
> >And i do know about Dirty Reads. It's basically the SELECT statement will
> >not
> >wait for a transaction with a lock to finish before getting the data. For
> >example,
> >if a transaction is at the stage of modifying a column from 'USA' to
> >'Canada',
> >the SELECT statement will fetch me USA even though the record
> >is locked and in the process of having the column's value being changed
to
> >Canada.
> >Am i correct with my explanation?
> Only partially. It depends on how "far" the processing of the other
> transaction has gone. If the update has already performed but the
> transaction is not yet finished, the SELECT statement with nolock hint
> will return the value 'Canada'.
> This can cause quite unexpected side effects when the other transaction
> later has to be rolled back - you'll have read a value that logically
> never even existed in the database. It might even be that the rollback
> occured BECAUSE the value 'Canada' violates a business rule - it's
> inserted in the table first, then the trigger starts that checks the
> business rules and a rollback is initiated if the business rules were
> violated. Normal locking behaviour ensures that nobody ever sees the
> "illegal" value 'Canada', as this row is locked until the transaction
> finishes; performing dirty read means that you run the risk of returning
> this "illegal" value.
>
> >If i run this SP with parameters in the SQL Query Analyzer, it takes 1
> >second
> >to return 11 rows. However, the front-end application which is ASP based,
> >when calls this SP, it takes 23-25 seconds for the ASP page to load!
> >If i use WITH (NOLOCK) on all the tables, the ASP page loads in 4-5
> >seconds!
> If the SP executes in 1 second from QA, then you should be able to see
> comparable performance from any other client. I suspect that this
> difference is caused by something in the ASP code. As I have never used
> ASP myself, I'll leave it to others to comment on this.
> Your solution to use dirty reads looks more like a workaround than like a
> fix. I hope someone more ASP-savvy then me can help you find the real
> cause of the delay.
> And I suggest you think very long and very hard about what harm might be
> caused if your SP reads and uses data changed by an unfinished transaction
> that might even be rolled back.|||On Tue, 14 Sep 2004 08:02:05 -0400, serge wrote:

>As far ASP having a problem, yes it has a serious problem the ASP page.
(snip)
>I'll keep investigating.

Hi Serge,

You might consider looking in the microsoft.public hierarchy of
newsgroups. There are lots of them with "asp" somewhere in the name, so
there's a good chance you'll find an answer to this in one of those
groups. I know too little (read: nothing <g>) about asp to recognise which
group might be the best suited.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||serge (sergea@.nospam.ehmail.com) writes:
> If i run this SP with parameters in the SQL Query Analyzer, it takes 1
> second to return 11 rows. However, the front-end application which is
> ASP based, when calls this SP, it takes 23-25 seconds for the ASP page
> to load!

If the ASP code calls it three times, there is something to be fixed.

But exactly how was it run? Was any of the calls preceded by
SET FMTONLY ON? FMTONLY it a sort of NOEXEC thing, SQL Server only
returns information about result sets.

Assuming that your ASP code uses, ADO, make sure that you use
CommantType = adStoredProcedure. Do not use adCommandText with an
EXEC statement or a ODBC call syntax.

If there there is an indexed view or indexed computed column involved
somewhere, this can explain the difference between ASP and QA. In QA,
the setting ARITHABORT is ON by default, but it's off when you use
ADO. Thus, issuing SET ARITHABORT ON (or setting it default for the
database with ALTER DATABASE or for the server with sp_configure could
give some effect.)

Also, SET NOCOUNT ON is good.

> If i use WITH (NOLOCK) on all the tables, the ASP page loads in 4-5
> seconds!

This could indicate that there is some blocking. Are you alone on the
server when it takes 25 seconds to run? Unless there are concurrency
issues, I find it difficult to believe that (NOLOCK) has that drastic
effect. There is some cost for locks, but I find it difficult to believe
that it is that huge.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||A lot of good information you've provided me here. I will have to look at
the ASP
code and investigate all your points.

To answer your question. Yes i am alone on the server when it takes 25
seconds to run.
Checking SQL Profiler, i see 3 sets of the same SPs being called and the
main SP
that is slow takes about 6.5 seconds to run, multiply that by 3, that's 20
seconds.
The asp page is also calling other SPs, totaling 23-25 seconds.

I'll have to investigate much further and go through your points and other
people's points
i've received and find out what i can fix/improve.

Thanks again!

> If the ASP code calls it three times, there is something to be fixed.
> But exactly how was it run? Was any of the calls preceded by
> SET FMTONLY ON? FMTONLY it a sort of NOEXEC thing, SQL Server only
> returns information about result sets.
> Assuming that your ASP code uses, ADO, make sure that you use
> CommantType = adStoredProcedure. Do not use adCommandText with an
> EXEC statement or a ODBC call syntax.
> If there there is an indexed view or indexed computed column involved
> somewhere, this can explain the difference between ASP and QA. In QA,
> the setting ARITHABORT is ON by default, but it's off when you use
> ADO. Thus, issuing SET ARITHABORT ON (or setting it default for the
> database with ALTER DATABASE or for the server with sp_configure could
> give some effect.)
> Also, SET NOCOUNT ON is good.
> > If i use WITH (NOLOCK) on all the tables, the ASP page loads in 4-5
> > seconds!
> This could indicate that there is some blocking. Are you alone on the
> server when it takes 25 seconds to run? Unless there are concurrency
> issues, I find it difficult to believe that (NOLOCK) has that drastic
> effect. There is some cost for locks, but I find it difficult to believe
> that it is that huge.

performance tradeoff b/w stored procedure and views

i have to develop a cryatal report from SQL database.
I have a option to choose stored procedure OR a view
i need to know which will work better on a large network in terms of speed a
nd performance
Thanx in advancessaud wrote:
> i have to develop a cryatal report from SQL database.
> I have a option to choose stored procedure OR a view
> i need to know which will work better on a large network in terms of
> speed and performance
> Thanx in advance
Athough there will be a little less network traffic involved in calling a
stored procedure as opposed to selecting from a view, the network is pretty
much irrelevant to this question, unless you are doing something like
returning all the records to Crystal where they are filtered, grouped and
sorted. With a stored procedure, all of this activity can take place
_before_ the records are sent over the wire, so you may see an improvement
in network traffic in that respect.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||I would definitely choose stored procedures over views.
1. In a stored procedure, you can use variables and temporary tables to make
complex calculations from varios sources. In a view, you are pretty limited.
With stored procedures you can avoid using e.g. array variables in Crystal
Reports and keep it as a simple presentation layer.
2. By avoiding calculations in Crystal Reports, you can decrease network
traffic a lot. By just sending the 20 resulting rows instead of the 20000
rows involved in the caculation, you could gain a lot in performance.
To sum up: Do your calculations in stored procedures and your presentation
in Crystal Reports.
- Kristoffer -
"ssaud" <ssaud.1l655w@.mail.codecomments.com> wrote in message
news:ssaud.1l655w@.mail.codecomments.com...
> i have to develop a cryatal report from SQL database.
> I have a option to choose stored procedure OR a view
> i need to know which will work better on a large network in terms of
> speed and performance
> Thanx in advance
>
> --
> ssaud
> ---
> Posted via http://www.codecomments.com
> ---
>|||Using stored procedures allows you to execute the same execution plan for
the report, therefore cutting down on system resources required to complete
the query.
"ssaud" <ssaud.1l655w@.mail.codecomments.com> wrote in message
news:ssaud.1l655w@.mail.codecomments.com...
> i have to develop a cryatal report from SQL database.
> I have a option to choose stored procedure OR a view
> i need to know which will work better on a large network in terms of
> speed and performance
> Thanx in advance
>
> --
> ssaud
> ---
> Posted via http://www.codecomments.com
> ---
>

Performance question

I have written a stored procedure to obtain businesses in a given zipcode
radius(eg: Get all chinese restaurants in 10 miles from 94568 zipcode). All
the fields being used in the main stored procedure query are indexed. When I
run the stored procedure for the first time after a long time, it takes a
really long time to return results (2-3 minutes). But any runs after this ar
e
very fast (1 second or less).
Question: Does anyone know why this may be happening (my gut feeling is that
it has to do with indexes being cached in memory, but it would be good if
someone can explain this more clearly). What can I do to make sure that this
query always runs very fast? Is there a way to preload tables or indexes in
the memory as this query is one of the main queries in the application and
will be run a lot.Some more info on this performance issue:
When I run 'estimated execution plan' on the stored procedure in the Query
Analyzer, it seems to be saying there is full table scan on the the table
containing the businesses, even though the zipcode field is indexed.
What does this mean?
THanks,
Ashhad
"Ashhad Syed" wrote:

> I have written a stored procedure to obtain businesses in a given zipcode
> radius(eg: Get all chinese restaurants in 10 miles from 94568 zipcode). Al
l
> the fields being used in the main stored procedure query are indexed. When
I
> run the stored procedure for the first time after a long time, it takes a
> really long time to return results (2-3 minutes). But any runs after this
are
> very fast (1 second or less).
> Question: Does anyone know why this may be happening (my gut feeling is th
at
> it has to do with indexes being cached in memory, but it would be good if
> someone can explain this more clearly). What can I do to make sure that th
is
> query always runs very fast? Is there a way to preload tables or indexes i
n
> the memory as this query is one of the main queries in the application and
> will be run a lot.
>
>|||On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:

>I have written a stored procedure to obtain businesses in a given zipcode
>radius(eg: Get all chinese restaurants in 10 miles from 94568 zipcode). All
>the fields being used in the main stored procedure query are indexed. When
I
>run the stored procedure for the first time after a long time, it takes a
>really long time to return results (2-3 minutes). But any runs after this a
re
>very fast (1 second or less).
>Question: Does anyone know why this may be happening (my gut feeling is tha
t
>it has to do with indexes being cached in memory, but it would be good if
>someone can explain this more clearly). What can I do to make sure that thi
s
>query always runs very fast? Is there a way to preload tables or indexes in
>the memory as this query is one of the main queries in the application and
>will be run a lot.
>
Hi Ashhad,
Please post the table definitions (as CREATE TABLE statements, including
all constraints and indexes) and the code of the stored proc.
See www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||I will do it later today. Thanks.
"Hugo Kornelis" wrote:

> On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>
> Hi Ashhad,
> Please post the table definitions (as CREATE TABLE statements, including
> all constraints and indexes) and the code of the stored proc.
> See www.aspfaq.com/5006.
> --
> Hugo Kornelis, SQL Server MVP
>|||Hugo:
Here is the sql script for all tables and stored procs involved:
Ashhad
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ZIPCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ZIPCodes]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[specialty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[specialty]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[vm_serviceprovider]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[vm_serviceprovider]
GO
CREATE TABLE [dbo].[ZIPCodes] (
[ZIPCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIPType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CityName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CityType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateAbbr] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AreaCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Latitude] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Longitude] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[specialty] (
[spid] [int] NULL ,
[law_specialty] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[vm_serviceprovider] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhoneAreaCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SIC_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SIC_Desc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MarketingStatement] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[NoOfEmp] [int] NULL ,
[YearsInBusiness] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MobileEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_No] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Expiration] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CSC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhoneNotify] [int] NULL ,
[PhoneNotify_No] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailNotify] [int] NULL ,
[CellNotify] [int] NULL ,
[CellNotify_No] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FaxNotify] [int] NULL ,
[OtherCertification] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[LastReferralDate] [datetime] NULL ,
[Subscriber] [int] NULL ,
[Updated_By] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Updated] [datetime] NULL ,
[timezone] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BusinessHours] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[open_wends] [int] NULL ,
[provide_loner_car] [int] NULL ,
[leadsquota] [int] NULL ,
[pic1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pic2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pic3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PackageID] [int] NULL ,
[websiteurl] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[plan_enrollment_date] [datetime] NULL ,
[next_billing_date] [datetime] NULL ,
[credit] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adsize] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[frachise] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[msa] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branch] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sales] [money] NULL ,
[sales_range] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[employee_range] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[internalrating] [float] NULL ,
[business_type] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[admin_notes] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[total_calls] [int] NULL ,
[last_call] [datetime] NULL ,
[sendalert] [int] NULL ,
[ph_referredby] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[college] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[marketing_flag] [int] NULL ,
[bar_association] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[monthly_budget] [money] NULL ,
[account_onoff] [int] NULL ,
[call_enabled] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[vm_serviceprovider] WITH NOCHECK ADD
CONSTRAINT [PK_vm_serviceprovider_1] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_ZIPCodes] ON [dbo].[ZIPCodes]([ZIPCode]) ON
[PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_specialty] ON [dbo].[specialty]([zip]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[vm_serviceprovider] WITH NOCHECK ADD
CONSTRAINT [DF_vm_serviceprovider_total_calls] DEFAULT (0) FOR [total_calls]
GO
CREATE INDEX [IX_vm_serviceprovider_ph] ON
[dbo].[vm_serviceprovider]([Phone]) ON [PRIMARY]
GO
CREATE INDEX [idx_vmsp_username] ON [dbo].[vm_serviceprovider]([UserName])
ON [PRIMARY]
GO
CREATE INDEX [idx_vmsp_password] ON [dbo].[vm_serviceprovider]([Password])
ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_5] ON
[dbo].[vm_serviceprovider]([Company]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_6] ON
[dbo].[vm_serviceprovider]([LName], [FName], [State]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_7] ON
[dbo].[vm_serviceprovider]([LName]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_8] ON
[dbo].[vm_serviceprovider]([Address]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_9] ON
[dbo].[vm_serviceprovider]([City]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_10] ON
[dbo].[vm_serviceprovider]([City]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_11] ON
[dbo].[vm_serviceprovider]([Zip]) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[RadiusAssistant]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[RadiusAssistant]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[RadiusAssistant](
@.ZIPCode char(5),
@.Miles decimal(18, 9)
) RETURNS
@.MaxPoints TABLE (
Latitude decimal(10,8),
Longitude decimal(11,8),
MaxLat decimal(10,8),
MinLat decimal(10,8),
MaxLong decimal(11,8),
MinLong decimal(11,8))
AS
BEGIN
DECLARE
@.Latitude decimal(10,8),
@.Longitude decimal(11,8)
SELECT @.Latitude = Latitude,
@.Longitude = Longitude
FROM [dbo].[ZIPCodes]
WHERE ZIPCode = @.ZIPCode
AND CityType = 'D'
IF 0 = @.@.rowcount
RETURN /* invalid zip */
DECLARE @.MilesPerDegree decimal(10,8)
SET @.MilesPerDegree = 69.172
DECLARE
@.MaxLat decimal(10, 8),
@.MinLat decimal(10, 8),
@.MaxLong decimal(11, 8),
@.MinLong decimal(11, 8)
SET @.MaxLat = @.Latitude + @.Miles / @.MilesPerDegree
SET @.MinLat = @.Latitude - (@.MaxLat - @.Latitude)
SET @.MaxLong = @.Longitude + @.Miles / (Cos(@.MinLat * PI() / 180) *
@.MilesPerDegree)
SET @.MinLong = @.Longitude - (@.MaxLong - @.Longitude)
INSERT @.MaxPoints
SELECT @.Latitude, @.Longitude, @.MaxLat, @.MinLat, @.MaxLong, @.MinLong
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[RadiusSearch3]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[RadiusSearch3]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[RadiusSearch3]
@.ZIPCode char(5),
@.Miles decimal(11, 6),
@.BusinessType varchar(50)
AS
/*'<A href=''http://www.mapquest.com/maps/map.adp?address='+ IsNUll(
t.Address,'') + '&city='+ t.City +'&state='+ t.state +'&zipcode='+ t.zip
+'&country=US&cid=lfmaplink'' target=_blank> Map </A>'*/
SELECT top 100 t.id, t.marketing_flag,
[dbo].[DistanceAssistant](z.Latitude,z.Longitude,r.Latitude,r.Longitude) as
distance, '<b>' + t.FNAME + ' '+ t.LNAME + '</b>, ' + t.COMPANY as
companyname, '<b>' + t.FNAME + ' '+ t.LNAME + '</b><br>' + t.COMPANY +
'<br>' + t.ADDRESS + '<br>' + t.CITY + ', '+ t.State + ' ' + t.ZIP +
'<A href=''http://maps.google.com/maps?f=q&hl=en&q='+ IsNUll(
t.Address,'') + ', '+ t.City +', '+ t.state +' '+ t.zip +'''
target=_blank> Map </A>'+ '<b>'+ cast (cast
([dbo].[DistanceAssistant](z.Latitude,z.Longitude,r.Latitude,r.Longitude) as
dec (5, 1)) as varchar) + ' miles away </b><br><br>' +
'<b> Attorney Experience: </b>'+ cast ((DATEPART(yyyy, GETDATE())-
t.YearsInBusiness) as varchar)+ ' years' + '<b> Firm Size: </b>'+
CASE
WHEN NoOfEmp IS NULL THEN 'Not Known'
WHEN NoOfEmp <= 5 THEN 'Small'
WHEN NoOfEmp> 5 and noOfEmp < 30 THEN 'Medium'
WHEN NoOfEmp>= 30 THEN 'Large'
ELSE 'Not Known'
END + '</b><br><br>' + substring ( IsNull( t.MarketingStatement, ''),
1, 200) + '<A href=SP.aspx?ID='+ convert(varchar,t.ID) + ' target=_blank>
More </A>' + '<br><br><b>PRACTICE FOCUS:</b><br>' + t.business_type as
[Attorney Info],
CASE call_enabled
WHEN 'Y' THEN '<A class=link href=CallAttorney.aspx?ID=' +
cast(t.id as varchar) + ' target=_blank> CLICK HERE TO CALL! </A>'
ELSE 'Call feature not available'
END as [call]
/* add any other fields here */
/* Distance Assistant required */
FROM [dbo].[ZIPCodes] z,
[dbo].[RadiusAssistant](@.ZIPCode, @.Miles) r
, [dbo].[specialty] u
, [dbo].[vm_serviceprovider] t
/* if you wanted to join this with your stores/dealers/users table simply
add a line of code here */
/* and see below for the other line of code */
/*
[dbo].[vm_serviceprovider] t
*/
WHERE 1=1
AND z.Latitude BETWEEN r.MinLat AND r.MaxLat
AND z.Longitude BETWEEN r.MinLong AND r.MaxLong
AND z.CityType = 'D' /* only one result per ZIP */
AND z.ZipType <> 'M' /* don't include "Military" ZIPs which don't have a
lat/long */
/* SQL caches the result of this function - so performance is not impacted
even though we run it twice */
AND
[dbo].[DistanceAssistant](z.Latitude,z.Longitude,r.Latitude,r.Longitude) <=
@.Miles
AND u.ZIP = z.ZIPCode
AND u.law_specialty = @.BusinessType
AND t.id = u.spid
/* dealer locator join continued */
/* match the ZIP Code Download ZIPCode column to the ZIP Code column on the
users/stores/dealers table */
/*
AND u.ZIPCode = z.ZIPCode
*/
ORDER BY
t.marketing_flag desc, distance asc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Also the name of the procedure being used to return businesses in a zipcode
radius is Radius3
"Hugo Kornelis" wrote:

> On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>
> Hi Ashhad,
> Please post the table definitions (as CREATE TABLE statements, including
> all constraints and indexes) and the code of the stored proc.
> See www.aspfaq.com/5006.
> --
> Hugo Kornelis, SQL Server MVP
>|||I managed to solve the problem. Thanks for your offer to look into this.
"Hugo Kornelis" wrote:

> On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>
> Hi Ashhad,
> Please post the table definitions (as CREATE TABLE statements, including
> all constraints and indexes) and the code of the stored proc.
> See www.aspfaq.com/5006.
> --
> Hugo Kornelis, SQL Server MVP
>|||u need an index on the lattitudes, not the zipcodes.
you are selecting WHERE lattitude.
regards,
doug

PErformance Question

Hi All,
Its kinda very vague question.. but I can't help it..
While trying to optimize a stored procedure which was taking 3 to 5 secs on
a development server. I could see it was doing
14000 reads (in profiler). I introduced 2 indexes to the tables used in the
SP and rewrote in a better way(removed cursor and introduced set based
operation).
After the change I could see the reads came down to 700, but the duration
remained the same.
Why doesnt the duration come down when the reads came down so much.
Is this a valid question? im not sure.. ie can we expect a better duration
if the reads are minimized?
If yes why doesnt the duration come down?
Thanks,
Pradeep KuttyAre there varchar fields in your table or in any index you have a datetime
field?
Perhaps a good idea would be to store these indexes in another filegroup (if
they are clustered)
regards,
"Pradeep Kutty" wrote:

> Hi All,
> Its kinda very vague question.. but I can't help it..
> While trying to optimize a stored procedure which was taking 3 to 5 secs o
n
> a development server. I could see it was doing
> 14000 reads (in profiler). I introduced 2 indexes to the tables used in th
e
> SP and rewrote in a better way(removed cursor and introduced set based
> operation).
> After the change I could see the reads came down to 700, but the duration
> remained the same.
> Why doesnt the duration come down when the reads came down so much.
> Is this a valid question? im not sure.. ie can we expect a better duration
> if the reads are minimized?
> If yes why doesnt the duration come down?
> Thanks,
> Pradeep Kutty
>
>|||Hi
Post your DDL and DML, without that, we can't pass much comment
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pradeep Kutty" wrote:

> Hi All,
> Its kinda very vague question.. but I can't help it..
> While trying to optimize a stored procedure which was taking 3 to 5 secs o
n
> a development server. I could see it was doing
> 14000 reads (in profiler). I introduced 2 indexes to the tables used in th
e
> SP and rewrote in a better way(removed cursor and introduced set based
> operation).
> After the change I could see the reads came down to 700, but the duration
> remained the same.
> Why doesnt the duration come down when the reads came down so much.
> Is this a valid question? im not sure.. ie can we expect a better duration
> if the reads are minimized?
> If yes why doesnt the duration come down?
> Thanks,
> Pradeep Kutty
>
>

Performance Question

I have a stored procedure that a report uses. When I run this sp in
management studio it takes about four and a half minutes to run. When I run
the report, the report takes more than 15 minutes to generate. Any idea why
there is such a large difference in the time it takes to run the sp vs
generate the report?
Thanks.How many records are returned? The number of records being rendered makes a
big difference with Reporting Services.
Also, a 4 1/2 minute report is very long, have you looked into optimizing
it. For instance, does your database need some additional indexes?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I have a stored procedure that a report uses. When I run this sp in
>management studio it takes about four and a half minutes to run. When I
>run the report, the report takes more than 15 minutes to generate. Any
>idea why there is such a large difference in the time it takes to run the
>sp vs generate the report?
> Thanks.
>|||Where is the data located?
Maybe on the reporting server it takes longer to transmit the info.
"Tim Kelley" wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.
>
>|||How is the data being rendered? Are you using a matrix versus a table
to display the data?
HTH
Jason Strate
On Oct 15, 2:27 pm, "Tim Kelley" <tkel...@.company.com> wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.|||The data is stored on a different SQL server.
Tim
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:99D473D3-5B0C-44DD-8326-5D63415A3510@.microsoft.com...
> Where is the data located?
> Maybe on the reporting server it takes longer to transmit the info.
>
>
> "Tim Kelley" wrote:
>> I have a stored procedure that a report uses. When I run this sp in
>> management studio it takes about four and a half minutes to run. When I
>> run
>> the report, the report takes more than 15 minutes to generate. Any idea
>> why
>> there is such a large difference in the time it takes to run the sp vs
>> generate the report?
>> Thanks.
>>|||Before optimizing the stored procedure it would run for 45 minutes and not
finish. The sp returns 998 records.
Tim
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
> How many records are returned? The number of records being rendered makes
> a big difference with Reporting Services.
> Also, a 4 1/2 minute report is very long, have you looked into optimizing
> it. For instance, does your database need some additional indexes?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>|||That is not many records so rendering should not be an issue. Try using With
Recompile when creating the stored procedure and see if that makes any
difference.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ubvT7s2DIHA.4772@.TK2MSFTNGP02.phx.gbl...
> Before optimizing the stored procedure it would run for 45 minutes and not
> finish. The sp returns 998 records.
> Tim
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
>> How many records are returned? The number of records being rendered makes
>> a big difference with Reporting Services.
>> Also, a 4 1/2 minute report is very long, have you looked into optimizing
>> it. For instance, does your database need some additional indexes?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tim Kelley" <tkelley@.company.com> wrote in message
>> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>>
>

Performance Question

Hello Everybody,
I do have a question abt performance of one of my report store procedure.
We have a reporing application using Microsoft Reporting Services a Report
Front End and SQL SERVER 2000 as a DB. I have written one report store proc.
That report store proc is taking arround 30 sec to run in Query Analyzer. I
had opened 10 query analyzer windows and ran that report at same time from
each window and it is taking arround 35-40 sec to run. But if 10 people
access that same report from Report Server at same time, it is taking very
long time.. arround 8-10 minutes...
so really wondering...what would be the reason. I have proper indexes
created on all the appropriate columns..
So pls let me know what i can do ?
ThanksGoing from 30 seconds to 10 minutes sounds like blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/defaul...kb;en-us;271509
aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F3C00A9E-9D21-4B58-98B5-C2A2A042BEE7@.microsoft.com...
> Hello Everybody,
> I do have a question abt performance of one of my report store procedure.
> We have a reporing application using Microsoft Reporting Services a Report
> Front End and SQL SERVER 2000 as a DB. I have written one report store
> proc.
> That report store proc is taking arround 30 sec to run in Query Analyzer.
> I
> had opened 10 query analyzer windows and ran that report at same time from
> each window and it is taking arround 35-40 sec to run. But if 10 people
> access that same report from Report Server at same time, it is taking very
> long time.. arround 8-10 minutes...
> so really wondering...what would be the reason. I have proper indexes
> created on all the appropriate columns..
> So pls let me know what i can do ?
> Thanks

Performance problem -- Execute stored procedure

Hello,
I have a problem on running one particular stored procedure. It takes
less than 1 second when I run this stored procedure using SQL query
analyzer. However, when I run the same stored procedure using
Reporting Services, it takes 3,4 minutes to execute. This stored
procedure returns 52 rows with 16 fields. Does anyone know why and
how to solve this problem?
Thanks!I have never seen this myself but have heard of it before. For whatever
reason the query plan is messed up for that stored procedure when executing
it from RS. Try one of the below (I would start off with the With Recompile
as a test of whether this is the problem).
Forcing a Stored Procedure to Recompile
SQL Server provides three ways to force a stored procedure to recompile:
a.. The sp_recompile system stored procedure forces a recompile of a
stored procedure the next time it is run.
b.. Creating a stored procedure that specifies the WITH RECOMPILE option
in its definition indicates that SQL Server does not cache a plan for this
stored procedure; the stored procedure is recompiled each time it is
executed. Use the WITH RECOMPILE option when stored procedures take
parameters whose values differ widely between executions of the stored
procedure, resulting in different execution plans to be created each time.
Use of this option is uncommon and causes the stored procedure to execute
more slowly, because the stored procedure must be recompiled each time it is
executed.
If you only want individual queries inside the stored procedure to be
recompiled, rather than the entire stored procedure, specify the RECOMPILE
query hint inside each query you want recompiled. This behavior mimics SQL
Server's statement-level recompilation behavior noted above, but in addition
to using the stored procedure's current parameter values, the RECOMPILE
query hint also uses the values of any local variables inside the stored
procedure when compiling the statement. Use this option when atypical or
temporary values are used in only a subset of queries belonging to the
stored procedure. For more information, see Query Hint (Transact-SQL).
c.. You can force the stored procedure to be recompiled by specifying the
WITH RECOMPILE option when you execute the stored procedure. Use this option
only if the parameter you are supplying is atypical or if the data has
significantly changed since the stored procedure was created.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<chiekot@.taiweb.com> wrote in message
news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> Hello,
> I have a problem on running one particular stored procedure. It takes
> less than 1 second when I run this stored procedure using SQL query
> analyzer. However, when I run the same stored procedure using
> Reporting Services, it takes 3,4 minutes to execute. This stored
> procedure returns 52 rows with 16 fields. Does anyone know why and
> how to solve this problem?
> Thanks!
>|||On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never seen this myself but have heard of it before. For whatever
> reason the query plan is messed up for that stored procedure when executing
> it from RS. Try one of the below (I would start off with the With Recompile
> as a test of whether this is the problem).
> Forcing a Stored Procedure to Recompile
> SQL Server provides three ways to force a stored procedure to recompile:
> a.. The sp_recompile system stored procedure forces a recompile of a
> stored procedure the next time it is run.
> b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> in its definition indicates that SQL Server does not cache a plan for this
> stored procedure; the stored procedure is recompiled each time it is
> executed. Use the WITH RECOMPILE option when stored procedures take
> parameters whose values differ widely between executions of the stored
> procedure, resulting in different execution plans to be created each time.
> Use of this option is uncommon and causes the stored procedure to execute
> more slowly, because the stored procedure must be recompiled each time it is
> executed.
> If you only want individual queries inside the stored procedure to be
> recompiled, rather than the entire stored procedure, specify the RECOMPILE
> query hint inside each query you want recompiled. This behavior mimics SQL
> Server's statement-level recompilation behavior noted above, but in addition
> to using the stored procedure's current parameter values, the RECOMPILE
> query hint also uses the values of any local variables inside the stored
> procedure when compiling the statement. Use this option when atypical or
> temporary values are used in only a subset of queries belonging to the
> stored procedure. For more information, see Query Hint (Transact-SQL).
> c.. You can force the stored procedure to be recompiled by specifying the
> WITH RECOMPILE option when you execute the stored procedure. Use this option
> only if the parameter you are supplying is atypical or if the data has
> significantly changed since the stored procedure was created.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <chie...@.taiweb.com> wrote in message
> news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > Hello,
> > I have a problem on running one particular stored procedure. It takes
> > less than 1 second when I run this stored procedure using SQL query
> > analyzer. However, when I run the same stored procedure using
> > Reporting Services, it takes 3,4 minutes to execute. This stored
> > procedure returns 52 rows with 16 fields. Does anyone know why and
> > how to solve this problem?
> > Thanks!
Also, to improve the performance of the stored procedure in general,
you could evaluate it with the Database Engine Tuning Advisor and
implement the suggested indexes where acceptable. Hope this helps
further.
Regards,
Enrique Martinez
Sr. Software Consultant|||Bruce and Enrique, Thank you very much for your responses. We solved
this problem. What our DBA told me is that he changed that the stored
procedure is created with SET QUOTED_IDENTIFIER to ON. I appreciate
all your suggestions.
Retards,
Chieko
On Oct 15, 6:03 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>
>
> > I have never seen this myself but have heard of it before. For whatever
> > reason the query plan is messed up for that stored procedure when executing
> > it from RS. Try one of the below (I would start off with the With Recompile
> > as a test of whether this is the problem).
> > Forcing a Stored Procedure to Recompile
> > SQL Server provides three ways to force a stored procedure to recompile:
> > a.. The sp_recompile system stored procedure forces a recompile of a
> > stored procedure the next time it is run.
> > b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> > in its definition indicates that SQL Server does not cache a plan for this
> > stored procedure; the stored procedure is recompiled each time it is
> > executed. Use the WITH RECOMPILE option when stored procedures take
> > parameters whose values differ widely between executions of the stored
> > procedure, resulting in different execution plans to be created each time.
> > Use of this option is uncommon and causes the stored procedure to execute
> > more slowly, because the stored procedure must be recompiled each time it is
> > executed.
> > If you only want individual queries inside the stored procedure to be
> > recompiled, rather than the entire stored procedure, specify the RECOMPILE
> > query hint inside each query you want recompiled. This behavior mimics SQL
> > Server's statement-level recompilation behavior noted above, but in addition
> > to using the stored procedure's current parameter values, the RECOMPILE
> > query hint also uses the values of any local variables inside the stored
> > procedure when compiling the statement. Use this option when atypical or
> > temporary values are used in only a subset of queries belonging to the
> > stored procedure. For more information, see Query Hint (Transact-SQL).
> > c.. You can force the stored procedure to be recompiled by specifying the
> > WITH RECOMPILE option when you execute the stored procedure. Use this option
> > only if the parameter you are supplying is atypical or if the data has
> > significantly changed since the stored procedure was created.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > <chie...@.taiweb.com> wrote in message
> >news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > > Hello,
> > > I have a problem on running one particular stored procedure. It takes
> > > less than 1 second when I run this stored procedure using SQL query
> > > analyzer. However, when I run the same stored procedure using
> > > Reporting Services, it takes 3,4 minutes to execute. This stored
> > > procedure returns 52 rows with 16 fields. Does anyone know why and
> > > how to solve this problem?
> > > Thanks!
> Also, to improve the performance of the stored procedure in general,
> you could evaluate it with the Database Engine Tuning Advisor and
> implement the suggested indexes where acceptable. Hope this helps
> further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -

Performance Problem

I have the following DDL/indexes and the following Stored procedure. I am
getting 28 seconds for per user which is unexceptable. What will be the best
way to index this table ?
UserName column has 28000 rows for 1 user (User1) and TR column has mostly
usique numbers........
Thanks for any help.
CREATE TABLE [dbo].[CostCompAvailTemp] (
[UserName] [char] (40) NOT NULL ,
[TR] [decimal](10, 0) NOT NULL ,
[RHour] [smallint] NULL ,
[Def] [decimal](5, 3) NULL ,
[Price] [decimal](4, 2) NULL ,
[RDL] [smallint] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
[dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
GO
CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTem
p]([TR]) ON
[PRIMARY]
GO
----
--
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
declare @.TR as int
/* clean up old */
delete from CostCompAvailTemp
where UserName = @.UserName
set @.TR = 1
while @.TR < 6
begin
insert into CostCompAvailTemp
select @.UserName,
case @.TR
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline,
CreditAvail,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end,
Null
from AvailabilityDetail
where AvailNumber = @.AvailNum
set @.TR = @.TR + 1
end
/* clean then invalid values */
delete from CostCompAvailTemp
where TR < 1
or TR is Null
GOI think the main problem of your process is with this statement
delete from CostCompAvailTemp
where TR < 1
or TR is Null
here your are not using the index you create since the column TR is not a
part of the index you could either add the column TR to the index or create
a
second index for this column. you could also consider create a primary key
for your temp table.
Greetings,
Lic. Alfonso Rafael Chavez de León
Consultor TI y DBA
Xignux Corporativo SA de CV
"DXC" wrote:

> I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the be
st
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvail
Temp]([TR]) ON
> [PRIMARY]
> GO
> ----
--
>
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> declare @.TR as int
> /* clean up old */
> delete from CostCompAvailTemp
> where UserName = @.UserName
> set @.TR = 1
> while @.TR < 6
> begin
> insert into CostCompAvailTemp
> select @.UserName,
> case @.TR
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline,
> CreditAvail,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end,
> Null
> from AvailabilityDetail
> where AvailNumber = @.AvailNum
> set @.TR = @.TR + 1
> end
> /* clean then invalid values */
> delete from CostCompAvailTemp
> where TR < 1
> or TR is Null
>
> GO
>|||"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
>I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the
> best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailT
emp]([TR])
> ON
> [PRIMARY]
> GO
>
First, use one query, not 5.
Second, don't insert crap, and then clean it up later. Just refrain from
inserting the invalid rows to begin with.
Here's a rewrite using a temp table, a cross join and a derived table:
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
set nocount on
delete from CostCompAvailTemp
where UserName = @.UserName
declare @.TR table(tr int primary key)
insert into @.TR(tr) values (1)
insert into @.TR(tr) values (2)
insert into @.TR(tr) values (3)
insert into @.TR(tr) values (4)
insert into @.TR(tr) values (5)
insert into CostCompAvailTemp
select * from
(
select
@.Username UserName,
case tr.tr
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline RHour,
CreditAvail Def,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end Price,
Null RDL
from AvailabilityDetail
cross join @.TR tr
where AvailNumber = @.AvailNum
) dt
where TR < 1
or TR is Null|||Thanks but developers are telling me that TR column will have duplicates...
..
"David Browne" wrote:

> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
> First, use one query, not 5.
> Second, don't insert crap, and then clean it up later. Just refrain from
> inserting the invalid rows to begin with.
> Here's a rewrite using a temp table, a cross join and a derived table:
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> set nocount on
> delete from CostCompAvailTemp
> where UserName = @.UserName
>
> declare @.TR table(tr int primary key)
> insert into @.TR(tr) values (1)
> insert into @.TR(tr) values (2)
> insert into @.TR(tr) values (3)
> insert into @.TR(tr) values (4)
> insert into @.TR(tr) values (5)
> insert into CostCompAvailTemp
> select * from
> (
> select
> @.Username UserName,
> case tr.tr
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline RHour,
> CreditAvail Def,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end Price,
> Null RDL
> from AvailabilityDetail
> cross join @.TR tr
> where AvailNumber = @.AvailNum
> ) dt
> where TR < 1
> or TR is Null
>
>

Performance problem

need help ASAP on this.
I had a stored procedure that pulls the "bookings" for our company
(sales, if you will) for the past year. Before, it looked like this:
SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
UVT1, SLNAME, UVT2, AXTXT, USL1
FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
'#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE
UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
'3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
TXUYUF01.VANR NOT LIKE 'ZZ%' AND
bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
It was running fine. It was a little slow, but not too bad.
Fastforward to today. We added a new unit to our company, that now
requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
Now the query looks like this:
SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
UVT1, SLNAME, UVT2, AXTXT, USL1
FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
'#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE
UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
'3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
TXUYUF01.VANR NOT LIKE 'ZZ%' AND
bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
UBNR = '001'
Problem? Though these tables have changed only modestly since this
changeover (number of rows), the query takes 5 minutes to complete, and
the CPU usage on a dual-processor server goes to 100%. I can't even
bring up the task manager until the query completes. This wasn't a
problem with the old procedure.
Can someone help me figure this out and what I need to do?
UPDATE: I have redone the query to look like this as per some other
people in the SQL Server Central forum
I have put a non-clustered index on the table TXUYUV00 involving UBLA,
UBNR and USL1, and have redone the query to this:
SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
UVT1, SLNAME, UVT2, AXTXT, USL1
FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE
bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
TXUYUF01.VANR NOT LIKE 'ZZ%' AND
bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
It still runs and runs and takes a ton of CPU time. The Clustered
Index s is taking 49% of the execution plan. When I run the query,
I have to stop it immediately because it makes the CPU go to 90% and
the system stays there.
It didn't used to do this. I can't figure out why it does now.Which version of SQL are you using? 2000 or 2005?
Regards
Colin Dawson
www.cjdawson.com
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> need help ASAP on this.
> I had a stored procedure that pulls the "bookings" for our company
> (sales, if you will) for the past year. Before, it looked like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
> LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
> It was running fine. It was a little slow, but not too bad.
> Fastforward to today. We added a new unit to our company, that now
> requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
> Now the query looks like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
> and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
> TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
> UBNR = '001'
>
> Problem? Though these tables have changed only modestly since this
> changeover (number of rows), the query takes 5 minutes to complete, and
> the CPU usage on a dual-processor server goes to 100%. I can't even
> bring up the task manager until the query completes. This wasn't a
> problem with the old procedure.
> Can someone help me figure this out and what I need to do?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
> TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
> IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
> ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
> TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
> CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
> TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
>
> It still runs and runs and takes a ton of CPU time. The Clustered
> Index s is taking 49% of the execution plan. When I run the query,
> I have to stop it immediately because it makes the CPU go to 90% and
> the system stays there.
> It didn't used to do this. I can't figure out why it does now.
>|||Ok. I've just taken a very quick look at this select statement and it'll be
very difficult to help without more information.
Here's what we'll need to know...
1. what version of SQL server are you using?
2. what tables to does is column come from? Please modify the sql that I've
attached below to include table alias's for all the columns. It'll help
when it comes to re-jitting the code to make it more efficient.
3. what does the execution plan look like? Please include a text based
execution place (use set showplan on to get it)
4. can you include ddl and some example data so that we'll be able to
actually test the the procedure. It's important because we don't know what
datatypes these columns are.
I've cleaned up the second SQL statement to help others decipher what's
happening, here's the code...
SELECT
UKDR,
VANR,
VMGS,
VPR1,
UDAT,
VERA,
UBLN,
VMGL,
ReportingGroup,
UVT1,
SLNAME,
UVT2,
AXTXT,
USL1
FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON bdg_view_GroupingByReceivableCustomer.KKDR =
TXUYUV00.UKDR)
INNER JOIN TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN
and TXUYUV00.UBLA=TXUYUF01.VBLA
AND TXUYUV00.UBNR=TXUYUF01.VBNR
AND TXUYUV00.UPLT = TXUYUF01.VPLT )
INNER JOIN TXMYTX00 ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR
AND TXUYUV00.UBNR = TXMYTX00.AXBNR
and TXUYUV00.UPLT=TXMYTX00.AXPLT)
LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE UBLA<>'JB'
AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3'
AND TXUYUF01.VDAT >= 20050926
AND TXUYUF01.VDAT <= 20060924
AND TXUYUF01.VANR NOT LIKE 'ZZ%'
AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
AND TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
and UBNR = '001'
Regards
Colin Dawson
www.cjdawson.com
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> need help ASAP on this.
> I had a stored procedure that pulls the "bookings" for our company
> (sales, if you will) for the past year. Before, it looked like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
> LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
> It was running fine. It was a little slow, but not too bad.
> Fastforward to today. We added a new unit to our company, that now
> requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
> Now the query looks like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
> and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
> TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
> UBNR = '001'
>
> Problem? Though these tables have changed only modestly since this
> changeover (number of rows), the query takes 5 minutes to complete, and
> the CPU usage on a dual-processor server goes to 100%. I can't even
> bring up the task manager until the query completes. This wasn't a
> problem with the old procedure.
> Can someone help me figure this out and what I need to do?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
> TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
> IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
> ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
> TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
> CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
> TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
>
> It still runs and runs and takes a ton of CPU time. The Clustered
> Index s is taking 49% of the execution plan. When I run the query,
> I have to stop it immediately because it makes the CPU go to 90% and
> the system stays there.
> It didn't used to do this. I can't figure out why it does now.
>|||> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
Try creating this same index on the other tables as well. You are joining
to several tables using these columns, and you need the index on all of
them. Put the column with the most distinct values first in your index, and
the column with the fewest distinct values last. I think this will speed
things up.
However, it is hard to say without proper DDL. Post DDL showing your
tables, keys, and indexes, and we may be able to give a better answer.
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> need help ASAP on this.
> I had a stored procedure that pulls the "bookings" for our company
> (sales, if you will) for the past year. Before, it looked like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
> LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
> It was running fine. It was a little slow, but not too bad.
> Fastforward to today. We added a new unit to our company, that now
> requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
> Now the query looks like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
> and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
> TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
> UBNR = '001'
>
> Problem? Though these tables have changed only modestly since this
> changeover (number of rows), the query takes 5 minutes to complete, and
> the CPU usage on a dual-processor server goes to 100%. I can't even
> bring up the task manager until the query completes. This wasn't a
> problem with the old procedure.
> Can someone help me figure this out and what I need to do?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
> TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
> IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
> ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
> TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
> CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
> TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
>
> It still runs and runs and takes a ton of CPU time. The Clustered
> Index s is taking 49% of the execution plan. When I run the query,
> I have to stop it immediately because it makes the CPU go to 90% and
> the system stays there.
> It didn't used to do this. I can't figure out why it does now.
>|||Brent
In addition to others I want to ask why do you use dates as intereger
datatype? Am I right?
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
Or it is just wrong typing? Shoul be
> '3' AND TXUYUF01.VDAT >= '20050926' AND TXUYUF01.VDAT <= '20060924' AND
Run DBCC FREEPROCCACHE and your query and see what is going on
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> need help ASAP on this.
> I had a stored procedure that pulls the "bookings" for our company
> (sales, if you will) for the past year. Before, it looked like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
> LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
> It was running fine. It was a little slow, but not too bad.
> Fastforward to today. We added a new unit to our company, that now
> requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
> Now the query looks like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
> and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
> TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
> UBNR = '001'
>
> Problem? Though these tables have changed only modestly since this
> changeover (number of rows), the query takes 5 minutes to complete, and
> the CPU usage on a dual-processor server goes to 100%. I can't even
> bring up the task manager until the query completes. This wasn't a
> problem with the old procedure.
> Can someone help me figure this out and what I need to do?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
> TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
> IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
> ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
> TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
> CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
> TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
>
> It still runs and runs and takes a ton of CPU time. The Clustered
> Index s is taking 49% of the execution plan. When I run the query,
> I have to stop it immediately because it makes the CPU go to 90% and
> the system stays there.
> It didn't used to do this. I can't figure out why it does now.
>|||I am using SQL Server 2000.
I didn't design this database; I'm tapping into the database set up by
our vendors for reporting purposes. All date columns are integer type,
using the yyyymmdd format (which, actually, really works great for
sorting because you don't have to have one program say '10/26/06' is a
string and before '10/27/05').
I apologize for my ignorance on some of your questions. First, how do
I even get the DDL for the tables in question? Also, I was under the
impression that FREEPROCCACHE would only work with ad hoc SQL
procedures, and I didn't think this would qualify as it is an actual
stored procedure. Will this tie up the system for others if I do this?
Colin:
I can't figure out what you're asking in #2:
2. what tables to does is column come from? Please modify the sql that
I've
attached below to include table alias's for all the columns. It'll
help
when it comes to re-jitting the code to make it more efficient.|||Hi Brent,
what I meant to say was, what table does each column come from? It is
important to know which tables each of the columns comes from, as this will
have a major impact on how the query can be modified.
Regards
Colin Dawson
www.cjdawson.com
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147784999.293403.286010@.g10g2000cwb.googlegroups.com...
>I am using SQL Server 2000.
> I didn't design this database; I'm tapping into the database set up by
> our vendors for reporting purposes. All date columns are integer type,
> using the yyyymmdd format (which, actually, really works great for
> sorting because you don't have to have one program say '10/26/06' is a
> string and before '10/27/05').
> I apologize for my ignorance on some of your questions. First, how do
> I even get the DDL for the tables in question? Also, I was under the
> impression that FREEPROCCACHE would only work with ad hoc SQL
> procedures, and I didn't think this would qualify as it is an actual
> stored procedure. Will this tie up the system for others if I do this?
> Colin:
> I can't figure out what you're asking in #2:
> 2. what tables to does is column come from? Please modify the sql that
> I've
> attached below to include table alias's for all the columns. It'll
> help
> when it comes to re-jitting the code to make it more efficient.
>|||On 16 May 2006 06:09:59 -0700, Brent White wrote:

>I am using SQL Server 2000.
>I didn't design this database; I'm tapping into the database set up by
>our vendors for reporting purposes. All date columns are integer type,
>using the yyyymmdd format (which, actually, really works great for
>sorting because you don't have to have one program say '10/26/06' is a
>string and before '10/27/05').
Hi Brent,
But the datetime would still be a better choice. Sorts as intended as
well, but has much better vallidation and enables easy date/time
calculations.

>I apologize for my ignorance on some of your questions. First, how do
>I even get the DDL for the tables in question?
See www.aspfaq.com/5006.

> Also, I was under the
>impression that FREEPROCCACHE would only work with ad hoc SQL
>procedures, and I didn't think this would qualify as it is an actual
>stored procedure. Will this tie up the system for others if I do this?
SQL Server stores execution plans for both stored procedures and ad-hoc
queries. FREEPROCCACHE will flush them all.
It will also reduce your popularity if you do this on a busy production
system. Better move to a test or dev server before playing around with
these options.
Hugo Kornelis, SQL Server MVP|||I have not tried the FREEPROCCACHE yet, but I did take part of the
query where it's pulling from TXUYUV00 and TXMYTX00 and made a view out
of the subset of TXMYTX00, and the row retrieval time is shorter than
ever (and I mean shorter than even before the problem surfaced), so for
now I think this is going to work. We made the person who uses the
report the most very happy.
As for the datetime, that's not my choice because it was done
externally. I have a function that converts it to an actual datetime
both in SQL Server and in Crystal Reports. Generally if I use a
parameter query in SQL Server for a stored procedure, I capture the
input dates as date time and convert just the parameters to the numeric
form and filter the recordsets that way. Saves a load off the SQL
Server having to convert the date fields for every record, and I then
capture the numeric date in Crystal Reports, converting the date to the
standard date/time.