Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
p2bl
Yak Posting Veteran
54 Posts |
 Posted - 2003-09-28 : 06:45:19
|
| I have a 30m+ rows table "examScore",wit the followingDDL:CREATE TABLE [dbo].[examScore] ( [id] [int] IDENTITY (1, 1) NOT NULL , [examSequId] [int] NOT NULL , [seatId] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [studentId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [score] [numeric](18, 1) NULL , [examId] [int] NOT NULL , [remark] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]Primary key on examId and studentId,and I create a index called "IX_examScore" on column "examId" and "examSequId"now,if I run select * from examScore where examId=@n1 and examSequId=@n2 in the query analyzer,if the return 500 or more rows ,the execute plan shows SQL Server use "PK_examScore" not "IX_examScore",and it runs nearly abot 7 seconds!Then,I force it user IX_examScore with the codeselect * from examScore with (index(IX_examScore)) where examId=@n1 and examSequId=@n2,it runs only 1s,the execute plan shows what cost much is "Bookmark lookup"(about 99%),I do not wanner add a "with (index ....)" in my SQL clause,and I guess if as the table gets bigger,the "Bookmark lookup" will cost more time.Am i right?Any suggestion?========================look! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-28 : 08:12:51
|
Two things to try:1. Run UPDATE STATISTICS dbo.examScore2. See if this query works better:SELECT * FROM dbo.examScore WHERE id IN (SELECT id FROM dbo.examScore WHERE examId=@n1 and examSequId=@n2)The subquery will almost certainly use the index, but it won't avoid the bookmark lookup. Make sure you run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before you do any of these. This will flush the data and procedure caches and will give you a true sense of how long it actually takes to run. You should probably rerun your original queries the same way. The 2nd query may have overly benefited from data caching.BTW, you don't seem to have a primary key or clustered index on this table. Adding either of these may allow the optimizer to use a seek somewhere.Man, it helps to actually read the question!  |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-29 : 03:17:53
|
| I'm guessing that PK_examScore as a primary key is clustered? Maybe not a good idea with that many rows.A clustered index will take less reads to access the data page than than a non-clustered index so the server will always use it if the costs are similar.Look at the estimated query plan to see the estimated number of rows. If this is very low for the clustered index then this will be used and indicates a problem with statistics.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
p2bl
Yak Posting Veteran
54 Posts |
Posted - 2003-09-30 : 00:52:46
|
Thanks for ur reply!BOL says that it's not a good idea to create cluster index on primary key.¡¡I notice this for the first time, ,in the past I always create clustered index on primary key,maybe in this case,I shoud create the clustered index on the 'ID' column.I will try....========================look! |
|
|
|
p2bl
Yak Posting Veteran
54 Posts |
Posted - 2003-09-30 : 00:55:11
|
| sorry for some mistake.BOL says that it's not a good idea to create cluster index on primary key should be BOL says that it's not a good idea to create cluster index on too many columns========================look! |
|
|
|
|
|
|
|
|
It won't use index,and it slows down