469,926 Members | 1,526 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,926 developers. It's quick & easy.

Slow Query with Date

Hello Everybody

I run the following query against to identical databases. Execution
time on the first DB is 0 seconds, on the other 6 seconds!

SELECT dbo.HRMABZ.EMPKEY , dbo.HRMABZ.CONNUMB
, dbo.HRM_CALENDER.Datum, dbo.HRMABZ.ABZTXT

FROM dbo.HRM_CALENDER INNER JOIN dbo.HRMABZ
ON dbo.HRM_CALENDER.Datum >= dbo.HRMABZ.ABZDATF
AND dbo.HRM_CALENDER.Datum <= dbo.HRMABZ.ABZDATT

WHERE (dbo.HRMABZ.ABZTECH = 'N')
AND (dbo.HRMABZ.ABZLDLT = 'N')
AND (dbo.HRM_CALENDER.Valid = 1)

I tried to analyze to query execution in the execution plan and found
two different arguments in a 'nested loop / inner join' which I dont
understand. The nested loop does consume most of the time:

On the fast Server the argument says:
'Outer References:([HRMABZ].[ABZDATT],[HRMABZ].[ABZDATF])'

On the slow server the argument says:
'WHERE: ([HRM_Calender].[Datum]>=[HRMABZ].[ABZDATF] AND
[HRM_Calender].[]<=[HRMABZ].[ABZDATT])'

Additional information for the two databases:
- Slow database runs on a SQL2000 SP3a English, W2K SP3
- Fast database runs on a SQL2000 SP3a German, W2k SP3

Can somebody explain me the difference between the two execution plans
and how I could force the slow database acting the same way as the
fast one does?

Thank you
Thomi
Jul 23 '05 #1
7 3412
Thomi Baechler (th***************@azul.ch) writes:
I run the following query against to identical databases. Execution
time on the first DB is 0 seconds, on the other 6 seconds!
So what you mean with identical? Do they have exactly the same data? Or
do they have the same schemas but different data?
SELECT dbo.HRMABZ.EMPKEY , dbo.HRMABZ.CONNUMB
, dbo.HRM_CALENDER.Datum, dbo.HRMABZ.ABZTXT

FROM dbo.HRM_CALENDER INNER JOIN dbo.HRMABZ
ON dbo.HRM_CALENDER.Datum >= dbo.HRMABZ.ABZDATF
AND dbo.HRM_CALENDER.Datum <= dbo.HRMABZ.ABZDATT

WHERE (dbo.HRMABZ.ABZTECH = 'N')
AND (dbo.HRMABZ.ABZLDLT = 'N')
AND (dbo.HRM_CALENDER.Valid = 1)

I tried to analyze to query execution in the execution plan and found
two different arguments in a 'nested loop / inner join' which I dont
understand. The nested loop does consume most of the time:

On the fast Server the argument says:
'Outer References:([HRMABZ].[ABZDATT],[HRMABZ].[ABZDATF])'

On the slow server the argument says:
'WHERE: ([HRM_Calender].[Datum]>=[HRMABZ].[ABZDATF] AND
[HRM_Calender].[]<=[HRMABZ].[ABZDATT])'


Well, the quey is a join between two tables, and SQL Server could
start with any of the two using the filters in the WHERE clause
for an initial filter, and then look up a row in the other table.
In the two different query plans, is the same table being subject
to a clustered index scan?

Could you post the complete query plans?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Thomi Baechler (th***************@azul.ch) writes:
I tried to analyze to query execution in the execution plan and found
two different arguments in a 'nested loop / inner join' which I dont
understand. The nested loop does consume most of the time:

On the fast Server the argument says:
'Outer References:([HRMABZ].[ABZDATT],[HRMABZ].[ABZDATF])'

On the slow server the argument says:
'WHERE: ([HRM_Calender].[Datum]>=[HRMABZ].[ABZDATF] AND
[HRM_Calender].[]<=[HRMABZ].[ABZDATT])'


Also, information about the number of rows in each table would help.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
Hi Erland

Thank you for your fast answer!

Identical means: Full backup of the database on our clients server and
restore on our testserver -> exact the same data in both databases

