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.EMPK EY , dbo.HRMABZ.CONN UMB
, dbo.HRM_CALENDE R.Datum, dbo.HRMABZ.ABZT XT
FROM dbo.HRM_CALENDE R INNER JOIN dbo.HRMABZ
ON dbo.HRM_CALENDE R.Datum >= dbo.HRMABZ.ABZD ATF
AND dbo.HRM_CALENDE R.Datum <= dbo.HRMABZ.ABZD ATT
WHERE (dbo.HRMABZ.ABZ TECH = 'N')
AND (dbo.HRMABZ.ABZ LDLT = 'N')
AND (dbo.HRM_CALEND ER.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 7 3627
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.EMPK EY , dbo.HRMABZ.CONN UMB , dbo.HRM_CALENDE R.Datum, dbo.HRMABZ.ABZT XT
FROM dbo.HRM_CALENDE R INNER JOIN dbo.HRMABZ ON dbo.HRM_CALENDE R.Datum >= dbo.HRMABZ.ABZD ATF AND dbo.HRM_CALENDE R.Datum <= dbo.HRMABZ.ABZD ATT
WHERE (dbo.HRMABZ.ABZ TECH = 'N') AND (dbo.HRMABZ.ABZ LDLT = 'N') AND (dbo.HRM_CALEND ER.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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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?
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_PROF ILE 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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Hello
I run the UPDATE...STATIS TICS 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__524 42E1F]),
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(Gat her 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__524 42E1F]),
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_PROF ILE 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.
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(Gat her 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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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(Gat her 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ray Gurganus |
last post by:
I'm running MySQL 4.0.16 on Windows 2003. I just added the mysqld-nt
command line option to enable the slow query log, started MySQL, and the
option showed up as turned on. Then later I restarted the server, and the
slow query log option went back to being turned off. Is this a Windows
problem in not remembering the service parameter? Has anyone else seen
this?
|
by: Gary Wales |
last post by:
We have two main query types running against a table of some 2 million
rows and have gotten query response down to well under a second by
using the right indexes.
Problem is that we are running an advertising campaign that brings a
concentrated block of users to the site. When this happens one of the
queries which relies on a particluar index comes severely of the rails
and can take up to 2 minutes filling the slow query log for 15 to 20...
|
by: Thomas Bartkus |
last post by:
Is it possible that the elapsed time being measured includes waiting for the
client to acknowledge that it has received all the data? In *addition* to
the server execution time?
Documentation seems to *imply* that the slow query log only looks at server
execution time. But, it doesn't acknowledge this directly and there seems
to be a distinct connection between slow network pipes and slow queries.
For example - even the simplest...
|
by: Stefan |
last post by:
The following query gives me the right output, but takes almost 10
minutes to run...
Any obvious things that I can improve on? I know this is diffuclt to
do without realizign what I'm trying to do... I am not sure how to
make it run faster.
Thanks.
select * from tblCobol As C
where VersionNum =
|
by: zdk |
last post by:
I have table name "actionlog",and one field in there is "date_time"
date_time (Type:datetime) example value : 11/1/2006 11:05:07
if I'd like to query date between 24/07/2006 to 26/07/2006(I don't
need time),how to write SQL command?
select * from actionlog where date_time 24/07/2006 AND date_time <
24/07/2006 ??
| |
by: Dave Hammond |
last post by:
Hi All,
I'm trying to use the slow-query-log (with
--log-queries-not-using-indexes enabled) to determine if any queries
need optimization, and have a few questions about some entries I'm
seeing.
First question: given a table defined as:
CREATE TABLE `oa_location` (
|
by: sbowman |
last post by:
I have a database that I was using for Monthly Statistics. I've
recently loaded hundreds of thousands of records of historical data.
Now my queries are running really slow!! Is there anything I can do??
Thanks!
Shelley
|
by: wizofaus |
last post by:
I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.
Now I've hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It's a very simple query of the form:
SELECT Min(MyValue)...
|
by: xoinki |
last post by:
hi all,
I wanted to know few general details about MySQL slow query log.
My slow log has a statement like
# User@Host: root @ localhost
here, what does this "" signify? why is this needed?
I could not find any documentation expaining slow query log format.
Regards,
|
by: phill86 |
last post by:
Hi
I am re-posting this thread because it has become very confusing and I have got some way to solving the problem so it is a slightly different question from the initial thread.
here is the original tread
http://bytes.com/topic/access/answers/872005-query-date-range
Just to clarify what I am trying to achieve....
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |