May 18, 2009 at 7:03 am
Dear All,
I have a table called login_user (Information contains login user) which contains 10 lakhs records.
When we use select * from login_user, it takes 10 to 15 time to retrive the data.
This table contains 28 columns having primary key and also used indexing.
It is difficult to find out what is root cause this table taking many times to retrive and execute the query.
Could you please suggest me what are the best possible way we can optimize the table so that we can improve the performance our application using this table?
Thanks a lot in advance.
Best Regards
Sumanta Panda
May 18, 2009 at 7:12 am
sk.panda (5/18/2009)
Could you please suggest me what are the best possible way we can optimize the table so that we can improve the performance our application using this table?
Good indexes that support the queries that are being run.
If you do a SELECT * FROM table, with no conditions in it, it will take a while to run. SQL has to retrieve all million (?) rows and send them over the network.
Does your app really select the entire table? If so, why? Who's going to read a million rows?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2009 at 7:37 am
I'm in agreement with Gail on this, why are you returning millions of rows?
If you really want to understand why the query is running slowly, you need to examine the execution plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2009 at 10:10 am
by my maths it's 1 million rows total.
Add a where clause to your query and index the where - you'll find it goes quicker.
If the filtering is being done in the app ( hence the selct * ) then sack your developer(s) who wrote the code. I do actually struggle a bit to imagine a system with 1 million users where simple questions are being posted on the forum - worrying!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 19, 2009 at 4:26 am
Hi All,
We are not retriving the query without condition in the WHERE Clause.But i am facing problem in some of the query i am using this table.It takes many time.
Please suggest.
Thanks!!!
Sumanta Panda
May 19, 2009 at 5:29 am
Then make sure that you have good indexes that support the queries that are being run. If you're not sure what constitutes a good index for a query, post the query, table def and index def and we can help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2009 at 6:15 am
Out of date statistics will seriously affect the query and might mean it does lots of I/O and doesn't consider indexes.
To fix either
(1) Turn on auto create and auto update statistics
(2) run sp_updatestatistics
(3) Issue an UPDATE STATISTICS command
More detail on all of these is books online.
You have another thread going that may be the same fault, this will be posted there as well.
Tim
.
May 19, 2009 at 8:57 am
As Gail mentioned please supply more information
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2009 at 1:26 am
Hey Colin, its all very well saying "sack your developer(s)" but what kind of database professional is gonna quite happily send a million rows across a network no questions asked?
I wouldn't necessarily absolve the developers of any responsibility, however, if the database guys aren't familiar with a WHERE clause then perhaps the problem is a bit closer to home.
May 20, 2009 at 1:50 pm
Basically, you need to find out WHAT cause the slow query response first. In order to do that you need:
- Run Profiler or manual trace so you could see execution plan and CPU, reads, writes, duration statistics during query execution;
- Run the query manually or place a filter to get results on that particular query;
- Add that information to the question here if you can’t interpret it yourself.
Major problem that you can see from the trace:
- High CPU time (over 5ms) means that the logic of the query needs adjustment;
- High ## of reads (over 10) means that query is not using any index (either there is no index or statistics is not up to date);
- High duration usually represents problems with query (for example, usage of SELECT DISTINCT).
Execution plan XML or text will give you better ideas of what is going on in the query: is there any table/index scans vs. index seek, is there loops, and so on.
There is no way anybody can suggest any improvement without seeing actual query code and/or knowing what data you need to get. A lot of problems are not in the single table because we are talking about relational database thus at least one more table should be involved in the query.
Network and other hardware related problems can be reviewed after query analysis.
Alex Prusakov
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply