Query works then later timeouts

We have a query which joins 3 tables where all 3 of the tables have less tha
n 300 rows each.
The query is fine when there is less than 50 users but when it gets over 50
it runs until it timeouts. During this time while it is running and not ret
urning I can do selects on each individual table but if I run the query with
the joins it never returnsWhat is the waittype for that spid during the slow times?
Andrew J. Kelly
SQL Server MVP
"Darin Browne" <anonymous@.discussions.microsoft.com> wrote in message
news:00EDE2AB-4CE0-4014-A0F6-18552B409DB1@.microsoft.com...
> We have a query which joins 3 tables where all 3 of the tables have less
than 300 rows each.
> The query is fine when there is less than 50 users but when it gets over
50 it runs until it timeouts. During this time while it is running and not
returning I can do selects on each individual table but if I run the query
with the joins it never returns.
> We've checked locks, if anything is blocking it, indexes used and
everything is fine during the never ending select.
> Any suggestions on what we could do? We're stumped.
> Thanks.
>

Query with Joins problem

Hello

Let me explain the problem I am having:
I have two tables, data_t and a_data_t
a_data_t is the archive table of data_t

The two tables are exactly the same.

In the table values are stored:
Value (A numeric value)
Code (A text code to identify a report with data)
Line (The line number)
Col (The Col Number)
EDate (The date of entry)
Grp (A number of a group the data belongs to)

I want to get the value from data_t minus the value from a_data_t with
the same Code, Line and Col but with a different EDate (To view the
variance).

Here is my statement:

select d1.line, d1.col, (IsNull(d1.value,0) - IsNull(d2.value,0)) as
value from data_t d1
full outer join a_data_t d2 on d1.Code = d2.Code and d2.line = d2.line
and d1.col = d2.col
where
d1.Code = 'XC001' and d1.line between 1 and 20 and d1.grp = 26
and d1.EDate = '2006/06' and d2.grp = 26 and d2.EDate = '2006/05'
order by d1.line, d1.col

It works fine EXCEPT when there is a value in either of the tables that
isn't in the other one, then a value is not given.

Example:
data_t doens't have a value for line=1 and col=2 and grp=26 and Code =
'XC001' and EDate = '2006/06'
a_data_t has the value of 50000 for the same details (Except Edate of
'2006/5')
Instead of returning -50000 it doesn't return anything.

I hope I could explain it correctly.
Any help will be greatly appreciated.

Thanks.(wilhelm.kleu@.gmail.com) writes:
> Here is my statement:
> select d1.line, d1.col, (IsNull(d1.value,0) - IsNull(d2.value,0)) as
> value from data_t d1
> full outer join a_data_t d2 on d1.Code = d2.Code and d2.line = d2.line
> and d1.col = d2.col
> where
> d1.Code = 'XC001' and d1.line between 1 and 20 and d1.grp = 26
> and d1.EDate = '2006/06' and d2.grp = 26 and d2.EDate = '2006/05'
> order by d1.line, d1.col
> It works fine EXCEPT when there is a value in either of the tables that
> isn't in the other one, then a value is not given.

This is because thw WHERE clause nullifiles the benefit of the full
join. The full join operation bulids a table which consists of the
union of all rows in both tables, and when a row in one table does
not have a match in the other, all columns for that other table are
NULL.

Then you add a WHERE condition where you filter away all NULL values,
so you only get rows that are in both tables.

Try replacing WHERE with AND and see what happens. I'm not sure this
will give the desired result, but without knowledge of the keys it's
a bit difficult to say what you are looking for.

A standard suggestion for this sort of questions is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it easy to copy and paste and develop a tested solution.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David
|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
|||Thank you so much
Ida

>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and
>
>.
>
|||Thank you.

>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>

query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida|||Thank you so much
Ida

>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
> news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>|||Thank you.

>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
> news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and[vbcol=seagreen]
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>

query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida|||Thank you so much
Ida
>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
>> Can someone please re-write this query for me using
better
>> joins? Thank you very much.
>> select distinct(c.research), t.FAX, t.NAME, CAST
>> (exp_AUDITDATE AS varchar(11))
>> AS AUDITDATE from teachers t (nolock), cases c
(nolock),
>> RecordLog l (nolock)
>> where l.postedDate > 120703 and
>> l.postedDate < GETDATE() and l.seqno = c.sequence_no
and
>> l.PostedDate = c.PostedDate
>> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
>> exp_auditdate is null)
>> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>>
>> Ida
>
>.
>|||Thank you.
>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
>> Can someone please re-write this query for me using
better
>> joins? Thank you very much.
>> select distinct(c.research), t.FAX, t.NAME, CAST
>> (exp_AUDITDATE AS varchar(11))
>> AS AUDITDATE from teachers t (nolock), cases c
(nolock),
>> RecordLog l (nolock)
>> where l.postedDate > 120703 and
>> l.postedDate < GETDATE() and l.seqno = c.sequence_no
and
>> l.PostedDate = c.PostedDate
>> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
>> exp_auditdate is null)
>> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>>
>> Ida
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>

