Hi All,
I have a table that currently contains approx. 8 million records.
I'm running a SELECT query against this table that in some
circumstances is either very quick (ie results returned in Query
Analyzer almost instantaneously ), or very slow (ie 30 to 40 seconds to
return results), and I'm trying to work out how I improve performance.
Essentially the query I'm running is nothing more complex than:
SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id
[tier] is a smallint column with a non-clustered, non-unique index on
it. [member_id] is a numeric column with a clustered, unique index on
it.
When I supply a [tier] value of 1, it returns results instantaneously .
I have no idea if this is meaningful, but the tier = 1 records were
loaded first into the table, and comprise approximately 5 million
records.
When I supply a [tier] value of 2, the results take 30 to 40 seconds.
tier =2 records were loaded second, and comprise approximately 3
million records.
I've tried running an execution plan, and while I'm no expert, it
appears to me that the index on tier isn't being used, even if I use:
tier = CAST(2 as SMALLINT)
I'm wondering if anyone can give me ANY advice on how to get any
better performance out of this SELECT statement?
Also, out of curiosity, can a disk defragment have a positive impact
on SELECT query performance?
Any help very much appreciated!
Much warmth,
Murray 4 10435
On Mon, 23 Feb 2004 16:30:39 GMT, M Wells
<pl**********@p lanetthoughtful .org> wrote: I'm wondering if anyone can give me ANY advice on how to get any better performance out of this SELECT statement?
Also, out of curiosity, can a disk defragment have a positive impact on SELECT query performance?
Also, additional to the above post, have there been any SELECT
performance improvements realised with the Service Packs? I've just
noticed that this server is returning a SELECT @@VERSION of 8.00.194,
which, as I understand it, means that it currently has no service
packs installed...
I'm wondering if updating to Service Pack 3a (which I know is a good
idea anyway), might improve the performance of this query?
Much warmth,
Murray
M Wells (pl**********@p lanetthoughtful .org) writes: Essentially the query I'm running is nothing more complex than:
SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id
[tier] is a smallint column with a non-clustered, non-unique index on it. [member_id] is a numeric column with a clustered, unique index on it.
When I supply a [tier] value of 1, it returns results instantaneously . I have no idea if this is meaningful, but the tier = 1 records were loaded first into the table, and comprise approximately 5 million records.
When I supply a [tier] value of 2, the results take 30 to 40 seconds. tier =2 records were loaded second, and comprise approximately 3 million records.
I've tried running an execution plan, and while I'm no expert, it appears to me that the index on tier isn't being used, even if I use:
This is a little funny. In most situations that index on tier would
be meaningless, but the TOP 1 should change everything.
The reason the index is meaningless is because for each hit in tier, SQL
Server needs to access the data page, in what is called a bookmark lookup,
and many bookmark lookups can easily be more expensive than scanning
the table.
However, since SQL Server users the clustered key as the row pointer in
a nonclustered index, SQL Server should be able to find that single row
by seeking the index on tier and then perform a single lookup.
So, indeed, upgrade to SP3, and see if it resolves the issue. No
guarantees that it will.
If it doesn't - it would be interesting to have a repro to give to
Microsoft, but producing a repro with eight million rows could mean
some problems.
This could be an alternative way of writing the query:
SELECT t.*
FROM tbl t
WHERE t.memberid = (SELECT MIN(t2.memberid )
FROM tbl t2
WHERE t2.tier = n)
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
"M Wells" <pl**********@p lanetthoughtful .org> wrote in message
news:j0******** *************** *********@4ax.c om... On Mon, 23 Feb 2004 16:30:39 GMT, M Wells <pl**********@p lanetthoughtful .org> wrote:
I'm wondering if anyone can give me ANY advice on how to get any better performance out of this SELECT statement?
Also, out of curiosity, can a disk defragment have a positive impact on SELECT query performance? Also, additional to the above post, have there been any SELECT performance improvements realised with the Service Packs? I've just noticed that this server is returning a SELECT @@VERSION of 8.00.194, which, as I understand it, means that it currently has no service packs installed...
The optimizer does get updated from time to time, so it's possible. I'm wondering if updating to Service Pack 3a (which I know is a good idea anyway), might improve the performance of this query?
Regardless, there are enough security issues and the like fixed with the
latest SP, that you should update to 3a.
Much warmth,
Murray
The nonclustered index on [tier] internally store the clustered key
[member_id]as part of index key, meaning with the same [tier] value the rows
are sorted on [member_id] already in the index. Thus the plan should have
been just a simple scan on the nonclustered index. I tried to create
table/index with the same schema and verified on SP3 that is the case.
The fact that different [tier] values caused such a big performance
difference indicate there might be some physical locality issue with part of
the index. You can try to defrag the index to see whether that helps to
make the behavior consistent.
Last, you can use the following workaround to force the plan to use the
nonclustered index, assuming the index id for the nonclustered index is 2:
SELECT TOP 1 * FROM Table1 with (index=2) WHERE tier=n ORDER BY member_id
--
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"M Wells" <pl**********@p lanetthoughtful .org> wrote in message
news:dm******** *************** *********@4ax.c om... Hi All,
I have a table that currently contains approx. 8 million records.
I'm running a SELECT query against this table that in some circumstances is either very quick (ie results returned in Query Analyzer almost instantaneously ), or very slow (ie 30 to 40 seconds to return results), and I'm trying to work out how I improve performance.
Essentially the query I'm running is nothing more complex than:
SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id
[tier] is a smallint column with a non-clustered, non-unique index on it. [member_id] is a numeric column with a clustered, unique index on it.
When I supply a [tier] value of 1, it returns results instantaneously . I have no idea if this is meaningful, but the tier = 1 records were loaded first into the table, and comprise approximately 5 million records.
When I supply a [tier] value of 2, the results take 30 to 40 seconds. tier =2 records were loaded second, and comprise approximately 3 million records.
I've tried running an execution plan, and while I'm no expert, it appears to me that the index on tier isn't being used, even if I use:
tier = CAST(2 as SMALLINT)
I'm wondering if anyone can give me ANY advice on how to get any better performance out of this SELECT statement?
Also, out of curiosity, can a disk defragment have a positive impact on SELECT query performance?
Any help very much appreciated!
Much warmth,
Murray This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Benoit Le Goff |
last post by:
Hello.
I test some query on sql server 2000 (sp2 on OS windows 2000) and i
want to know why a simple query like this :
select * from Table Where Column like '%value'
is more slow on 2000 than on sql 7.
And this case arrive only if the % character is in the begin.
If you test this :
select * from Table Where Column like 'v%alue'
|
by: Dan Berlin |
last post by:
I have two tables:
T1 : Key as bigint, Data as char(20) - size: 61M records
T2 : Key as bigint, Data as char(20) - size: 5M records
T2 is the smaller, with 5 million records.
They both have clustered indexes on Key.
I want to do:
|
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: Thomi Baechler |
last post by:
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
|
by: jim.clifford |
last post by:
Hello.
I have a slow response with a system that I am setting up. The OS is
Win 2000 Server with SQL Server 2000. My first execution of the SQL
procedure is slow (about 40 seconds), while the second execution is
faster (approx. 3 seconds and what I expect).
Using query analyser I can leave the window up and come back ( after
say 10 minutes ) and my next response is will be slow (and faster
straight after this).
The query itself is...
| |
by: HC |
last post by:
I posted this in one of the VB forums but I'm starting to think it
might be more appropriate to have it here, since it really seems to be
a SQL server (MSDE/Express 2005) problem:
Hey, all, I have a problem with queries against a SQL server instance
that I just found and is causing me fits. I hope someone can point
me
in the right direction, please. TIA.
Basically, I got a Vista OS machine to test my VB6 app on it as some
|
by: pramodbura |
last post by:
We have a quick query regarding SQL performance.
We have SQL Server 2000 (32 Bit) and SQL Server 2005 (64 Bit) as two
separate instances on a DB Server.
We were analysing the execution times for the same stored procedure on
both instances:
1. Through Remote Desktop of the actual DB server
2. Through Query Analyser of my local machine.
The results were as follows:
|
by: Peter Nurse |
last post by:
Two (almost) identical SQL Server databases (DB1 backed up and
restored to DB2 yesterday). DB2.dbo.GetSchPaymentsTD took 1.5 seconds
(!) to execute DB1.dbo.GetSchPaymentsTD took less than a millisecond
with identical code and data.
I'm guessing this is some sort of indexing issue and the code is below
but I'm not sure it's relevant because . . .
.. . . I dropped DB2.dbo.GetSchPaymentsTD and then recreated it (with
identical code)...
|
by: Marc Baker |
last post by:
Bear with me here folks, I don't know much MS SQL performance.
Wondering if someone can point me in the right direction. I have 1
particular database where queries are slow. To test this theory, I am
running a SELECT COUNT(*) on some of the tables that have many rows
(2-4 million rows). Example: A table with approx 3.5 million rows, a
count takes over 10 mins. to run initially. If I just do a count on
the indexed field, SELECT...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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
| | |