SQL Server Performance Forum – Threads Archive

Select Performance Issue

I have got a simple table with four columns (PK,FieldName,FieldValue,DocumentID)of types (bigint,varchar,varchar,bigint). The table holds around 300 000 records and if I perform a simple select query like "Select * from table where DocumentID= 15" it takes sql2k around 1.5 seconds to return the result. I find this performance rather slow than fast and tried indexing, which didn’t improve the performance. Is there anything else I can do ? Any help is very much appreciated Cheers
Rolf

---

Get rid of your * and replace it with the column names. This sentence doesn’t make any sense at all: I find this performance rather slow than fast and tried indexing, which didn’t improve the performance. The only index that would make a difference is if you have one on DocumentID. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Why do you think the query is slow? Nathan H.O.
Moderator
SQL-Server-Performance.com

---

How many milliseconds are you trying to shave off that 1.5 seconds?

---

I was hoping to be able to perfom this query in half of that time ( 800ms). I indexed the document DocumentID column as well as modified the query so that it only returns the columns I need which improved the query by 100 ms.

---

Is the index on DocumentID a clustered index? It could help speed up the query if the DocumentID is not unique and thus return multiple rows. You could also try and create a covering index (one that includes all columns you return and not only the DocumentID) but such an index could take up quite a lot of space if it involves varchar. What kind of hardware is on the server you perform this query on (cpu, ram, raid disk setup)? What is the cpu load and memory usage on the server and are are any other applications running?

---

You could pin the table in memory. lol Probably not the best idea though. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Thanks for all you suggestions. I might give the clustered index a go. My box has got 2 CPU’s 2.4 Xeon, I Gig of Ram and 3 Scasi harddrives. I have got Sql2k, Jboss and Tomcat running on it. The cpu load never exceeds more than 50%

---

One other thing I think that would give your your performance increase. If you only have 300k rows, you are a LONG ways away from needing a BIGINT. Change those fields to INT, rebuild the indexes, and make sure you have a clustered index on the table. I bet your time will be right where you want it. Also, have you ran this with SET STATISTICS TIME ON and a client trace to see how long this thing takes to execute vs. how long it takes to return the rows? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

also examine the length frequency distribution of your varchars. If they are consistently using up to the limit you may see a small performance improvement by changing them to fixed char fields

---

If you run Sql2k, Jboss and Tomcat on same server make sure to limit the amount of memory sql uses (and jboss and tomcat if possible) so that they don’t compete for it which could lead to performance issues.

---

Yeah for sure if you create a clustered index on DocumentID you should get your fastest performance. Bear in mind if you are removing the clustered index from another column that performance of other queries that use different columns to search will be affected. Is your query returning one row or multiple rows btw? Just wondering if your DocumentID is unique. I would definitely expect sub second performance for this query when you’re only talking about 300,000 rows. Change your index. Dave.

---

]]>