Query with a lot of left joins - Rewrite?

Hello,

I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?

How would I rewrite left joins? Any examples?

Thanks.

Moving to T-SQL inside SQL Server, where you are most likely to find experts to answer this question.|||

When you have that many referenced tables/joins, you will get a perf hit, espcially if you have large tables involved. The only trick in the book that helps is to filter large tables down to smaller #temp tables. Then use these #temp tables for your join. You will reduce the log need to process your large tables.

e.g.

Code Snippet

select *

into #tmp1

from large_table1

where filter=123

select *

into #tmp2

from large_table2

where filter=xyz

select *

from #tmp1 left join #tmp2 on ...

|||

Before applying any kind of performance tuning to your query, first answer this question. Is there any performance issue when testing it? There is nothing particularly slow about a query with 11 left joins, if your data is structured correctly and (very important) indexex correctly.

The kinds of things you can "guess" and do (like breaking down into temp tables, as OJ states) are excellent strategies if the optimizer fails you, but 9 times out of 10, even for really complex queries, SQL Server will do whatever you are trying to do to improve performance of the joins on their own.

If you don't have a test environment with adeqate amounts of data to do performance testing, this process is a heck of a lot more work, but as a rule, I don't like to do any performance guessing, do performance testing/reactive tuning BEFORE you get to your production environment if at all possible.

Query with a lot of left joins - Rewrite?

Hello,

I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?

How would I rewrite left joins? Any examples?

Thanks.

Moving to T-SQL inside SQL Server, where you are most likely to find experts to answer this question.|||

When you have that many referenced tables/joins, you will get a perf hit, espcially if you have large tables involved. The only trick in the book that helps is to filter large tables down to smaller #temp tables. Then use these #temp tables for your join. You will reduce the log need to process your large tables.

e.g.

Code Snippet

select *

into #tmp1

from large_table1

where filter=123

select *

into #tmp2

from large_table2

where filter=xyz

select *

from #tmp1 left join #tmp2 on ...

|||

Before applying any kind of performance tuning to your query, first answer this question. Is there any performance issue when testing it? There is nothing particularly slow about a query with 11 left joins, if your data is structured correctly and (very important) indexex correctly.

The kinds of things you can "guess" and do (like breaking down into temp tables, as OJ states) are excellent strategies if the optimizer fails you, but 9 times out of 10, even for really complex queries, SQL Server will do whatever you are trying to do to improve performance of the joins on their own.

If you don't have a test environment with adeqate amounts of data to do performance testing, this process is a heck of a lot more work, but as a rule, I don't like to do any performance guessing, do performance testing/reactive tuning BEFORE you get to your production environment if at all possible.

Query VS Stored Procedure problem

Hello.

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

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

Thanks in advance.

Raza:

Please provide a listing of your stored procedure.

Dave

|||

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

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

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

here is the query.

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

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

Thanks

|||

Some suggestions to identify the problem:

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

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

Thanks.

|||

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

So the procedure is:

create procedure procName
as

<your query>

go

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

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

Try this:

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

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

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

Can you explain what is your requierment on date field

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

Tom Phillips wrote:

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

Try this:

WHERE (sim.TRANSACTION_STAMP BETWEEN CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) AS DATETIME) AND CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) + ' 23:59:59') AS DATETIME) AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Tom,

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

USE Northwind
GO

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

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

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

RazaRana wrote:

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

...

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

Thanks

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

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

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

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

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

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

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

Thanx

Query too slow! Need some performance enhancing tips!

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

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

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

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

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

Hi Gavin,

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

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

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

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

Best, Hugo
--

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

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

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

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

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

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

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

Tony

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

Query takes more time from user interface or in a network

I execute a pretty big sql query which joins multiple tables and I reviewed indexes on all these tables. I am happy with the result when I run the query using SSMS in the server locally i.e., where my SQL Server database is installed. It takes 4 seconds to get around 17000 records. If I run the same query in a network or from my desktop using SSMS i.e., i connect to the above mentioned SQL Server using SSMS, it takes more than 60 seconds. Not sure how to solve this. If someone could help me, it will be of great help.

Thanks.

Is the server local to you or installed in another office?

When you run the query on your machine enable the Client Statistics (second row of buttons at the top, next to the Actual Execution Plan button). What is the output from this?