Complete query plans: sure, I can post the complete plan. I am an absolute
newbie. Can your tell me how to do that?

Number of rows: HRMABZ has about 4000 rows and HRM_CALENDER about 3800 rows

As far as I know is the only differnce between the two DBs/Servers the
language. I have to join dates (datetime) and my guess would be that the
english server has to do a language conversion of the date which leads to .
Could that be possible?

Thank you very much!
Thomi


So what you mean with identical? Do they have exactly the same data? Or
do they have the same schemas but different data?

Well, the quey is a join between two tables, and SQL Server could
start with any of the two using the filters in the WHERE clause
for an initial filter, and then look up a row in the other table.
In the two different query plans, is the same table being subject
to a clustered index scan?

Could you post the complete query plans?

Jul 23 '05 #4
Thomi Bächler (th***************@azul.ch) writes:
Identical means: Full backup of the database on our clients server and
restore on our testserver -> exact the same data in both databases
Hm, I wonder if the statistics are the same? Run an UPDATE STATISTICS
WITH FULLSCAN on bnth tables on both servers.
Complete query plans: sure, I can post the complete plan. I am an absolute
newbie. Can your tell me how to do that?
For a simple query, you issue this command before you issue the query:

SET SHOWPLAN_TEXT ON

This command is also an implicit SET NOEXEC ON, so the query will not be
executed.

You can also use SET STATISTICS_PROFILE ON. In this case the query will
be executed.
Number of rows: HRMABZ has about 4000 rows and HRM_CALENDER about 3800
rows
Ah, that appears it would make it a toss up which plan to use.
As far as I know is the only differnce between the two DBs/Servers the
language. I have to join dates (datetime) and my guess would be that the
english server has to do a language conversion of the date which leads
to . Could that be possible?


Not very likely. If all date columns are datetime, there is no conversion
to make, since datetime is a binary value. If some column is varchar, there
is always a conversion, no matter the language of the server. And what
matters more than the server language is the collations, but they are
obviously the same, as you copied the database with BACKUP/RESTORE.

But the hardware configuration of the machines could matter, for instance
the number of available processors.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
Hello

I run the UPDATE...STATISTICS on both tables, on both servers. Nothing
changed!

Execution plan is as follows:

Fast Server (german, SQL2000 SP3a, single Pentium, 768MB RAM)
-------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([HRMABZ].[ABZDATT],
[HRMABZ].[ABZDATF]))
|--Sort(ORDER BY:([HRMABZ].[ABZDATT] ASC, [HRMABZ].[ABZDATF] ASC))
| |--Clustered Index
Scan(OBJECT:([XHRMDTA036].[dbo].[HRMABZ].[PK__HRMABZ__52442E1F]),
WHERE:([HRMABZ].[ABZTECH]='N' AND [HRMABZ].[ABZLDLT]='N'))
|--Table Spool
|--Index Spool(SEEK:([HRM_CALENDER].[Datum] >=
[HRMABZ].[ABZDATF] AND [HRM_CALENDER].[Datum] <= [HRMABZ].[ABZDATT]))
|--Table Scan(OBJECT:([XHRMDTA036].[dbo].[HRM_CALENDER]),
WHERE:([HRM_CALENDER].[Valid]=1))
Slow Server (english, SQL2000 SP3a, dual Pentium, 1G RAM)
---------------------------------------------------------
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join,
WHERE:([HRM_CALENDER].[Datum]>=[HRMABZ].[ABZDATF] AND
[HRM_CALENDER].[Datum]<=[HRMABZ].[ABZDATT]))
|--Clustered Index
Scan(OBJECT:([XHRMDTA036].[dbo].[HRMABZ].[PK__HRMABZ__52442E1F]),
WHERE:([HRMABZ].[ABZTECH]='N' AND [HRMABZ].[ABZLDLT]='N'))
|--Table Spool
|--Table Scan(OBJECT:([XHRMDTA036].[dbo].[HRM_CALENDER]),
WHERE:([HRM_CALENDER].[Valid]=1))

To be honest, I dont understand the details in the execution plan. I would
appreciate any help!

