Select query takes 3 seconds to pull 330 records. Need Optimization

Question

Hi all,

I have and normal select query that take nearly 3 seconds to execute (Select * from users). There are only 310 records in the user table.

The configuration of the my production server is

SQl Server Express Editon Server Configuration : Pentium 4 HT, 3 ghz , 2 GB ram

Column Nam  Type NULL COMMENTS
Column Nam     Type   NULL      
user_companyId    int   Not  Null
user_userId    int   Not Null Primary Column
user_FirstName    nvarchar(50)  Null       
user_lastName     nvarchar(60)  Null       
user_logon     nvarchar(50)  Null       
user_password     nvarchar(255)  Null       
user_emailid      nvarchar(255)  Null       
user_status    bit   Null       
user_Notification     bit   Null       
user_role      int   Null       
user_verifyActivation    nvarchar(255)  Null       
user_verifyEmail      nvarchar(255)  Null       
user_loginattempt     smallint  Null       
user_createdby    int   Null       
user_updatedby    int   Null       
user_createddate      datetime  Null       
user_updateddate      datetime  Null       
user_Department   nvarchar(1000)  Null       
user_Designation      nvarchar(1000)  Null

Answer 1

Without the table structure to know what your table looks like, we can't answer such a question....

What about not using SELECT * FROM Users, but actually specify which fields you really need from the table??

SELECT user_companyId, user_userId,
       user_FirstName, user_lastName, user_logon
FROM Users

How does that perform?? Do you still need 3 seconds for this query, or is this significantly faster?

If you really need all the users and all their attributes, then maybe that's just the time it takes on your system to retrieve that amount of data. The best way to speed up things is to limit the attributes retrieved (do you really need the user's photo??) by specifying a list of fields, and to limit the number of elements retrieved using a WHERE clause (do you REALLY need ALL users? Not just those that.........)

Marc

Answer 2

Well, indexes don't much matter here--you're getting a table scan with that query.

So, there are only a few other items that could be bogging this down. One is row size. What columns do you have? If you have tons of text or image columns, this could be causing a delay in bringing these back.

As for your hardware, what's your HDD's RPMs? Remember, this is reading off of a disk, so if there are any other IO tasks being carried out, this will obviously cause a performance hit.

Answer 3

Is anything else happening on this machine?

Even if you made worst possible data structure, SELECT * FROM Users should not take 3 seconds for 310 records. Either there is more (a lot more) records inside or there is some problem outside of SQL server (some other process blocking code or hardware issues).

Answer 4

As there is no where clause this isn't down to indexes etc, Sql will do a full table scan and return all the data. I'd be looking at other things running on the machine, or SQL having run for a long time and used up a lot of VM. Bottom line is that this isn't a SQL issue - it's a machine issue.

Answer 5

The best thing to do is profile the server and also pay attention to what kinds of locks are occurring during the query.

If you are using any isolation level options, or the default, determine if you can lower the isolation level, which will decrease the locking occurring on the table. The more locking that occurs, the more conflicts you will have where the query has to wait for other queries to complete. However, be very careful when lowering the isolation level, because you need to understand how that will effect the data you get back.

Determine if you can add where criteria to your query. Restricting the records you query can reduce locks.

Edit: If you find locking issues, you need to also evaluate any other queries that are being run.

Answer 6

There's a number of things you should consider:

• Don't use the Express edition, it's probably called that for a reason. Go with a real DBMS (and, yes, this includes the non-express SQL Server).
• Use of "select * from" is always a bad idea unless you absolutely need every column. Change it to get only the columns you need.
• Are you using a "where" or "order by" clause. If so, you need to ensure you have indexes set up correctly (even for 330 rows, since tables always get bigger than you think).
• Use EXPLAIN, or whatever tool Microsoft provides as an equivalent. It will show you why the query is running slow.
• If your DB is on a different machine, there may be network issues (not necessarily problems, you may have stateful packet filters that slow down connections, for example).
• Examine the system loads of the boxes you're using. If there are other processes using a lot of CPU grunt or disk I/O, they may be causing slowdown.

Answer 7

If it's consistently 3 seconds, the problem isn't the query or the schema, for any reason that wouldn't be obviously irrational to the designer. I'd guess it's hardware or network issues. Is there anything you can do with the database that doesn't take 3 seconds? What do SET STATISTICS IO ON and SET STATISTICS TIME ON tell you? I bet there's nothing there that supports 3 seconds.

Answer 8

Without a better indexing strategy, leaving certain columns out will only reduce the impact on the network, which shouldn't be awfulf for only 310 rows. My guess is that it's a locking issue.

So consider using:

SELECT * FROM Users (NOLOCK);

This will mean that you don't respect any locks that are currently on the table by other connections. You may get 'dirty reads', seeing data which hasn't been committed yet - but it could be worthwhile from a performance perspective.

Let's face it - if you've considered the ramifications, then it should be fine...

Rob

Answer 9

The first thing you should do for any performance problem is to be an execution plan for the query - this is a representation of what run path SQL server chooses when it executes your query. Best place to look for info on how to do this is Google - you want a statistics plan as it includes information about how many rows are returned.

This doesn't sound like a problem with the execution plan however, as the query is so simple - in fact I'm fairly sure that query counts as a "trivial plan", i.e. there is only 1 possible plan. 

This leaves locking or hardware issues (is the query only slow on your production database, and is it always slow or does the execution time vary?) the query will attemp to get a shared lock on the whole table - if anyone is writing then you will be blocked from Reading until the writer is finished. You can check to see if this us the source of your problem by looking at the DMVs see http://sqllearningsdmvdmf.blogspot.com/2009/03/top-10-resource-wait-times.html

Finally, there are restrictions on SQL express in terms of CPU utilisation, memory use etc... What is the load on your server like? (operations per second)

Answer 10

is there a possible way that the performance may degrade based on the column size (length of the data in the column)..

in your table u got last 2 columns with the size of NVARCHAR(1000), is it always filled with that amount of data..??

am not a sql expert, but consider the packetsize its about to return for 310 records with these 2 columns & without ll be different..

i saw similar post here in stack.. u can just go through this

performance-in-sql