SQL Server Performance Forum – Threads Archive

Cannot understand where is the problem

Hi Guys, I am using SQL 2005 Enterprise Edition with 8GB or RAM and Dual core DELL SERVER.
I am writing a simple query to extract some data but i cannot understand why query is slow, here is my code ***********************************************************
CREATE TABLE [dbo].[#TMP]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[E1] [char](13),
[E2] [varchar](25),
[E3] [varchar](50),
[E4] [datetime],
[E5] [int],
[E6] [int],
[E7] [int],
[E8] [Money],
[E9] [char](10),
[E10] [varchar](100),
[E11] [varchar](50),
[E12] [varchar](20),
[E13] [char](2),
[E14] [varchar](10),
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
)
INSERT INTO #TMP (E1,E2,E3,E4,E5,E6,E7,E8,E9,E11,E12,E13,E14)
SELECT E1,E2,E3,E4,E5,E6,E7,E8,E9,E11,E12,E13,E14 FROM TABLE2 —TABLE2 have only 100 record SELECT ID FROM #TMP WHERE ID BETWEEN 1 AND 40 —RESULT IS INSTANT SELECT E1,E2,E3,E4,E5,E6,E7,E8,E9,E11,E12,E13,E14 FROM #TMP WHERE ID BETWEEN 1 AND 40 —RESULT TAKING 4-5 SECONDS DROP TABLE #TMP
*********************************************************** Is there thing missing?
Please assist. ________________________________________________
Be great in act, as you have been in thought.

---

When you select the ID column only, SQL Server can read all the data it needs from the Primary Key index. When you select other columns, SQL Server has to read data from the table as well, which will take more time than reading from an index. Other than that, the server may be busy doing other things. When you repeat the two queries, you will notice that especially the second query will finish quicker. This is because the data from the table will have been cached after the first run, and does not need to be read from disk.

---

Adriaan, thanks for your quick reply.
Server is idle no process is running, I/O queue is almost zero % utlilize. It is wired, that if i use
SELECT ID,E1 * FROM #TMP result is still fast but as i keep adding E2,E3… speed to retrieve records seems to be slower and slower. Is there any thing like indexing or Statistics i can define to fasten speed while using more columns? ________________________________________________
Be great in act, as you have been in thought.

---

No, it just take time to read the data from the columns. You have a dual-core processor, so you might try to add the following phrase at the very end of your query: OPTION (MAXDOP 1) This suppresses one of SQL Server’s longstanding bugs: on a multi-processor computer, it may use too many processors to execute a simple query.

---

Adriaan, Thanks i will try to use this Clause tomorrow.
One more question, When i execute query it return result immediately (with 1 or more columns) but keep on executing and finish job like in 3-5 seconds (although all records were returned in <1 sec). Any idea what process is doing even after returning all records? ________________________________________________
Be great in act, as you have been in thought.

---

Setup a trace in Profiler to see the exact activity. As you are using a temp table, I guess this might be activity to drop the temp table from tempdb, and after that perhaps also the Auto-Shrink for tempdb (one of the options that you should unmark on any database).

---

one thought is that you could do a covering index which means you create a non-clustered index that uses every field of output that way it just has to go to the index rather than the actual table. Your table is not too wide so this should work. Cheers! Michael B
Sr. DBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

---

the above table has a clustered index, so the arguments re index is not valid run
SET STATISTICS TIME ON
before the 2 queries if the cpu is not high, then think the problem is on the client side, not the server side

---

The primary key is the clustered index in this case so it is reading the data rows anyway. I don’t understand why it would be slow though. Hank

---

Think about that for a minute. Create an index, in this case, that includes all the columns in the table. Essentially duplicate the table, not sure how that would improve performance. Hank

---

It is clear that query including bookmark lookups on top of reading index is slower but 4-5 seconds for reading 100 rows table is still too slow. It can’t be explained by bookmark lookups.

---

– what volume (mb) does this query produce ? 100 rows is how many MB ?
– are there lobs int (varchar(max) or oldstyle ?)
– what’s the space usage of your starting table ? (npages)[?]

---

]]>