Any ideas?
Thank you
Thomi
Am Wed, 8 Dec 2004 20:44:16 +0000 (UTC) schrieb Erland Sommarskog:
Thomi Bächler (th***************@azul.ch) writes:
Identical means: Full backup of the database on our clients server and
restore on our testserver -> exact the same data in both databases


Hm, I wonder if the statistics are the same? Run an UPDATE STATISTICS
WITH FULLSCAN on bnth tables on both servers.
Complete query plans: sure, I can post the complete plan. I am an absolute
newbie. Can your tell me how to do that?


For a simple query, you issue this command before you issue the query:

SET SHOWPLAN_TEXT ON

This command is also an implicit SET NOEXEC ON, so the query will not be
executed.

You can also use SET STATISTICS_PROFILE ON. In this case the query will
be executed.
Number of rows: HRMABZ has about 4000 rows and HRM_CALENDER about 3800
rows


Ah, that appears it would make it a toss up which plan to use.
As far as I know is the only differnce between the two DBs/Servers the
language. I have to join dates (datetime) and my guess would be that the
english server has to do a language conversion of the date which leads
to . Could that be possible?


Not very likely. If all date columns are datetime, there is no conversion
to make, since datetime is a binary value. If some column is varchar, there
is always a conversion, no matter the language of the server. And what
matters more than the server language is the collations, but they are
obviously the same, as you copied the database with BACKUP/RESTORE.

But the hardware configuration of the machines could matter, for instance
the number of available processors.

Jul 23 '05 #6
Thomi Bächler (th***************@azul.ch) writes:
Execution plan is as follows:

Fast Server (german, SQL2000 SP3a, single Pentium, 768MB RAM)
-------------------------------------------------------------

Slow Server (english, SQL2000 SP3a, dual Pentium, 1G RAM)
---------------------------------------------------------
|--Parallelism(Gather Streams)


So there one was one more differences between the machines you didn't
tell us about! To wit, the number of CPUs.

Parallelism can be a great thing for a really wild query. But unfortunately,
the optimizer appears to be overly optimistic about the wonders of
parallelism, and use parallel plans when it shouldn't.

Add this at the end of the query:

OPTION (MAXDOP 1)

MAXDOP = Max Degree of Parallelism

This option forces a non-parallel plan. Sometimes a non-parallel plan
may be slower than the parallel plan, but this option may still be useful.
If you have an 8-way box, you may not want that wild query to monopolize
the entire server, but if 2-3 CPUs are working with it that may be OK.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7
Hi Erland

That did the trick! The query runs now with the same speed on both DBs. We
changed to: Properties -> Processor -> Parallelism -> Use 1 processor. We
think in our environment that might be best anyway.

Thank you for your help!
Thomi

Thomi Bächler (th***************@azul.ch) writes:
Execution plan is as follows:

Fast Server (german, SQL2000 SP3a, single Pentium, 768MB RAM)
-------------------------------------------------------------

Slow Server (english, SQL2000 SP3a, dual Pentium, 1G RAM)
---------------------------------------------------------
|--Parallelism(Gather Streams)


So there one was one more differences between the machines you didn't
tell us about! To wit, the number of CPUs.

Parallelism can be a great thing for a really wild query. But unfortunately,
the optimizer appears to be overly optimistic about the wonders of
parallelism, and use parallel plans when it shouldn't.

Add this at the end of the query:

OPTION (MAXDOP 1)

MAXDOP = Max Degree of Parallelism

This option forces a non-parallel plan. Sometimes a non-parallel plan
may be slower than the parallel plan, but this option may still be useful.
If you have an 8-way box, you may not want that wild query to monopolize
the entire server, but if 2-3 CPUs are working with it that may be OK.

Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Ray Gurganus | last post: by
1 post views Thread by Gary Wales | last post: by
1 post views Thread by Thomas Bartkus | last post: by
5 posts views Thread by Stefan | last post: by
2 posts views Thread by zdk | last post: by
reply views Thread by Dave Hammond | last post: by
3 posts views Thread by sbowman | last post: by
29 posts views Thread by wizofaus | last post: by
3 posts views Thread by xoinki | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.